An Example of Chart Creation

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

As we have said, creating a useful chart from scratch requires using the properties and methods of the Chart object. As we will see, this object is quite complex, with a great many properties, methods, and children. Before plunging into a discussion of these items, we want to give an example of chart creation. This will put our future discussion into some perspective. In fact, we will have several occasions to make reference to this code in the sequel.

Figure 21-5. The results of CreateChart

Figure 21-5. The results of CreateChart

So, the CreateChart procedure shown in Example 21-2 produces the chart in Figure 21-5. It may not be pretty, but it does illustrate much of the chart making process.

Example 21-2. The CreateChart Procedure

Sub CreateChart()

' Create an embedded chart

Dim co As ChartObject Dim cw As Long, rh As Long

' Get data for positioning chart cw = Columns(1).Width rh = Rows(1).Height

' Position chart using column width and row height units Set co = ActiveSheet.ChartObjects.Add(cw * 3, rh * 0.5, cw * 8, rh * 20)

' Name it co.Name = "ChartExample"

' Set chart type co.Chart.ChartType = xlLine

' Add data series co.Chart.SeriesCollection.Add

Source:=ActiveSheet.Range("A1:B6"), Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True

' Add axes

' (This is actually the default setting, ' but is added here for illustration) With co.Chart

.HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlCategory, xlSecondary) = False .HasAxis(xlValue, xlPrimary) = True .HasAxis(xlValue, xlSecondary) = False End With

' Axis title formatting With co.Chart.Axes(xlCategory) .HasTitle = True .AxisTitle.Caption = "Types" .AxisTitle.Border.Weight = xlMedium End With

With co.Chart.Axes(xlValue) .HasTitle = True

With .AxisTitle

.Caption = "Quantity for 1999" .Font.Size = 6 .Orientation = xlHorizontal .Characters(14, 4).Font.Italic = True .Border.Weight = xlMedium End With End With

' Change the category names (Types) to lower case ' (On the worksheet they are in upper case) co.Chart.Axes(xlCategory).CategoryNames = Array("a", "b", "c", "d", "e")

' Set the crossing point on the (primary) value axis at 50 co.Chart.Axes(xlValue).CrossesAt = 50

' Horizontal but no vertical gridlines co.Chart.Axes(xlValue).HasMajorGridlines = True co.Chart.Axes(xlCategory).HasMajorGridlines = False

' Outside Tickmarks on category axis co.Chart.Axes(xlCategory).MajorTickMark = xlTickMarkCross

' Move tick labels to below chart area co.Chart.Axes(xlCategory).TickLabelPosition = xlTickLabelPositionNextToAxis

' Set chart area fill to solid white co.Chart.ChartArea.Interior.Color = RGB(255, 255, 255)

' Set plot area fill to gray co.Chart.PlotArea.Interior.Colorlndex = 15

' Format chart title With co.Chart.ChartTitle

.Caption = "Great Chart" .Font.Size = 14 .Font.Bold = True .Border.Weight = xlThick End With

Before looking at the main properties, methods, and children of the Chart object, we can get one simple, but important, item out of the way.

Namely, it is possible for two or more embedded charts to overlap, which raises the question of how to control which chart object appears on the top. Every ChartObject object has an order, called its z-order, that indicates the object's relative position with respect to an imaginary z-axis that comes directly out of the monitor at right angles, towards the user, as pictured in Figure 21-6.

The ChartObject object has a read-only ZOrder property that is used to return the z-order of the ChartObject. It also has BringToFront and SendToBack methods for changing the z-order. These properties can be used to shuffle the order of ChartObject objects.

End Sub

Was this article helpful?

0 0

Post a comment