VBA Multi-Dimensional Arrays - Array Literal Syntax

Question

I want to create a multi-dimensional array where I assign all the values at once instead of going through all the array coordinate values one by one. I believe this is called setting 'array literals'. Anyway, all my variables are string values. The code below doesn't give me a syntax error but when I step through I'm getting a "Compile error: Can't assign to array" message on pkg= line. How do I make this work?

Sub test_array2()

Dim pkg(2, 2) As String

pkg = [{"PRetail","Retail Packaged"};{"PFoodservice","Foodservice 
Packaged"}]

Debug.Print pkg(1, 1)

End Sub

Just a warning about doing this this way - the "identifier" (i.e. the part within the [ ... ]) is limited to 255 characters. You get a "Identifier too long" syntax error if you exceed the limit. — YowE3K
– YowE3K, Commented Dec 21, 2017 at 21:58

Tim Williams · Accepted Answer · 2017-12-21 21:46:41Z

5

You cannot assign directly to an array like that, so you need to use a variant:

Sub test_array2()

    Dim pkg As Variant

    pkg = [{"PRetail","Retail Packaged";"PFoodservice","FoodservicePackaged "}]

    Debug.Print pkg(1, 1)

End Sub

answered Dec 21, 2017 at 21:46

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

GSerg Over a year ago

You can assign to a non-variant array, just not a fixed size one like Dim pkg(2, 2). In this case the array also has to contain Variants due to the return type of the [], so the correct declaration would be Dim pkg() As Variant, at which point the difference with Dim pkg As Variant is a technicality.

Collectives™ on Stack Overflow

VBA Multi-Dimensional Arrays - Array Literal Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related