Create an empty 2d array

Question

I don't like uninitialized VBA arrays, since it's necessary to check if array is initialized, each time prior using UBound() or For Each to avoid an exception, and there is no native VBA function to check it. That is why I initialize arrays, at least doing them empty with a = Array(). This eliminates the need for extra check in most of cases, so there are no problems with 1d arrays.

For the same reason I tried to create an empty 2d array. It's not possible simply do ReDim a(0 To -1, 0 To 0), transpose 1d empty array or something similar. The only way I came across by chance, is to use MSForms.ComboBox, assign empty array to .List property and read it back. Here is the example, which works in Excel and Word, you need to insert UserForm to VBA Project, place ComboBox on it, and add the below code:

Private Sub ComboBox1_Change()

    Dim a()
    ComboBox1.List = Array()
    a = ComboBox1.List
    Debug.Print "1st dimension upper bound = " & UBound(a, 1)
    Debug.Print "2nd dimension upper bound = " & UBound(a, 2)

End Sub

After combo change the output is:

1st dimension upper bound = -1
2nd dimension upper bound = 0

Actually it's really the empty 2d array in debug:

Is there more elegant way to create an empty 2d array, without using ComboBox, or UserForm controls in general?

Answer 1

This is only going to work for Windows (not for Mac):

Option Explicit

#If Mac Then
#Else
    #If VBA7 Then
        Private Declare PtrSafe Function SafeArrayCreate Lib "OleAut32.dll" (ByVal vt As Integer, ByVal cDims As Long, ByRef rgsabound As SAFEARRAYBOUND) As LongPtr
        Private Declare PtrSafe Function VariantCopy Lib "OleAut32.dll" (pvargDest As Any, pvargSrc As Any) As Long
        Private Declare PtrSafe Function SafeArrayDestroy Lib "OleAut32.dll" (ByVal psa As LongPtr) As Long
    #Else
        Private Declare Function SafeArrayCreate Lib "OleAut32.dll" (ByVal vt As Integer, ByVal cDims As Long, ByRef rgsabound As SAFEARRAYBOUND) As Long
        Private Declare Function VariantCopy Lib "OleAut32.dll" (pvargDest As Variant, pvargSrc As Any) As Long
        Private Declare Function SafeArrayDestroy Lib "OleAut32.dll" (ByVal psa As Long) As Long
    #End If
#End If

Private Type SAFEARRAYBOUND
    cElements As Long
    lLbound As Long
End Type
Private Type tagVariant
    vt As Integer
    wReserved1 As Integer
    wReserved2 As Integer
    wReserved3 As Integer
    #If VBA7 Then
        ptr As LongPtr
    #Else
        ptr As Long
    #End If
End Type

Public Function EmptyArray(ByVal numberOfDimensions As Long, ByVal vType As VbVarType) As Variant
    'In Visual Basic, you can declare arrays with up to 60 dimensions
    Const MAX_DIMENSION As Long = 60
    
    If numberOfDimensions < 1 Or numberOfDimensions > MAX_DIMENSION Then
        Err.Raise 5, "EmptyArray", "Invalid number of dimensions"
    End If

    #If Mac Then
        Err.Raise 298, "EmptyArray", "OleAut32.dll required"
    #Else
        Dim bounds() As SAFEARRAYBOUND
        #If VBA7 Then
            Dim ptrArray As LongPtr
        #Else
            Dim ptrArray As Long
        #End If
        Dim tVariant As tagVariant
        Dim i As Long
        '
        ReDim bounds(0 To numberOfDimensions - 1)
        '
        'Make lower dimensions [0 to 0] instead of [0 to -1]
        For i = 1 To numberOfDimensions - 1
            bounds(i).cElements = 1
        Next i
        '
        'Create empty array and store pointer
        ptrArray = SafeArrayCreate(vType, numberOfDimensions, bounds(0))
        '
        'Create a Variant pointing to the array
        tVariant.vt = vbArray + vType
        tVariant.ptr = ptrArray
        '
        'Copy result
        VariantCopy EmptyArray, tVariant
        '
        'Clean-up
        SafeArrayDestroy ptrArray
    #End If
End Function

You can now create empty arrays with different number of dimensions and data types:

Sub Test()
    Dim arr2D() As Variant
    Dim arr4D() As Double
    '
    arr2D = EmptyArray(2, vbVariant)
    arr4D = EmptyArray(4, vbDouble)
    Stop
End Sub

Update 30/09/2022

I've created an EmptyArray method (same signature) in my MemoryTools library on GitHub. That version will work on both Windows and Mac.

Answer 2

Idk man - I think you stumbling onto this property was pretty wild.

I'd probably stop here and just do:

Function Empty2DArray() As Variant
With CreateObject("Forms.ComboBox.1")
    .List = Array()
    Empty2DArray = .List
End With
End Function

And use it like: a = Empty2DArray

You don't need to create the userform or combobox - you can just use CreateObject.

But as others have said, it probably makes more sense to do error handling when checking whether or not your arrays are initialized.