Microsoft Office Tutorials and References
In Depth Information
You declare an array the same way you typically declare variables. The variable declaration starts
with the Dim statement, followed by the array name and the data type. The array name ends with a
pair of parentheses to indicate that it’s an array, with the count of elements, if known, placed inside
For example, the following statement declares an array named myDays , which will be populated
with all seven days of the week. Notice the data type is String, because weekday names are text
values, such as “Sunday,” Monday,” and so on.
Dim myDays(6) As String
You can also declare arrays using the Public , Private , and Static keywords, just as you can with
other variables, with the same results in terms of scope and visibility.
To declare an array as Public , place a statement like this at the top of your module:
Public MyArray(1) As String
With the Public declaration, you can share an array across procedures. For example, if you run
either of the following two macros, the array elements of Hello and Goodbye will be displayed in a
‘Fill the array MyArray with values.
MyArray(0) = “Hello”
MyArray(1) = “Goodbye”
‘Run the TestPublicArrayExample macro to display MyArray.
‘Display the values contained in the array MyArray.
Dim i As Integer
For i = 0 To UBound(MyArray, 1)
You may have noticed the UBound statement in the preceding macro. You will
learn more about upper and lower boundaries in the upcoming section named
Boundaries in Arrays.
A Static array is an array that is sized in the declaration statement. For example, the following
declaration statement declares an Integer array that has 11 rows and 11 columns:
Dim MyArray(10, 10) as Integer