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?

I don't know the answer, but I did find a nifty way to crash excel. I'm wondering if this affects others. Try merely writing the following line in a subroutine (save your work first): a=Array();redim a(,0 to 0) — JNevill
– JNevill, Commented Jan 2, 2020 at 18:51
I think this is an XY problem. What you should be asking is how do I easily check if an array is initialised? . A good solution is provided on CPearsom.com here — chris neilsen
– chris neilsen, Commented Jan 2, 2020 at 21:01
I must admit the question itself might sound quite broad, so it easily may be treated as XY broblem. Note, I already posted the link how to check if array is initialized, IMO OERN-like methods must be the last resort, and the only excuse to use them is the significant simplicity and performance against others. And the real point is about to change 2d array handling pattern and get rid of extra check. You know, "If at first the idea is not absurd, then there is no hope for it." — omegastripes
– omegastripes, Commented Jan 2, 2020 at 21:47

Cristian Buse · Accepted Answer · 2022-09-30 13:49:30Z

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.

user1274820 · Accepted Answer · 2020-01-02 21:06:23Z

3

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.

edited Jan 2, 2020 at 21:06

answered Jan 2, 2020 at 20:58

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Collectives™ on Stack Overflow

Create an empty 2d array

2 Answers 2

Update 30/09/2022

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Update 30/09/2022

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related