MS-Excel / General Formatting

---

Using Array Variables

In VBA, an array is a group of variables of the same data type. Why would you need to use an array? Well, suppose you wanted to store twenty employee names in variables to use in a procedure. One way to do this would be to create 20 variables named, say, employee1, employee2, and so on. However, it's much more efficient to create a single employees array variable that can hold up to 20 names. VBA creates a single variable with 20 different "slots" into which you can add data (such as employee names). Such an array variable is akin to an Excel range that consists of 20 cells in a row or column: the range is a single entity, but it contains 20 slots (cells) into which you can insert data. The major difference is that you almost always use an array variable to hold data of a single type, such as String. When you declare an array variable you specify the data type, as shown here:

Dim employees(19) As String

As you can see, this declaration is very similar to one you would use for a regular variable. The difference is the 19 enclosed in parentheses. The parentheses tell VBA that you're declaring an array, and the number tells VBA how many elements you'll need in the array. Why 19 instead of 20? Well, each element in the array is assigned a subscript, where the first element's subscript is 0, the second is 1, and so on up to, in this case, 19. Therefore, the total number of elements in this array is 20.

You use a subscript to refer to any element simply by enclosing its index number in the parentheses, like so:

employees(0) = "Ponsonby"

By default, the subscripts of VBA arrays start at 0 (this is called the lower bound of the array) and run up to the number you specify in the Dim statement (this is called the upper bound of the array). If you would prefer your array index numbers to start at 1, include the following statement at the top of the module (in other words, before declaring your first array and before your first procedure):

Option Base 1

Note, too, that after resetting the lower bound in this way, if you want to declare an array with the same number of elements, then you need to adjust the upper bound in the Dim statement accordingly. For example, with the lower bound set to 1, if you want to declare an array variable named employees and you want it to hold up to 20 names, then you need to declare it like so:

Dim employees(20) As String