Programming Excel with VBA and .NET

Create More Complex Charts

You don’t have to use the ChartWizard method to plot a chart. If you like, you can use the individual chart members instead. The following code illustrates creating a stock chart without ChartWizard:

Sub CreateChart(  )
    Dim ws As Worksheet, chrt As Chart
    Set ws = ActiveSheet
    ' Create the chart
    Set chrt = ThisWorkbook.Charts.Add(, ws)
    ' Name the chart sheet.
    chrt.Name = "Stock Price History"
    ' Plot the data in a named range.
    chrt.SetSourceData ws.[HistoryData], xlColumns
    ' Set the chart type to Open, High, Low, Close.
    chrt.ChartType = xlStockOHLC
    ' Dates are in descending order, so reverse the axis.
    chrt.Axes(xlCategory).ReversePlotOrder = True
End Sub

The main reason to use this approach rather than the ChartWizard method is that the ChartType property supports the full set of xlChartType constants. ChartWizard supports only a subset. The xlStockOHLC type is one of the types not available through ChartWizard. Another reason to use this approach is that it doesn’t add much complexity if you are already changing other chart settings, such as reversing the plot order as shown earlier.

The disadvantage of this approach is that you have to know what properties and methods control each aspect of the chart. The easiest way to solve this riddle is to turn on Macro Recording, create your chart, format it as you want it to appear, then turn off Macro Recording and examine the generated code.

For example, this code was generated in response to reformatting the chart ...