Microsoft Office Tutorials and References
In Depth Information
The Option Base Statement
THE opTion BAsE sTATEMEnT
When learning arrays, it’s common for some head-scratching and confusion to accompany the
concept of zero-based numbering. In the declaration statement Dim myDays(6) As String , you might
wonder why the array shows the number 6 in parentheses, when there are seven days in a week.
In zero-based numbering, the first element of any array is represented by the default number of 0.
The second element is represented by the number 1, and so on. That is why an array of seven
weekday elements is represented by the number 6 in the statement Dim myDays(6) As String .
VBA does provide a way for specifying that the first element of the array be number 1, which is
more intuitive for most people. You can do this by placing the statement Option Base 1 at the top
of the module. Personally, I have never specified Option Base 1 because I’ve become accustomed to
VBA’s default settings.
Here’s a visual look at zero-based numbering in action. Figure 10-2
shows five text elements that you might manually place into an array
Note the element index numbers starting with the default of 0. In
the following macro, the array named FamilyArray is populated
in the order of the pictured elements. Further, a variable named
FamilyMember is assigned the element 2 item, which is actually the
third item in the list of names because the list starts at number 0.
Therefore, when the MsgBox FamilyMember command is executed, Tom will be displayed in the
Message Box because Tom occupies the element 2 position in the array named FamilyArray .
Dim FamilyArray() As Variant
Dim FamilyMember As String
FamilyArray = Array(“Bill”, “Bob”, “Tom”, “Mike”, “Jim”)
FamilyMember = FamilyArray(2)
To test this concept a bit further, enter the statement Option Base 1 at the very top of the module.
When you run the ArrayTest macro again, you’ll see that FamilyArray(2) returns Bob, because
the array elements were counted starting at base number 1.
It’s a fair question to ask why VBA uses zero-based numbering in the first place.
It turns out, most other programming languages use zero-based numbering for
their arrays because of the way arrays are stored in memory. The topic is rather
complicated, but in simple English, the subscript (the numbers in the parentheses
following the array’s variable name) refers to an offset position in memory from
the array’s starting position. Therefore, the first element has a starting position
of 1, but the array’s subscript is translated into the offset memory address of 0.
The second element is offset at 1, and so on.