The For...Nextloop adds a new series to the chart for each product. The loop uses the UBoundand
LBoundfunctions to avoid having to know the Option Basesetting for the module. The range object
rngYAxisis assigned a reference to the chosen region data within the current product data.
Range(vProducts(i))refers to the ranges containing the product tables. Each range has been assigned
a name corresponding to the text entries in vProducts(i). iRegion is used as the row offset into the
product data to refer to the correct region data. The column offset is 1 so that the name of the region is
excluded from the data. Resize ensures that the data range has one row and three columns. The range
object rngXAxis is assigned a reference to the month names at the top of the product data table.
Following the With statement, MangoesToRegionuses the NewSeries method to add a new empty
series to the chart. The NewSeries method returns a reference to the new series, which supplies the
With...End Withreference that is used by the lines between Withand End With. The Nameproperty
of the series, which appears in the legend, is assigned the current product name.
The Values property of the new series is assigned a reference to rngYAxis. The XValues property
could have been assigned a direct reference to rngXAxis in the same way. However, both properties can
also be defined by a formula reference as an external reference in the A1 or R1C1 style. The string value
generated and assigned to the Mangoes series XValues property is:
The final section of code is as follows:
‘Define chart title
cht.ChartTitle.Text = vRegions(iRegion + LBound(vRegions) - 1)
‘Give name to chartobject
cbo.Name = “RegionChart”
The ChartTitle.Textproperty is assigned the appropriate string value in the vRegions array, using
the value of iRegionas an index to the array. To avoid having to know the OptionBase setting for the
module, LBound(vRegions)-1 has been used to adjust the index value in iRegion, which ranges
from 1 to 4. If the OptionBasesetting is 0, this expression returns a value of 1, which adjusts the value
of iRegionsuch that it ranges from 0 to 3. If the OptionBase setting is 1, the expression returns 0,
which does not change the iRegionvalue so it still has the range of 1 to 4. Another way to handle the
Option Baseis to use the following code:
Cht.ChartTitle.Text = vaRegions(iRegion - Array(0,1)(1))
The code finally changes the name of the ChartObject to RegionChart.
Defining Chart Series with Arrays
Achart series can be defined by assigning a VBA array to its Values property. This can come in handy if
you want to generate a chart that is not linked to the original data. The chart can be distributed in a sep-
arate workbook that is independent of the source data.
Chapter 8: Charts