VBA - Allocating multidimensional array directly using brackets

Question

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!

Bobort · Accepted Answer · 2015-11-24 14:14:09Z

2

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.

edited Nov 24, 2015 at 14:14

answered Sep 22, 2014 at 13:33

Bobort

3,23835 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bjørn H. Sandvik Over a year ago

Well played, my friend.. Well played.

Alex K. · Accepted Answer · 2014-09-21 15:15:55Z

2

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

answered Sep 21, 2014 at 15:15

Alex K.

177k32 gold badges276 silver badges299 bronze badges

1 Comment

Bjørn H. Sandvik Over a year ago

Thank you for the clarification. I settled on writing a function using ParamArray to assemble the array.. I'm no fan of using Application methods.

Collectives™ on Stack Overflow

VBA - Allocating multidimensional array directly using brackets

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related