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.
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
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