Tuesday, February 13, 2007

VBA Declaration of Variables

In VBA you are not required to declare a variable. This is bad programming practice but nevertheless is allowed. There is an option to force the declaration of variables and that is to use the Option Explicit command at the top of a module.

However the next poor programming “feature” within VBA is that variable declarations can be made without assigning the type.
E.g. Dim testString. This will create a variable called testString as a type variant which is the default. It would be better to declare the variable as Dim testString As String. This actually forces the variable to be a string. This makes it easier to trap errors and reduces the memory used.

However VBA also allows you to declare multiple variables on one line. E.g. Dim upper, lower As Single. Unfortunately as I found out today although the syntax for this is valid, only the very last variable in this line will be assigned as the variable type Single. All others will be given Variant (as the default). The way to get around this is to write the line as following; Dim upper As String, lower As String.
This then achieves the desired result of all variables declared with the correct type.

Labels: ,


Posted by: Jethro | 3:24 PM |