What Is VBA Option Explicit?
VBA Option Explicit helps users declare all the variables. It is important to declare the variables in excel. If any variable is not defined, we will get an error. Variables are an integral part of any coding language, and VBA is no different. Declaring variables and working with those variables is the smartest way in VBA. However, there are chances that we declare the variable as “MyValue” and do a spelling mistake while using it as “MyVlaue”.
To avoid these kinds of mistakes and code efficiently, we need to use the command “Option Explicit”. We must have observed the line “Option Explicit” at the top of the module in some workbooks as shown in the following image:
At the beginner level, we might not have bothered about what exactly it is. Option Explicit in VBA is the command available to make the declaration mandatory using the DIM statement. If we do not declare a variable and still try to use it in the code, then VBA Option Explicit command will not allow it to use until we declare it using the DIM statement.
VBA Option Explicit statement forces us to declare the variables explicitly and not allow us to use the variables implicitly.
Key Takeaways
- VBA Option Explicit is used to declare the variables used in Excel. The statement does not allow us to use variables without defining them using the DIM statement.
- An explicit way of defining a variable is by using the DIM statement.
- The VBA Option Explicit statement can be added manually to the existing module, and we can also remove it at any point in time.
- In simple terms, VBA Option Explicit avoids the spelling mistake of variable names.
- Similarly, it also highlights the non-defined variables with the compile error message
What Is Explicit And Implicit Variable Declaration?
Explicit and Implicit are the two technical words, and they may sound strange. Let us explain practically to you.
Explicit Declaration:
Declaring variables by using the VBA DIM statement is the explicit way of declaring variables.For example, look at the following code.
Dim k As Long
K = 500
We have declared the variable “k” by using the DIM statement and assigned the data type, Long, to it.
Next, we have assigned the value 500 for the variable “k”. Here, we have defined the variable explicitly and then assigned the value to it.
Implicit Declaration:
Using the implicit way, we need to define the variable with the DIM statement and assign a data type to it. For example, look at the following code.
K = 500
We have not declared the variable “k” by using the DIM statement and rather simply assigned the value to it. In this case, the VBA compiler will create a variable name that is been used even though it was not explicitly defined before the value assigned to it.
If we use the OPTION EXPLICIT statement, we cannot use the variables implicitly and need to use the explicit way of declaration of variables.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Activate Option Explicit In VBA
By default, when we open the Visual Basic Editor Window, we will not see the option explicit statement. We will see a blank coding window like the following image:
Let’s follow the steps listed below to activate the option explicit statement.
Step 1 – Go to the Tools tab and click on Options.
Step 2 – This will bring the Options window like the following image.
Step 3 – Check the box Require Variable Declaration.
Click on OK, and it will close the Options window.
However, as soon we close, we will not see the Option Explicit statement in the existing module.
Go to the Insert tab and click on Module.
This will add the new module, and in this new module, we will see the Option Explicit statement at the top of the module.
How To Add It Manually?
If we activate the option explicit by using the previous steps, it will add the option explicit statement for all the modules added after activating it. However, we can add the option explicit statement without activating it also.
As we know, the Option Explicit statement should be at the top of the module i.e., the very first line should be this statement.
In the module (blank coding area), type the word Option Explicit.
After we type the statement, hit the Enter key and it will turn to blue color.
We cannot add the Option Explicit statement in between the sub-procedures. For example, look at the following image.
In the above image, we have added the Option Explicit statement in the middle of the coding area. When we try to run the code, it shows Compile error: saying Only comments may appear after End Sub, End Function, or End Property.
Examples
Example #1 – Avoid Typing Mistakes With Option Explicit
When we code, it is quite common that we make typo errors; Option Explicit will save us in those situations. For example, look at the following code.
Sub Example1_Option_Explicit()
Dim MyValue As String
MyVlaue = “Hello”
MsgBox MyValue
End Sub
We have written this code without activating the Option Explicit statement at the top of the module.
Let us explain the code line by line.
- 1 – We have defined the variable “MyValue” by using the DIM statement and assigned the data type String to it.
- 2 – We have assigned the value “Hello” to the defined variable.
- 3 – In the message box, we are showing the value assigned to the variable.
When we run this code, we should see the variable value “Hello” in the message box. Let’s run the code and see.
We can see only the blank message box. Let’s closely examine the code to understand the real problem.
When we have assigned the value (in the second line) to the variable, we have misspelled the variable name “MyVlaue” (letters L and A are interchanged) instead of “MyValue”.
Since we have not enabled the Option Explicit, VBA compile treats the misspelled variable as a variable. We have assigned a value to the misspelled variable, and the correct variable has no value assigned to it. Hence, in the message box, we are seeing just the blank value.
This is where the Option Explicit statement saves us from these unnoticeable mistakes. Add the Option Explicit word at the top of the module.
Now run the code and see what happens.
Before it executes the code, Option Explicit statement finds the typo error and returns the compile error saying, Variable not defined. It has highlighted the typo error variable name as well.
Example #2 – Variable Declaration Mandatory
When we add the Option Explicit statement, we need to declare the variables explicitly.
For example, look at the following code.
Option Explicit
Sub Example2_Option_Explicit()
MyValue = “Hello”
MsgBox MyValue
End Sub
We have the Option Explicit statement enabled at the top but have not defined the variable “MyValue” explicitly; rather, we have just assigned the value to the variable implicitly.
Let’s run the code and see what happens.
Because we have not declared the variable explicitly with the option explicit enabled at the top, it will not allow us to execute the procedure before we define the variable explicitly using the DIM statement.
This would serve as a reminder for us just in case we forgot to define the variable at the start of the macro procedure.
Example #3 – Implicit Variables Are Variant Data Type And Consume More Memory
If we do not use the Option Explicit statement, the variables that we use becomes a variant data type, and the variant data types are expensive in memory.
For example, look at the following code.
Sub Example3_Option_Explicit()
MyValue = 50
MsgBox MyValue
End Sub
Without the option explicit statement, we have assigned an integer value to the variable “MyValue”, which is fine.
However, one thing we need to be aware of in these kinds of practices is that the variable “MyValue” can hold all kinds of values i.e., variant data types.
A variant data type acquires 16 bytes of memory. In contrast, an integer data type takes only 2 bytes of the memory i.e., a variant data type takes 8 times more than the integer data type memory.
In this sense, it is always a good practice to enable the option explicit statement.
Important Things To Note
- By default, the VBA option explicit statement is not enabled.
- VBA Option Explicit statement returns compile error if the variable is not defined explicitly.
- An implicit way of defining a variable is using the variable directly without defining and assigning the data type.
- VBA Option Explicit should be always at the top of the module, and nothing should be added before this statement.
- When we enable the option explicit statement, it will add VBA option explicit statement in the existing module.
Frequently Asked Questions
Option Explicit statement in VBA allows us to have better control over variable usage and keeps us updated on the typo mistakes of variable names.
We can add the option explicit statement manually at the top of the module, hence when we add this manually, we may make typing mistakes hence option explicit may not work.
Step 1 – Go to the Tools tab and click on Options.
Step 2 – This will bring the Options window like the following.
Step 3 – Check the box Require Variable Declaration.
Click on OK, and it will close the Options window.
This will not add the Option Explicit statement in the existing module. We need to add the new module to see the Option Explicit statement, or else we need to type the Option Explicit statement manually at the top of the module.
Recommended Articles
This has been a guide to VBA Option Explicit. Here we explain how to use the option explicit statements to declare variables in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply