How to Create a Bar Chart in VBA (With Example)

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:

VBA bar chart

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.