Macro recorded VBA not creating same column graph


This is my data in Excel, I am trying to create a column graph from it

picture of excel data

Data in column A is for the column labels and data in column B is for the column heights.

This is a picture of the graph I'm looking for: manual and desired graph

I need to do this through VBA so I created the graph manually whilst recording a macro. I got this code:

Sub Macro5()
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Report!$A$1:$B$10")
ActiveChart.ChartGroups(1).Overlap = 0
ActiveChart.ChartGroups(1).GapWidth = 0
ActiveChart.ChartTitle.Text = "Frequency"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Frequency"
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).Font
    .BaselineOffset = 0
    .Bold = msoFalse
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(89, 89, 89)
    .Fill.Transparency = 0
    .Size = 14
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Spacing = 0
    .Strike = msoNoStrike
End With
End Sub

Now, when I run this macro again, it doesn't give me the same graph that I created when I recorded this macro.

This is the graph I get when I run the macro: incorrect graph

So, my question is why is it doing this and how do I fix it? How would I make a graph like the one I made manually from the data I have?

Recording the macro didn't work at all for me and gives me a completely different graph as you can see.

To summarize I created a graph manually and recorded a macro but running the macro doesn't create the graph I created before.

Show source
| excel-vba   | vba   | excel   | graph   | charts   2016-12-10 06:12 1 Answers

Answers to Macro recorded VBA not creating same column graph ( 1 )

  1. 2016-12-10 06:12

    Below are the programmatic steps you need to take to replicate your chart.

    You must have initially followed steps that aren't the same as the ones taken by the chart wizard. The generated code from the chart wizard isn't always that helpful.

    The steps:

    • create the data in the worksheet
    • create a new shape with a chart and get the chart reference
    • assign the data to the chart
    • get the first series in the chart
    • assign the labels to the series
    • change the 'GapWidth' property on the chart's first 'ChartGroup' to get that chunky look
    • set chart title

    Just drop this code into an empty module and run it:

    Option Explicit
    Sub CreateGraph()
        Dim ws As Worksheet
        Dim rngLabels As Range
        Dim rngData  As Range
        Dim shpChart As Shape
        Dim cht As Chart
        Dim srs As Series
        ' set a reference to worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        ' get ranges for labels and data
        Set rngLabels = ws.Range("A1:A10")
        Set rngData = ws.Range("B1:B10")
        'uncomment if you want to fake up some data for the this
        'rngLabels.Value = WorksheetFunction.Transpose(Array(10, 20, 30, 40, 50, 60, 70, 80, 90, 100))
        'rngData.Value = WorksheetFunction.Transpose(Array(0, 1, 4, 9, 4, 3, 6, 4, 8, 6))
        ' create a chart shape, get chart reference and set source data
        Set shpChart = ws.Shapes.AddChart2(201, xlColumnClustered)
        Set cht = shpChart.Chart
        cht.SetSourceData Source:=rngData, PlotBy:=xlColumns
        ' update the series object with labels
        Set srs = cht.SeriesCollection(1)
        srs.XValues = rngLabels
        ' make the graph 'chunky'
        cht.ChartGroups(1).GapWidth = 0
        ' set chart title
        cht.ChartTitle.Text = "Frequency"
    End Sub

    enter image description here

Leave a reply to - Macro recorded VBA not creating same column graph

◀ Go back