Declare a 0-Length String Array in VBA - Impossible?

Question

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.

Just to be clear, you want an array with one item in it? Or do you mean you want an empty array? Arrays in VBA are 0-based, so a "0 length" array is technically an array with one value in it at position 0. — TylerH, Mar 12 '19 at 14:15
IIRC just declaring the array without a specified size will keep it "empty", though trying to access that without applying a redim to it before will cause an error (i.e. declared like `Dim test() As String`) — bassfader, Mar 12 '19 at 14:18
I suppose `Dim results As Variant` is out of the question? Passing typed arrays around is always a pain in the neck, I've long decided to pick my battles and use `Variant` for any kind of array. Is the variant subtype `String` in the variant array? If so, you're doing processing that doesn't need to be happening. — Mathieu Guindon, Mar 12 '19 at 14:29
@bassfader yeah I've thought of that. But that won't help. Because I want other code to loop through the array of strings- looping 0 times i.e. doing nothing in the case of an empty array. Like you said, it would throw an error in that case, so it's a dead end. — Colm Bhandal, Mar 12 '19 at 14:54
@MathieuGuindon Yeah I've thought of relaxing the type too. And I've done that in other places. But it violates the whole ethos of a type system. Which is why I've written this conversion function. — Colm Bhandal, Mar 12 '19 at 14:57
@TylerH I want an array with no elements in it. I.e. 0 elements. My hack puts in one element, which is an empty string, but that's not really what I want. I want a truly empty array. — Colm Bhandal, Mar 12 '19 at 14:59
@Nathan_Sav in your suggestion you are declaring a variant array. I need a string array. The entire use case is based on conversion to a string array from a variant array, so even if that works, it's not much use to me. — Colm Bhandal, Mar 12 '19 at 15:00
It does (`Variant` is to VBA such as `dynamic` is to C#) - but then again the type system isn't particularly "safe" either, and when you need to outright fight it (as is the case for typed arrays), I think it's better to take a step back and consider the options -- an uninitialized `Variant` will be `Empty`, which can be conveniently verified with `IsEmpty`; once initialized with an array, `IsArray` tells us about the array subtype. I've come to use `Variant` whenever I need an array, and feel that I'm pretty much *only* using `Variant` for pluralilzed variables that hold... arrays. — Mathieu Guindon, Mar 12 '19 at 15:01
@MathieuGuindon Yeah I understand that point of view. A few replies I'd have: safety isn't the only advantage of stronger types. There's readability too- a user knows what to expect from a function when they see it's type. So if they see string() they'll be able to confidently use the elements as strings, trusting that I've done my job as a developer and converted from variants behind the scenes. Without this, we'd be relying on comments/context/code-inspection/method name/other usages to figure out what the type should be. Which is extra work for a user. So it really is a trade-off... — Colm Bhandal, Mar 12 '19 at 15:08
@Nathan_Sav your other suggestion does not work- setting the function return value to vbnullstring. The return type of the function must be an array of strings, not a single string. — Colm Bhandal, Mar 12 '19 at 15:11
It is. Speaking of readability, I see you're using the long-deprecated and objectively harmful [Systems Hungarian](https://www.joelonsoftware.com/2005/05/11/making-wrong-code-look-wrong/) notation. Why are you putting in all these prefixes if they aren't helping in situations like this? IMO if *anything* warrants name-prefixing, it's something that identifies a `Variant` as an array. — Mathieu Guindon, Mar 12 '19 at 15:13
@ColmBhandal Well, set something like `arrOutput(0)=vbnullstring` — Nathan_Sav, Mar 12 '19 at 15:15
@MathieuGuindon Good question, though we digress. We decided to name variables in this way to avoid what we called "casing noise". We are putting a large number of VBA files under version control and Excel's VBA editor has the charming property that whenever you Dim a variable anywhere it renames all occurrences of that string everywhere to match the new case: https://stackoverflow.com/questions/248760/vb6-editor-changing-case-of-variable-names. A nightmare for Git version control. The Hungarian notation helps us here. We certainly won't be using it when we move to C#. — Colm Bhandal, Mar 12 '19 at 15:20
@Nathan_Sav I'm not sure what you're suggesting. It doesn't look like it will achieve what the question asks. Please elaborate in an answer if you have found a solution. — Colm Bhandal, Mar 12 '19 at 15:21
`arrOutput = Split(vbNullString)` gives an array with an LBound of 0 and UBound of -1. That's as close as it gets without memory hacks. — Comintern, Mar 12 '19 at 15:21
^^ what @Comintern said. Branch your function to check for the existence of keys, and return `Split(vbNullString)` if there are none, otherwise proceed to redim and convert each element. Actually, I'd make a function and call it `EmptyStringArray` that returns just that. — Mathieu Guindon, Mar 12 '19 at 15:26

Comintern · Accepted Answer · 2019-03-12T16:37:42.060

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

Did you actually mean the `SafeArrayRedim` to be inside the `Not (vtype And VT_BY_REF)` check? — GSerg, Mar 12 '19 at 16:49
You probably can simplify this code substantially by accessing the pointer to the array using `Public Declare PtrSafe Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" (ByRef Ptr() As Any) As LongPtr` instead of manually copying memory, and taking a string array as a parameter for the `EmptyArray` function — Erik A, Mar 12 '19 at 16:54
@ErikA That won't work for arrays of e.g. strings or UDTs because VB converts strings before passing them to `Declare`d functions, so that `VarPtrArray` will return you the address of the useless temporary converted copy. — GSerg, Mar 12 '19 at 16:58
Also, the third parameter to `RtlMoveMemory` is `SIZE_T`, which is `LongPtr`. `SafeArrayRedim` returns a `Long` and should also be `PtrSafe`. — GSerg, Mar 12 '19 at 17:55

score 4 · Answer 2 · answered Mar 12 '19 at 15:48

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.

Amusingly, if the OP did go with the untyped option as suggested in the comments, that would be simply calling `Array()` without arguments. — GSerg, Mar 12 '19 at 16:50

Erik A · Answer 3 · 2019-03-12T16:55:51.807

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

Won't this leak the memory for the array? I thought arrays created with `SafeArrayCreate` needed to be freed with `SafeArrayDestroy`. — Comintern, Mar 12 '19 at 16:41
@Comintern You were 100% right, s did contain a copy of the array and the original 0-length array was not being destroyed. Adjusted now — Erik A, Mar 12 '19 at 16:49
I'm pretty sure `SafeArrayDestroy` is how VB kills the arrays it created "normally", so there should be no difference, it should call that upon detecting that the array variable has a non-zero pointer. But it is important to call `SafeArrayDestroy` if you create an array that [refers to a portion of an existing array](https://stackoverflow.com/q/11713408/11683). — GSerg, Mar 12 '19 at 17:03

Mark E. · Answer 4 · 2023-01-05T09:44:27.263

SafeArrayCreateVector

One other option, mentioned in answers elsewhere,^{1 2 3} is with SafeArrayCreateVector. While SafeArrayCreate returns a pointer as shown by Erik A, this one returns an array directly. You'd need a declaration for each type, like this:

 Private Declare PtrSafe Function VectorBoolean Lib "oleaut32" Alias "SafeArrayCreateVector" ( _
 Optional ByVal vt As VbVarType = vbBoolean, Optional ByVal lLow As Long = 0, Optional ByVal lCount As Long = 0) _
 As Boolean()
 
 Private Declare PtrSafe Function VectorByte Lib "oleaut32" Alias "SafeArrayCreateVector" ( _
 Optional ByVal vt As VbVarType = vbByte, Optional ByVal lLow As Long = 0, Optional ByVal lCount As Long = 0) _
 As Byte()

The same works for Currency, Date, Double, Integer, Long, LongLong, Object, Single, String and Variant.

If you're willing to stuff those into a module, you can create a function that works just like Array() but with an initial argument that sets the type:

Function ArrayTyped(vt As VbVarType, ParamArray argList()) As Variant
    
    Dim ub As Long: ub = UBound(argList) + 1
    Dim ret As Variant 'a variant to hold the array to be returned

    Select Case vt
        Case vbBoolean: Dim bln() As Boolean: bln = VectorBoolean(, , ub): ret = bln
        Case vbByte: Dim byt() As Byte: byt = VectorByte(, , ub): ret = byt
        Case vbCurrency: Dim cur() As Currency: cur = VectorCurrency(, , ub): ret = cur
        Case vbDate: Dim dat() As Date: dat = VectorDate(, , ub): ret = dat
        Case vbDouble: Dim dbl() As Double: dbl = VectorDouble(, , ub): ret = dbl
        Case vbInteger: Dim i() As Integer: i = VectorInteger(, , ub): ret = i
        Case vbLong: Dim lng() As Long: lng = VectorLong(, , ub): ret = lng
        Case vbLongLong: Dim ll() As LongLong: ll = VectorLongLong(, , ub): ret = ll
        Case vbObject: Dim obj() As Object: obj = VectorObject(, , ub): ret = obj
        Case vbSingle: Dim sng() As Single: sng = VectorSingle(, , ub): ret = sng
        Case vbString: Dim str() As String: str = VectorString(, , ub): ret = str
    End Select
    
    Dim argIndex As Long
    For argIndex = 0 To ub - 1
        ret(argIndex) = argList(argIndex)
    Next
    
    ArrayTyped = ret
    
End Function

This gives empty or filled arrays, like Array(). For example:

Dim myLongs() as Long
myLongs = ArrayTyped(vbLong, 1,2,3) '<-- populated Long(0,2)

Dim Pinnochio() as String
Pinnochio = ArrayTyped(vbString) '<-- empty String(0,-1)

Same ArrayTyped() Function With SafeArrayRedim

I like this function, but all those API calls for each type seem bloated. It seems the same function can be done with SafeArrayRedim, and just one API call. Declared as such:

Private Declare PtrSafe Function PtrRedim Lib "oleaut32" Alias "SafeArrayRedim" (ByVal arr As LongPtr, ByRef dims As Any) As Long

The same ArrayTyped function could then look like this:

Function ArrayTyped(vt As VbVarType, ParamArray argList()) As Variant
    
    Dim ub As Long: ub = UBound(argList) + 1
    Dim ret As Variant 'a variant to hold the array to be returned
    
    Select Case vt
        Case vbBoolean: Dim bln() As Boolean: ReDim bln(0): PtrRedim Not Not bln, ub: ret = bln
        Case vbByte: Dim byt() As Byte: ReDim byt(0): PtrRedim Not Not byt, ub: ret = byt
        Case vbCurrency: Dim cur() As Currency: ReDim cur(0): PtrRedim Not Not cur, ub: ret = cur
        Case vbDate: Dim dat() As Date: ReDim dat(0): PtrRedim Not Not dat, ub: ret = dat
        Case vbDouble: Dim dbl() As Double: ReDim dbl(0): PtrRedim Not Not dbl, ub: ret = dbl
        Case vbInteger: Dim i() As Integer: ReDim i(0): PtrRedim Not Not i, ub: ret = i
        Case vbLong: Dim lng() As Long: ReDim lng(0): PtrRedim Not Not lng, ub: ret = lng
        Case vbLongLong: Dim ll() As LongLong: ReDim ll(0): PtrRedim Not Not ll, ub: ret = ll
        Case vbObject: Dim obj() As Object: ReDim obj(0): PtrRedim Not Not obj, ub: ret = obj
        Case vbSingle: Dim sng() As Single: ReDim sng(0): PtrRedim Not Not sng, ub: ret = sng
        Case vbString: Dim str() As String: ReDim str(0): PtrRedim Not Not str, ub: ret = str
        Case vbVariant: Dim var() As Variant: ReDim var(0): PtrRedim Not Not var, ub: ret = var
    End Select
    
    Dim argIndex As Long
    For argIndex = 0 To ub - 1
        ret(argIndex) = argList(argIndex)
    Next
    
    ArrayTyped = ret
    
End Function

A couple of other resources:

Following logic here you can also do this with user defined types. Just add another API call like the others. More discussion here.
If anyone wants empties with multiple dimensions, there is another interesting approach using SafeArrayCreate here.

Paco Verschut · Answer 5 · 2022-07-26T23:13:33.740

What you can do is declare a variable length array of whatever type you need by declaring it with no specified length in the Dim. Then call IsArray with the array variable. It will return True but we're not interested in that, this just initialises the array.

Dim lStringArr() As String

' Call IsArray to initialise the array
IsArray lStringArr

' Print the amount of elements in the array to the Immediate window
Debug.Print UBound(lStringArr) - LBound(lStringArr) + 1
' It should print 0 without errors

score 0 · Answer 6 · answered Aug 24 '23 at 13:05

0

Seems simelar to How to create an allocated but zero-length array that are for-loopable?

Dim lStringArr As String() = {}

creates an Array with length 0 that is not Nothing.

answered Aug 24 '23 at 13:05

Johann Wiengold

45
6

Declare a 0-Length String Array in VBA - Impossible?

Use Case

Hack

Update since answer

Notes

6 Answers6

Linked