Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac
- Option Explicit forces you to declare all the variables.
- It’s a statement that you can use at the starting 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 code and also highlights the variable in the code that you need to declare.
When you add 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 show 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.
How to Activate Option Explicit in VBA
To activate the option explicit statement in a module, use the following steps.
- First, open the Visual Basic Editor and in the tools menu click on the options.
- After that, in the options in, go to the editor tab and tick mark “Require Variable Declaration”.
- In the end, click OK.
- 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 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 have defined value to this variable.
And in the end, I have used a message box show the value of the variable, but if you look carefully I have miss-spelled that variable as “MyTxt” instead of “myText”.
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 takes it as a separate variable, and as I am not using option explicit statement, it doesn’t show me an error.
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 statement, 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
If you liked this article, then please subscribe to our YouTube Channel for Video Tutorials.