Microsoft took the ambitious leap to attempt to replace the entire charting engine on which they relied for the past 15 years. Although I appreciate their desire to do something completely new, there was not quite enough development time to completely finish the task. This leaves us with three problems:
Code for all the new features is not backward compatible with previous versions of Excel. There is a nagging subset of features that work in the current version of Excel but do not work in earlier versions of Excel. However, all the good features in charting are new to Excel 2007; consequently, hardly any of the code in this chapter is backward compatible with Excel 97–Excel 2003. If you need to write code to create Excel 2003 charts, you can use the examples from Chapter 10 of our book VBA and Macros for Microsoft Excel (ISBN 978-0-7897-3129-0, Que Publishing). The project file from that chapter is available at www.mrexcel.com/vba2007data.html.
Microsoft was able to finish some work on the macro recorder for the new charting features. The macro recorder can record most actions on the Design and Layout ribbons, but it completely ignores actions on the Format ribbon or in the Format dialog boxes. VBA code is available to micro-format chart elements, but you have to write code to perform actions on the Format ribbon from scratch. You have this book as a reference to help with that endeavor. You should also review “Using the Watch Window to Discover Object Settings,” later in this chapter for a technique to learn VBA code that is not recorded by the macro recorder.
There are bugs in the Excel 2007 charting engine. In some cases, editing the SERIES
function causes Excel to crash. In other cases, the SERIES
function won’t even appear. Try creating a chart below row 1100. Page down, page back up, and the chart will render in strange ways. To mitigate this problem, watch for an Office 2007 service release in 2008 and download it as soon as it is available.
Charts have been completely rewritten in Excel 2007. Most code from Excel 2003 will continue to work in Excel 2007. However, if you write code to take advantage of the new charting features, that code will not be backward compatible with Excel 2003.
The following are some of the new methods and features available in Excel 2007:
ApplyLayout
—. This method applies one of the chart layouts available on the Design ribbon.
SetElement
—. This method chooses any of the built-in element choices from the Layout ribbon.
ChartFormat
—. This object enables you to change the fill, glow, line, reflection, shadow, soft edge, or 3D format of most individual chart elements. This is similar to settings on the Format ribbon.
AddChart
—. This method enables you to add a chart to an existing worksheet.
If you go back far enough in Excel history, you find that all charts used to be created as their own chart sheets. Then, in the mid-1990s, Excel added the amazing capability to embed a chart right onto an existing worksheet. This allowed a report to be created with tables of numbers and charts all on the same page, something we take for granted today.
These two different ways of dealing with charts have made it necessary for us to deal with two separate object models for charts. When a chart is on its own standalone chart sheet, you are dealing with a Chart object. When a chart is embedded in a worksheet, you are dealing with a ChartObject object. Excel 2007 introduces a third evolutionary branch because objects on a worksheet are also a member of the Shapes collection.
In Excel 2003, to reference the color of the chart area for an embedded chart, you would have to refer to the chart in this manner:
Worksheets("Jan").ChartObjects("Chart 1").Chart.ChartArea.Interior.ColorIndex = 4
In Excel 2007, you can instead use the Shapes collection:
Worksheets("Jan").Shapes("Chart 1").Chart.ChartArea.Interior.ColorIndex = 4
In any version of Excel, if a chart is on its own chart sheet, you don’t have to specify the container; you can simply refer to the Chart object:
Sheets("Chart1").ChartArea.Interior.ColorIndex = 4
In earlier versions of Excel, you used the Charts.Add
command to add a new chart. You then specified the source data, the type of chart, and whether the chart should be on a new sheet or embedded on an existing worksheet. The first three lines of the following code create a clustered column chart on a new chart sheet. The fourth line moves the chart back to be an embedded object in Sheet1
:
Charts.Add ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1:E4") ActiveChart.ChartType = xlColumnClustered ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
If you plan to share your macros with people who still use Excel 2003, you should use the Charts.Add
method. However, if your application will only be running in Excel 2007, you can use the new AddChart
method. The code for the AddChart
method can be as simple as the following:
' Create chart on the current sheet ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range("A1:E4") ActiveChart.ChartType = xlColumnClustered
Or, you can specify the chart type, size, and location as part of the AddChart
method, as described in the next section.
The AddChart
method has additional parameters you can use to specify the type of chart, the chart’s location on the worksheet, and the size of the chart.
The location and size of a chart are specified in points (72 points = 1 inch). For example, the Top
parameter requires the number of points from the top of row 1 to the top edge of the worksheet.
The following code creates a chart that roughly covers the range C11:J30:
Sub SpecifyLocation() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Shapes.AddChart(xlColumnClustered, _ Left:=100, Top:=150, _ Width:=400, Height:=300).Select ActiveChart.SetSourceData Source:=WS.Range("A1:E4") End Sub
It would require a lot of trial and error to randomly figure out the exact distance in points to cause a chart to line up with a certain cell. Luckily, you can ask VBA to tell you the distance in points to a certain cell. If you ask for the Left
property of any cell, you find the distance to the top-left corner of that cell. You can also ask for the width of a range or the height of a range. For example, the following code creates a chart in exactly C11:J30:
Sub SpecifyExactLocation() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Shapes.AddChart(xlColumnClustered, _ Left:=WS.Range("C11").Left, _ Top:=WS.Range("C11").Top, _ Width:=WS.Range("C11:J11").Width, _ Height:=WS.Range("C11:C30").Height).Select ActiveChart.SetSourceData Source:=WS.Range("A1:E4") End Sub
In this case, you are not moving the location of the Chart object; rather, you are moving the location of the container that contains the chart. In Excel 2007, it is either the ChartObject or the Shape object. If you try to change the actual location of the chart, you move it within the container. Because you can actually move the chart area a few points in either direction inside the container, the code will run, but you will not get the desired results.
To move a chart that has already been created, you can reference either ChartObject or the Shape and change the Top
, Left
, Width
, and Height
properties as shown in the following macro:
Sub MoveAfterTheFact() Dim WS As Worksheet Set WS = Worksheets("Sheet1") With WS.ChartObjects("Chart 9") .Left = WS.Range("C21").Left .Top = WS.Range("C21").Top .Width = WS.Range("C1:H1").Width .Height = WS.Range("C21:C25").Height End With End Sub
When a new chart is created, it is given a sequential name, such as Chart 1. If you select a chart and then look in the name box, you see the name of the chart. In Figure 11.1, the name of the chart is Chart 16. This does not mean that there are 16 charts on the worksheet. In this particular case, many individual charts have been created and deleted.
This means that on any given day that your macro runs, the Chart object might have a different name. If you need to reference the chart later in the macro, perhaps after you have selected other cells and the chart is no longer active, you might ask VBA for the name of the chart and store it in a variable for later use, as shown here:
Sub RememberTheName() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Shapes.AddChart(xlColumnClustered, _ Left:=WS.Range("C11").Left, _ Top:=WS.Range("C11").Top, _ Width:=WS.Range("C11:J11").Width, _ Height:=WS.Range("C11:C30").Height _ ).Select ActiveChart.SetSourceData Source:=WS.Range("A1:E4") ' Remember the name in a variable ThisChartObjectName = ActiveChart.Parent.Name ' more lines of code... ' then later in the macro, you need to re-assign the chart With WS.Shapes(ThisChartObjectName) .Chart.SetSourceData Source:=WS.Range("A20:E24"), PlotBy:=xlColumns .Top = WS.Range("C26").Top End With End Sub
In the preceding macro, the variable ThisChartObjectName
contains the name of the Chart object. This method works great if your changes will happen later in the same macro. However, after the macro finishes running, the variable will be out of scope, and you won’t be able to access the name later.
If you want to be able to remember a chart name, you could store the name in an out-of-the-way cell on the worksheet. The first macro here stores the name in cell Z1, and the second macro then later modifies the chart using the name stored in cell Z1:
Sub StoreTheName() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Shapes.AddChart(xlColumnClustered, _ Left:=WS.Range("C11").Left, _ Top:=WS.Range("C11").Top, _ Width:=WS.Range("C11:J11").Width, _ Height:=WS.Range("C11:C30").Height _ ).Select ActiveChart.SetSourceData Source:=WS.Range("A1:E4") Range("Z1").Value = ActiveChart.Parent.Name End Sub
After the previous macro stored the name in cell Z1, the following macro will use the value in Z1 to figure out which macro to change:
Sub ChangeTheChartLater() Dim WS As Worksheet Set WS = Worksheets("Sheet1") MyName = WS.Range("Z1").Value With WS.Shapes(MyName) .Chart.SetSourceData Source:=WS.Range("A20:E24"), PlotBy:=xlColumns .Top = WS.Range("C26").Top End With End Sub
If you need to modify a preexisting chart—such as a chart that you did not create—and there is only one chart on the worksheet, you can use this line of code:
WS.ChartObjects(1).Chart.Interior.ColorIndex = 4
If there are many charts and you need to find the one with the upper-left corner located in cell A4, you could loop through all the Chart objects until you find one in the correct location, like this:
For each Cht in ActiveSheet.ChartObjects If Cht.TopLeftCell.Address = "$A$4" then Cht.Interior.ColorIndex = 4 end if Next Cht
With charts in Excel 2007, there are three levels of chart changes. The global chart settings—chart type and style—are on the Design ribbon. Selections from the built-in element settings appear on the Layout ribbon. You make micro-changes by using the Format ribbon.
The macro recorder in Excel 2007 does a great job of recording changes on the Design and Layout ribbon, so if you need to make certain changes, you can quickly record a macro and then copy its code.
There are 73 built-in chart types in Excel 2007. To change a chart to one of the 73 types, you use the ChartType
property. This property can either be applied to a chart or to a series within a chart. Here’s an example that changes the type for the entire chart:
ActiveChart.ChartType = xlBubble
To change the second series on a chart to a line chart, you use this:
ActiveChart.Series(2).ChartType = xlLine
Table 11.1 lists the 73 chart type constants that you can use to create various charts. The sequence of Table 11.1 matches the sequence of the charts in the Chart Type dialog.
Table 11.1. Chart Types for Use in VBA
Chart Type | Constant | |
---|---|---|
Clustered Column |
| |
Stacked Column |
| |
100% Stacked Column |
| |
3-D Clustered Column |
| |
Stacked Column in 3-D |
| |
100% Stacked Column in 3-D | x | |
3-D Column |
| |
Clustered Cylinder |
| |
Stacked Cylinder |
| |
100% Stacked Cylinder |
| |
3-D Cylinder |
| |
Clustered Cone |
| |
Stacked Cone |
| |
100% Stacked Cone |
| |
3-D Cone |
| |
Clustered Pyramid |
| |
Stacked Pyramid |
| |
100% Stacked Pyramid |
| |
3-D Pyramid |
| |
Line |
| |
Stacked Line |
| |
100% Stacked Line |
| |
Line with Markers |
| |
Stacked Line with Markers |
| |
100% Stacked Line with Markers |
| |
3-D Line |
| |
Pie |
| |
Pie in 3-D |
| |
Pie of Pie |
| |
Exploded Pie |
| |
Exploded Pie in 3-D |
| |
Bar of Pie |
| |
Clustered Bar |
| |
Stacked Bar |
| |
100% Stacked Bar |
| |
Clustered Bar in 3-D |
| |
Stacked Bar in 3-D |
| |
100% Stacked Bar in 3-D |
| |
Clustered Horizontal Cylinder |
| |
Stacked Horizontal Cylinder |
| |
100% Stacked Horizontal Cylinder |
| |
Clustered Horizontal Cone |
| |
Stacked Horizontal Cone |
| |
100% Stacked Horizontal Cone |
| |
Clustered Horizontal Pyramid |
| |
Stacked Horizontal Pyramid |
| |
100% Stacked Horizontal Pyramid |
| |
Area |
| |
Stacked Area |
| |
100% Stacked Area |
| |
3-D Area |
| |
Stacked Area in 3-D |
| |
100% Stacked Area in 3-D |
| |
Scatter with only Markers |
| |
Scatter with Smooth Lines and Markers |
| |
Scatter with Smooth Lines |
| |
Scatter with Straight Lines and Markers |
| |
Scatter with Straight Lines |
| |
High-Low-Close |
| |
Open-High-Low-Close |
| |
Volume-High-Low-Close |
| |
Volume-Open-High-Low-Close |
| |
3-D Surface |
| |
Wireframe 3-D Surface |
| |
Contour |
| |
Wireframe Contour |
| |
Doughnut |
| |
Exploded Doughnut |
| |
Bubble |
| |
Bubble with a 3-D Effect |
| |
Radar |
| |
Radar with Markers |
| |
Filled Radar |
|
Excel 2007 allows you to create a custom chart template with all your preferred settings, such as colors and fonts. This is a great technique for saving time when you are creating a chart with a lot of custom formatting.
A VBA macro can make use of a custom chart template, provided that you plan on distributing the custom chart template to each person who will run your macro.
In Excel 2007, you save custom chart types as .crtx
files and store them in the %appdata%MicrosoftTemplatesCharts
folder.
To apply a custom chart type, you use the following:
ActiveChart.ApplyChartTemplate ("MyChart.crtx")
If the chart template does not exist, VBA returns an error. If you would like Excel to simply continue without displaying a debug error, you can turn off an error handler before the code and turn it back on when you are done. Here’s how you do that:
On Error Resume Next ActiveChart.ApplyChartTemplate ("MyChart.crtx") On Error GoTo 0 ' that final character is a zero
Two galleries—the Chart Layout gallery and the Styles gallery—make up the bulk of the Design ribbon.
The Chart Layout gallery offers from 4 to 12 combinations of chart elements. These combinations are different for various chart types. When you look at the gallery shown in Figure 11.2, the ToolTips for the layouts show that the layouts are imaginatively named Layout 1 through Layout 11.
To apply one of the built-in layouts in a macro, you have to use the ApplyLayout
method with a number from 1 through 12 to correspond to the built-in layouts. The following code will apply Layout 1 to the active chart:
ActiveChart.ApplyLayout 1
Whereas line charts offer 12 built-in layouts, other types such as radar charts offer as few as 4 built-in layouts. If you attempt to specify apply a layout number that is larger than the layouts available for the current chart type, Excel returns a runtime error 5. Unless you just created the active chart in the same macro, there is always the possibility that the person running the macro changed your line charts to radar charts, so include some error handling before you use the ApplyLayout
command.
Clearly, to effectively use a built-in layout, you must have actually built a chart by hand and found a layout that you actually like.
As shown in Figure 11.3, the Styles gallery contains 48 styles. These styles are also numbered sequentially, with Styles 1 through 8 in row 1, Styles 9 through 16 in row 2, and so on. These styles actually follow a bit of a pattern:
Styles 1, 9, 17, 25, 33, and 41 (that is, the styles in column 1) are monochrome.
Styles 2, 10, 18, 26, 34, and 42 (that is, the styles in column 2) use different colors for each point.
All the other styles use hues of a particular theme color.
Styles 1 through 8 are simple styles.
Styles 17 through 24 use moderate effects.
Styles 33 through 40 have intense effects.
Styles 41 through 48 appear on a dark background.
If you are going to mix styles in a single workbook, consider staying within a single row or a single column of the gallery.
To apply a style to a chart, you use the ChartStyle
property, assigning it a value from 1
to 48
:
ActiveChart.ChartStyle = 1
The ChartStyle
property changes the colors in the chart. However, a number of formatting changes from the Format ribbon do not get overwritten when you change the ChartStyle
property. For example, in Figure 11.4, the second series previously had a glow applied and the third series had a clear glass bevel applied. Running the preceding code did not clear that formatting.
To clear any previous formatting, you use the ClearToMatchStyle
method:
ActiveChart.ChartStyle = 1 ActiveChart.ClearToMatchStyle
The Layout ribbon contains a number of built-in settings. Figure 11.5 shows a few of the built-in menu items for the Legend tab. There are similar menus for each of the icons in the figure.
If you use a built-in menu item to change the titles, legend, labels, axes, gridlines, or background, it is probably handled in code that uses the SetElement
method, which is new in Excel 2007.
SetElement
does not work with the More choices at the bottom of each menu. It also does not work with the 3-D Rotation button. Other than that, you can use SetElement
to change everything in the Labels, Axes, Background, and Analysis groups.
The macro recorder always works for the built-in settings on the Layout ribbon. If you don’t feel like looking up the proper constant in this book, you can always quickly record a macro.
The SetElement
method is followed by a constant that specifies which menu item to select. For example, if you want to choose Show Legend at Left, you can use this code:
ActiveChart.SetElement msoElementLegendLeft
Table 11.2 shows all the available constants that you can use with the SetElement
method. These are in roughly the same order as they appear on the Layout ribbon.
Table 11.2. Constants Available with SetElement
Layout Ribbon Icon | Chart Element Constant |
---|---|
Chart Title |
|
Chart Title |
|
Chart Title |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Axis Titles |
|
Legend |
|
Legend |
|
Legend |
|
Legend |
|
Legend |
|
Legend |
|
Legend |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Labels |
|
Data Table |
|
Data Table |
|
Data Table |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
Axis |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
GridLines |
|
Plot Area |
|
Plot Area |
|
Chart Wall |
|
Chart Wall |
|
Chart Floor |
|
Chart Floor |
|
Trendline |
|
Trendline |
|
Trendline |
|
Trendline |
|
Trendline |
|
Lines |
|
Lines |
|
Lines |
|
Lines |
|
Lines |
|
Up/Down Bars |
|
Up/Down Bars |
|
Error Bar |
|
Error Bar |
|
Error Bar |
|
Error Bar |
|
The Layout ribbon’s built-in menus enable you to add a title above a chart, but they don’t enable you to change the characters in a chart title or axis title.
In the user interface, you can simply double-click the chart title text and type a new title to change the title. Unfortunately, the macro recorder does not record this action.
To specify a chart title, you must type this code:
ActiveChart.ChartTitle.Caption = "My Chart"
Similarly, you can specify the axis titles by using the Caption
property. The following code changes the axis title along the category axis:
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Caption = "Months"
In Excel 2007, the macro recorder does not record any actions that happen in the Format ribbon or in the More dialog boxes on the Layout ribbon. This is incredibly frustrating. It is particularly frustrating because Excel 2003 could record these changes with the macro recorder. One solution, if you still have Excel 2003 installed, is to format your chart in Excel 2003 while the macro recorder is on. You can then use that code in Excel 2007, although you then cannot make use of all the new formatting features. For information on discovering charting elements without the assistance of the macro recorder, see the section “Using the Watch Window to Discover Object Settings,” later in this chapter.
Excel 2007 introduces a new object called the ChartFormat object. This object contains the settings for Fill
, Glow
, Line
, PictureFormat
, Shadow
, SoftEdge
, TextFrame2
, and ThreeD
. You can access the ChartFormat object by using the Format
method on many chart elements. Table 11.3 lists a sampling of chart elements that can be formatted using the Format
method.
Table 11.3. Chart Elements to Which Formatting Applies
Chart Element | VBA to Refer to This Chart Element |
---|---|
Chart Title |
|
Axis Title - Category |
|
Axis Title - Value |
|
Legend |
|
Data Labels for Series 1 |
|
Data Labels for Point 2 |
|
Data Table |
|
Axes - Horizontal |
|
Axes - Vertical |
|
Axis - Series (Surface Charts Only) |
|
Major Gridlines |
|
Minor Gridlines |
|
Plot Area |
|
Chart Area |
|
Chart Wall |
|
Chart Back Wall |
|
Chart Side Wall |
|
Chart Floor |
|
Trendline for Series 1 |
|
Droplines |
|
Up/Down Bars |
|
Error Bars |
|
Series(1) |
|
Series(1) DataPoint |
|
The Format
method is the gateway to settings for Fill
, Glow
, and so on. Each of those objects has different options. The following sections give examples of how to set up each type of format.
As shown in Figure 11.6, the Shape Fill drop-down on the Format ribbon allows you to choose a single color, a gradient, a picture, or a texture for the fill.
To apply a specific color, you can use the RGB (red, green, blue) setting. To create a color, you specify a value from 0
to 255
for levels of red, green, and blue. The following code applies a simple blue fill:
Dim cht As Chart Dim upb As UpBars Set cht = ActiveChart Set upb = cht.ChartGroups(1).UpBars upb.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
If you would like an object to pick up the color from a specific theme accent color, you use the ObjectThemeColor
property. The following code changes the bar color of the first series to accent color 6 (which is an orange color in the Office theme but might be another color if the workbook is using a different theme):
Sub ApplyThemeColor() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(1) ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6 End Sub
To apply a built-in texture, you use the PresetTextured
method. The following code applies a green marble texture to the second series, but there are 20 different textures that you can apply:
Sub ApplyTexture() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(2) ser.Format.Fill.PresetTextured (msoTextureGreenMarble) End Sub
When you type PresetTextured
followed by an open parenthesis, the VB Editor offers a complete list of possible texture values.
To fill the bars of a data series with a picture, you use the UserPicture
method and specify the path and filename of an image on the computer, as in the following example:
Sub FormatWithPicture() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(1) MyPic = "C:PodCastTitle1.jpg" ser.Format.Fill.UserPicture (MyPic) End Sub
Gradients are more difficult to specify than fills. Excel 2007 offers three methods that help you set up the common gradients. The OneColorGradient
and TwoColorGradient
methods require that you specify a gradient direction such as msoGradientFromCorner
. You can then specify one of four styles, numbered 1
through 4
, depending on whether you want the gradient to start at the top left, top right, bottom left, or bottom right. After using a gradient method, you need to specify the ForeColor
and the BackColor
settings for the object. The following macro sets up a two-color gradient using two theme colors:
Sub TwoColorGradient() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(1) MyPic = "C:PodCastTitle1.jpg" ser.Format.Fill.TwoColorGradient msoGradientFromCorner, 3 ser.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent6 ser.Format.Fill.BackColor.ObjectThemeColor = msoThemeColorAccent2 End Sub
When using the OneColorGradient
method, you specify a direction, a style (1
through 4
), and a darkness value between 0
and 1
(0
for darker gradients or 1
for lighter gradients).
When using the PresetGradient
method, you specify a direction, a style (1
through 4
), and the type of gradient (for example, msoGradientBrass
, msoGradientLateSunset
, or msoGradientRainbow
). Again, as you are typing this code in the VB Editor, the AutoComplete tool provides a complete list of the available preset gradient types.
The LineFormat object formats either a line or the border around an object. You can change numerous properties for a line, such as the color, arrows, dash style, and so on.
The following macro formats the trendline for the first series in a chart:
Sub FormatLineOrBorders() Dim cht As Chart Set cht = ActiveChart With cht.SeriesCollection(1).Trendlines(1).Format.Line .DashStyle = msoLineLongDashDotDot .ForeColor.RGB = RGB(50, 0, 128) .BeginArrowheadLength = msoArrowheadShort .BeginArrowheadStyle = msoArrowheadOval .BeginArrowheadWidth = msoArrowheadNarrow .EndArrowheadLength = msoArrowheadLong .EndArrowheadStyle = msoArrowheadTriangle .EndArrowheadWidth = msoArrowheadWide End With End Sub
When you are formatting a border, the arrow settings are not relevant, so the code is shorter than the code for formatting a line. The following macro formats the border around a chart:
Sub FormatBorder() Dim cht As Chart Set cht = ActiveChart With cht.ChartArea.Format.Line .DashStyle = msoLineLongDashDotDot .ForeColor.RGB = RGB(50, 0, 128) End With End Sub
To create a glow, you have to specify a color and a radius. The radius value can be from 1
to 20
. A radius of 1
is barely visible, and a radius of 20
is often way too thick.
A glow is actually applied to the shape outline. If you try to add a glow to an object where the outline is set to None
, you cannot see the glow.
The following macro adds a line around the title and adds a glow around that line:
Sub AddGlowToTitle() Dim cht As Chart Set cht = ActiveChart cht.ChartTitle.Format.Line.ForeColor.RGB = RGB(255, 255, 255) cht.ChartTitle.Format.Line.DashStyle = msoLineSolid cht.ChartTitle.Format.Glow.Color.ObjectThemeColor = msoThemeColorAccent6 cht.ChartTitle.Format.Glow.Radius = 8 End Sub
A shadow is composed of a color, a transparency, and the number of points by which the shadow should be offset from the object. If you increase the number of points, it appears that the object is farther from the surface of the chart. The horizontal offset is known as OffsetX
, and the vertical offset is known as OffsetY
.
The following macro adds a light blue shadow to the box surrounding a legend:
Sub FormatShadow() Dim cht As Chart Set cht = ActiveChart With cht.Legend.Format.Shadow .ForeColor.RGB = RGB(0, 0, 128) .OffsetX = 5 .OffsetY = -3 .Transparency = 0.5 .Visible = True End With End Sub
No chart elements can have reflections applied. The Reflection settings on the Format ribbon are constantly grayed out when a chart is selected. Similarly, the ChartFormat object does not have a reflection object.
There are six levels of soft edge settings. The settings feather the edges by 1, 2.5, 5, 10, 25, or 50 points. The first setting is barely visible. The biggest settings are usually larger than most of the chart elements you are likely to format.
Microsoft says that the following is the proper syntax for SoftEdge
:
Chart.Seriess(1).Points(i).Format.SoftEdge.Type = msoSoftEdgeType1
However, msoSoftEdgeType1
and words like it are really variables defined by Excel. To try a cool trick, go to the VB Editor and open the Immediate window by pressing Ctrl+G. In the Immediate window, type Print msoSoftEdgeType2
and press Enter. The Immediate window tells you that using this word is equivalent to typing 2
. So, you could either use msoSoftEdgeType2
or the value 2
.
If you use msoSoftEdgeType2
, your code will be slightly easier to understand than if you use simply 2
. However, if you hope to format each point of a data series with a different format, you might want to use a loop such as this one, in which case it is far easier to use just the numbers 1
through 6
than msoSoftEdgeType1
through msoSoftEdgeType6
, as shown in this macro:
Sub FormatSoftEdgesWithLoop() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(1) For i = 1 To 6 ser.Points(i).Format.SoftEdge.Type = i Next i End Sub
It is a bit strange that the soft edges are defined as a fixed number of points. In a chart that is sized to fit an entire sheet of paper, a 10-point soft edge might work fine. However, if you resize the chart so that you can fit six charts on a page, a 10-point soft edge applied to all sides of a column might make the column completely disappear.
The 3-D settings handle three different menus on the Format ribbon. In the Shape Effects drop-down, settings under Preset, Bevel, and 3-D are all actually handled by the ThreeD object in the ChartFormat object. This section discusses settings that affect the 3-D rotation. The next section discusses settings that affect the bevel and 3-D format.
The methods and properties that can be set for the ThreeD object are very broad. In fact, the 3-D settings in VBA include more preset options than do the menus on the Format ribbon.
Figure 11.7 shows the presets available in the 3-D Rotation fly-out menu.
To apply one of the 3-D rotation presets to a chart element, you use the SetPresetCamera
method, as shown here:
Sub Assign3DPreset() Dim cht As Chart Dim shp As Shape Set cht = ActiveChart Set shp = cht.Shapes(1) shp.ThreeD.SetPresetCamera msoCameraIsometricLeftDown End Sub
Table 11.4 lists all the possible SetPresetCamera
values. If the first column indicates that it is a bonus or an Excel 2003 style, the value is a preset that is available in VBA but was not chosen by Microsoft to be included in the 3-D Rotation fly-out menu.
Table 11.4. 3-D Preset Formats and Their VBA Constant Values
Description | VBA Value | |
---|---|---|
Parallel group, row 1, column 1 | Isometric Left Down |
|
Parallel group, row 1, column 2 | Isometric Right Up |
|
Parallel group, row 1, column 3 | Isometric Top Up |
|
Parallel group, row 1, column 4 | Isometric Bottom Down |
|
Parallel group, row 2, column 1 | Isometric OffAxis1 Left |
|
Parallel group, row 2, column 2 | Isometric OffAxis1 Right |
|
Parallel group, row 2, column 3 | Isometric OffAxis1 Top |
|
Parallel group, row 2, column 4 | Isometric OffAxis2 Left |
|
Parallel group, row 3, column 1 | Isometric OffAxis2 Right |
|
Parallel group, row 3, column 2 | Isometric OffAxis2 Top |
|
Parallel group, bonus selection | Isometric Bottom Up |
|
Parallel group, bonus selection | Isometric Left Up |
|
Parallel group, bonus selection | Isometric OffAxis3 Bottom |
|
Parallel group, bonus selection | Isometric OffAxis3 Left |
|
Parallel group, bonus selection | Isometric OffAxis3 Right |
|
Parallel group, bonus selection | Isometric OffAxis4 Bottom |
|
Parallel group, bonus selection | Isometric OffAxis4 Left |
|
Parallel group, bonus selection | Isometric OffAxis4 Right |
|
Parallel group, bonus selection | Isometric Right Down |
|
Parallel group, bonus selection | Isometric Top Down |
|
Perspective group, row 1, column 1 | Perspective Front |
|
Perspective group, row 1, column 2 | Perspective Left |
|
Perspective group, row 1, column 3 | Perspective Right |
|
Perspective group, row 1, column 4 | Perspective Below |
|
Perspective group, row 2, column 1 | Perspective Above |
|
Perspective group, row 2, column 2 | Perspective Relaxed Moderately |
|
Perspective group, row 2, column 3 | Perspective Relaxed |
|
Perspective group, row 2, column 4 | Perspective Contrasting Left Facing |
|
Perspective group, row 3, column 1 | Perspective Contrasting Right Facing |
|
Perspective group, row 3, column 2 | Perspective Heroic Extreme Left Facing |
|
Perspective group, row 3, column 3 | Perspective Heroic Extreme Right Facing |
|
Perspective group, bonus selection | Perspective Above Left Facing |
|
Perspective group, bonus selection | Perspective Above Right Facing |
|
Perspective group, bonus selection | Perspective Heroic Left Facing |
|
Perspective group, bonus selection | Perspective Heroic Right Facing |
|
Perspective group, Excel 2003 styles | Legacy Perspective Bottom |
|
Perspective group, Excel 2003 styles | Legacy Perspective Lower Left |
|
Perspective group, Excel 2003 styles | Legacy Perspective Lower Right |
|
Perspective group, Excel 2003 styles | Legacy Perspective Front |
|
Perspective group, Excel 2003 styles | Legacy Perspective Left |
|
Perspective group, Excel 2003 styles | Legacy Perspective Right |
|
Perspective group, Excel 2003 styles | Legacy Perspective Top |
|
Perspective group, Excel 2003 styles | Legacy Perspective Upper Left |
|
Perspective group, Excel 2003 styles | Legacy Perspective Upper Right |
|
Oblique group, row 1, column 1 | Oblique Upper Left |
|
Oblique group, row 1, column 2 | Oblique Upper Right |
|
Oblique group, row 1, column 3 | Oblique Lower Left |
|
Oblique group, row 1, column 4 | Oblique Lower Right |
|
Oblique group, bonus selection | Oblique Bottom |
|
Oblique group, bonus selection | Oblique Left |
|
Oblique group, bonus selection | Oblique Right |
|
Oblique group, bonus selection | Oblique Top |
|
Oblique group, bonus selection | Orthographic Front |
|
Oblique group, Excel 2003 styles | Legacy Oblique Bottom |
|
Oblique group, Excel 2003 styles | Legacy Oblique Lower Left |
|
Oblique group, Excel 2003 styles | Legacy Oblique Lower Right |
|
Oblique group, Excel 2003 styles | Legacy Oblique Front |
|
Oblique group, Excel 2003 styles | Legacy Oblique Left |
|
Oblique group, Excel 2003 styles | Legacy Oblique Right |
|
Oblique group, Excel 2003 styles | Legacy Oblique Top |
|
Oblique group, Excel 2003 styles | Legacy Oblique Upper Left |
|
Oblique group, Excel 2003 styles | Legacy Oblique Upper Right |
|
If you prefer not to use the presets, you can explicitly control the rotation around the x-, y-, or z-axis. You can use the following properties and methods to change the rotation of an object:
RotationX
—. Returns or sets the rotation of the extruded shape around the x-axis, in degrees. This can be a value from -90
through 90
. A positive value indicates upward rotation; a negative value indicates downward rotation.
RotationY
—. Returns or sets the rotation of the extruded shape around the y-axis, in degrees. Can be a value from -90
through 90
. A positive value indicates rotation to the left; a negative value indicates rotation to the right.
RotationZ
—. Returns or sets the rotation of the extruded shape around the z-axis, in degrees. Can be a value from -90
through 90
. A positive value indicates upward rotation; a negative value indicates downward rotation.
IncrementRotationX
—. Changes the rotation of the specified shape around the x-axis by the specified number of degrees. You specify an increment from -90
to 90
. Negative degrees tip the object down, and positive degrees tip the object up. (You can use the RotationX
property to set the absolute rotation of the shape around the x-axis.)
IncrementRotationY
—. Changes the rotation of the specified shape around the y-axis by the specified number of degrees. A positive value tilts the object left, and a negative value tips the object right. (You can use the RotationY
property to set the absolute rotation of the shape around the y-axis.)
IncrementRotationZ
—. Changes the rotation of the specified shape around the z-axis by the specified number of degrees. A positive value tilts the object left, and a negative value tips the object right. (You can use the RotationZ
property to set the absolute rotation of the shape around the z-axis.)
IncrementRotationHorizontal
—. Changes the rotation of the specified shape horizontally by the specified number of degrees. You specify an increment from -90
to 90
to specify how much (in degrees) the rotation of the shape is to be changed horizontally. A positive value moves the shape left; a negative value moves it right.
IncrementRotationVertical
—. Changes the rotation of the specified shape vertically by the specified number of degrees. You specify an increment from -90
to 90
to specify how much (in degrees) the rotation of the shape is to be changed horizontally. A positive value moves the shape left; a negative value moves it right.
ResetRotation
—. Resets the extrusion rotation around the x-axis and the y-axis to 0
so that the front of the extrusion faces forward. This method does not reset the rotation around the z-axis.
There are 12 presets in the Bevel fly-out menu. These presets affect the bevel on the top face of the object. Usually in charts you see the top face; however, there are some bizarre rotations of a 3-D chart where you see the bottom face of charting elements.
The Format Shape dialog contains the same 12 presets as the Bevel fly-out, but allows you to apply the preset to the top or bottom face. You can also control the width and height of the bevel. The VBA properties and methods correspond to the settings on the 3-D Format category of the Format Shape dialog. (see Figure 11.8).
You set the type of bevel by using the BevelTopType
and BevelBottomType
properties. You can further modify the bevel type by setting the BevelTopInset
value to set the width and the BevelTopDepth
value to set the height. The following macro adds a bevel to the columns of Series 1:
Sub AssignBevel() Dim cht As Chart Dim ser As Series Set cht = ActiveChart Set ser = cht.SeriesCollection(1) ser.Format.ThreeD.Visible = True ser.Format.ThreeD.BevelTopType = msoBevelCircle ser.Format.ThreeD.BevelTopInset = 16 ser.Format.ThreeD.BevelTopDepth = 6 End Sub
The 12 possible settings for the bevel type are shown in Table 11.5; these settings correspond to the thumbnails in the fly-out menu. To turn off the bevel, you use msoBevelNone
.
Usually, the accent color used in a bevel is based on the color used to fill the object. If you would like control over the extrusion color, however, you first specify that the extrusion color type is custom and then specify either a theme accent color or an RGB color, as in the following example:
ser.Format.ThreeD.ExtrusionColorType = msoExtrusionColorCustom ' either use this: ser.Format.ThreeD.ExtrusionColor.ObjectThemeColor = msoThemeColorAccent1 ' or this: ser.Format.ThreeD.ExtrusionColor.RGB = RGB(255, 0, 0)
You use the Depth
property to control the amount of extrusion in the bevel, and you specify the depth in points. Here’s an example:
ser.Format.ThreeD.Depth = 5
For the contour, you can specify either a color and a size of the contour or both. You can specify the color as an RGB value or a theme color. You specify the size in points, using the ContourWidth
property. Here’s an example:
ser.Format.ThreeD.ContourColor.RGB = RGB(0, 255, 0) ser.Format.ThreeD.ContourWidth = 10
The Surface drop-downs are controlled by the following properties:
PresetMaterial
—. This contains choices from the Material drop-down.
PresetLighting
—. This contains choices from the Lighting drop-down.
LightAngle
—. This controls the angle from which the light is shining on the object.
The Material drop-down menu from the 3-D category of the Format dialog box offers 11 settings, although it appears that Microsoft designed a twelfth setting in the object model. It is not clear why Microsoft does not offer the SoftMetal
style in the dialog box, but you can use it in VBA. There are also three legacy styles in the object model that are not available in the Format dialog box. In theory, the new Plastic2
material is better than the old Plastic
material. Table 11.6 shows the settings for each thumbnail.
Table 11.6. VBA Constants for Material Types
Type | VBA Constant | Value | |
---|---|---|---|
Matte |
|
| |
Warm Matte |
|
| |
Plastic |
|
| |
Metal |
|
| |
Dark Edge |
|
| |
Soft Edge |
|
| |
Flat |
|
| |
Wire Frame |
|
| |
Powder |
|
| |
Translucent Powder |
|
| |
Clear |
|
| |
|
| ||
|
| ||
|
| ||
Bonus |
|
|
In Excel 2003, the material property was limited to matte, metal, plastic, and wire frame. Microsoft apparently was not happy with the old matte, metal, and plastic settings. It left those values in place to support legacy charts but created the new Matte2
, Plastic2
, and Metal2
settings. These settings are actually available in the dialog box. In VBA, you are free to use either the old or the new settings. The columns in Figure 11.9 compare the new and old settings. The final column is for the SoftMetal
setting that Microsoft left out of the Format dialog box. This was probably an aesthetic decision instead of an “oh no; this setting crashes the computer” decision. You can feel free to use msoMaterialSoftMetal
to create a look that has a subtle difference from charts others create using the settings in the Format dialog box.
The Lighting drop-down menu from the 3-D category of the Format dialog box offers 15 settings. The object model offers these 15 settings, plus 13 legacy settings from the Excel 2003 Lighting toolbar. Table 11.7 shows the settings for each of these thumbnails.
Table 11.7. VBA Constants for Lighting Types
VBA Constant | Value | ||
---|---|---|---|
Neutral Category | |||
ThreePoint |
|
| |
Balanced |
|
| |
Soft |
|
| |
Harsh |
|
| |
Flood |
|
| |
Contrasting |
|
| |
Warm Category | |||
Morning |
|
| |
Sunrise |
|
| |
Sunset |
|
| |
Cool Category | |||
Chilly |
|
| |
Freezing |
|
| |
Special Category | |||
Flat |
|
| |
TwoPoint |
|
| |
Glow |
|
| |
BrightRoom |
|
| |
Legacy Category | |||
Flat 1 |
|
| |
Flat 2 |
|
| |
Flat 3 |
|
| |
Flat 4 |
|
| |
Harsh 1 |
|
| |
Harsh 2 |
|
| |
Harsh 3 |
|
| |
Harsh 4 |
|
| |
Normal 1 |
|
| |
Normal 2 |
|
| |
Normal 3 |
|
| |
Normal 4 |
|
| |
Mixed |
|
|
It is frustrating that the macro recorder does not record certain actions when you’re working with charts. Actually, there are two levels of frustration. First, the macro recorder does not record the action of creating SmartArt graphics because Microsoft made a conscious decision not to allow you to create SmartArt using VBA. I do not agree with that decision, but I can understand why the macro recorder doesn’t record these steps. Second, when you are using the Format ribbon, the macro recorder does nothing; however, you can control all the actions with the Format ribbon by using VBA.
With earlier versions of Excel, I relied on the macro recorder to teach me which objects, properties, and methods responded to various actions in the Excel interface. Without the macro recorder, it becomes very difficult to learn these aspects.
In case you need to use a property that is not covered in this book, there is a way to be able to explore the properties for certain chart elements. The following is an example in which the macro defines a Chart object variable and a ChartGroup object variable and then stops:
Sub ExploreChartElements() Dim cht As Chart Dim chtg As ChartGroup Dim ser As Series Set cht = ActiveChart Set chtg = cht.ChartGroups(1) Set ser = cht.SeriesCollection(1) Stop End Sub
The Stop
command in the macro is key to the success of this technique. Excel enters Break mode when it encounters the Stop
code. This allows you to examine the object variables while they are still in scope.
You follow these steps to discover new chart properties:
Enter the preceding macro in your workbook.
Create a chart.
Select the chart.
Run the macro. VBA stops and highlights the Stop
line in yellow. You are now in Break mode.
Right-click the ser
object variable and choose Add Watch. Click OK in the Add Watch dialog box. Excel displays a new Watch window at the bottom of the VB Editor. This window displays a single line with a pair of eyeglasses, a plus sign, and the name of the variable, as shown in Figure 11.10.
Click the plus sign next to the watch. A list of many properties for the series opens. One property is the Format
property. This is where all of the Format ribbon settings are stored.
Click the plus sign next to the Format
entry. It expands to show the settings for Fill
, Glow
, Line
, and so on.
Click the plus sign next to the Fill
entry. You see many settings that define the fill used in Series 1. The GradientDegree
setting is highlighted in Figure 11.11. You can see that Gradient Degree
is a property of the Fill
property, and Fill
is a property of the Format
property. From this, you can ascertain that the proper code would be this:
ser.Format.Fill.GradientDegree = 0.8825
This isn’t exactly as easy as using the macro recorder to examine objects, properties, and methods, but it makes it possible to figure out how to write code.
In the ExploreChartElements macro, there are variables for the chart, the series, and the chart group. You might have to add watches for each of these variables and begin exploring to find the actual setting.
The Watch window is “somewhat” live. With a few steps, you can change the chart formatting in the Excel user interface and then return to the VB Editor to discover the new settings:
While in Break mode, switch back to Excel using Alt+Tab or by clicking Excel in the taskbar.
Make some changes to the active chart in Excel. Make sure not to deactivate the chart.
Switch back to the VB Editor.
There is a yellow arrow to the left of the Stop
line in your code window. Grab this arrow and drag upward to point to the line that set up your watched variable. In the current example, you are watching the ser
variable, so you just have to move up one line, to the Set ser
line.
Press the F8 key to rerun the line highlighted in yellow. The Watch window updates to show the settings you made in step 2.
When you have finished exploring, click the Reset button in the VBA toolbar (the square dot located below the Run menu).
In Charts & Graphs for Microsoft Excel 2007, I showed off some amazing charts that don’t look like they are possible to create using Excel. Building these charts usually involves adding a rogue data series that appears in the chart as an XY series to complete some effect. The process of creating these charts manually is very tedious and will ensure that most people would never resort to creating such charts.
However, if you could automate the process of creating the charts using VBA, the creation of the charts starts to become feasible.
If you are a fan of stock charts in the Wall Street Journal or finance.yahoo.com, you will recognize the chart type known as Open-High-Low-Close (OHLC) chart. Microsoft Excel does not offer such a chart. Its High-Low-Close (HLC) chart is missing the left-facing dash that represents the opening for each period. Now, you might feel that HLC charts are close enough to OHLC chart, but one of my personal pet peeves is that the WSJ can create better-looking charts than Excel can create.
In Figure 11.12, you can see a true OHLC chart in the top of the figure and Excel’s HLC chart in the bottom of the figure.
In Excel 2007, you can specify a custom picture to be used as the marker in a chart. I immediately went to Photoshop and created a left-facing dash as a GIF file. This tiny graphic makes up for the fundamental flaw in Excel’s chart marker selection: Excel offers a right-facing dash, but not a left-facing dash. You can download LeftDash.gif from www.mrexcel.com/getcode2007.html.
In the Excel user interface, you would indicate that the Open series should have a custom picture and then specify LeftDash.gif as the picture. In VBA code, you use the UserPicture
method, as shown here:
ActiveChart Cht.SeriesCollection(1).Fill.UserPicture ("C:leftdash.gif")
To create a true OHLC chart, follow these steps:
Create a line chart from four series; Open, High, Low, Close.
Change the line style to none for all four series.
Eliminate the marker for the High and Low series.
Add a High-Low line to the chart.
Change the marker for Close to a right-facing dash (called a dot in VBA) with a size of 9.
Change the marker for Open to a custom picture and load LeftDash.gif as the fill for the series.
The following code creates the top chart in Figure 11.12:
Sub CreateOHCLChart() ' Download leftdash.gif from the sample files for this book ' and save it in the same folder as this workbook Dim Cht As Chart Dim Ser As Series ActiveSheet.Shapes.AddChart(xlLineMarkers).Select Set Cht = ActiveChart Cht.SetSourceData Source:=Range("Sheet1!$A$1:$E$33") ' Format the Open Series With Cht.SeriesCollection(1) .MarkerStyle = xlMarkerStylePicture .Fill.UserPicture ("C:leftdash.gif") .Border.LineStyle = xlNone .MarkerForegroundColorIndex = xlColorIndexNone End With ' Format High & Low Series With Cht.SeriesCollection(2) .MarkerStyle = xlMarkerStyleNone .Border.LineStyle = xlNone End With With Cht.SeriesCollection(3) .MarkerStyle = xlMarkerStyleNone .Border.LineStyle = xlNone End With ' Format the Close series Set Ser = Cht.SeriesCollection(4) With Ser .MarkerBackgroundColorIndex = 1 .MarkerForegroundColorIndex = 1 .MarkerStyle = xlDot .MarkerSize = 9 .Border.LineStyle = xlNone End With ' Add High-Low Lines Cht.SetElement (msoElementLineHiLoLine) Cht.SetElement (msoElementLegendNone) End Sub
Suppose that you have results from 3,000 scientific trials. There must be a good way to produce a chart of those results. However, if you just select the results and create a chart, you will end up with chaos (see Figure 11.13).
The trick to creating an effective frequency distribution is to define a series of categories, or bins. A FREQUENCY
array function counts the number of items from the 3,000 results that fall within each bin.
The process of creating bins manually is rather tedious and requires knowledge of array formulas. It is better to use a macro to perform all of the tedious calculations.
The macro in this section requires you to specify a bin size and a starting bin. If you expect results in the 0 to 100 range, you might specify bins of 10 each, starting at 0. This would create bins of 0–10, 11–20, 21–30 and so on. If you specify bin sizes of 15 with a starting bin of 5, the macro will create bins of 5–20, 21–35, 36–50 and so on.
To use the following macro, your trial results should start in row 2 and should be in the rightmost column of a data set. Three variables near the top of the macro define the starting bin, the ending bin, and the bin size:
' Define Bins BinSize = 10 FirstBin = 0 LastBin = 100
After that, the macro skips a column and then builds a range of starting bins. In cell D4 in Figure 11.14, the 10 is used to tell Excel that you are looking for the number of values larger than the 0 in D3, but equal to or less than the 10 in D4.
Although the bins extend from D3:D13, the FREQUENCY
function entered in column E needs to include one extra cell, in case any results are larger than the last bin. This single formula returns many results. Formulas that return more than one answer are called array formulas. In the Excel user interface, you specify an array formula by holding down Ctrl+Shift while pressing Enter to finish the formula. In Excel VBA, you need to use the FormulaArray
property. The following lines of the macro set up the array formula in column E:
' Enter the Frequency Formula Form = "=FREQUENCY(R2C" & FinalCol & ":R" & FinalRow & "C" & FinalCol & _ ",R3C" & NextCol & ":R" & _ LastRow & "C" & NextCol & ")" Range(Cells(FirstRow, NextCol + 1), Cells(LastRow, NextCol + 1)). _ FormulaArray = Form
It is not evident to the reader if the bin indicated in column D is the upper or lower limit. The macro builds readable labels in column G and then copies the frequency results over to column H.
After the macro builds a simple column chart, the following line eliminates the gap between columns, creating the traditional histogram view of the data:
Cht.ChartGroups(1).GapWidth = 0
The macro to create the chart in Figure 11.14 follows:
Sub CreateFrequencyChart() ' Find the last column FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column ' Find the FinalRow FinalRow = Cells(Rows.Count, FinalCol).End(xlUp).Row ' Define Bins BinSize = 10 FirstBin = 0 LastBin = 100 'The bins will go in row 3, two columns after FinalCol NextCol = FinalCol + 2 FirstRow = 3 NextRow = FirstRow - 1 ' Set up the bins for the Frequency function For i = FirstBin To LastBin Step BinSize NextRow = NextRow + 1 Cells(NextRow, NextCol).Value = i Next i ' The Frequency function has to be one row larger than the bins LastRow = NextRow + 1 ' Enter the Frequency Formula Form = "=FREQUENCY(R2C" & FinalCol & ":R" & FinalRow & "C" & FinalCol & _ ",R3C" & NextCol & ":R" & _ LastRow & "C" & NextCol & ")" Range(Cells(FirstRow, NextCol + 1), Cells(LastRow, NextCol + 1)). _ FormulaArray = Form ' Build a range suitable a chart source data LabelCol = NextCol + 3 Form = "=R[-1]C[-3]&""-""&RC[-3]" Range(Cells(4, LabelCol), Cells(LastRow - 1, LabelCol)).FormulaR1C1 = _ Form ' Enter the > Last formula Cells(LastRow, LabelCol).FormulaR1C1 = "="">""&R[-1]C[-3]" ' Enter the < first formula Cells(3, LabelCol).FormulaR1C1 = "=""<""&RC[-3]" ' Enter the formula to copy the frequency results Range(Cells(3, LabelCol + 1), Cells(LastRow, LabelCol + 1)).FormulaR1C1 = _ "=RC[-3]" ' Add a heading Cells(2, LabelCol + 1).Value = "Frequency" ' Create a column chart Dim Cht As Chart ActiveSheet.Shapes.AddChart(xlColumnClustered).Select Set Cht = ActiveChart Cht.SetSourceData Source:=Range(Cells(2, LabelCol), _ Cells(LastRow, LabelCol + 1)) Cht.SetElement (msoElementLegendNone) Cht.ChartGroups(1).GapWidth = 0 Cht.SetElement (msoElementDataLabelOutSideEnd) End Sub
The stacked area chart shown in Figure 11.15 is incredibly difficult to create in the Excel user interface. Although the chart appears to contain four independent charts, this chart actually contains nine series:
The first series contains the values for the East region.
The second series contains 1,000 minus the East values. This series is formatted with a transparent fill.
Series 3, 5, and 7 contain values for Central, Northwest, and Southwest.
Series 4, 6, and 8 contain 1,000 minus the preceeding series.
The final series is a XY series used to add labels for the left axis. There is one point for each gridline. The markers are positioned at an X position of 0. Custom data labels are added next to invisible markers to force the labels along the axis to start again at 0 for each region.
To use the macro provided here, your data should begin in column A and row 1. The macro adds new columns to the right of the data and new rows below the data, so the rest of the worksheet should be blank.
Two variables at the top of the macro define the height of each chart. In the current example, leaving a height of 1000
allows the sales for each region to fit comfortably. The LabSize
value should indicate how frequently labels should appear along the left axis. This number must be evenly divisible into the chart height. In this example, values of 500
, 250
, 200
, 125
, or 100
would work:
' Define the height of each area chart ChtHeight = 1000 ' Define Tick Mark Size ' ChtHeight should be an even multiple of LabSize LabSize = 200
The macro builds a copy of the data to the right of the original data. New “dummy” series are added to the right of each region to calculate 1,000 minus the data point. In Figure 11.16, this series is shown in G1:O5.
The macro then creates a stacked area chart for the first eight series. The legend for this chart indicates values of East, dummy, Central, dummy, and so on. To delete every other legend entry, use this code:
' Fill the dummy series with no fill For i = FinalSeriesCount To 2 Step -2 Cht.SeriesCollection(i).Interior.ColorIndex = xlNone Next i
Similarly, the fill for each even numbered series in the chart needs to be set to transparent:
' Fill the dummy series with no fill For i = FinalSeriesCount To 2 Step -2 Cht.SeriesCollection(i).Interior.ColorIndex = xlNone Next i
The trickiest part of the process is adding a new final series to the chart. This series will have far more data points than the other series. Range B8:C28 contains the X and Y values for the new series. You will see that each point has an X value of 0 to ensure that it appears along the left side of the plot area. The Y values increase steadily by the value indicated in the LabSize
variable. In column A next to the X and Y points are the actual labels that will be plotted next to each marker. These labels give the illusion that the chart starts over with a value of 0
for each region.
The process of adding the new series is actually much easier in VBA than in the Excel user interface. The following code identifies each component of the series and specifies that it should be plotted as an XY chart:
' Add the new series to the chart Set Ser = Cht.SeriesCollection.NewSeries With Ser .Name = "Y" .Values = Range(Cells(AxisRow + 1, 3), Cells(NewFinal, 3)) .XValues = Range(Cells(AxisRow + 1, 2), Cells(NewFinal, 2)) .ChartType = xlXYScatter .MarkerStyle = xlMarkerStyleNone End With
Finally, code from earlier in this chapter applies a data label from column A to each point in the final series:
' Label each point in the series ' This code actually adds fake labels along left axis For i = 1 To TickMarkCount Ser.Points(i).HasDataLabel = True Ser.Points(i).DataLabel.Text = Cells(AxisRow + i, 1).Value Next i
The complete code to create the stacked chart in Figure 11.16 is shown here:
Sub CreatedStackedChart() Dim Cht As Chart Dim Ser As Series FinalRow = Cells(Rows.Count, 1).End(xlUp).Row FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column OrigSeriesCount = FinalCol - 1 FinalSeriesCount = OrigSeriesCount * 2 ' Define the height of each area chart ChtHeight = 1000 ' Define Tick Mark Size ' ChtHeight should be an even multiple of LabSize LabSize = 200 ' Make a copy of the data NextCol = FinalCol + 2 Cells(1, 1).Resize(FinalRow, FinalCol).Copy _ Destination:=Cells(1, NextCol) FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column ' Add in new columns to serve as dummy series MyFormula = "=" & ChtHeight & "-RC[-1]" For i = FinalCol + 1 To NextCol + 2 Step -1 Cells(1, i).EntireColumn.Insert Cells(1, i).Value = "dummy" Cells(2, i).Resize(FinalRow - 1, 1).FormulaR1C1 = MyFormula Next i ' Figure out the new Final Column FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column ' Build the Chart ActiveSheet.Shapes.AddChart(xlAreaStacked).Select Set Cht = ActiveChart Cht.SetSourceData Source:=Range(Cells(1, NextCol), Cells(FinalRow, FinalCol)) Cht.PlotBy = xlColumns ' Clear out the even number series from the Legend For i = FinalSeriesCount - 1 To 1 Step -2 Cht.Legend.LegendEntries(i).Delete Next i ' Set the axis Maximum Scale & Gridlines TopScale = OrigSeriesCount * ChtHeight With Cht.Axes(xlValue) .MaximumScale = TopScale .MinorUnit = LabSize .MajorUnit = ChtHeight End With Cht.SetElement (msoElementPrimaryValueGridLinesMinorMajor) ' Fill the dummy series with no fill For i = FinalSeriesCount To 2 Step -2 Cht.SeriesCollection(i).Interior.ColorIndex = xlNone Next i ' Hide the original axis labels Cht.Axes(xlValue).TickLabelPosition = xlNone ' Build a new range to hold a rogue XY series that will ' be used to create left axis labels AxisRow = FinalRow + 2 Cells(AxisRow, 1).Resize(1, 3).Value = Array("Label", "X", "Y") TickMarkCount = OrigSeriesCount * (ChtHeight / LabSize) + 1 ' Column B contains the X values. These are all zero Cells(AxisRow + 1, 2).Resize(TickMarkCount, 1).Value = 0 ' Column C contains the Y values. Cells(AxisRow + 1, 3).Resize(TickMarkCount, 1).FormulaR1C1 = _ "=R[-1]C+" & LabSize Cells(AxisRow + 1, 3).Value = 0 ' Column A contains the labels to be used for each point Cells(AxisRow + 1, 1).Value = 0 Cells(AxisRow + 2, 1).Resize(TickMarkCount - 1, 1).FormulaR1C1 = _ "=IF(R[-1]C+" & LabSize & ">=" & ChtHeight & ",0,R[-1]C+" & LabSize & ")" NewFinal = Cells(Rows.Count, 1).End(xlUp).Row Cells(NewFinal, 1).Value = ChtHeight ' Add the new series to the chart Set Ser = Cht.SeriesCollection.NewSeries With Ser .Name = "Y" .Values = Range(Cells(AxisRow + 1, 3), Cells(NewFinal, 3)) .XValues = Range(Cells(AxisRow + 1, 2), Cells(NewFinal, 2)) .ChartType = xlXYScatter .MarkerStyle = xlMarkerStyleNone End With ' Label each point in the series ' This code actually adds fake labels along left axis For i = 1 To TickMarkCount Ser.Points(i).HasDataLabel = True Ser.Points(i).DataLabel.Text = Cells(AxisRow + i, 1).Value Next i ' Hide the Y label in the legend Cht.Legend.LegendEntries(Cht.Legend.LegendEntries.Count).Delete End Sub
The websites of Andy Pope (www.andypope.info) and Jon Peltier (peltiertech.com/) are filled with examples of unusual charts that require extraordinary effort. If you find that you will regularly be creating stacked charts or any other chart like those on their websites, taking the time to write the VBA will ease the pain of creating the charts in the Excel user interface.
You can export any chart to an image file on your hard drive. The ExportChart
method requires you to specify a filename and a graphic type. The available graphic types depend on graphic file filters installed in your Registry. It is a safe bet that JPG, BMP, PNG, and GIF will work on most computers.
For example, the following code exports the active chart as a GIF file:
Sub ExportChart() Dim cht As Chart Set cht = ActiveChart cht.Export Filename:="C:Chart.gif", Filtername:="GIF" End Sub
Since Excel 2003, Microsoft has supported an Interactive
argument in the Export
method. Excel help indicates that if you set Interactive
to TRUE
, Excel asks for additional settings depending on the file type. However, the dialog to ask for additional settings never appears, at least not for the four standard types of JPG, GIF, BMP, or PNG.
With the ability to export a chart to a graphic file, you also have the ability to load a graphic file into an Image
control in a userform. This means you can create a dialog box in which someone can dynamically control values used to plot a chart.
To create the dialog shown in Figure 11.17, follow these steps:
In the VBA window, choose Insert, UserForm. In the Properties window, rename the form frmChart
.
Resize the userform.
Add a large Image
control to the userform.
Add two spin buttons named sbX
and sbY
. Set them to have a minimum of 1
and a maximum of 5
.
Add a Label3
control to display the formula.
Add a command button labeled Close
.
Enter this code in the code window behind the form:
Private Sub CommandButton1_Click() Unload Me End Sub Private Sub sbX_Change() Worksheets("Surface").Range("O2").Value = Me.sbX.Value Worksheets("Surface").Shapes(1).Chart.Export "C:Chart.gif" Me.Label3.Caption = Worksheets("Surface").Range("O4").Value Me.Image1.Picture = LoadPicture("C:Chart.gif") End Sub Private Sub sbY_Change() Worksheets("Surface").Range("O3").Value = Me.sbY.Value Worksheets("Surface").Shapes(1).Chart.Export "C:Chart.gif" Me.Label3.Caption = Worksheets("Surface").Range("O4").Value Me.Image1.Picture = LoadPicture("C:Chart.gif") End Sub Private Sub UserForm_Initialize() Me.sbX = Worksheets("Surface").Range("O2").Value Me.sbY = Worksheets("Surface").Range("O3").Value Me.Label3.Caption = Worksheets("Surface").Range("O4").Value Worksheets("Surface").Shapes(1).Chart.Export "C:Chart.gif" Me.Image1.Picture = LoadPicture("C:Chart.gif") End Sub
Use Insert, Module to add a Module1
component with this code:
Sub ShowForm() frmChart.Show End Sub
As someone changes the spin buttons in the userform, Excel writes new values to the worksheet. This causes the chart to update. The userform code then exports the chart and displays it in the userform (refer to Figure 11.17).
A pivot chart is a chart that uses a pivot table as the underlying data source. Unfortunately, pivot charts don’t have the cool “show pages” functionality that regular pivot tables have. You can overcome this problem with a quick VBA macro that creates a pivot table and then a pivot chart based on the pivot table. The macro then adds the customer field to the report filter area of the pivot table. It then loops through each customer and exports the chart for each customer.
In Excel 2007, you first create a pivot cache by using the PivotCache.Create
method. You can then define a pivot table based on the pivot cache. The usual procedure is to turn off pivot table updating while you add fields to the pivot table. Then you update the pivot table in order to have Excel perform the calculations.
It takes a bit of finesse to figure out the final range of the pivot table. If you have turned off the column and row totals, the chartable area of the pivot table starts one row below the PivotTableRange1
area. You have to resize the area to include one fewer row to make your chart appear correctly.
After the pivot table is created, you can switch back to the Charts.Add
code discussed earlier in this chapter. You can use any formatting code to get the chart formatted as you desire.
The following code creates a pivot table and a single pivot chart that summarize revenue by region and product:
Sub CreateSummaryReportUsingPivot() Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long Dim ChartDataRange As Range Dim Cht As Chart Set WSD = Worksheets("Data") ' Delete any prior pivot tables For Each PT In WSD.PivotTables PT.TableRange2.Clear Next PT WSD.Range("I1:Z1").EntireColumn.Clear ' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row FinalCol = WSD.Cells(1, Application.Columns.Count). _ End(xlToLeft).Column Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol) Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:= _ xlDatabase, SourceData:=PRange.Address) ' Create the Pivot Table from the Pivot Cache Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _ Cells(2, FinalCol + 2), TableName:="PivotTable1") ' Turn off updating while building the table PT.ManualUpdate = True ' Set up the row fields PT.AddFields RowFields:="Region", ColumnFields:="Product", _ PageFields:="Customer" ' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 End With With PT .ColumnGrand = False .RowGrand = False .NullString = "0" End With ' Calc the pivot table PT.ManualUpdate = False PT.ManualUpdate = True ' Define the Chart Data Range Set ChartDataRange = _ PT.TableRange1.Offset(1, 0).Resize(PT.TableRange1.Rows.Count - 1) ' Add the Chart WSD.Shapes.AddChart.Select Set Cht = ActiveChart Cht.SetSourceData Source:=ChartDataRange ' Format the Chart Cht.ChartType = xlColumnClustered Cht.SetElement (msoElementChartTitleAboveChart) Cht.ChartTitle.Caption = "All Customers" Cht.SetElement msoElementPrimaryValueAxisThousands End Sub
Figure 11.18 shows the resulting chart and pivot table.
Charts provide a visual picture that can help to summarize data for a manager. In Chapter 12, “Data Mining with Advanced Filter,” you will learn about using the Advanced Filter tools to quickly produce reports.