How to use Option Explicit Statement in VBA

Key Points

• Option Explicit forces you to declare all the variables.
• It’s a statement that you can use at the start of a module.
• You can enter it manually or activate it from the options.
• You need to use Option Explicit only once.
• It also helps you to identify typing errors while using variables.

What is VBA Option Explicit

Option Explicit is a statement that you can use at the beginning of a module to force yourself to declare all the variables. When you add this statement, VBA shows you a compile error when you execute the code and also highlights the variable in the code that you need to declare.

When you add an option explicit at the beginning of a module, VBA adds a separator line below it, and then you can start writing a procedure.

And when you have the option explicit statement, and you run a procedure, VBA checks if there is a variable that is not declared and shows you an error message.

Option Explicit
Sub myMacro()
a = 50
MsgBox a
End Sub   

Above is the message that you get when a variable is not declared.

Activate Option Explicit in VBA

To activate the option explicit statement in a module, use the following steps.

1. First, open the Visual Basic Editor and in the tools menu click on the options.
   
2. After that, in the options in, go to the editor tab and tick mark “Require Variable Declaration”.
   
3. In the end, click OK.
4. Once you do that, VBA will add option explicit whenever you add a new module.

But it doesn’t add this statement to your existing module, so you need to add them manually to each of the existing modules one by one.

How to Add it Manually?

As I mentioned the Option Explicit statement must go before the first procedure in the module (Sub or Function).

So, you need to add this statement above the first procedure (General Declarations area) and make sure to add it only once.

If you add it within a procedure, or between two procedures, VBA will show you an error when you try to execute any of the code in the module.

Examples (Why using Option Explicit Statement is a Good Habit)

Let me show you an example to make you understand why the Option Explicit statement is highly recommended to use.

Look at the following code.

Sub myMacro()
  Dim myText As String    
  myText = "Puneet"    
  MsgBox   MyTxt    
End Sub

In this code, I have declared a variable “myText” as a string and then defined the value of this variable.

And in the end, I used a message box that shows the value of the variable, but if you look carefully, I have miss-spelled that variable as “MyTxt” instead of “myText” (typo error).

Now when I run this code, it shows me a blank message box.

I need your 2 minutes to help you understand the real problem here.

When I mistyped the variable name, VBA took it as a separate variable, and as I am not using the explicit statement option, it doesn’t show me an error to notify me.

That is why the message box uses the second variable (mistyped) which has no value assigned to it.

Now think for a second; if you are writing a lengthy code and not using option explicit statements, it will be hard for you to trace down this error until you reread the entire code.

But with the option explicit statement ON, when I run this code, it shows an error.

Option Explicit
Sub myMacro()
Dim myText As String 
myText = "Puneet" 
MsgBox MyTxt 
End Sub

There’s one more problem: Undeclared variables are automatically assigned the Variant data type, which can hold any data type.

While Variant types are flexible, they consume more memory and processing power than specific data types like Integer or String.

This can negatively impact the performance of your VBA application, especially in loops or large-scale data processing.

Wrap Up

Option Explicit is considered a best practice in VBA programming, as it can significantly reduce the number of runtime errors and make your code more manageable and maintainable.