Simplifying object references

As you probably already know, references to objects can become very lengthy. For example, a fully qualified reference to a Range object may look like this:

Figure 14-3:

You can instruct Excel to not display these types of alerts while running a macro.

Workbooks("MyBook.xls").Worksheets("Sheet1") .Range("InterestRate")

If your macro frequently uses this range, you may want to create an object variable by using the Set command. For example, the following statement assigns this Range object to an object variable named Rate:

Set Rate = WorkbooksCMyBook.xls") _

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

After defining this object variable, you can use the variable Rate rather than the lengthy reference. For example, you can change the value of the cell named InterestRate:

Rate.Value = .085

This is much easier to type (and understand) than the following statement:

Workbooks("MyBook.xls").Worksheets("Sheet1"). Range("InterestRate") = .085

In addition to simplifying your coding, using object variables also speeds up your macros considerably. After creating object variables, I've seen some macros execute twice as fast as before.

0 0

Post a comment