Monday, 21 January 2013

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.