Option Base 1

All arrays start at 0. by default.
All arrays have a lower bound of zero, by default.

Dim arValues(10) As String         'contains 11 items  

These 11 items can be accessed using the following notation:

Debug.Print arValues(0) 
Debug.Print arValues(1)
. . .
Debug.Print arValues(10)

Using To

The lower bound should always be explicitly stated using the "To" keyword in the declaration.
The following two lines are equivalent:

Dim arValues(10) As String         'contains 11 items  
Dim arValues(0 To 10) As String 'contains 11 items

The lower bound can also be changed using the "To" keyword.

Dim arValues(1 To 10) As String    'contains 10 items  
Dim arValues(6 To 10) As String 'contains 5 items

All arrays will start at zero unless they are explicitly defined to start at another number.

Using Option Base 1

You can use the "Option Base 1" declaration at the top of a code module to change the default lower bound to 1 instead of 0.
The following two lines are equivalent when using Option Base 1:

Option Base 1 

Dim arValues(10) As String 'contains 10 items (not 11)
Dim arValues(1 To 10) As String 'contains 10 items

This statement only affects the module it is declared in.

Never use Option Base 1

There is no need to use this declaration as changing the default behaviour will confuse people.
If you want an array that starts at position 1 (instead of position 0) just use the To keyword to define the lower bound.

Dim arValues(1 To 10) As String    'contains 10 items  

Excel Ranges Use Base 1

When an array is populated straight from a range the array will start at 1 and not 0.
For more information refer to the Cells & Ranges > Working With Arrays


This can only be changed on a per module basis.
If you have more than one module containing array manipulations you need to add this statement to the top of every code module.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext