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
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
What 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.
How 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.