Search code examples
arraysexcelvbamultidimensional-arrayis-empty

Create an empty 2d array

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:

locals window

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

Solution

  • 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.