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.

Figure 11.1. You can select a chart and look in the name box to find the name of the chart.

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

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.

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.

Figure 11.2. The built-in layouts are numbered 1 through 11. For other chart types, you might have from 4 to 12 layouts.

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

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:

Figure 11.3. The built-in styles are numbered 1 through 48.

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.

Figure 11.4. Setting the ChartStyle property does not override all settings.

To clear any previous formatting, you use the ClearToMatchStyle method:

ActiveChart.ChartStyle = 1
ActiveChart.ClearToMatchStyle

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.

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.

Changing an Object’s Fill

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.

Figure 11.6. Fill options include a solid color, a gradient, a texture, or a picture.

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

Tip

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.

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

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

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

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

Chart.Seriess(1).Points(i).Format.SoftEdge.Type = msoSoftEdgeType1

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

Formatting 3-D Rotation Settings

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.

Figure 11.7. Whereas the 3-D Rotation menu offers 25 presets, VBA offers 62 presets.

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

Menu Location	Description	VBA Value
Parallel group, row 1, column 1	Isometric Left Down	msoCameraIsometricLeftDown
Parallel group, row 1, column 2	Isometric Right Up	msoCameraIsometricRightUp
Parallel group, row 1, column 3	Isometric Top Up	msoCameraIsometricTopUp
Parallel group, row 1, column 4	Isometric Bottom Down	msoCameraIsometricBottomDown
Parallel group, row 2, column 1	Isometric OffAxis1 Left	msoCameraIsometricOffAxis1Left
Parallel group, row 2, column 2	Isometric OffAxis1 Right	msoCameraIsometricOffAxis1Right
Parallel group, row 2, column 3	Isometric OffAxis1 Top	msoCameraIsometricOffAxis1Top
Parallel group, row 2, column 4	Isometric OffAxis2 Left	msoCameraIsometricOffAxis2Left
Parallel group, row 3, column 1	Isometric OffAxis2 Right	msoCameraIsometricOffAxis2Right
Parallel group, row 3, column 2	Isometric OffAxis2 Top	msoCameraIsometricOffAxis2Top
Parallel group, bonus selection	Isometric Bottom Up	msoCameraIsometricBottomUp
Parallel group, bonus selection	Isometric Left Up	msoCameraIsometricLeftUp
Parallel group, bonus selection	Isometric OffAxis3 Bottom	msoCameraIsometricOffAxis3Bottom
Parallel group, bonus selection	Isometric OffAxis3 Left	msoCameraIsometricOffAxis3Left
Parallel group, bonus selection	Isometric OffAxis3 Right	msoCameraIsometricOffAxis3Right
Parallel group, bonus selection	Isometric OffAxis4 Bottom	msoCameraIsometricOffAxis4Bottom
Parallel group, bonus selection	Isometric OffAxis4 Left	msoCameraIsometricOffAxis4Left
Parallel group, bonus selection	Isometric OffAxis4 Right	msoCameraIsometricOffAxis4Right
Parallel group, bonus selection	Isometric Right Down	msoCameraIsometricRightDown
Parallel group, bonus selection	Isometric Top Down	msoCameraIsometricTopDown
Perspective group, row 1, column 1	Perspective Front	msoCameraPerspectiveFront
Perspective group, row 1, column 2	Perspective Left	msoCameraPerspectiveLeft
Perspective group, row 1, column 3	Perspective Right	msoCameraPerspectiveRight
Perspective group, row 1, column 4	Perspective Below	msoCameraPerspectiveBelow
Perspective group, row 2, column 1	Perspective Above	msoCameraPerspectiveAbove
Perspective group, row 2, column 2	Perspective Relaxed Moderately	msoCameraPerspectiveRelaxedModerately
Perspective group, row 2, column 3	Perspective Relaxed	msoCameraPerspectiveRelaxed
Perspective group, row 2, column 4	Perspective Contrasting Left Facing	msoCameraPerspectiveContrastingLeftFacing
Perspective group, row 3, column 1	Perspective Contrasting Right Facing	msoCameraPerspectiveContrastingRightFacing
Perspective group, row 3, column 2	Perspective Heroic Extreme Left Facing	msoCameraPerspectiveHeroicExtremeLeftFacing
Perspective group, row 3, column 3	Perspective Heroic Extreme Right Facing	msoCameraPerspectiveHeroicExtremeRightFacing
Perspective group, bonus selection	Perspective Above Left Facing	msoCameraPerspectiveAboveLeftFacing
Perspective group, bonus selection	Perspective Above Right Facing	msoCameraPerspectiveAboveRightFacing
Perspective group, bonus selection	Perspective Heroic Left Facing	msoCameraPerspectiveHeroicLeftFacing
Perspective group, bonus selection	Perspective Heroic Right Facing	msoCameraPerspectiveHeroicRightFacing
Perspective group, Excel 2003 styles	Legacy Perspective Bottom	msoCameraLegacyPerspectiveBottom
Perspective group, Excel 2003 styles	Legacy Perspective Lower Left	msoCameraLegacyPerspectiveBottomLeft
Perspective group, Excel 2003 styles	Legacy Perspective Lower Right	msoCameraLegacyPerspectiveBottomRight
Perspective group, Excel 2003 styles	Legacy Perspective Front	msoCameraLegacyPerspectiveFront
Perspective group, Excel 2003 styles	Legacy Perspective Left	msoCameraLegacyPerspectiveLeft
Perspective group, Excel 2003 styles	Legacy Perspective Right	msoCameraLegacyPerspectiveRight
Perspective group, Excel 2003 styles	Legacy Perspective Top	msoCameraLegacyPerspectiveTop
Perspective group, Excel 2003 styles	Legacy Perspective Upper Left	msoCameraLegacyPerspectiveTopLeft
Perspective group, Excel 2003 styles	Legacy Perspective Upper Right	msoCameraLegacyPerspectiveTopRight
Oblique group, row 1, column 1	Oblique Upper Left	msoCameraObliqueTopLeft
Oblique group, row 1, column 2	Oblique Upper Right	msoCameraObliqueTopRight
Oblique group, row 1, column 3	Oblique Lower Left	msoCameraObliqueBottomLeft
Oblique group, row 1, column 4	Oblique Lower Right	msoCameraObliqueBottomRight
Oblique group, bonus selection	Oblique Bottom	msoCameraObliqueBottom
Oblique group, bonus selection	Oblique Left	msoCameraObliqueLeft
Oblique group, bonus selection	Oblique Right	msoCameraObliqueRight
Oblique group, bonus selection	Oblique Top	msoCameraObliqueTop
Oblique group, bonus selection	Orthographic Front	msoCameraOrthographicFront
Oblique group, Excel 2003 styles	Legacy Oblique Bottom	msoCameraLegacyObliqueBottom
Oblique group, Excel 2003 styles	Legacy Oblique Lower Left	msoCameraLegacyObliqueBottomLeft
Oblique group, Excel 2003 styles	Legacy Oblique Lower Right	msoCameraLegacyObliqueBottomRight
Oblique group, Excel 2003 styles	Legacy Oblique Front	msoCameraLegacyObliqueFront
Oblique group, Excel 2003 styles	Legacy Oblique Left	msoCameraLegacyObliqueLeft
Oblique group, Excel 2003 styles	Legacy Oblique Right	msoCameraLegacyObliqueRight
Oblique group, Excel 2003 styles	Legacy Oblique Top	msoCameraLegacyObliqueTop
Oblique group, Excel 2003 styles	Legacy Oblique Upper Left	msoCameraLegacyObliqueTopLeft
Oblique group, Excel 2003 styles	Legacy Oblique Upper Right	msoCameraLegacyObliqueTopRight

 

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.

Changing the Bevel and 3-D Format

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).

Figure 11.8. You can control the 3-D Format settings, such as bevel, surface, and lighting.

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.

Table 11.5. Bevel Types

Constant
	msoBevelCircle
	msoBevelRelaxedInset
	msoBevelCross
	msoBevelCoolSlant
	msoBevelAngle
	msoBevelSoftRound
	msoBevelConvex
	msoBevelSlope
	msoBevelDivot
	msoBevelRiblet
	msoBevelHardEdge
	msoBevelArtDeco

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	msoMaterialMatte2	5
	Warm Matte	msoMaterialWarmMatte	8
	Plastic	msoMaterialPlastic2	6
	Metal	msoMaterialMetal2	7
	Dark Edge	msoMaterialDarkEdge	11
	Soft Edge	msoMaterialSoftEdge	12
	Flat	msoMaterialFlat	14
	Wire Frame	msoMaterialWireFrame	4
	Powder	msoMaterialPowder	10
	Translucent Powder	msoMaterialTranslucentPowder	9
	Clear	msoMaterialClear	13
		msoMaterialMatte	1
		msoMaterialPlastic	2
		msoMaterialMetal	3
Bonus		msoMaterialSoftMetal	15

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.

Figure 11.9. Comparison of some new and old material presets.

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

	Type	VBA Constant	Value
Neutral Category
	ThreePoint	msoLightRigThreePoint	13
	Balanced	msoLightRigBalanced	14
	Soft	msoLightRigSoft	15
	Harsh	msoLightRigHarsh	16
	Flood	msoLightRigFlood	17
	Contrasting	msoLightRigContrasting	18
Warm Category
	Morning	msoLightRigMorning	19
	Sunrise	msoLightRigSunrise	20
	Sunset	msoLightRigSunset	21
Cool Category
	Chilly	msoLightRigChilly	22
	Freezing	msoLightRigFreezing	23
Special Category
	Flat	msoLightRigFlat	24
	TwoPoint	msoLightRigTwoPoint	25
	Glow	msoLightRigGlow	26
	BrightRoom	msoLightRigBrightRoom	27
Legacy Category
	Flat 1	msoLightRigLegacyFlat1	1
	Flat 2	msoLightRigLegacyFlat2	2
	Flat 3	msoLightRigLegacyFlat3	3
	Flat 4	msoLightRigLegacyFlat4	4
	Harsh 1	msoLightRigLegacyHarsh1	9
	Harsh 2	msoLightRigLegacyHarsh2	10
	Harsh 3	msoLightRigLegacyHarsh3	11
	Harsh 4	msoLightRigLegacyHarsh4	12
	Normal 1	msoLightRigLegacyNormal1	5
	Normal 2	msoLightRigLegacyNormal2	6
	Normal 3	msoLightRigLegacyNormal3	7
	Normal 4	msoLightRigLegacyNormal4	8
	Mixed	msoLightRigMixed	-2

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.

Figure 11.12. Excel’s built-in High-Low-Close chart leaves out the Open mark for each data point.

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:

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).

Figure 11.13. Try to chart the results from 3,000 trials and you will have a jumbled mess.

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.

Figure 11.14. The macro summarizes the results into bins and provides a meaningful chart of the data.

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:

Figure 11.15. A single chart appears to hold four different charts.

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.

Figure 11.16. Extra data to the right and below the original data are created by the macro to create the chart.

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.

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:

Figure 11.17. This dialog box is a VBA userform displaying a chart. The chart redraws based on changes to the dialog controls.

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).