I

Jwi r66 Mil Apr W»y Jun

Figure 18-7: Applying a shadow to a chart.

With MyChart.ChartArea.Format.Shadow .Visible = msoTrue .Blur = 10 .Transparency = 0.4 .OffsetX = 6 .OffsetY = 6 End With

The example that follows adds a subtle shadow to the plot area of the chart:

With MyChart.PlotArea.Format.Shadow .Visible = msoTrue .Blur = 3

.Transparency = 0.6 .OffsetX = 1 .OffsetY = 1 End With

If an object has no fill, applying a shadow to the object has no visible effect. For example, a chart's title usually has a transparent background (no fill color). To apply a shadow to an object that has no fill, you must first add a fill color. This example applies a white fill to the chart's title and then adds a shadow:

MyChart.ChartTitle.Format.Fill.BackColor.RGB = RGB(255, 255, 255) With MyChart.ChartTitle.Format.Shadow .Visible = msoTrue .Blur = 3

.Transparency = 0.3 .OffsetX = 2 .OffsetY = 2 End With

ADDING A BEVEL

Adding a bevel to a chart can provide an interesting 3-D effect. Figure 18-8 shows a chart with a beveled chart area. To add the bevel, I used the ThreeD property to access the ThreeDFormat object. The code that added the bevel effect is:

Sa les

40.000 1

¡lliilii

Jjh Ff- b Mpr Apr May Jun

Figure 18-8: This chart has a bevel effect.

With MyChart.ChartArea.Format.ThreeD .Visible = msoTrue .BevelTopType = msoBevelDivot .BevelTopDepth = 12 .BevelTopInset = 32 E nd With

CROSS- Chapter 30 contains some additional charting examples that deal with color.

REFERENCE

4 PREY

NEXT

Changing the Data Used in a Chart

The examples so far in this chapter have used the SourceData property to specify the complete data range for a chart. In many cases, you'll want to adjust the data used by a particular chart series. To do so, access the Values property of the Series object. The Series object also has an XValues property that stores the category axis values.

Understanding a Chart's SERIES Formula

The data used in each series in a chart is determined by its SERIES formula. When you select a data series in a chart, the SERIES formula appears in the formula bar. This is not a real formula: In other words, you can't use it in a cell, and you can't use worksheet functions within the SERIES formula. You can, however, edit the arguments in the SERIES formula.

A SERIES formula has the following syntax:

=SERIES(series_name, category_labels, values, order, sizes)

The arguments that you can use in the SERIES formula are

■ series_name: (Optional) A reference to the cell that contains the series name used in the legend. If the chart has only one series, the name argument is used as the title. This argument can also consist of text in quotation marks. If omitted, Excel creates a default series name (for example, Series 1).

■ category_labels: (Optional) A reference to the range that contains the labels for the category axis. If omitted, Excel uses consecutive integers beginning with 1. For XY charts, this argument specifies the * values. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values (or text in quotation marks) enclosed in curly brackets.

■ values: (Required) A reference to the range that contains the values for the series. For XY charts, this argument specifies the Y values. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values enclosed in curly brackets.

■ order: (Required) An integer that specifies the plotting order of the series. This argument is relevant only if the chart has more than one series. For example, in a stacked column chart, this parameter determines the stacking order. Using a reference to a cell is not allowed.

■ sizes: (Only for bubble charts) A reference to the range that contains the values for the size of the bubbles in a bubble chart. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of values enclosed in curly brackets.

Range references in a SERIES formula are always absolute, and they always include the sheet name. For example:

A range reference can consist of a noncontiguous range. If so, each range is separated by a comma, and the argument is enclosed in parentheses. In the following SERIES formula, the values range consists of B2:B3 and B5:B7:

You can substitute range names for the range references. If you do so (and the name is a workbook-level name), Excel changes the reference in the SERIES formula to include the workbook. For example:

=SERIES(Sheet1!$B$1,,budget.xlsx!CurrentData,1)

Note The Values property corresponds to the third argument of the SERIES formula, and the XValues property corresponds to the second argument of the SERIES formula. See the sidebar, "Understanding a Chart's SERIES Formula."

Changing chart data based on the active cell

Figure 18-9 shows a chart that's based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

I like your TV ads

Figure 18-9 shows a chart that's based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.

I like your TV ads

Mouse- Pads Inc.

lUHft 1

tr«

:ii..ti— 1

iqnm Inrjiwir^ n

IK

n*

nS

»>

tm rim ** «nni-n^l

UK

UK

its

îï*r

Romai MtfU-niiirfaiiEj

*

ilk

m

Ul

im

ftu jk lûflmtfi liy ■.

H

m

A

<91

m

■ i br vtor

u%

JS*

HS

A

m 'nirdy

m

»

in

■on

m

A

WV

m

-: flitTtH JJ* fcro^wtJtdtk

»

M

lit

Ml

■H

m

1A

AS

Ufc

M

-Jdf A r!>-.-- V «■!■'■ iJfilKl.ldl

■A

I*

Iv-H

ijfc

ITS

1

Ml

IDk

M

1 1 "V ivij- miFj;'.' ■ d:

TA

1M

IS

ÏJ*

IIS

14^1 dii^IV P!KWt

il*

tfl

Jt*

Bfc

EM

(Imh.tIU *T>vfnrird

US

rt

H4)

n*

14

l w aAi ru c inn -Mi yvi r « i»pi

HI

m

nx

Figure 18-9: This chart always displays the data from the row of the active cell.

This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If CheckBox1 Then Call UpdateChart End Sub

In other words, every time the user moves the cell cursor, the Worksheet_ SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:

Sub UpdateChart()

Dim ChtObj As ChartObject Dim UserRow As Long

Set ChtObj = ActiveSheet.ChartObj ects(1) UserRow = ActiveCell.Row

If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then ChtObj.Visible = False

Else

ChtObj.Chart.SeriesCollection(1).Values = _

Range(Cells(UserRow, 2), Cells(UserRow, 6)) ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text ChtObj.Visible = True End If End Sub

The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn't have data, the ChartObject object is hidden, and the underlying text is visible ("Cannot display chart"). Otherwise, the code sets the Values property for the Series object to the range in columns 2-6 of the active row. It also sets the ChartTitle object to correspond to the text in column A.

CD- This example, named chart active cell.xlsm, is available on the companion ROM CD-ROM.

Using VBA to determine the ranges used in a chart

The previous example demonstrated how to use the Values property of a Series object to specify the data used by a chart series. This section discusses using VBA macros to identify the ranges used by a series in a chart. For example, you might want to increase the size of each series by adding a new cell to the range.

Following is a description of three properties that are 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 * 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 might 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 * 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, an array is always returned. Unfortunately, the object model provides no way to get a Range object used by a Series object.

One possible solution is to write code to parse the SERIES formula and extract the range addresses. This sounds simple, but it's actually a difficult task because a SERIES formula can be very complex. Following are a few examples of valid SERIES formulas.

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1) =SERIES(,,Sheet1!$B$2:$B$4,1)

=SERIES(,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1) =SERIES("Sales Summary",,Sheetl!$B$2:$B$4,1) =SERIES(,{"Jan","Feb","Mar"},Sheet1!$B$2:$B$4,1)

=SERIES(,(Sheet1!$A$2,Sheet1!$A$4),(Sheet1!$B$2,Sheet1!$B$4),1) =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1,Sheet1!$C$2:$C$4)

As you can see, a SERIES formula can have missing arguments, use arrays, and even use noncontiguous range addresses. And, to confuse the issue even more, a bubble chart has an additional argument (for example, the last SERIES formula in the preceding list). Attempting to parse the arguments is certainly not a trivial programming task.

I spent a lot of time working on this problem, and I eventually arrived at a solution. The trick involves evaluating the SERIES formula by using a dummy function. This function accepts the same arguments as a SERIES formula and returns a 2 * 5 element array that contains all the information in the SERIES formula.

I simplified the solution by creating four custom VBA functions, each of which accepts one argument (a reference to a Series object) and returns a two-element array. These functions are the following:

■ seriesname_from_series: The first array element contains a string that describes the data type of the first SERIES argument (Range, Empty, or String). The second array element contains a range address, an empty string, or a string.

■ xvalues_from_series: The first array element contains a string that describes the data type of the second SERIES argument (Range, Array, Empty, or String). The second array element contains a range address, an array, an empty string, or a string.

■ values_from_series: The first array element contains a string that describes the data type of the third SERIES argument (Range or Array). The second array element contains a range address or an array.

■ bubblesize_from_series: The first array element contains a string that describes the data type of the fifth SERIES argument (Range, Array, or Empty). The second array element contains a range address, an array, or an empty string. This function is relevant only for bubble charts.

Note that I did not create a function to get the fourth SERIES argument (plot order). This argument can be obtained directly by using the PlotOrder property of the Series object.

CD- The VBA code for these functions is too lengthy to be listed here, but the code is available ROM on the companion CD-ROM in a file named get series ranges .xlsm. These functions are documented in such a way that they can be easily adapted to other situations.

The following example demonstrates the values_from_series function. It displays the address of the values range for the first series in the active chart.

Sub ShowValueRange() Dim Ser As Series Dim x As Variant

Set Ser = ActiveChart.SeriesCollection(1) x = VALUES_FROM_SERIES(Ser) If x(1) = "Range" Then

MsgBox Range(x(2)).Address End If End Sub

The variable x is defined as a variant and will hold the two-element array that's returned by the values_from_series function. The first element of the x array contains a string that describes the data type. If the string is Range, the message box displays the address of the range contained in the second element of the x array.

Figure 18-10 shows another example. The chart has three data series. Buttons on the sheet execute macros that expand and contract each of the data ranges.

I lJ

Figure 18-10: This workbook demonstrates how to expand and contract the chart series by using VBA macros.

The ContractAllSeries procedure is listed below. This procedure loops through the seriesCollection collection and uses the xvalue_from_series and the values_ from_series functions to retrieve the current ranges. It then uses the Resize method to decrease the size of the ranges.

Sub ContractAllSeries() Dim s As Series Dim Result As Variant Dim DRange As Range

For Each s In ActiveSheet.ChartObjects(1).Chart.SeriesCollection Result = XVALUES_FROM_SERIES(s) If Result(1) = "Range" Then

Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then

Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.XValues = DRange End If End If

Result = VALUES_FROM_SERIES(s) If Result(1) = "Range" Then

Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then

Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.Values = DRange End If End If Next s End Sub

The ExpandAllSeries procedure is very similar. When executed, it expands each range by one cell.

Figure 18-10 shows another example. The chart has three data series. Buttons on the sheet execute macros that expand and contract each of the data ranges.

Using VBA to Display Arbitrary Data Labels on a Chart

One of the most frequent complaints about Excel's charting is its inflexible data labeling feature. For example, consider the XY chart in Figure 18-11. It would be useful to display the associated name for each data point. However, you can search all day, and you'll never find the Excel command that lets you do this automatically. Such a command doesn't exist. Data labels are limited to the data values only -unless you want to edit each data label manually and replace it with text (or a formula) of your choice.

A

1

t

: 1

IhnH'

AlllT

RKlwd

LM

]LS

hd

¿09

:i)

liJ

us

¿35

n

^ji.-in

1"

all

ITT

CAlrfli

m

a>

DM

m

LM

ja

i]

if

u

ii

: '

before And After ResuUj *

Figure 18-11: An XY chart with no data labels.

The DataLabelsFromRange procedure works with the first chart on the active sheet. It prompts the user for a range and then loops through the Points collection and changes the Text property to the values found in the range.

Sub DataLabelsFromRange() Dim DLRange As Range Dim Cht As Chart

Dim i As Integer, Pts As Integer

' Specify chart

Set Cht = ActiveSheet.ChartObj ects(1) .Chart

' Prompt for a range

On Error Resume Next

Set DLRange = Application.InputBox _

(prompt:="Range for data labels?", Type:=8) If DLRange Is Nothing Then Exit Sub On Error GoTo 0

' Add data labels

Cht.SeriesCollection(1) .ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False

' Loop through the Points, and set the data labels Pts = Cht.SeriesCollection(1).Points.Count For i = 1 To Pts

Cht.SeriesCollection(l).

Points(i).DataLabel.Text = DLRange(i)

Next i End Sub

CD- This example, named ® data labels .xlsm, is available on the companion CD-ROM. ROM

Figure 18-12 shows the chart after running the DataLabelsFromRange procedure and specifying A2:A9 as the data range.

Before And After Results

» Pm*

4 John

• chuck # George

4 DM«

+ £r»ri

-1

+ flkhsrd i

-'

i-1

Before

Before

Figure 18-12: This XY chart has data labels, thanks to a VBA procedure.

A data label in a chart can also consist of a link to a cell. To modify the DataLabelsFromRange procedure so it creates cell links, just change the statement within the For-Next loop to:

Cht.SeriesCollection(1).Points(i).DataLabel.Text = _ "=" & "'" & DLRange.Parent.Name & "'!" & _ DLRange(i).Address(ReferenceStyle:=xlR1C1)

Note The preceding procedure is rather crude and does very little error checking. In addition, it only works with the first Series object. The Power Utility Pak add-in (which you can obtain by using the coupon in the back of the book) includes a much more sophisticated chart data-labeling utility.

4 PREY

NEXT

Displaying a Chart in a UserForm

In Chapter 15, I describe a way to display a chart in a UserForm. The technique saves the chart as a GIF file and then loads the GIF file into an Image control on the UserForm.

The example in this section uses that same technique but adds a new twist: The chart is created on the fly and uses the data in the row of the active cell. Figure 18-13 shows an example.

Mirf hMlfl»gW feyvrric -I

I j J E j P J Discount Moui«Pad& CWIMIW Swrviv RiMilli Hrtqjv

3 13* lA JHfli

Si zr H-JW < W** I "H! 11IH n*4 'lu . l «. Jir ii"««.' L s, pf'i - v I kU y DJ" A-rt* : nr

^mple^t*; ** DnpKYlti If* "Ptipful L-i^j'i.-, c c j in: >'i..<.'i 'l-jjodLIe Bfic i-jji* i -ft i i: i ■'■<

I fek.4 yi>'X "V JEtl v?y jf ■ j Cf 7i:d jcl i

OiMgitv Cfrripfi*

3fHi_m

Store hours are convenient lis

0 0

Post a comment