Table of Contents
Creating a bar chart in VBA involves using the charting tools available in the Visual Basic for Applications (VBA) environment. To do this, you need to first define the data series you want to chart, specify the chart type, add a title and axes labels, and then format the chart. Finally, you can use the chart.ChartArea property to save and display the chart.
You can use the following basic syntax to create a bar chart in Excel by using VBA:
Sub CreateBarChart()
Dim MyChart As ChartObject
'get input range from user
Set Rng = Application.InputBox(Prompt:="Select chart input range", Type:=8)
'create bar chart
Set MyChart = Worksheets("Sheet1").ChartObjects.Add(Left:=ActiveCell.Left, _
Width:=400, Top:=ActiveCell.Top, Height:=300)
MyChart.Chart.SetSourceData Source:=Rng
MyChart.Chart.ChartType = xlColumnClustered
End Sub
This particular macro will prompt the user for an input range, then automatically generate a bar chart using the input range and insert it into the sheet called Sheet1 with the top left corner of the chart located in the currently active cell.
The following example shows how to use this macro in practice.
Example: How to Create a Bar Chart in VBA
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
Suppose we would like to use VBA to generate a bar chart using this dataset.
We can create the following macro to do so:
Sub CreateBarChart()
Dim MyChart As ChartObject
'get input range from user
Set Rng = Application.InputBox(Prompt:="Select chart input range", Type:=8)
'create bar chart
Set MyChart = Worksheets("Sheet1").ChartObjects.Add(Left:=ActiveCell.Left, _
Width:=400, Top:=ActiveCell.Top, Height:=300)
MyChart.Chart.SetSourceData Source:=Rng
MyChart.Chart.ChartType = xlColumnClustered
End Sub
To run this macro, we can click on the Developer tab along the top ribbon in Excel, then click Macros.
We can then click the one titled CreateBarChart and then click Run:
Once we click Run, we will be prompted for an input range for our bar chart:
We will type A1:B7, then press OK.
The following bar chart will automatically be created and displayed with the top left corner of the chart located in the currently active cell, which happens to be cell D1:
Note: You can change the values for the Width and Height arguments in the ChartObjects.Add() function to adjust the width and height of the bar chart, respectively.