Populate VBA Array with variable values

Question

sorry I haven’t found the way to solve this issue. When trying to define an array in this way

Dim Arr As Variant: Arr = [{"Value1", "Value2"}]

works perfectly, but when trying to do the same using variables (i.e.)

Dim Arr as variant
v1 = "Value1"
v2 = "Value2"
Arr = [{v1, v2}]

will throw a syntax error. Please is there some way to solve it? I do need to use variables to populate the array. Thanks in advance

square brackets in VBA is shorthand for `Application.Evaluate()` which means anything in the square brackets will be treated as **literal** text and evaluated as if it was entered into the formula bar. That's why you cannot use variables - initialise it as a proper `Array` object instead. — SierraOscar, Apr 25 '19 at 16:29

score 3 · Answer 1 · answered Apr 25 '19 at 16:28

Arr = [{"Value1", "Value2"}]

is

Arr = Application.Evaluate("{""Value1"", ""Value2""}")

You are making the Excel formula engine to parse the expression, and it parses it as an array constant.
Obviously the formula engine knows nothing about VBA variables.

You should simply use

Arr = Array(v1, v2)

Populate VBA Array with variable values

1 Answers1