Column Chart through VBA

How to Create Chart through VBA in Microsoft Excel

In this article, we are going to learn how to create chart through VBA in Microsoft Excel.

Let’s understand with a simple exercise that how we can create chart through VBA in Microsoft Excel.

We have data in range A1:F10 in which column A contains student’s name, column B Marks 1, column C Marks 2, column D Marks 3, column E contains Total Score, and column F contains Average of Marks.

Follow below given steps and code:-

Insert a command button from Developer tab>Insert>Command Button 1.
Press the key Alt+F11 to open the VBE page to write the macro.
Then go to insert tab and insert a module.
Write below mentioned code in the page.

Private Sub CommandButton1_Click()

Range("A2:A10,F1:F10").Select

ActiveSheet.Shapes.AddChart.Select

ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$2:$A$10,'Sheet1'!$F$2:$F$10")

ActiveChart.ChartType = xlColumnClustered

ActiveSheet.ChartObjects(1).Activate

ActiveSheet.ChartObjects(1).Cut

Sheets("Sheet2").Select

ActiveSheet.Paste

Sheets("Sheet1").Select

Range("F11").Activate

End Sub

Code Explanation:- First, we will define the range of selection to insert the chart then we set the data source and chart type. Before performing an action on the chart, we need to activate it, and then we select the destination where we want to paste our chart, and after that we need to return to sheet 1.