Declaring variable types

You don't usually have to worry about the type of data you assign to a variable. Excel handles all the details for you behind the scenes. For example, if you have a variable named MyVar, you can assign a number of any type to that variable. You can even assign a text string to it later in the procedure.

Mj^ But if you want your procedures to execute as fast as possible (and avoid some potentially nasty problems), tell Excel what type of data will be assigned to each of your variables. This is known as declaring a variable's type. (Refer to Chapter 7 for complete details.) Get into the habit of declaring all variables that you use.

In general, you should use the data type that requires the smallest number of bytes yet can still handle all the data assigned to it. When VBA works with data, execution speed depends on the number of bytes VBA has at its disposal. In other words, the fewer bytes data uses, the faster VBA can access and manipulate the data.

If you use an object variable (as described in the preceding section), you can declare the variable as a particular object type. Here's an example:

Dim Rate as Range

Set Rate = WorkbooksCMyBook.xls") _

.Worksheets("Sheet1").Range("InterestRate")

0 0

Post a comment