Excel 2010 Bible

Part VI: Programming Excel with VBA

902

If you don’t declare a variable, Excel uses the Variant data type. In general, the best technique is

to use the data type that uses the smallest number of bytes yet can still handle all the data assigned

to it. An exception is when you’re performing floating-point calculations. In such a case, it is

always best to use the Double data type (rather than the Single data type) to maintain maxi-

mum precision. Another exception involves the Integer data type. Although the Long data type

uses more bytes, it usually results in faster performance.

When VBA works with data, execution speed is a function of the number of bytes that VBA has at

its disposal. In other words, the fewer bytes that are used by data, the faster VBA can access and

manipulate the data.

To declare a variable, use the Dim statement before you use the variable for the first time. For

example, to declare the variable Units as a Long data type, use the following statement:

Dim Units as Long

To declare the variable UserName as a string, use the following statement:

Dim UserName as String

If you declare a variable within a procedure, the declaration is valid only within that procedure. If

you declare a variable outside of any procedures (but before the first procedure), the variable is

valid in all procedures in the module.

If you use an object variable (as described in “Simplifying object references,” earlier in this chap-

ter), you can declare the variable as the appropriate object data type. The following is an example:

Dim Rate as Range

Set Rate = Workbooks(“MyBook.xlsx”).Worksheets(“Sheet1”).Range(“IntRate”)

To force yourself to declare all the variables that you use, insert the following statement at the top

of your module:

Option Explicit

If you use this statement, Excel displays an error message if it encounters a variable that hasn’t

been declared. After you get into the habit of correctly declaring all your variables, you will find

that it helps eliminate errors and makes spotting errors easier.