Top Menu

Tag Archives | arrays

Initialising Arrays in VBA

I was asked today if it was possible to initialise an array in VBA using a single line statement. The answer is actually no not really although there is a trick that can!

In terms of static arrays, you can’t. The only way is to either set each element separately if each element is a specific value, or if each element initially holds the same value then use a loop to assign that value to each element in turn.

There is a trick however by using a variant variable as the array. As we know a variant holds any type of data. So the following code would create a variant that holds an array of 5 values starting at 5 and increasing by 5. The final line would output a value of 20 in the MsgBox as the array will always be zero-based.

Dim varMyArray As Variant
varMyArray = Array(5,10,15,20,25)
Msgbox varMyArray(3)

 

This code doesn’t fix the array to 6 elements as the Variant variable varMyArray could be changed in size by more code. The following code displays 20 in the message and then in the next MsgBox displays 9.

Dim varMyArray As Variant
varMyArray = Array(5,10,15,20,25)
Msgbox varMyArray(3)
varMyArray = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)    
MsgBox varMyArray(9)

 

And because we are working with a Variant data type, it can hold anything including mixed data types. So the following code works too, with the MsgBox displaying Page.

Dim varMyArray As Variant
varMyArray = Array(5,"Chris",15,"Page",25)
Msgbox varMyArray(3)