Part VI: Automating Excel
Simplifying object references
As you may have discovered, references to objects can get lengthy—especially if your code
refers to an object that’s not on the active sheet or in the active workbook. For example, a
fully qualified reference to a Range object may look like this:
Workbooks("MyBook.xlsx").Worksheets("Sheet1").Range("IntRate")
If your macro uses this range frequently, you may want to use the Set command to create
an object variable. For example, to assign this Range object to an object variable named
Rate, use the following statement:
Set Rate = Workbooks("MyBook.xlsx").Worksheets("Sheet1").
Range("IntRate")
After this variable is defined, you can use the object variable Rate instead of the lengthy
reference. Here’s an example:
Rate.Font.Bold = True
Rate.Value = .0725
Besides simplifying your coding, using object variables speeds your macros quite a bit.
We’ve seen complex macros execute twice as fast after creating object variables.
Declaring variable types
Usually, you don’t have to worry about the type of data that’s assigned to a variable. Excel
handles all these details behind the scenes. For example, if you have a variable named
MyVar, you can assign a number of any type to it. You can even assign a text string to it
later in the procedure.
If you want your procedures to execute as fast as possible, though, you should tell Excel
in advance what type of data is going be assigned to each of your variables. Providing this
information in your VBA procedure is known as declaring a variable’s type.
Table 47.1 lists all of the data types that VBA supports. This table also lists the number of
bytes that each type uses and the approximate range of possible values.
TA B L E 47.1 VBA Data Types
Data Type Bytes Used Approximate Range of Values
Byte 1 0 to 255
Boolean 2 True or False
Integer 2 – 32,76 8 to 32,767
Long (long integer) 4 –2,147,483,648 to 2,147,483,647
Single (single-preci-
sion floating-point)
4 –3.4E38 to –1.4E–45 for negative values; 1.4E–
45 to 4E38 for positive values
