An Array is a collection of items “packaged” into a single variable; in VBA and many other programming languages, Arrays are commonly used structures.
Defining an Array in VBA
The months of the year provide a good example. If we needed to store information for each month without using Arrays, we would need twelve (12) separate variables, one for each month. To simplify, we could create an array with 12 elements, one for each month. There are several ways to declare arrays in VBA:
| Option 1 |
Dim Month(1 To 12) As Integer |
| Option 2 |
Dim Month(1,12) |
| Option 3 |
Dim Month(11) |
| Option 4 |
Option Base 1
Dim Month(12) |
The first two options are the most intuitive: the first option creates an array with 12 elements, from element 1 to 12. The second option uses a matrix structure, which may feel familiar. You can also check out our tutorial on Loops in VBA, as well as tips and shortcuts for Selecting a Cell or Finding the Last Occupied Cell.
Arrays in VBA are similar to arrays in other programming languages where the default base is zero. Therefore, the array described in option 3 starts from 0 and goes up to 11, meaning it has 12 elements. However, to make the process more intuitive, VBA allows you to change the default base using the Option Base 1 statement, as shown in option 4. It is recommended to place the Option Base 1 declaration at the beginning of each module.
Array Sizing
Unlike variables, whose declaration is optional (you can read more about this in our Variables in VBA tutorial), the size of an array must be declared before storing any elements in it.
The examples shown so far are one-dimensional arrays, for simplicity. However, it’s important to note that Arrays can have up to 60 dimensions or more.
Let’s look at a three-dimensional example: if you want to store monthly working hours data for each employee based on their shift, a smart approach would be using an array. To size the array, we need to know the maximum values each variable could take. For months, the maximum value will be 12. The total number of employees depends on the company’s workforce; here we assume a value of 10. Since each shift is 8 hours, the maximum number of shifts will be 3. The declaration would look like this:
Example:
Option Base 1
Dim Hours (12, 10, 3)
Since the base is 1, 12 represents the number of months, 10 represents the number of employees, and 3 represents the number of shifts.
Therefore, to store the information, you will need to fill in the corresponding spots for each month, employee, and shift. Here are some examples:
Example a): If “Employee #3” worked 125 hours in the second shift in July, the value assignment in the Array would be:
Hours(7, "Employee#3", 2) = 125
Example b): If “Employee #8” worked 150 hours in the first shift in April, the value assignment in the Array would be:
Hours(4, "Employee#8", 1) = 150
Example c): If “Employee #1” worked 200 hours in the third shift in December, the value assignment in the Array would be:
Hours(12, "Employee#1", 3) = 200
Extracting or Reading Data from the Array
Fortunately for us, VBA is very intuitive. To write a value from a specific element of an array to a cell or variable, simply refer to the array’s position, as shown in the following example for a three-dimensional array:
Example in a cell:
Range("A2") = Hours(7, "Employee#3", 2)Example in a variable:
ResultHP = Hours(7, "Employee#3", 2)
With this approach, make sure that the elements you are referring to exist in the array, as VBA will show a dimensioning error if they don’t. To avoid such errors, you can use the Lbound and Ubound functions to find the lower and upper bounds of your array, respectively.
Dynamic Sizing of the Array
An advanced use of arrays refers to dynamic sizing. If we revisit the previous example, the number of employees might change over time.
To handle this, we must initialize the array as empty, and it is essential to assign its dimensions before storing any data in it using the Redim command, as shown below:
Option Base 1
Dim Hours() As Integer
Redim Hours(12, 10, 3)
The size values for redimensioning can be constants, as in the example above, or they can also be variables previously defined, as shown here:
Option Base 1
Dim Hours() As Integer
Nemp = 10
Redim Hours(12, Nemp, 3)
The Redim command can expand or reduce the array’s size. However, keep in mind that it will delete any previously stored information in the array. To expand the array and preserve its data, use the Redim Preserve statement. Below is an example showing “empty spaces” added to the array, represented by the green-backgrounded elements.
Here is a code example for resizing the number of employees in the illustrated example:
Option Base 1
Dim Hours(12, 10, 3) As Integer
Nemp = 12
ReDim Preserve Hours(12, Nemp, 3)
Remember that reducing the array’s size will cause you to lose data for the elements that are “excluded” from the array. For instance, if we resize from a 3×3 array to a 2×2 array, we lose data from the white-background elements.
As with the previous example, to preserve data for the elements in the blue-background areas, use the ReDim Preserve statement.
Want to learn more? Contact us…
