Option Explicit helps to define variables when writing code. It’s possible that the developer misspelt a variable or forgot to define it in his code. The code will run without giving an error message, but we will still get a result. If we have a lot of code, like thousands of lines, but only a few variables are undefined or misspelt, it will be very time-consuming to check each line of code again and again. VBA has a solution called “Option Explicit Statement” to ensure this doesn’t happen. The statement is at the top of the code, which means that any user must define the variable. It is also important because it shows which variable has yet to be determined.
Methods to use Option Explicit in VBA
There are two methods to use Option Explicit in VBA.
METHOD 1: The first way is to write a statement option explicit at the top of our sub-procedure and then continue with the rest of our code, as shown in the image below.
Sub Option_Explicit_Example() x = 50 MsgBox x End Sub
Above, there is a blue sentence that says “Option Explicit.” Now, when we run the code, we get the following error.
METHOD 2: We don’t need to write Option Explicit on every code page. We can tell VBA to add this statement to our modules by giving it some instructions. Below are the steps to follow.
Step 1: Select the Tools tab in the VB Editor’s headers, and then click on Options from the list of options that appears.
Step 2: When you click on Options, a box like the one below will open.
Step 3: Choose the “Require Variable Declaration” option and click OK.
Start the module again to notice the changes in the VB Editor. The VB Editor will now have the Option Explicit option for any module.
Examples
Write a VBA command button to implement Option Explicit.
Step 1: Open Excel and click the Developer tab ->Visual Visual Basic->Insert Module.
Step 2: The VBA editor window appears.
Step 3: Write code to display a Msgbox with the value 30 whenever the Run icon is clicked.
Step 4: Click on Run icon on the top of VBA editor. Now a MsgBox displays 30.
Step 5: We will manually add the option explicit statement to the top of your code.
Step 6: Click the Run icon again, and this time the compile error appears.
Step 7: Since we have yet to state our variable, it compiles incorrectly. It highlights undeclared variables. Fix the code by putting the variable definition at the top of your module.
Option Explicit Sub Option_Explicit_Example() Dim x As Integer x = 30 MsgBox x End Sub
Step 8: Clik on the Run icon to see the output Msgbox.
