Creating and formatting a chart automatically in Excel with VBA

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!

Leave a Reply

Your email address will not be published.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram