Is it really not possible to declare a 0-length array in VBA? If I try this:
Dim lStringArr(-1) As String
I get a compile error saying range has no values. If I try to trick the compiler and redim at runtime like this:
ReDim lStringArr(-1)
I get a subscript out of range error.
I've varied the above around a bit but with no luck e.g.
Dim lStringArr(0 To -1) As String
Use Case
I want to convert a variant array to a string array. The variant array may be empty as it comes from the Keys property of a dictionary. The keys property gives back an array of variants. I want an array of strings to use in my code, as I have some functions for processing string arrays I'd like to use. Here's the conversion function I'm using. This throws a subscript out of range error due to lMaxIndex being = -1:
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
ReDim lStringArr(lMaxIndex)
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
Hack
Return a singleton array containing an empty string. Note- this isn't what we want. We want an empty array- such that looping over it is like doing nothing. But a singleton array containing an empty string will often work e.g. if we later want to join all the strings together in the string array.
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
If lMaxIndex < 0 Then
ReDim lStringArr(1)
lStringArr(1) = ""
Else
ReDim lStringArr(lMaxIndex)
End If
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
Update since answer
Here is the function I'm using for converting a variant array to a string array. Comintern's solution seems more advanced and general, and I may switch to that one day if I'm still stuck coding in VBA:
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
If lMaxIndex < 0 Then
mVariantArrayToStringArray = Split(vbNullString)
Else
ReDim lStringArr(lMaxIndex)
End If
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
Notes
- I use Option Explicit. This can't change as it safeguards the rest of the code in the module.
As noted in the comments, you can do this "natively" by calling Split on a vbNullString, as documented here:
expression - Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
If you need a more general solution (i.e., other data types, you can call the SafeArrayRedim function in oleaut32.dll directly and request that it re-dimensions the passed array to 0 elements. You do have to jump through a couple of hoops to get the base address of the array (this is due to a quirk of the VarPtr function).
In the module declarations section:
'Headers
Private Type SafeBound
cElements As Long
lLbound As Long
End Type
Private Const VT_BY_REF = &H4000&
Private Const PVDATA_OFFSET = 8
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any, _
ByVal length As Long)
Private Declare Sub SafeArrayRedim Lib "oleaut32" (ByVal psa As LongPtr, _
ByRef rgsabound As SafeBound)
The procedure - pass it an initialized array (any type) and it will remove all elements from it:
Private Sub EmptyArray(ByRef vbArray As Variant)
Dim vtype As Integer
CopyMemory vtype, vbArray, LenB(vtype)
Dim lp As LongPtr
CopyMemory lp, ByVal VarPtr(vbArray) + PVDATA_OFFSET, LenB(lp)
If Not (vtype And VT_BY_REF) Then
CopyMemory lp, ByVal lp, LenB(lp)
Dim bound As SafeBound
SafeArrayRedim lp, bound
End If
End Sub
Sample usage:
Private Sub Testing()
Dim test() As Long
ReDim test(0)
EmptyArray test
Debug.Print LBound(test) '0
Debug.Print UBound(test) '-1
End Sub
Per Comintern's comment.
Make a dedicated utility function that returns the result of the VBA.Strings.Split function, working off vbNullString, which is effectively a null string pointer, which makes the intent more explicit than using an empty string literal "", which would also work:
Public Function EmptyStringArray() As String()
EmptyStringArray = VBA.Strings.Split(vbNullString)
End Function
Now branch your function to check for the existence of keys, and return EmptyStringArray if there are none, otherwise proceed to resize your result array and convert each source element.
If we're going to use WinAPI anyway, we can also cleanly create the array from scratch using the WinAPI SafeArrayCreate function instead of redimensioning it.
Struct declarations:
Public Type SAFEARRAYBOUND
cElements As Long
lLbound As Long
End Type
Public Type tagVariant
vt As Integer
wReserved1 As Integer
wReserved2 As Integer
wReserved3 As Integer
pSomething As LongPtr
End Type
WinAPI declarations:
Public Declare PtrSafe Function SafeArrayCreate Lib "OleAut32.dll" (ByVal vt As Integer, ByVal cDims As Long, ByRef rgsabound As SAFEARRAYBOUND) As LongPtr
Public Declare PtrSafe Sub VariantCopy Lib "OleAut32.dll" (pvargDest As Any, pvargSrc As Any)
Public Declare PtrSafe Sub SafeArrayDestroy Lib "OleAut32.dll"(ByVal psa As LongPtr)
Use it:
Public Sub Test()
Dim bounds As SAFEARRAYBOUND 'Defaults to lower bound 0, 0 items
Dim NewArrayPointer As LongPtr 'Pointer to hold unmanaged string array
NewArrayPointer = SafeArrayCreate(vbString, 1, bounds)
Dim tagVar As tagVariant 'Unmanaged variant we can manually manipulate
tagVar.vt = vbArray + vbString 'Holds a string array
tagVar.pSomething = NewArrayPointer 'Make variant point to the new string array
Dim v As Variant 'Actual variant
VariantCopy v, ByVal tagVar 'Copy unmanaged variant to managed one
Dim s() As String 'Managed string array
s = v 'Copy the array from the variant
SafeArrayDestroy NewArrayPointer 'Destroy the unmanaged SafeArray, leaving the managed one
Debug.Print LBound(s); UBound(s) 'Prove the dimensions are 0 and -1
End Sub
