Creating and formatting a chart automatically in Excel with VBA

Using Excel involves creating a lot of charts and tables. Most of the time, the operations are really simple but time consuming. Creating and formatting a chart takes time. In this post, we are exploring how we can create a chart automatically with some VBA code.

The preparations and the result

Start by creating some data labels and values in the range A1:B7. I created the labels "Load [%]" for the x-axis and "Fuel consumption [g/kWh]" for the y-axis. Press alt+F11 to go into the VBA editor. Write or paste the code below in the VBA editor. Go to the run macro and run the Creat_chart sub

The code

First, we are adding the chart to the active sheet. The chart is a Scatter with lines and with the style 2 formatting (241).  Next, we select the source data from the cells A1:B7 on the active sheet. You can format the axes with the MaximumScale and MinimumScale methods. The height and width are done with the With (pun intended).

We finish by setting the chart's title, x-axis title, and y-axis title. The axes titles are picked from the cells A1 and B1 where we had the text for the axes.

Sub Create_chart()

    ' Add the chart and select it

    ActiveSheet.Shapes.AddChart2(241, xlXYScatterLines).Select
    ' Set the source data

    ActiveChart.SetSourceData Source:= ActiveSheet.Range("$A$1:$B$7")
    ' Set scales for x and y axes

    ' X-axis

    ActiveChart.Axes(xlCategory).MaximumScale = 100

    ' Y-axis

    ActiveChart.Axes(xlValue).MinimumScale = 150
    ' Set the height and width of the chart

    With ActiveChart.Parent

        .Height = 300

        .Width = 600

    End With
    ' Set chart title

    ActiveChart.ChartTitle.Text = "Fuel consumption at different loads"

    ' Add axis titles

    With ActiveChart

        'X axis name

        .Axes(xlCategory, xlPrimary).HasTitle = True

        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("A1").Value

        'y-axis name

        .Axes(xlValue, xlPrimary).HasTitle = True

        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("B1").Value

    End With

Happy coding!