Creating an Embedded Chart

To add an embedded chart to a worksheet, use the Add() method of the ChartObects collection object. The AddEmbeddedChart() sub procedure creates the same column chart as the AddChartSheet() sub procedure listed in the previous section; however, it embeds the chart on an existing worksheet named Embedded Charts.

Public Sub AddEmbeddedChart() Dim dataRange As Range

Set dataRange = Range(frmDataRange.txtDataRange.Text) frmDataRange.Hide

Sheets("Create Chart").ChartObjects.Add Left:=200, _

Top:=50, Width:=500, Height:=350 Sheets("Create Chart").ChartObjects(1).Activate With ActiveChart

.ChartType = xlColumnClustered

.SeriesCollection.NewSeries

.HasLegend = True

.Legend.Position = xlRight

.Axes(xlCategory).MinorTickMark = xlOutside

.Axes(xlValue).MinorTickMark = xlOutside

.Axes(xlValue).MaximumScale = Application.WorksheetFunction.RoundUp( _ Application.WorksheetFunction.Max(dataRange), -1) .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _

"X-axis Labels" .Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-axis" .SeriesCollection(1).Name = "Sample Data" .SeriesCollection(1).Values = dataRange End With End Sub

When adding an embedded chart, the Add() method of the ChartObjects collection object accepts four parameters that define the position of the upper-left corner of the chart on the worksheet, as well as the chart width and height. The position properties of the Add() method (Left and Top) are relative to the upper-left corner of cell A1 and are in units of points. The Activate method of the ChartObject object is equivalent to selecting the chart because only one Chart object is contained in a ChartObject object.

Before setting the properties of the Chart object, the chart must contain at least one Series object. Thus, the NewSeries method is used to add an empty Series object to the chart. This is another difference from adding chart sheets, where a Series object is automatically added on creation of the chart sheet. The properties of the Chart object are then set in the same manner as was done with the chart sheet.

The preceding examples demonstrate only a small fraction of the objects, properties, and methods available in a Chart object. Don't be intimidated by the breadth of the Chart object and its components! Always remember that a large problem can be broken into many smaller, more manageable problems. Once you learn how to access a chart, setting the properties of any of its component objects is basically the same. The hard part is learning what objects are available to the specific chart being manipulated. The number of component objects in a Chart object varies with the chart type (column, bar, scatter, and so on) and with the subcategory of chart type (clustered, stacked, 3D, and so on). For example, a 3D column chart has Wall, Floor, and Corners objects, but a clustered column chart does not have these objects.

To learn the differences between chart types or to just learn what is available for a specific chart type, use recorded macros. First, create the chart from the Excel application then alter its appearance with the macro recorder turned on. Be careful to record only a small number of actions, say two to three at one time, because the macro recorder adds a lot of unnecessary code (setting default values). Keep in mind that as you select a component of the chart with the mouse, you are really selecting a component object of the Chart object. The dialog box that appears when the component object is double-clicked or selected from the chart menu sets the properties of that object. For example, the Format Axis dialog box shown in Figure 9.9 appears when the user double-clicks on a chart axis.

Format Axis

The Format Axis dialog box.

Format Axis

The Format Axis dialog box.

Refedit Excel

Figure 9.9 shows some of the properties of the Axis object. If the macro recorder is on while these properties are altered, the VBA code used to set these properties will be recorded when OK is clicked in the dialog box. After recording a small macro, proceed to the VBA IDE to examine the recorded code. If any of the code needs clarification, select the unknown keyword and press F1 to retrieve its documentation from the online help. This is an extremely helpful tool for learning how to program specific Excel components and the advantage should be exploited.

Figure 9.9 shows some of the properties of the Axis object. If the macro recorder is on while these properties are altered, the VBA code used to set these properties will be recorded when OK is clicked in the dialog box. After recording a small macro, proceed to the VBA IDE to examine the recorded code. If any of the code needs clarification, select the unknown keyword and press F1 to retrieve its documentation from the online help. This is an extremely helpful tool for learning how to program specific Excel components and the advantage should be exploited.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

  • ensio kolkkala
    How to select a chart embedded with VBA?
    6 years ago

Post a comment