Arrays 101.02: Creating Arrays in VBA

Introduction

In Arrays 101.01 the discussion was about arrays in general.  In this post, the subject is how to create arrays using VBA.  Later posts will get into how to add data to arrays.

What does an array look like in VBA

You can identify an array variable  in VBA by the parentheses in the Dim statement.  The parenthesis surround the dimension(s) of the array.  (See Arrays 101.01 for more on array dimensions) The following are declarations for arrays

• Dim arr1(1 to 5) as <data type>
• Dim arr2(5) as <data type>
• Dim arr3() as <data type> 

Variants (e.g. Dim ABC1 or Dim ABC2 as Variant) may become arrays, depending on how they are treated in the following code

• Array: ABC1= Array(1,2) 
• Not Array: ABC2= 12

What is not a VBA array

As implied above, constants cannot be declared as arrays.

Also, any variable declared of a specific datatype other than Variant and without the parentheses cannot be an array. The following are not arrays and cannot be made into arrays

• Dim i as Integer
• Dim str as String
• Dim rng as Range

Dimensioning Arrays

Before using and array,  the upper and lower limits of each dimension must be known. "Dimensioning" is specifying the number of elements in an array.  (See Why "Dim"?)  below are the valid Dim statements for arrays from above, and a few more.

Dim arr1(1 to 5)  As <data type>

This declares a one dimensional array with an index that runs from 1 to 5, inclusive.  

NOTE: The first number must be less than or equal to the second

Dim arr2(5)  As <data type>

This array also has an upper limit on its index of 5, but the lower limit is either 0 or 1.  This is because VBA, like many other languages, consider the index to be pointing at the offset from the beginning of a memory range rather then the memory range itself.  Therefore the default would be for a lower limit of 0.

However, if the module has Option Base 1 at the top, before the first Sub or Function, the lower limit would be 1.

NOTE:  The only options are 1 or 0.  One cannot declare Option base 2

Compiler Error for illegal Option Base

Dim arr3(2,5,2) As <data type>

This is a 3 dimensional array.  Assuming Option Base 0, it has 3 levels in the first dimension, 6 in the second, and 3 in the third for a total of 3 * 6 * 3 = 48 data points.

Dim arr3(-2 To 2, 2 To 10) As <data type>

This is a 2 dimensional array.  Assuming Option Base 0, it has 3 levels in the first dimension, 6 in the second, and 3 in the third for a total of 5 * 9 = 45 data points.

Dim arr4() As <data type>

This is an undimensioned array.  It requires a ReDim command to make it useful, e.g. ReDim arr4(1 To 4) creates a 4 member array.