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!