If you’re writing a VBA macro longer than a few lines, I strongly recommend you use Option Explicit. When Option Explicit is enabled, you are required to declare all your variables.
Enabling Option Explicit
To enable Option Explicit, simply type Option Explicit at the very top of your Visual Basic Editor. It must be placed above your first procedure (Sub or Function).
Guess what? You don’t have to remember to do this each time you start a macro! The VBA editor is smart enough to type it for you. Just follow these steps:
- Open your Visual Basic Editor
- Click Tools » Options
- Check the box next to “Require Variable Declaration”
Your editor will now place the words
Option Explicit at the top of each new module.
Benefits of Option Explicit
Enabling Option Explicit has many benefits:
- Catches mistyped variable names. If you try using a variable that you never defined, as would happen if you mistyped a name, you would receive an error. I’ll demonstrate this later.
- Saves memory. If you don’t declare your variables and you don’t use Option Explicit, your VBA compiler implicitly assumes your variables are of data type Variant. This reserves a whopping 16 bytes of data for each variable!
- Saves time. Because your VBA compiler has to choose the underlying data type for all your variant variables, your macros will run slower.
Let’s try to write a code that adds 10 to a variable that is already set to 5. The answer should be 15.
First, we’ll try it without Option Explicit.
Sub OptionExplicitDemo() Dim iSample As Integer iSample = 5 iSample = iSampl + 10 MsgBox (iSample) End Sub
The message box shows a value of 10! What happened? Look carefully and you’ll see we misspelled our variable
Option Explicit will fix that problem. Let’s try it again.
Option Explicit Sub OptionExplicitDemo() Dim iSample As Integer iSample = 5 iSample = iSampl + 10 MsgBox (iSample) End Sub
The compiler caught our mistake. Not only did it generate an error, it also highlighted the variable we mistyped. Just press “OK” on the error box, correct the mistake, and run the macro again.
Option Explicit can, quite literally, save you hours of debugging time!
In this tutorial you learned the importance of Option Explicit. You learned its benefits and how how to enable it by default. You also went through a demonstration showing how it can be used to catch your mistakes. You should always use Option Explicit in your macro development.