Microsoft Office Tutorials and References
In Depth Information
USING THE WITH-END WITH CONSTRUCT
generally records what is selected (that is, an object) and then uses the generic
Selection object in subsequent statements. For example, here’s a portion of the
macro recorded in the preceding section:
ActiveChart.PlotArea.Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
It’s not really necessary for a macro to select the PlotArea object. In fact, it’s
hardly ever necessary to select an object before you manipulate its properties in a
macro. Selecting an object may even make the code run slower. The five statements
in the preceding code block can be rewritten as follows:
With ActiveChart.PlotArea.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
USING THE WITH-END WITH CONSTRUCT
You’ll notice that the macro recorder makes frequent use of the With-End With
construct. In the preceding code, the object being manipulated is the Interior
object of the PlotArea object. Specifically, the code is changing the ColorIndex
property and the Pattern property.
The code can be rewritten so that it doesn’t use the With-End With construct.
Those four statements can be replaced by these two:
ActiveChart.PlotArea.Interior.ColorIndex = 2
ActiveChart.PlotArea.Interior.Pattern = xlSolid
If you need to change many properties of an object, using With-End With is
usually a more efficient approach.
The Pattern property is assigned a built-in constant, xlSolid . The
constant xlSolid has a value of 1. Using these built-in constants simply makes
it easier to remember various arbitrary values.
ADDING ERROR-HANDLING CODE
If you use the FormatChart macro recorded in the previous section long enough,
you’ll discover that it will generate an error in a few situations. In particular, you’ll
get an error message if a chart is not active, or if the chart does not have a legend.
Search JabSto ::




Custom Search