VBA Create Chart in Excel VBA. Chart is an object in a Workbook. Charts are also called as graphs. The charts collection has a chart object. Graphs or Charts are used to represents data visually. In this tutorial we learn how to create an embed chart and chart sheet in Excel VBA.
We can create chart in two different ways. Let us learn about those two types of chart creation.
- Embedded Charts
- Chart Sheets
The first method we can embed chart in the existing or specified Worksheet in a Workbook. In the second method chart sheet is created separately in the Workbook.
Let us learn how to create the embedded charts in Excel VBA. An embed chart created in the active Worksheet in a Workbook.
'Create an Embedded chart Sub VBAF1_Create_Embedded_Chart_Simple() ActiveSheet.Shapes.AddChart.Chart.SetSourceData Sheets("Charts").Range("A1:B5") End Sub
We have created quite simple one line statement to create a chart. The above code also written as in the following way.
‘An Embedded chart using VBA Sub VBAF1_Create_Embedded_Chart() ‘Declare Variables Dim oWs As Worksheet Dim rRng As Range Dim oCht As Object ‘Define an object Set oCht = ActiveSheet.Shapes.AddChart ‘Embedded Chart statement oCht.Chart.SetSourceData Sheets("Charts").Range("A1:B5") End Sub
Output: You can see below output screenshot from the above procedures.
We can create Chart Sheet(s) on its own chart sheet in Excel workbook using VBA. Here is the VBA code to create simple chart sheet in Excel workbook.
'Chart Sheet creation using VBA Sub VBAF1_Create_ChartSheet() 'Define Variables Dim MyChart As Chart Set MyChart = Charts.Add With MyChart .SetSourceData Sheets("Charts").Range("A1:B5") .ChartType = xlColumnClustered End With End Sub
Output: Here is the output screenshot from the above VBA code. In the below image we can notice that separate sheet created on its own chart sheet. Default name of the chart sheet is Chart1 , Chart2, Chart3,…etc.
Here are the step by step instructions to use above macro codes in Visual basic editor.
- Open Visual Basic Editor(VBE) or you can click Alt +F11
- Go to code window or click F7
- Copy above specified macro code or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- See output screenshots for output of the above macro procedures.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.