VBA Option Base
Option Base is used to declare the default lower bound of array elements. It is declared at module level and is valid only for the current module.
By default (and thus if no Option Base is specified), the Base is 0. Which means that the first element of any array declared in the module has an index of 0.
If Option Base 1 is specified, the first array element has the index 1
Example in Base 0 :
Option Base 0
Sub BaseZero()
Dim myStrings As Variant
' Create an array out of the Variant, having 3 fruits elements
myStrings = Array("Apple", "Orange", "Peach")
Debug.Print LBound(myStrings) ' This Prints "0"
Debug.Print UBound(myStrings) ' This print "2", because we have 3 elements beginning at 0 ->
0,1,2
For i = 0 To UBound(myStrings)
Debug.Print myStrings(i) ' This will print "Apple", then "Orange", then "Peach"
Next i
End SubSame Example with Base 1
Option Base 1
Sub BaseOne()
Dim myStrings As Variant
' Create an array out of the Variant, having 3 fruits elements
myStrings = Array("Apple", "Orange", "Peach")
Debug.Print LBound(myStrings) ' This Prints "1"
Debug.Print UBound(myStrings) ' This print "3", because we have 3 elements beginning at 1 ->
1,2,3
For i = 0 To UBound(myStrings)
Debug.Print myStrings(i) ' This triggers an error 9 "Subscript out of range"
Next i
End SubThe second example generated a Subscript out of range (Error 9) at the first loop stage because an attempt to access the index 0 of the array was made, and this index doesn't exists as the module is declared with Base 1
The correct code with Base 1 is :
For i = 1 To UBound(myStrings)
Debug.Print myStrings(i) ' This will print "Apple", then "Orange", then "Peach"
Next iIt should be noted that the Split function always creates an array with a zero-based element index regardless of any Option Base setting. Examples on how to use the Split function can be found here
Split Function
Returns a zero-based, one-dimensional array containing a specified number of substrings.
In Excel, the Range.Value and Range.Formula properties for a multi-celled range always returns a 1-based 2D Variant array.
Likewise, in ADO, the Recordset.GetRows method always returns a 1-based 2D array.
One recommended 'best practice' is to always use the LBound and UBound functions to determine the extents of an array.
'for single dimensioned array
Debug.Print LBound(arr) & ":" & UBound(arr)
Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
'for two dimensioned array
Debug.Print LBound(arr, 1) & ":" & UBound(arr, 1)
Debug.Print LBound(arr, 2) & ":" & UBound(arr, 2)
Dim i As long, j As Long
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
Debug.Print arr(i, j)
Next j
Next iThe Option Base 1 must be at the top of every code module where an array is created or re-dimensioned if arrays are to be consistently created with an lower boundary of 1.