Monday, January 18, 2010

Using "Option Explicit" in excel Module

All VBA code involves declaring variables some way or other. While VBA allows a great deal of flexibility in declaring variables, this flexibility can easily lead to broke coding practices. This post describes practices you should clinch regarding variable declaration. While adopting these practices can lead to more typing, following them makes code run faster, and makes the code easier to debug and maintain.
By default, VBA doesn't require that you declare your variables using the Dim statement. If the compiler encounters a name (variable) that it doesn't recognize as an existing variable, one of the VBA reserved words, or a property or method of a referenced library, it will create a new variable by that name. While this may seem convenient, if the code is just to add two numbers, as it won’t require too much of memory space and in today’s world of gigs & tera who care about memory, but it can lead to bugs in the code that are difficult to find. Suppose you declare a variable with the name intRow1 and later misspell that name as intRo1, the compiler will not flag that as an error. Instead, it will create a new variable named intRo1 and initialize it to an empty string, a value of 0, or a Nothing object reference, depending on the context in which it is used. This means that the (correct) variable intRow1 will not contain the expected value. If the code is big you may find it difficult to fix the bug and may not realize where it’s going wrong.
You can prevent this type of mistake by requiring that all variables be declared with a Dim statement. As the first line of code in the module, above and before any other lines, use:
Option Explicit
This statement requires that all variables be declared using a Dim statement. If any of the variable is not declared after using this statement, complier will show an error message "Compile Error: Variable Not Defined" and highlight the said variable. You can set an option in the VBA Editor that will automatically add an Option Explicit directive in all newly created code modules. In the VBA Editor, go to the Tools menu, choose Options and then select the Editor tab and check the Require Variable Declaration option. Please refer to the sample code

Before Using the "Option Explicit"

Sub Test()

x = 1 + 2

End Sub

The above code will run without compiler showing any error

AfterUsing the "Option Explicit"

Option Explicit
Sub Test()
x = 1 + 2
End Sub

The above code will not run and complier will show error "Variable Not Defined". So declare varibale "x" to run the code.