Enabling | Benefits | Example

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:

  1. Open your Visual Basic Editor
  2. Click Tools » Options
    VBA Enable Option Explicit

  3. Check the box next to “Require Variable Declaration”
    VBA Option Explicit Options

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:

  1. 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.
  2. 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!
  3. Saves time. Because your VBA compiler has to choose the underlying data type for all your variant variables, your macros will run slower.

Example

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

Result:
VBA Option Implicit

The message box shows a value of 10! What happened? Look carefully and you’ll see we misspelled our variable iSample as iSampl in line 4.

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

Result:
VBA Option Explicit Error

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.

Result:
VBA Option Explicit Error

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.

If you really want to learn VBA, grab a copy of our Ultimate VBA Training Bundle before reading our next tutorial. We specifically created these cheat sheets to help you get the most out of our upcoming lessons. Together, the set has over 200 practical tips covering the 125 most important topics in Excel VBA. We also stuffed it with 140 helpful macro examples.