What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure

What is the use of Option Explicit while writing VBA Code/ How to avoid typing mistakes on variable names in VBA Code / How to find misspelled variable in a VBA procedure ?


We use so many variables while writing VBA codes and we will do so many tasks using these variables, like assigning a value, retrieving the assigned value, etc.

Probably, a variable will be used several times in a sub. If you have made a spelling mistake while typing the variable name, VBA will not detect it automatically. VBA will consider the misspelled word as an object variable.

To avoid this we can use the key word Option Explicit. This should be used out side any procedure and before starting of any sub.

If we have used this, while running any sub under that VBA will detect any undeclared variables in that sub and stop running the sub asking to declare it. Without delcaring the variable, VBA will not allow you to execute the sub.

Note: You don't need to type this keyword always. Goto VBE > Tools > Options > Click on 'Require variable declaration'. VBA will automatically add this keyword to any modules.

How to exit from a sub / How to stop executing a sub while running a macro

Everyone knows we can use the key combination CTRL + BREAK to stop running the execution of macros.

But while coding you will come across the following scenarios.

1. How to stop a macro in between a sub without executing further codes.

2. How to exit the execution upon one condition

3. How to stop running the current sub and return to the main sub


Solution : We have two key words 'Exit Sub' and 'End'

When to use this ?

Very simple ......

If we use 'END' in codes, all the executions will be stopped. No more codes will be processed.

But, The 'Exit Sub' can be used to stop running a sub and return to the place where it's called.

Eg :  Here we have 2 subs, The Main Sub calls the Process1 sub while running. In Process1 sub, we have a condition. If condition does not meet, it will return to the main sub and execute further codes. If I would have used 'END' instead of Exit Sub, the macro will stop running and no more codes will be processed.

Sub Main()
     Process1
     codes ....
     codes...
End Sub

Sub Process1()
If Activecell.value <> 1 Then Exit Sub
Codes .....
Codes.....
End Sub