CODEWITHSUNDEEP
vbaexcelmultidimensional-array
Bjørn H. Sandvik
Bjørn H. Sandvik

Reputation: 543

VBA - Allocating multidimensional array directly using brackets

I am trying to find a practical way of directly allocating values to a multidimensional array in VBA without iterating. I Googled a solution which kind of works, but it fails when I try to use it in combination with variables..

This works:

Sub SomeSub()
    Dim vArray As Variant
    Dim iCounter As Integer
    vArray = [{"Zip", "22150";"City", "Springfield"; "State", "VA"}]

    For iCounter = LBound(vArray, 1) To UBound(vArray, 1)
        Debug.Print vArray(iCounter, 1), vArray(iCounter, 2)
    Next iCounter
End Sub

This, however, does not, and raises a "Type mismatch" error instead. The difference is that I try (and wish) to use variables instead of constant values:

Sub SomeSub()
    Dim vArray As Variant

    Dim iZip As Integer
    Dim sCity As String
    Dim sState As String

    iZip = 22150
    sCity = "Springfield"
    sState = "VA"

    Dim iCounter As Integer
    vArray = [{"Zip", iZip;"City", sCity; "State", sState}]

    For iCounter = LBound(vArray, 1) To UBound(vArray, 1)
        Debug.Print vArray(iCounter, 1), vArray(iCounter, 2)
    Next iCounter
End Sub

I find little or no information on this method for allocating arrays, so I'm hoping that someone has some insight to offer.

Thanks!

Upvotes: 2

Views: 2791

Answers (2)

Bobort
Bobort

Reputation: 3218

VBA also has a built-in function called Array. You can use Array within the Array function to make it "multidimensional."

Sub SomeSub()
    Dim vArray As Variant

    Dim iZip As Integer
    Dim sCity As String
    Dim sState As String

    iZip = 22150
    sCity = "Springfield"
    sState = "VA"
    vArray = Array(Array("Zip", iZip), Array("City", sCity), Array("State", sState))    

    Dim iCounter As Integer

    For iCounter = LBound(vArray, 1) To UBound(vArray, 1)
        Debug.Print vArray(iCounter, 1), vArray(iCounter, 2)
    Next iCounter
End Sub

Though the above code does answer the OP, I am editing this answer with more information. Technically, this is not "multidimensional." Rather, it is "jagged." It's a Variant of a single-dimensional array of single-dimensional arrays. In VBA, when you define a truly multidimensional array, you would use this syntax:

Sub someSub()
    Dim vArray(5, 5) As Long
    vArray(0, 1) = 5
    vArray(0, 2) = 3
    vArray(1, 3) = 4
    Debug.Print vArray(1, 3)   'Prints 4
End Sub

Another Stackflow user suggested this technical correction, and I wanted to incorporate that knowledge into the answer.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175826

The [...] syntax is a shortcut for Application.Evaluate() and as you have discovered it only works for runtime constant expressions, you can use a real call to Application.Evaluate() passing a dynamc string, but (*imo) thats quite hacky.

Alternatives:

  • Create a factory function that accepts a paramarray() of arguments, step through it using a modulus counter to figure out what dimension to allocate to.

  • As the first dimension appears to be a fixed string use a User Defined Type to allow udt_var.City = "XXX" / udt_var.Zip = 12345 ...

  • Use a Collection (or Dictionary) which unlike the above is iterable (the latter by key).

Upvotes: 2

Related Questions