VBA Option Statement

The Option statement is used to set module-level options in VBA. There are four options that can be set: Explicit, Base, Compare, and Private Module.

Option Explicit

Option Explicit makes explicit variable declarations required for the module. If Option Explicit is declared at the top of a module and a variable is not declared a compile error will occur when trying to run the code. If Option Explicit is not used then variables do not need to be declared. Variables that are not declared are type Variant by default. It is recommended to always use Option Explicit.

Option Explicit

Public Sub Example()

    Dim i As Long

    i = 1

    j = 2 'Causes compile error because variable is not declared explicitly

End Sub

Option Base

The Option Base statement controls the default starting index for arrays. Option Base can be set to 1 or 0. If Option Base is omitted then arrays start at index 0 by default. Declaring Option Base 0 is unnecessary because arrays are 0-indexed by default. The Option Base statement does not affect the starting index for ParamArray parameters. ParamArrays always start at index 0.

Option Base 0

Public Sub Example()

    Dim Arr()

    Arr = Array(1, 2, 3)

    Debug.Print LBound(Arr) 'Prints 0

End Sub

Option Base 1

Public Sub Example()

    Dim Arr()

    Arr = Array(1, 2, 3)

    Debug.Print LBound(Arr) 'Prints 1

End Sub

Option Compare

The Option Compare statement controls the way strings are compared when using comparison operators. Option Compare can be set to Binary or Text. Binary comparison compares text according to the binary representations of the string's characters which means that string comparison will be case-sensitive. Text comparison does case-insensitive comparison of strings using the sort order of the system locale. If Option Compare is omitted then Binary is the default comparison method. Declaring Option Compare Binary is unnecessary because Binary is the default comparison mode. Option Compare Database can be used in Microsoft Access only which compares strings using the sort order according to the locale Id of the database where the string comparison occurs.

Option Compare Binary

Public Sub Example()

    Debug.Print "A" = "a" 'Prints False

End Sub

Option Compare Text

Public Sub Example()

    Debug.Print "A" = "a" 'Prints True

End Sub

Option Private Module

Option Private Module makes all Public members in a module only available to the project in which the module is located.

Option Private Module

Public Sub Example()

    'This procedure is only available inside the
    'VBA project in which it is declared.

    Debug.Print "Hello, World!"

End Sub