Excel VBA Create Bar Chart: Step-by-Step Guide and 4 Examples to Create Clustered or Stacked Bar Charts with Macros

In this VBA Tutorial, you learn how to create a clustered or stacked bar chart with macros.

This VBA Tutorial is accompanied by Excel workbooks containing the macros I use in the examples below. You can get immediate access to these example workbooks by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  
  
  • If you're a beginner, learn how to start working with macros here.
    
    
  • Understand essential VBA terms here.
    
    
  • Learn how to enable or disable macros here.
    
    
  • Learn about the Visual Basic Editor here.
    
    
  • Learn how to refer to objects here.
    
    
  • Learn how to create references to cell range here.
    
    
  • Learn about Sub procedures here.
    
    
  • Learn how to work with properties here.
    
    
  • Learn how to work with methods here.
    
    
  • Learn how to create and assign values to variables here.
    
    
  • Learn about data types here.
    
    
• Practical VBA applications and macro examples:
  
  
  • Learn how to work with worksheets here.
    
    
  • Learn how to find the last row in a worksheet here.
    
    
  • Learn how to find the last column in a worksheet here.
    
    
  • Learn how to specify the width of a column here.
    
    
  • Learn how to check if a cell is empty here.
    
    
  • Learn how to delete a sheet here.
    
    
• Tutorials about other useful topics:
  
  
  • Learn how to import and consolidate data using Power Query here.

You can find additional VBA and Macro Tutorials in the Archives.

#1: Create an embedded clustered or stacked bar chart (without selecting the source data range)

VBA code to create an embedded clustered or stacked bar chart (without selecting the source data range)

To create an embedded clustered or stacked bar chart (without selecting the source data range) using VBA, use a macro with the following statement structure:

Dim myChart As Chart
Set myChart = Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart
myChart.SetSourceData Source:=SourceDataRange

Process followed by VBA to create an embedded clustered or stacked bar chart (without selecting the source data range)

To create an embedded clustered or stacked bar chart (without selecting the source data range), follow these steps within your VBA code:

1. Declare an object variable (myChart) to represent the newly-created clustered or stacked bar chart.
   
   
2. Create a clustered or stacked bar chart with the Shapes.AddChart2 method.
   
   
3. Assign the Chart object representing the newly-created clustered or stacked bar chart to the myChart object variable.
   
   
4. Specify the source data for the newly-created clustered or stacked bar chart with the Chart.SetSourceData method.

VBA statement explanation

Line #1: Dim myChart As Chart

1. Item: Dim.
   
   
   • VBA construct: Dim statement.
     
     
   • Description: The Dim statement declares the myChart object variable and allocates storage space.
     
     
2. Item: myChart.
   
   
   • VBA construct: Object variable.
     
     
   • Description: myChart is an object variable of the Chart object data type. The purpose of myChart is to represent a reference to the newly-created clustered or stacked bar chart.
     
     
3. Item: Chart.
   
   
   • VBA construct: type part of the Dim statement.
     
     
   • Description: Chart is the data type of the declared variable. In other words, myChart is declared as of the Chart object data type.

Line #2: Set myChart = Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart

1. Item: Set.
   
   
   • VBA construct: Set statement.
     
     
   • Description: Set assigns an object reference to an object variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which an object reference is assigned to.
       
       
     • The object reference assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.
       
       
2. Item: myChart.
   
   
   • VBA construct: objectvar part of the Set statement, variable part of the assignment (=) operator, and object variable of the Chart object data type.
     
     
   • Description: myChart represents a reference to the newly-created clustered or stacked bar chart. Within the Set statement, myChart is the object variable to which an object reference is assigned to.
     
     
3. Item: =.
   
   
   • VBA construct: Assignment operator.
     
     
   • Description: The assignment operator assigns a value to a variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which the value is assigned to.
       
       
     • The value assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.
       
       
4. Item: Worksheet.
   
   
   • VBA construct: Worksheet object.
     
     
   • Description: Worksheet represents the worksheet where you insert the newly-created clustered or stacked bar chart.
     
     Use constructs such as the Application.ActiveSheet property or the Workbook.Worksheets property to return the appropriate Worksheet object. If you explicitly declare an object variable to represent Worksheet, use the Worksheet object data type.
     
     
5. Item: Shapes.
   
   
   • VBA construct: Worksheet.Shapes property.
     
     
   • Description: The Worksheet.Shapes property returns a Shapes collection representing all the shapes within Worksheet. Each Shape object within the Shapes collection represents an object in the drawing lawyer of the worksheet.
     
     
6. Item: AddChart2.
   
   
   • VBA construct: Shapes.AddChart2 method.
     
     
   • Description: The Shapes.AddChart2 method creates a chart. Shapes.AddChart2 returns a Shape object representing the newly-created clustered or stacked bar chart. This Shape object is added to the Shapes collection representing all the shapes within Worksheet.
     
     The AddChart2 method was introduced in Excel 2013 and isn't backward compatible. To create a chart in Excel 2007 or Excel 2010 using VBA, use the Shapes.AddChart method. For an explanation and example of how to work with the AddChart method, please refer to the appropriate section below.
     
     
7. Item: Style:=-1.
   
   
   • VBA construct: Style parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Style parameter of Shapes.AddChart2 specifies the style of the newly-created clustered or stacked bar chart.
     
     When you set Style to -1 (as in this macro structure), the newly-created chart gets the default style for the applicable clustered or stacked bar chart type.
     
     
8. Item: XlChartType:=ChartType.
   
   
   • VBA construct: XlChartType parameter of the Shapes.AddChart2 method.
     
     
   • Description: The XlChartType parameter of Shapes.AddChart2 specifies the type of the newly-created clustered or stacked bar chart. You can set XlChartType to any of the built-in constants within the XlChartType enumeration. For purposes of creating a clustered or stacked bar chart, use one of the following built-in constants or values:
     
     
     • xlPyramidBarClustered (or 109) to create a clustered pyramid bar chart.
       
       
     • xlPyramidBarStacked (or 110) to create a stacked pyramid bar chart.
       
       
     • xlPyramidBarStacked100 (or 111) to create a 100% stacked pyramid bar chart.
       
       
     • xlBarClustered (or 57) to create a clustered bar chart.
       
       
     • xlBarStacked (or 58) to create a stacked bar chart.
       
       
     • xlBarStacked100 (or 59) to create a 100% stacked bar chart.
       
       
     • xlConeBarClustered (or 102) to create a clustered cone bar chart.
       
       
     • xlConeBarStacked (or 103) to create a stacked cone bar chart.
       
       
     • xlConeBarStacked100 (or 104) to create a 100% stacked cone bar chart.
       
       
     • xlCylinderBarClustered (or 95) to create a clustered cylinder bar chart.
       
       
     • xlCylinderBarStacked (or 96) to create a stacked cylinder bar chart.
       
       
     • xlCylinderBarStacked100 (or 97) to create a 100% stacked cylinder bar chart.
       
       
9. Item: Left:=ChartDestination.Cells(1).Left.
   
   
   • VBA construct: Left parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Left parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart's left edge relative to the anchor which, in this case, is the left edge of column A. For purposes of this macro structure, Left's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
       
       
     • The Range.Left property returns a value representing the distance, in points, between the left edge of column A and the left edge of ChartDestination.
       
       

     Because of the above, “ChartDestination.Cells(1).Left” sets the left edge of the newly-created clustered or stacked bar chart to be at the left edge of ChartDestination.
     
     

10. Item: Top:=ChartDestination.Cells(1).Top.
    
    
    • VBA construct: Top parameter of the Shapes.AddChart2 method.
      
      
    • Description: The Top parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart's top edge relative to the anchor which, in this case, is the top edge of row 1. For purposes of this macro structure, Top's value is determined as follows:
      
      
      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
        
        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
        
        
      • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
        
        
      • The Range.Top property returns a value representing the distance, in points, between the top edge of row 1 and the top edge of ChartDestination.
        
        

      Because of the above, “ChartDestination.Cells(1).Top” sets the top edge of the newly-created clustered or stacked bar chart to be at the top edge of ChartDestination.
      
      

11. Item: Width:=ChartDestination.Width.
    
    
    • VBA construct: Width parameter of the Shapes.AddChart2 method.
      
      
    • Description: The Width parameter of Shapes.AddChart2 specifies the width, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Width's value is determined as follows:
      
      
      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
        
        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
        
        
      • The Range.Width property returns a value representing the width, in points, of ChartDestination.
        
        

      Because of the above, “ChartDestination.Width” sets the width of the newly-created clustered or stacked bar chart to be the width of ChartDestination.
      
      

12. Item: Height:=ChartDestination.Height.
    
    
    • VBA construct: Height parameter of the Shapes.AddChart2 method.
      
      
    • Description: The Height parameter of Shapes.AddChart2 specifies the height, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Height's value is determined as follows:
      
      
      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
        
        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
        
        
      • The Range.Height property returns a value representing the height, in points, of ChartDestination.
        
        

      Because of the above, “ChartDestination.Height” sets the height of the newly-created clustered or stacked bar chart to be the height of ChartDestination.
      
      

13. Item: NewLayout:=Boolean.
    • VBA construct: NewLayout parameter of the Shapes.AddChart2 method.
      
      
    • Description: The NewLayout parameter of Shapes.AddChart2 specifies whether the newly-created clustered or stacked bar chart is inserted by using certain dynamic formatting rules. The 2 main consequences of applying these dynamic formatting rules are that:
      
      
      • The chart title is displayed.
        
        
      • The legend is displayed only if the bar chart contains multiple series.
        
        

      You specify NewLayout's value as a Boolean (True or False).
      
      

      • If you set NewLayout to True, the new dynamic formatting rules apply.
        
        
      • If you set NewLayout to False, the new dynamic formatting rules don't apply.
        
        
14. Item: Chart.
    
    
    • VBA construct: Shape.Chart property.
      
      
    • Description: The Shape.Chart property returns a Chart object representing the chart contained within the shape. For purposes of this macro structure, the Chart object returned by Shape.Chart represents the newly-created clustered or stacked bar chart.
      
      
15. Item: Worksheet.Shapes.AddChart2(Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean).Chart.
    
    
    • VBA construct: objectexpression part of the Set statement and value part of the assignment (=) operator.
      
      
    • Description: This expression returns a Chart object representing the newly-created clustered or stacked bar chart. This Chart object is assigned to myChart.

Line #3: myChart.SetSourceData Source:=SourceDataRange

1. Item: myChart.
   
   
   • VBA construct: Object variable of the Chart object data type.
     
     
   • Description: myChart represents a reference to the newly-created clustered or stacked bar chart.
2. Item: SetSourceData.
   
   
   • VBA construct: Chart.SetSourceData method.
     
     
   • Description: The Chart.SetSourceData method sets the source data range for myChart.
     
     
3. Item: Source:=SourceDataRange.
   
   
   • VBA construct: Source parameter of the Chart.SetSourceData method.
     
     
   • Description: The Source parameter of Chart.SetSourceData specifies the cell range containing the source data for myChart.
     
     SourceDataRange is a Range object representing the cell range that contains the source data for myChart.
     
     You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent SourceDataRange, use the Range object data type.

Macro example to create an embedded clustered or stacked bar chart (without selecting the source data range)

The following macro example creates an embedded clustered bar chart (myChart) in the worksheet named “embedded clustered bar chart” (myWorksheet) of the workbook containing the macro (ThisWorkbook). For these purposes:

• The source data (mySourceData) is contained in cells A5 to F10 of myWorksheet; and
  
  
• The clustered bar chart is located over cells H5 to M24 of myWorksheet (myChartDestination).

Sub createEmbeddedClusteredBarChart()
    'source: https://powerspreadsheets.com/
    'creates an embedded clustered bar chart (without selecting the source data range)
    'for further information: https://powerspreadsheets.com/vba-create-bar-chart/

    'declare object variables to hold references to worksheet, source data cell range, created bar chart, and destination cell range
    Dim myWorksheet As Worksheet
    Dim mySourceData As Range
    Dim myChart As Chart
    Dim myChartDestination As Range

    'identify worksheet containing source data and created bar chart
    Set myWorksheet = ThisWorkbook.Worksheets("embedded clustered bar chart")

    With myWorksheet

        'identify source data
        Set mySourceData = .Range("A5:F10")

        'identify chart location
        Set myChartDestination = .Range("H5:M24")

        'create bar chart
        Set myChart = .Shapes.AddChart2(Style:=-1, XlChartType:=xlBarClustered, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height, NewLayout:=False).Chart

    End With

    'set source data for created bar chart
    myChart.SetSourceData Source:=mySourceData

End Sub

Effects of executing macro example to create an embedded clustered or stacked bar chart (without selecting the source data range)

The following image illustrates the results of executing the macro example. An embedded clustered bar chart is created over cells H5 to M24. The source data for this chart is in cells A5 to F10.

#2: Create an embedded clustered or stacked bar chart (selecting the source data range)

VBA code to create an embedded clustered or stacked bar chart (selecting the source data range)

To create an embedded clustered or stacked bar chart (selecting the source data range) using VBA, use a macro with the following statement structure:

SourceDataWorksheet.Activate
SourceDataRange.Select
DestinationWorksheet.Shapes.AddChart2 Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean

Process followed by VBA to create an embedded clustered or stacked bar chart (selecting the source data range)

To create an embedded clustered or stacked bar chart (selecting the source data range), follow these steps within your VBA code:

1. Activate the worksheet containing the source data.
   
   
2. Select the cell range containing the source data.
   
   
3. Create a clustered or stacked bar chart with the Shapes.AddChart2 method.

VBA statement explanation

Line #1: SourceDataWorksheet.Activate

1. Item: SourceDataWorksheet.
   
   
   • VBA construct: Worksheet object.
     
     
   • Description: SourceDataWorksheet represents the worksheet where the cell range containing the source data for the clustered or stacked bar chart is located.
     
     Use constructs such as the Workbook.Worksheets property to return the appropriate Worksheet object. If you explicitly declare an object variable to represent SourceDataWorksheet, use the Worksheet object data type.
     
     
2. Item: Activate.
   
   
   • VBA construct: Worksheet.Activate method.
     
     
   • Description: The Activate method makes SourceDataWorksheet the active sheet.
     
     The main reason for including this statement is that, generally, you can't select a cell on a worksheet that isn't active. If SourceDataWorksheet is already the active worksheet, you may omit this statement.

Line #2: SourceDataRange.Select

1. Item: SourceDataRange.
   
   
   • VBA construct: Range object.
     
     
   • Description: Range object representing the cell range that contains the source data for the clustered or stacked bar chart.
     
     You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent SourceDataRange, use the Range object data type.
     
     
2. Item: Select.
   
   
   • VBA construct: Range.Select method.
     
     
   • Description: The Select method selects SourceDataRange.

Line #3: ChartDestinationWorksheet.Shapes.AddChart2 Style:=-1, XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height, NewLayout:=Boolean

1. Item: ChartDestinationWorksheet.
   
   
   • VBA construct: Worksheet object.
     
     
   • Description: ChartDestinationWorksheet represents the worksheet where you insert the newly-created clustered or stacked bar chart.
     
     Use constructs such as the Application.ActiveSheet property or the Workbook.Worksheets property to return the appropriate Worksheet object.
     
     
2. Item: Shapes.
   
   
   • VBA construct: Worksheet.Shapes property.
     
     
   • Description: The Worksheet.Shapes property returns a Shapes collection representing all the shapes within ChartDestinationWorksheet. Each Shape object within the Shapes collection represents an object in the drawing lawyer of the worksheet.
     
     
3. Item: AddChart2.
   
   
   • VBA construct: Shapes.AddChart2 method.
     
     
   • Description: The Shapes.AddChart2 method creates a chart. Shapes.AddChart2 returns a Shape object representing the newly-created clustered or stacked bar chart. This Shape object is added to the Shapes collection representing all the shapes within ChartDestinationWorksheet.
     
     The AddChart2 method was introduced in Excel 2013 and isn't backward compatible. To create a chart in Excel 2007 or Excel 2010 using VBA, use the Shapes.AddChart method. For an explanation and example of how to work with the AddChart method, please refer to the appropriate section below.
     
     
4. Item: Style:=-1.
   
   
   • VBA construct: Style parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Style parameter of Shapes.AddChart2 specifies the style of the newly-created clustered or stacked bar chart.
     
     When you set Style to -1 (as in this macro structure), the newly-created chart gets the default style for the applicable clustered or stacked bar chart type.
     
     
5. Item: XlChartType:=ChartType.
   
   
   • VBA construct: XlChartType parameter of the Shapes.AddChart2 method.
     
     
   • Description: The XlChartType parameter of Shapes.AddChart2 specifies the type of the newly-created clustered or stacked bar chart. You can set XlChartType to any of the built-in constants within the XlChartType enumeration. For purposes of creating a clustered or stacked bar chart, use one of the following built-in constants or values:
     
     
     • xlPyramidBarClustered (or 109) to create a clustered pyramid bar chart.
       
       
     • xlPyramidBarStacked (or 110) to create a stacked pyramid bar chart.
       
       
     • xlPyramidBarStacked100 (or 111) to create a 100% stacked pyramid bar chart.
       
       
     • xlBarClustered (or 57) to create a clustered bar chart.
       
       
     • xlBarStacked (or 58) to create a stacked bar chart.
       
       
     • xlBarStacked100 (or 59) to create a 100% stacked bar chart.
       
       
     • xlConeBarClustered (or 102) to create a clustered cone bar chart.
       
       
     • xlConeBarStacked (or 103) to create a stacked cone bar chart.
       
       
     • xlConeBarStacked100 (or 104) to create a 100% stacked cone bar chart.
       
       
     • xlCylinderBarClustered (or 95) to create a clustered cylinder bar chart.
       
       
     • xlCylinderBarStacked (or 96) to create a stacked cylinder bar chart.
       
       
     • xlCylinderBarStacked100 (or 97) to create a 100% stacked cylinder bar chart.
       
       
6. Item: Left:=ChartDestination.Cells(1).Left.
   
   
   • VBA construct: Left parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Left parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart's left edge relative to the anchor which, in this case, is the left edge of column A. For purposes of this macro structure, Left's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
       
       
     • The Range.Left property returns a value representing the distance, in points, between the left edge of column A and the left edge of ChartDestination.
       
       

     Because of the above, “ChartDestination.Cells(1).Left” sets the left edge of the newly-created clustered or stacked bar chart to be at the left edge of ChartDestination.
     
     

7. Item: Top:=ChartDestination.Cells(1).Top.
   
   
   • VBA construct: Top parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Top parameter of Shapes.AddChart2 specifies the position, in points, of the newly-created clustered or stacked bar chart's top edge relative to the anchor which, in this case, is the top edge of row 1. For purposes of this macro structure, Top's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
       
       
     • The Range.Top property returns a value representing the distance, in points, between the top edge of row 1 and the top edge of ChartDestination.
       
       

     Because of the above, “ChartDestination.Cells(1).Top” sets the top edge of the newly-created clustered or stacked bar chart to be at the top edge of ChartDestination.
     
     

8. Item: Width:=ChartDestination.Width.
   
   
   • VBA construct: Width parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Width parameter of Shapes.AddChart2 specifies the width, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Width's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Width property returns a value representing the width, in points, of ChartDestination.
       
       

     Because of the above, “ChartDestination.Width” sets the width of the newly-created clustered or stacked bar chart to be the width of ChartDestination.
     
     

9. Item: Height:=ChartDestination.Height.
   
   
   • VBA construct: Height parameter of the Shapes.AddChart2 method.
     
     
   • Description: The Height parameter of Shapes.AddChart2 specifies the height, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Height's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Height property returns a value representing the height, in points, of ChartDestination.
       
       

     Because of the above, “ChartDestination.Height” sets the height of the newly-created clustered or stacked bar chart to be the height of ChartDestination.
     
     

10. Item: NewLayout:=Boolean.
    
    
    • VBA construct: NewLayout parameter of the Shapes.AddChart2 method.
      
      
    • Description: The NewLayout parameter of Shapes.AddChart2 specifies whether the newly-created clustered or stacked bar chart is inserted by using certain dynamic formatting rules. The 2 main consequences of applying these dynamic formatting rules are that:
      
      
      • The chart title is displayed.
        
        
      • The legend is displayed only if the bar chart contains multiple series.
        
        

      You specify NewLayout's value as a Boolean (True or False).
      
      

      • If you set NewLayout to True, the new dynamic formatting rules apply.
        
        
      • If you set NewLayout to False, the new dynamic formatting rules don't apply.

Macro example to create an embedded clustered or stacked bar chart (selecting the source data range)

The following macro example creates an embedded stacked bar chart (myChart) in the worksheet named “embedded stacked bar chart” (myWorksheet) of the workbook containing the macro (ThisWorkbook). For these purposes:

• The source data (mySourceData) is contained in cells A5 to F10 of myWorksheet; and
  
  
• The clustered bar chart is located over cells H5 to M24 of myWorksheet (myChartDestination).

Sub createEmbeddedStackedBarChart()
    'source: https://powerspreadsheets.com/
    'creates an embedded stacked bar chart (selecting the source data range)
    'for further information: https://powerspreadsheets.com/vba-create-bar-chart/

    'declare object variables to hold references to worksheet, source data cell range, created bar chart, and destination cell range
    Dim myWorksheet As Worksheet
    Dim mySourceData As Range
    Dim myChart As Chart
    Dim myChartDestination As Range

    'identify worksheet containing source data and created bar chart
    Set myWorksheet = ThisWorkbook.Worksheets("embedded stacked bar chart")

    'identify source data
    Set mySourceData = myWorksheet.Range("A5:F10")

    'activate worksheet and select source data
    myWorksheet.Activate
    mySourceData.Select

    With myWorksheet

        'identify chart location
        Set myChartDestination = .Range("H5:M24")

        'create bar chart
        .Shapes.AddChart2 Style:=-1, XlChartType:=xlBarStacked, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height, NewLayout:=False

    End With

End Sub

Effects of executing macro example to create an embedded clustered or stacked bar chart (selecting the source data range)

The following image illustrates the results of executing the macro example. An embedded stacked bar chart is created over cells H5 to M24. The source data for this chart is in cells A5 to F10.

#3: Create an embedded clustered or stacked bar chart (compatible with Excel 2007 and Excel 2010)

VBA code to create an embedded clustered or stacked bar chart (compatible with Excel 2007 and Excel 2010)

To create an embedded clustered or stacked bar chart in Excel 2007 or Excel 2010 using VBA, use a macro with the following statement structure:

Dim myChart As Chart
Set myChart = Worksheet.Shapes.AddChart(XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height).Chart
myChart.SetSourceData Source:=SourceDataRange

Process followed by VBA to create an embedded clustered or stacked bar chart (compatible with Excel 2007 and Excel 2010)

To create an embedded clustered or stacked bar chart (using VBA code that's compatible with Excel 2007 and Excel 2010), follow these steps within your VBA code:

1. Declare an object variable (myChart) to represent the newly-created clustered or stacked bar chart.
   
   
2. Create a clustered or stacked bar chart with the Shapes.AddChart method.
   
   
3. Assign the Chart object representing the newly-created clustered or stacked bar chart to the myChart object variable.
   
   
4. Specify the source data for the newly-created clustered or stacked bar chart with the Chart.SetSourceData method.

VBA statement explanation

Line #1: Dim myChart As Chart

1. Item: Dim.
   
   
   • VBA construct: Dim statement.
     
     
   • Description: The Dim statement declares the myChart object variable and allocates storage space.
     
     
2. Item: myChart.
   
   
   • VBA construct: Object variable.
     
     
   • Description: myChart is an object variable of the Chart object data type. The purpose of myChart is to represent a reference to the newly-created clustered or stacked bar chart.
     
     
3. Item: Chart.
   
   
   • VBA construct: type part of the Dim statement.
     
     
   • Description: Chart is the data type of the declared variable. In other words, myChart is declared as of the Chart object data type.

Line #2: Set myChart = Worksheet.Shapes.AddChart(XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height).Chart

1. Item: Set.
   
   
   • VBA construct: Set statement.
     
     
   • Description: Set assigns an object reference to an object variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which an object reference is assigned to.
       
       
     • The object reference assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.
       
       
2. Item: myChart.
   
   
   • VBA construct: objectvar part of the Set statement, variable part of the assignment (=) operator, and object variable of the Chart object data type.
     
     
   • Description: myChart represents a reference to the newly-created clustered or stacked bar chart. Within the Set statement, myChart is the object variable to which an object reference is assigned to.
     
     
3. Item: =.
   
   
   • VBA construct: Assignment operator.
     
     
   • Description: The assignment operator assigns a value to a variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which the value is assigned to.
       
       
     • The value assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Shape.Chart property.
       
       
4. Item: Worksheet.
   
   
   • VBA construct: Worksheet object.
     
     
   • Description: Worksheet represents the worksheet where you insert the newly-created clustered or stacked bar chart.
     
     Use constructs such as the Application.ActiveSheet property or the Workbook.Worksheets property to return the appropriate Worksheet object. If you explicitly declare an object variable to represent Worksheet, use the Worksheet object data type.
     
     
5. Item: Shapes.
   
   
   • VBA construct: Worksheet.Shapes property.
     
     
   • Description: The Worksheet.Shapes property returns a Shapes collection representing all the shapes within Worksheet. Each Shape object within the Shapes collection represents an object in the drawing lawyer of the worksheet.
     
     
6. Item: AddChart.
   
   
   • VBA construct: Shapes.AddChart method.
     
     
   • Description: The Shapes.AddChart method creates a chart. Shapes.AddChart returns a Shape object representing the newly-created clustered or stacked bar chart. This Shape object is added to the Shapes collection representing all the shapes within Worksheet.
     
     In Excel 2013, Microsoft introduced the AddChart2 method. AddChart2 is generally more powerful and easier to work with than AddChart. However, AddChart continues to be supported and, therefore, macros that use AddChart work appropriately in Excel 2007 and later.
     
     
7. Item: XlChartType:=ChartType.
   
   
   • VBA construct: XlChartType parameter of the Shapes.AddChart method.
     
     
   • Description: The XlChartType parameter of Shapes.AddChart specifies the type of the newly-created clustered or stacked bar chart. You can set XlChartType to any of the built-in constants within the XlChartType enumeration. For purposes of creating a clustered or stacked bar chart, use one of the following built-in constants or values:
     
     
     • xlPyramidBarClustered (or 109) to create a clustered pyramid bar chart.
       
       
     • xlPyramidBarStacked (or 110) to create a stacked pyramid bar chart.
       
       
     • xlPyramidBarStacked100 (or 111) to create a 100% stacked pyramid bar chart.
       
       
     • xlBarClustered (or 57) to create a clustered bar chart.
       
       
     • xlBarStacked (or 58) to create a stacked bar chart.
       
       
     • xlBarStacked100 (or 59) to create a 100% stacked bar chart.
       
       
     • xlConeBarClustered (or 102) to create a clustered cone bar chart.
       
       
     • xlConeBarStacked (or 103) to create a stacked cone bar chart.
       
       
     • xlConeBarStacked100 (or 104) to create a 100% stacked cone bar chart.
       
       
     • xlCylinderBarClustered (or 95) to create a clustered cylinder bar chart.
       
       
     • xlCylinderBarStacked (or 96) to create a stacked cylinder bar chart.
       
       
     • xlCylinderBarStacked100 (or 97) to create a 100% stacked cylinder bar chart.
       
       
8. Item: Left:=ChartDestination.Cells(1).Left.
   
   
   • VBA construct: Left parameter of the Shapes.AddChart method.
     
     
   • Description: The Left parameter of Shapes.AddChart specifies the position, in points, of the newly-created clustered or stacked bar chart's left edge relative to the anchor which, in this case, is the left edge of column A. For purposes of this macro structure, Left's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
       
       
     • The Range.Left property returns a value representing the distance, in points, between the left edge of column A and the left edge of ChartDestination.
       
       

     Because of the above, “ChartDestination.Cells(1).Left” sets the left edge of the newly-created clustered or stacked bar chart to be at the left edge of ChartDestination.
     
     

9. Item: Top:=ChartDestination.Cells(1).Top.
   
   
   • VBA construct: Top parameter of the Shapes.AddChart method.
     
     
   • Description: The Top parameter of Shapes.AddChart specifies the position, in points, of the newly-created clustered or stacked bar chart's top edge relative to the anchor which, in this case, is the top edge of row 1. For purposes of this macro structure, Top's value is determined as follows:
     
     
     • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
       
       You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
       
       
     • The Range.Cells and Range.Item properties (Cells(1)) return a Range object representing the first (top-left corner) cell within ChartDestination.
       
       
     • The Range.Top property returns a value representing the distance, in points, between the top edge of row 1 and the top edge of ChartDestination.
       
       

     Because of the above, “ChartDestination.Cells(1).Top” sets the top edge of the newly-created clustered or stacked bar chart to be at the top edge of ChartDestination.
     
     

10. Item: Width:=ChartDestination.Width.
    
    
    • VBA construct: Width parameter of the Shapes.AddChart method.
      
      
    • Description: The Width parameter of Shapes.AddChart specifies the width, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Width's value is determined as follows:
      
      
      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
        
        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
        
        
      • The Range.Width property returns a value representing the width, in points, of ChartDestination.
        
        

      Because of the above, “ChartDestination.Width” sets the width of the newly-created clustered or stacked bar chart to be the width of ChartDestination.
      
      

11. Item: Height:=ChartDestination.Height.
    
    
    • VBA construct: Height parameter of the Shapes.AddChart method.
      
      
    • Description: The Height parameter of Shapes.AddChart specifies the height, in points, of the newly-created clustered or stacked bar chart. For purposes of this macro structure, Height's value is determined as follows:
      
      
      • ChartDestination is a Range object representing the cells where you want the newly-created clustered or stacked bar chart to be located.
        
        You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent ChartDestination, use the Range object data type.
        
        
      • The Range.Height property returns a value representing the height, in points, of ChartDestination.
        
        

      Because of the above, “ChartDestination.Height” sets the height of the newly-created clustered or stacked bar chart to be the height of ChartDestination.
      
      

12. Item: Chart.
    
    
    • VBA construct: Shape.Chart property.
      
      
    • Description: The Shape.Chart property returns a Chart object representing the chart contained within the shape. For purposes of this macro structure, the Chart object returned by Shape.Chart represents the newly-created clustered or stacked bar chart.
      
      
13. Item: Worksheet.Shapes.AddChart(XlChartType:=ChartType, Left:=ChartDestination.Cells(1).Left, Top:=ChartDestination.Cells(1).Top, Width:=ChartDestination.Width, Height:=ChartDestination.Height).Chart.
    
    
    • VBA construct: objectexpression part of the Set statement and value part of the assignment (=) operator.
      
      
    • Description: This expression returns a Chart object representing the newly-created clustered or stacked bar chart. This Chart object is assigned to myChart.

Line #3: myChart.SetSourceData Source:=SourceDataRange

1. Item: myChart.
   
   
   • VBA construct: Object variable of the Chart object data type.
     
     
   • Description: myChart represents a reference to the newly-created clustered or stacked bar chart.
2. Item: SetSourceData.
   
   
   • VBA construct: Chart.SetSourceData method.
     
     
   • Description: The Chart.SetSourceData method sets the source data range for myChart.
     
     
3. Item: Source:=SourceDataRange.
   
   
   • VBA construct: Source parameter of the Chart.SetSourceData method.
     
     
   • Description: The Source parameter of Chart.SetSourceData specifies the cell range containing the source data for myChart.
     
     SourceDataRange is a Range object representing the cell range that contains the source data for myChart.
     
     You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent SourceDataRange, use the Range object data type.

Macro example to create an embedded clustered or stacked bar chart (compatible with Excel 2007 and Excel 2010)

The following macro example creates an embedded clustered bar chart (myChart) in the worksheet named “embedded bar chart compatible” (myWorksheet) of the workbook containing the macro (ThisWorkbook). For these purposes:

• The source data (mySourceData) is contained in cells A5 to F10 of myWorksheet; and
  
  
• The clustered bar chart is located over cells H5 to M24 of myWorksheet (myChartDestination).

This macro is the rough equivalent to a previous example (createEmbeddedClusteredBarChart). The main difference between both macros is the VBA construct they rely on to create the embedded clustered bar chart.

• createEmbeddedClusteredBarChart works with the Shapes.AddChart2 method. This method was introduced in Excel 2013 and isn't backward-compatible. Therefore, the createEmbeddedClusteredBarChart doesn't work appropriately in Excel 2007 or Excel 2010.
  
  
• The following macro example works with the Shapes.AddChart method. This method is compatible with Excel 2007 and later. Therefore, this macro works appropriately with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.

The resulting embedded clustered bar charts aren't identical. One of the reasons for some of these differences is the fact that the AddChart2 method accepts some additional parameters (particularly Style and NewLayout) that have no direct equivalent in the AddChart method.

Sub createEmbeddedClusteredBarChart20072010()

    'source: https://powerspreadsheets.com/
    'creates an embedded clustered bar chart. Code is compatible with Excel 2007 and Excel 2010
    'for further information: https://powerspreadsheets.com/vba-create-bar-chart/

    'declare object variables to hold references to worksheet, source data cell range, created bar chart, and destination cell range
    Dim myWorksheet As Worksheet
    Dim mySourceData As Range
    Dim myChart As Chart
    Dim myChartDestination As Range

    'identify worksheet containing source data and created bar chart
    Set myWorksheet = ThisWorkbook.Worksheets("embedded bar chart compatible")

    With myWorksheet

        'identify source data
        Set mySourceData = .Range("A5:F10")

        'identify chart location
        Set myChartDestination = .Range("H5:M24")

        'create bar chart
        Set myChart = .Shapes.AddChart(XlChartType:=xlBarClustered, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height).Chart

    End With

    'set source data for created bar chart
    myChart.SetSourceData Source:=mySourceData
End Sub

Effects of executing macro example to create an embedded clustered or stacked bar chart (compatible with Excel 2007 and Excel 2010)

The following image illustrates the results of executing the macro example. An embedded clustered bar chart is created over cells H5 to M24. The source data for this chart is in cells A5 to F10.

#4: Create a clustered or stacked bar chart in a chart sheet

VBA code to create a clustered or stacked bar chart in a chart sheet

To create a clustered or stacked bar chart in a chart sheet using VBA, use a macro with the following statement structure:

Dim myChart As Chart
Set myChart = Charts.Add2(Before:=BeforeSheet, After:=AfterSheet, NewLayout:=Boolean)
With myChart
    .SetSourceData Source:=SourceDataRange
    .ChartType = ChartTypeValue
End With

Process followed by VBA to create a clustered or stacked bar chart in a chart sheet

To create a clustered or stacked bar chart in a chart sheet, follow these steps within your VBA code:

1. Declare an object variable (myChart) to represent the newly-created clustered or stacked bar chart.
   
   
2. Create a chart with the Charts.Add2 method.
   
   
3. Assign the Chart object representing the newly-created chart to the myChart object variable.
   
   
4. Specify the source data for the newly-created chart with the Chart.SetSourceData method.
   
   
5. Specify that the newly-created chart is a clustered or stacked bar chart with the Chart.ChartType property.

VBA statement explanation

Line #1: Dim myChart As Chart

1. Item: Dim.
   
   
   • VBA construct: Dim statement.
     
     
   • Description: The Dim statement declares the myChart object variable and allocates storage space.
     
     
2. Item: myChart.
   
   
   • VBA construct: Object variable.
     
     
   • Description: myChart is an object variable of the Chart object data type. The purpose of myChart is to represent a reference to the newly-created clustered or stacked bar chart.
     
     
3. Item: Chart.
   
   
   • VBA construct: type part of the Dim statement.
     
     
   • Description: Chart is the data type of the declared variable. In other words, myChart is declared as of the Chart object data type.

Line #2: Set myChart = Charts.Add2(Before:=BeforeSheet, After:=AfterSheet, NewLayout:=Boolean)

1. Item: Set.
   
   
   • VBA construct: Set statement.
     
     
   • Description: Set assigns an object reference to an object variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which an object reference is assigned to.
       
       
     • The object reference assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Charts.Add2 method.
       
       
2. Item: myChart.
   
   
   • VBA construct: objectvar part of the Set statement, variable part of the assignment (=) operator, and object variable of the Chart object data type.
     
     
   • Description: myChart represents a reference to the newly-created clustered or stacked bar chart. Within the Set statement, myChart is the object variable to which an object reference is assigned to.
     
     
3. Item: =.
   
   
   • VBA construct: Assignment operator.
     
     
   • Description: The assignment operator assigns a value to a variable. For purposes of this macro structure:
     
     
     • myChart is the object variable to which the value is assigned to.
       
       
     • The value assigned to myChart is the Chart object representing the newly-created clustered or stacked bar chart, as returned by the Charts.Add2 method.
       
       
4. Item: Charts.
   
   
   • VBA construct: Charts collection.
     
     
   • Description: The Charts object is a collection representing all the chart sheets within the applicable workbook. Each chart sheet is represented by a Chart object.
     
     
5. Item: Add2.
   
   
   • VBA construct: Charts.Add2 method.
     
     
   • Description: The Add2 method creates a new chart sheet and returns a Chart object representing this newly-created chart sheet.
     
     
6. Item: Before:=BeforeSheet, After:=AfterSheet.
   
   
   • VBA construct: Before and After parameters of the Charts.Add2 method.
     
     
   • Description: The Before and After parameters of Charts.Add2 specify the sheet before (in the case of Before) or after (in the case of After) which the newly-created chart sheet (containing the clustered or stacked bar chart) is added.
     
     You generally:
     
     
     • Only specify 1 of these parameters (Before or After); and
       
       
     • Identify the appropriate sheet (BeforeSheet or AfterSheet) as an object. For these purposes, you can rely on constructs such as the Workbook.Sheets, Workbook.Charts or Workbook.Worksheets properties.
       
       

     If you omit both Before and After, the newly-inserted chart sheet is located before the active sheet.
     
     

7. Item: NewLayout:=Boolean.
   
   
   • VBA construct: NewLayout parameter of the Charts.Add2 method.
     
     
   • Description: The NewLayout parameter of Charts.Add2 specifies whether the newly-created clustered or stacked bar chart is inserted by using certain dynamic formatting rules. The 2 main consequences of applying these dynamic formatting rules are that:
     
     
     • The chart title is displayed.
       
       
     • The legend is displayed only if the bar chart contains multiple series.
       
       

     You specify NewLayout's value as a Boolean (True or False).
     
     

     • If you set NewLayout to True, the new dynamic formatting rules apply.
       
       
     • If you set NewLayout to False, the new dynamic formatting rules don't apply.

Lines #3 and #6: With myChart | End With

1. Item: With… End With.
   
   
   • VBA construct: With… End With statement.
     
     
   • Description: Statements within the With… End With statement are executed on the Chart object represented by myChart.

Line #4: .SetSourceData Source:=SourceDataRange

1. Item: SetSourceData.
   
   
   • VBA construct: Chart.SetSourceData method.
     
     
   • Description: The Chart.SetSourceData method sets the source data range for myChart.
     
     
2. Item: Source:=SourceDataRange.
   
   
   • VBA construct: Source parameter of the Chart.SetSourceData method.
     
     
   • Description: The Source parameter of Chart.SetSourceData specifies the cell range containing the source data for myChart.
     
     SourceDataRange is a Range object representing the cell range that contains the source data for myChart.
     
     You can usually return a Range object with constructs such as the Worksheet.Range, Worksheet.Cells (with Range.Item), Range.Offset or Range.Resize properties. If you explicitly declare an object variable to represent SourceDataRange, use the Range object data type.

Line #5: .ChartType = ChartTypeValue

1. Item: ChartType.
   
   
   • VBA construct: Chart.ChartType property.
     
     
   • Description: Chart.ChartType sets the type of a chart.
     
     
2. Item: =.
   
   
   • VBA construct: Assignment operator.
     
     
   • Description: The assignment operator assigns a value to a property. For purposes of this macro structure:
     
     
     • The Chart.ChartType property is the property to which the value is assigned.
       
       
     • The value assigned to ChartType is the value represented by ChartTypeValue.
       
       
3. Item: ChartTypeValue.
   
   
   • VBA construct: Built-in constant or value from the XlChartType enumeration.
     
     
   • Description: The built-in constants or values from the XlChartType enumeration specify the chart type of the newly-created clustered or stacked bar chart. For these purposes, use one of the following built-in constants or values:
     
     
     • xlPyramidBarClustered (or 109) to create a clustered pyramid bar chart.
       
       
     • xlPyramidBarStacked (or 110) to create a stacked pyramid bar chart.
       
       
     • xlPyramidBarStacked100 (or 111) to create a 100% stacked pyramid bar chart.
       
       
     • xlBarClustered (or 57) to create a clustered bar chart.
       
       
     • xlBarStacked (or 58) to create a stacked bar chart.
       
       
     • xlBarStacked100 (or 59) to create a 100% stacked bar chart.
       
       
     • xlConeBarClustered (or 102) to create a clustered cone bar chart.
       
       
     • xlConeBarStacked (or 103) to create a stacked cone bar chart.
       
       
     • xlConeBarStacked100 (or 104) to create a 100% stacked cone bar chart.
       
       
     • xlCylinderBarClustered (or 95) to create a clustered cylinder bar chart.
       
       
     • xlCylinderBarStacked (or 96) to create a stacked cylinder bar chart.
       
       
     • xlCylinderBarStacked100 (or 97) to create a 100% stacked cylinder bar chart.

Macro example to create a clustered or stacked bar chart in a chart sheet

The following macro example creates a stacked bar chart (myChart) in a chart sheet of the workbook containing the macro (ThisWorkbook). For these purposes:

• The source data (mySourceData) is contained in cells A5 to F10 of the worksheet named “chart sheet bar chart”; and
  
  
• The chart sheet containing the stacked bar chart is located before the first sheet within ThisWorkbook.

Sub createChartSheetStackedBarChart()

    'source: https://powerspreadsheets.com/
    'creates a stacked bar chart in a chart sheet
    'for further information: https://powerspreadsheets.com/vba-create-bar-chart/

    'declare object variables to hold references to source data and created bar chart
    Dim mySourceData As Range
    Dim myChart As Chart

    'identify source data
    Set mySourceData = ThisWorkbook.Worksheets("chart sheet bar chart").Range("A5:F10")

    'add chart sheet containing bar chart
    Set myChart = ThisWorkbook.Charts.Add2(Before:=ThisWorkbook.Sheets(1), NewLayout:=False)

    With myChart

        'set source data for created bar chart
        .SetSourceData Source:=mySourceData

        'set chart type of created bar chart
        .ChartType = xlBarStacked

    End With

End Sub

Effects of executing macro example to create a clustered or stacked bar chart in a chart sheet

The following image illustrates the results of executing the macro example. A stacked bar chart is created on a new chart sheet, which becomes the first sheet of the workbook. The source data for this chart is in a separate worksheet.

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

1. Identify or activate the workbook and worksheet that contains the source data and/or where the bar chart is to be created:
   
   
   • Workbook object.
     
     
   • Application.ActiveWorkbook property.
     
     
   • Application.ThisWorkbook property.
     
     
   • Application.Workbooks property.
     
     
   • Worksheet object.
     
     
   • Application.ActiveSheet property.
     
     
   • Workbook.Worksheets property.
     
     
   • Worksheet.Activate method.
     
     
2. Identify or select the cell range containing the source data and/or the cell range where the bar chart is to be created:
   
   
   • Range object.
     
     
   • Worksheet.Range property.
     
     
   • Worksheet.Cells property.
     
     
   • Application.Selection property.
     
     
   • Range.Cells property.
     
     
   • Range.Item property.
     
     
   • Range.Offset property.
     
     
   • Range.Resize property.
     
     
   • Range.Select method.
     
     
3. Work with the Shapes collection representing all Shape objects in a worksheet or the Charts collection representing all Chart objects in a workbook:
   
   
   • Shapes object.
     
     
   • Worksheet.Shapes property.
     
     
   • Charts object.
     
     
   • Workbook.Charts property.
     
     
4. Create a bar chart:
   
   
   • Shapes.AddChart2 method.
     
     
   • Shapes.AddChart method.
     
     
   • Charts.Add2 method.
     
     
   • Charts.Add method.
     
     
5. Return a Chart object:
   
   
   • Chart object.
     
     
   • Shape.Chart property.
     
     
6. Set the source data range for a bar chart:
   
   
   • Chart.SetSourceData method.
     
     
7. Specify the type of a bar chart:
   
   
   • Chart.ChartType property.
     
     
   • XlChartType enumeration.
     
     
8. Measure distances, heights or widths in points:
   
   
   • Range.Left property.
     
     
   • Range.Top property.
     
     
   • Range.Width property.
     
     
   • Range.Height property.
     
     
9. Work with variables and data types:
   
   
   • Dim statement.
     
     
   • Set statement.
     
     
   • = operator.
     
     
   • Boolean data type.
     
     
10. Simplify object references:
    
    
    • With… End With statement.