vba option explicit

Excel VBA Option Explicit – explicit variable declaration

The Excel VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do?

What does Option Explicit do?

The VBA Option Explicit statement can either:

  • Forces an explicit declaration of all variables in a VBA Module
  • Allow the implicit declaration of variables in VBA Modules

However, the first question most people have is…

What is explicit and implicit variable declaration?

Explicit variable declaration in VBA is the declaration of a variable with the use of the VBA Dim statement:

Dim x as Long 'Explicit declaration
x = 1

Implicit variable declaration on the other hand is the opposite – the usage of a variable without any preceding declaration. The VBA Compiler will create a variable that has been used even if it was not preceded with an explicit declaration:

x = 1 'Implicit declaration

Option Explicit usage

Now that we know what implicit and explicit declarations we can ponder on a second on why we would want to force explicit variable declaration. Well, for a number of reasons:

  • To prevent variable name typos
  • Clean up VBA Code
  • Optimize VBA performance
By default all implicit variables are Variant types. Variants are much less efficient than other data types (Longs, Integers, Strings etc.). Read more on VBA Performance here

Using Option Explicit

Option Explicit 'Declare that all variables are to be explicitly declared

Sub Main
 Dim x as Long
 x = 1 'OK!
 y = 10 'ERROR!
End Sub

option explicit errorWhat happens when you try to declare a variable implicitly. Believe me, it is better to fix Variable not defined errors than scramble to find variable name typos.

Turn on Option Explicit by default

For me the Option Explicit statement is a must requirement. I turn it on by default in all my macros. Even when using Variant variables.

turn on option explicitHow to turn on Option Explicit by default for all VBA Code Modules? Go to Tools->Options.... In the Options window select Require variable declaration. That is it! Similarly you can turn off the addition of the Option Explicit statement in all your VBA Code Modules by unchecking this option.