Editing Data Series

The SetSourceData method of the Chart object is the quickest way to define a completely new set of data for a chart. You can also manipulate individual series using the Series object, which is a member of the chart's SeriesCollection object. The following example is designed to show you how to access individual series.

The code will take the MangoesChart and delete all the series from it, and then replace them with four new series, one at a time. The new chart will contain product information for a region nominated by the user. To make it easier to locate each set of product data, names have been assigned to each product range in the worksheet. For example, A3 has been given the name Mangoes, corresponding to the label in A3. The final chart will be similar to the chart in Figure 8-5.

Figure 8-5

The following code converts MangoesChart to include the new data (note that the original chart must still be on the spreadsheet for this to work). Because MangoesToRegion is a fairly long procedure, it is examined in sections:

Sub MangoesToRegion()

Dim cbo As ChartObject

Dim cht As Chart

Dim scSeries As SeriesCollection

Dim iCount As Integer

Dim rngYAxis As Range

Dim rngXAxis As Range

Dim vProducts As Variant

Dim vRegions As Variant

Dim iRegion As Integer

Dim vAnswer As Variant

'Set up arrays for Product

& Region names

vProducts = Array("Mangoes"

', "Bananas", "Lychees", "Rambutan")

vRegions = Array("South", "

'North", "East", "West")

'Determine that MangoesChart exists

On Error Resume Next

Set cbo = Worksheets("Sales").ChartObjects("MangoesChart")

If cbo Is Nothing Then

MsgBox "MangoesChart was

not found - procedure aborted", vbCritical

Exit Sub

End If

On Error GoTo 0

MangoesToRegion first assigns the product names to vProducts and the region names to vRegions. It then tries to set the cbo object variable by assigning the variable a reference to the ChartObject named MangoesChart. If this fails, the procedure is aborted. Because it is not the main point of the exercise, this section of code has been kept very simple:

'Get Region number

Do 'While vAnswer < 1 Or vAnswer >

4

vAnswer = InputBox("Enter Region

number (1 to 4)")

If vAnswer = "" Then Exit Sub

If vAnswer >= 1 And vAnswer <= 4

Then

Exit Do

Else

MsgBox "Region must be 1, 2, 3

or 4", vbCritical

End If

Loop

iRegion = Clnt(vAnswer)

The user is then asked to enter the region number. The Do...Loop will continue until the user clicks Cancel, clicks OK without entering anything, or enters a number between 1 and 4. If a number between 1 and 4 is entered, the value is converted to an integer value, using the Clnt function, and assigned to iRegion:

'Set up new chart

Set cht = cbo.Chart

Set scSeries = cht.SeriesCollection

'Delete all existing chart series

For iCount = scSeries.Count To 1 Step -1

scSeries(iCount).Delete Next iCount

Next, cht is assigned a reference to the chart in the ChartObject. Then scSeries is assigned a reference to the SeriesCollection in the chart. The following For...Next loop deletes all the members of the collection. This is done backwards because deleting the lower number series first automatically decreases the item numbers of the higher series. In this case there will be no series 3 when you try to delete it, which will cause a run-time error. Alternatively, you could have deleted series 1 each time around the loop and the direction of the loop would not have mattered:

'Add Products for Region

For iCount = LBound(vProducts) To UBound(vProducts)

'Define chart ranges

Set rngYAxis = Range(vProducts(iCount)

1.Offset(iRegion, 1).Resize(1, 3)

Set rngXAxis = Range(vProducts(iCount)

I.Offset(Q, 1).Resize(1, 3)

'Add new series & assign data

With scSeries.NewSeries

.Name = vProducts(iCount)

.Values = rngYAxis

.XValues = "=" & rngXAxis.Address _

(RowAbsolute:=True, _

ColumnAbsolute:=True, _

ReferenceStyle:=xlR1C1, _

External:=True)

End With

Next iCount

The For...Next loop adds a new series to the chart for each product. The loop uses the UBound and LBound functions to avoid having to know the Option Base setting for the module. The range object rngYAxis is 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, MangoesToRegion uses 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 With reference that is used by the lines between With and End With. The Name property 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:

=[Charts2.xlsm]Sales!R3C2:R3C4

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"

End Sub

The ChartTitle.Text property is assigned the appropriate string value in the vRegions array, using the value of iRegion as an index to the array. To avoid having to know the Option Base 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 Option Base setting is 0, this expression returns a value of 1, which adjusts the value of iRegion such that it ranges from 0 to 3. If the Option Base setting is 1, the expression returns 0, which does not change the iRegion value so it still has the range of 1 to 4. Another way to handle the Option Base is 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.

0 0

Post a comment