Multidimensional Arrays
If you enter a single number between the parentheses in an array's Dim statement, VBA creates a one-dimensional array. But you also can create arrays with two or more dimensions (60 is the maximum). For example, suppose you wanted to store both a first name and a last name in your employee array. To store two sets of data with each element, you would declare a two-dimensional array, like so:
Dim employees(19,1) As String
The subscripts for the second number work like the subscripts you've seen already. In other words, they begin at 0 and run up to the number you specify. So this Dim statement sets up a "table" (or a matrix, as it's usually called) with 20 "rows" (one for each employee) and two "columns" (one for the first name and one for the last name). So if a one-dimensional array is like an Excel range consisting of cells in a single row or column, a multidimensional array is like an Excel range consisting of cells in multiple rows or columns.
Here are two statements that initialize the data for the first employee:
employees(0,0) = "Biff" employees(0,1) = "Ponsonby"
In this tutorial:
- Understanding Program Variables
- Declaring Variables
- Avoiding Variable Errors
- Variable Data Types
- Changing the Default Data Type
- Creating User-Defined Data Types
- Using Array Variables
- Dynamic Arrays
- Multidimensional Arrays
- Working with Constants
- Storing User Input in a Variable
- Getting Return Values from the Message Dialog Box
- Getting Input Using InputBox