VBA VBA Option Keyword

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Syntax

  • Option optionName [value]
  • Option Explicit
  • Option Compare {Text | Binary | Database}
  • Option Private Module
  • Option Base {0 | 1}

Parameters

OptionDetail
ExplicitRequire variable declaration in the module it's specified in (ideally all of them); with this option specified, using an undeclared (/mispelled) variable becomes a compilation error.
Compare TextMakes the module's string comparisons be case-insensitive, based on system locale, prioritizing alphabetical equivalency (e.g. "a" = "A").
Compare BinaryDefault string comparison mode. Makes the module's string comparisons be case sensitive, comparing strings using the binary representation / numeric value of each character (e.g. ASCII).
Compare Database(MS-Access only) Makes the module's string comparisons work the way they would in an SQL statement.
Private ModulePrevents the module's Public member from being accessed from outside of the project that the module resides in, effectively hiding procedures from the host application (i.e. not available to use as macros or user-defined functions).
Option Base 0Default setting. Sets the implicit array lower bound to 0 in a module. When an array is declared without an explicit lower boundary value, 0 will be used.
Option Base 1Sets the implicit array lower bound to 1 in a module. When an array is declared without an explicit lower boundary value, 1 will be used.

Remarks

It is much easier to control the boundaries of arrays by declaring the boundaries explicitly rather than letting the compiler fall back on an Option Base {0|1} declaration. This can be done like so:

Dim myStringsA(0 To 5) As String '// This has 6 elements (0 - 5)
Dim myStringsB(1 To 5) As String '// This has 5 elements (1 - 5)
Dim myStringsC(6 To 9) As String '// This has 3 elements (6 - 9)


Got any VBA Question?