Creating a combined chart in Excel using VBA

Creating a combined chart in Excel using VBA involves using different data series in a single chart, combining multiple chart types (e.g., column and line charts). 

Steps to Create a Combined Chart Using VBA:

1. Prepare the Data: For this example, let’s assume you have data in the range A1:C6:
   • Column A: Months
   • Column B: Sales
   • Column C: Costs
2. Create a Combined Chart: The combined chart will display « Sales » as a column chart and « Costs » as a line chart.

VBA Code:

Sub CreateCombinedChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    ' Reference the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Modify "Sheet1" to the actual sheet name
    ' Create a combined chart (column and line chart)
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    Set chart = chartObj.Chart
    ' Set the data range for the chart
    chart.SetSourceData Source:=ws.Range("A1:C6")
    ' Set the default chart type to clustered column
    chart.ChartType = xlColumnClustered  ' Default chart type: clustered columns
    ' Add a series (e.g., "Sales") as a column chart
    With chart.SeriesCollection.NewSeries
        .Name = "Sales"
        .XValues = ws.Range("A2:A6")
        .Values = ws.Range("B2:B6")
        .ChartType = xlColumnClustered  ' Column chart
    End With
    ' Add a series (e.g., "Costs") as a line chart
    With chart.SeriesCollection.NewSeries
        .Name = "Costs"
        .XValues = ws.Range("A2:A6")
        .Values = ws.Range("C2:C6")
        .ChartType = xlLine  ' Line chart
        .AxisGroup = 2  ' Place this series on the secondary axis
    End With
    ' Add a secondary axis for the "Costs" series
    chart.Axes(xlValue, xlSecondary).CategoryNames = ws.Range("A2:A6")
    chart.HasSecondaryAxis = True
    ' Customize the axes
    With chart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
        .TickLabels.NumberFormat = "#,##0"
    End With
    ' Customize the chart title and legend
    chart.HasTitle = True
    chart.ChartTitle.Text = "Combined Chart for Sales and Costs"
    chart.HasLegend = True
    chart.Legend.Position = xlLegendPositionBottom
End Sub

Explanation of the Code:

1. Declaration of Objects:
   • ws: Refers to the worksheet where the data and chart will be created.
   • chartObj: Represents the chart object that will be inserted into the worksheet.
   • chart: Refers to the actual chart being created.
2. Creating the Chart:
   • Set chartObj = ws.ChartObjects.Add(…): Adds a chart object to the worksheet with the specified dimensions (left, top, width, height).
   • chart.SetSourceData Source:=ws.Range(« A1:C6 »): Sets the data range from which the chart will be created.
3. Adding the Series:
   • For the first series (« Sales »), the chart type is set to xlColumnClustered (column chart).
   • For the second series (« Costs »), the chart type is set to xlLine (line chart), and the property .AxisGroup = 2 places this series on the secondary axis.
4. Secondary Axis:
   • chart.HasSecondaryAxis = True: Adds a secondary axis for the « Costs » series, allowing it to have a different scale than the « Sales » series.
5. Customizing the Axes:
   • The primary axis has major gridlines enabled, and tick labels are formatted to show numbers with commas.
   • The secondary axis is used for the « Costs » series.
6. Chart Customization:
   • The chart title is set to « Combined Chart for Sales and Costs ».
   • The legend is placed at the bottom of the chart using chart.Legend.Position = xlLegendPositionBottom.

Expected Result:

The code will create a combined chart where:

• The « Sales » series is displayed as a column chart.
• The « Costs » series is displayed as a line chart.
• The secondary axis is used for the « Costs » series, allowing a different scale for both series.

Further Customization:

• You can adjust colors, chart types, and other settings by modifying the properties of the chart and series.