Part VI: Automating Excel
If you use an object variable (as described in “Simplifying object references” earlier in
this chapter), you can declare the variable as the appropriate object data type. Here is an
example:
Dim Rate As Range
Set Rate = Workbooks("MyBook.xlsx").Worksheets("Sheet1").
Range("IntRate")
To force yourself to declare all of the variables that you use, insert the following statement
at the top of your module:
Option Explicit
If you use this statement and Excel encounters a variable that hasn’t been declared, Excel
displays an error message. After you get into the habit of correctly declaring all of your
variables, you’ll find that it not only can speed up code execution, but it helps eliminate
errors and simplifies spotting errors.
