Page 143 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
The Application Object

The following example displays the address of the cells currently selected:

MsgBox ActiveWindow.RangeSelection.Address

Selection Property
The Selection property returns an object type of whatever the current selection happens to be.
For cells, it will return a Range object; for charts, it will return a Chart object.

Assuming the current selection is a range of cells, this example will clear all values and formulas.

Selection.Clear

StatusBar Property
The StatusBar property allows you to change the current message displayed on the Status Bar
at the bottom of the Excel window. This message can be helpful when you have a procedure
that will take some time to complete because it allows you to keep the user informed that
something is happening. To disable any message you might have placed on the status bar, set
it to False.
This example is a modified version of the BuildChart procedure that was used previously to
demonstrate the ActiveChart property. Several lines of code were added to display messages
to the user on the Status Bar about what the macro was currently doing.

Inside Out

Pausing a Macro
One of the features of Excel is the ability to pull in data from outside sources. Suppose the
flower shop has decided to expand and now has multiple stores. As the owner, you want to
build a report that examines the sales data from each of the different stores every day.
However, the macro to build the report takes some time to compile all the information and
present in the manner you desire, so you’d like it to run overnight so that the report is wait­
ing for you in the morning.

Rather than having to wait until all stores have submitted their data to the main server and
then running your macro, you can use the Wait method to pause the macro until a specific
time. Using the code Application.Wait "20:00:00" instructs the macro to wait until
8:00 P.M. before continuing to execute. To pause for a specific time interval and not a spe­
cific time, you can add the time interval to the current time.
Careful testing should be done before setting too great of an interval for pausing. The Wait
Chapter 6
method will halt all activity within Microsoft Excel and also has the potential to prevent you
from using any other application on your computer.

117