Excel 2010 Bible

Part VI: Programming Excel with VBA

900

Preventing alert messages

One benefit of using a macro is that you can perform a series of actions automatically. You can

start a macro and then get a cup of coffee while Excel does its thing. Some operations cause Excel

to display messages that must be attended to, however. For example, if your macro deletes a sheet,

you see the message that is shown in the dialog box in Figure 44.3. These types of messages mean

that you can’t execute your macro unattended.

FIGURE 44.3

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

To avoid these alert messages (and automatically choose the default response) , insert the following

VBA statement:

Application.DisplayAlerts = False

To turn alerts back on, use this statement:

Application.DisplayAlerts = True

Simplifying object references

As you may have discovered, references to objects can get very 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 variable Rate instead of the lengthy reference. For

example

Rate.Value = .0725