Contents
The Option Base statement in VBA (Visual Basic for Applications) is used to change the default lower bound for arrays from 0 to 1. By default, the lower bound of an array in VBA is 0, meaning that the first element of an array is accessed with index 0. However, if you prefer to work with arrays starting from index 1, you can use Option Base 1 at the beginning of your module.
Here’s how to use Option Base:
Place at the Top of the Module
The Option Base statement must be placed before any procedures in a module, typically at the very top. It’s a declaration statement and affects all the arrays declared in the module.
Syntax
There are two options:
- Option Base 0: This is the default setting. Arrays will start at index 0.
- Option Base 1: Arrays will start at index 1.
Scope
The Option Base statement only affects arrays declared in the same module. Each module can have its own Option Base setting.
Examples
Without Option Base or with Option Base 0:
Dim myArray(5) As Integer
' myArray is indexed from 0 to 5With Option Base 1:
Option Base 1
Dim myArray(5) As Integer
' myArray is indexed from 1 to 5Limitation
You can’t use Option Base to set the lower bound to any number other than 0 or 1.
Consistency and Readability
It’s important to maintain consistency in using Option Base across different modules in your project for readability and to avoid confusion.
Remember that Option Base only affects arrays that are declared without specifying their bounds using the To keyword. For example, Dim myArray(1 To 5) As Integer will always create an array from 1 to 5, regardless of the Option Base setting.
Unlock Your Potential
Excel
Access
Power BI
