Microsoft Office Tutorials and References
In Depth Information
Determining the Ranges Used in a Chart
VBA Speed Tips (Continued)
After this variable is defined, you can use the variable ChtSer instead of the lengthy
Declaring variable types
Usually, you don’t have to worry about the type of data that’s assigned to a variable.
Excel handles all these details behind the scenes. For example, if you have a variable
named MyVar , you can assign a number or any type to it. You can even assign a text
string to it later in the procedure.
But if you want your procedures to execute as fast as possible, you should tell Excel in
advance what type of data is going be assigned to each of your variables. This is known as
declaring a variable’s type. Chapter 14 contains additional information about data types.
Determining the Ranges
Used in a Chart
You might need a VBA macro that must determine the ranges used by each series in
chart. For example, you may want to increase the size of each series by adding a new
cell. Following is a description of three properties that seem relevant to this task:
Formula property: Returns or sets the SERIES formula for the Series.
When you select a series in a chart, its SERIES formula is displayed in
the formula bar. The Formula property returns this formula as a string.
Values property: Returns or sets a collection of all the values in the series.
This can be a range on a worksheet or an array of constant values, but
not a combination of both.
XValues property: Returns or sets an array of x values for a chart series.
The XValues property can be set to a range on a worksheet or to an array
of values — but it can’t be a combination of both. The Xvalues property
can also be empty.
If you create a VBA macro that needs to determine the data range used by a
particular chart series, you may think that the Values property of the Series object is
just the ticket. Similarly, the XValues property seems to be the way to get the range
that contains the x values (or category labels). In theory, that certainly seems
correct. But in practice, it doesn’t work. When you set the Values property for a
Series object, you can specify a Range object or an array. But when you read this
property, it is always an array. Unfortunately, the object model provides no way to
get a Range object used by a Series object.