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: With Option Base 1:

Limitation

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.