- Declaring Variables
- Avoiding Variable Errors
- Variable Data Types
- Using Array Variables
- Working with Constants
- The Absolute Minimum
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 20 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 employee array variable that can hold up to 20 names. Here's how you would do that:
Dim employee(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 the subscript to refer to any element simply by enclosing its index number in the parentheses, like so:
employee(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