Macro recorded VBA not creating same column graph

Question

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

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:

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

Sub Macro5()
Range("A1:B10").Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Report!$A$1:$B$10")
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.ChartGroups(1).Overlap = 0
ActiveChart.ChartGroups(1).GapWidth = 0
ActiveChart.ChartTitle.Select
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
    .Fill.Solid
    .Size = 14
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Spacing = 0
    .Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
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:

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.

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-12-10T05:49:55

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
    'ws.Cells.Delete
    '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

Macro recorded VBA not creating same column graph

1 Answers