CODEWITHSUNDEEP
excelvbachartshistogram
Chuck Shultz
Chuck Shultz

Reputation: 51

Excel vba histogram bin

I'm creating a histogram using VBA. I have data on the "Raw Data" sheet. I can create the chart using the data, then I Cut and Paste it to my "Summary" tab using the code below.

'
' Histogram of Entries
'
Sheets("Raw Data").Select
Range("F" & intFirstRow + 1 & ":F" & intLastRow).Select
ActiveSheet.Shapes.AddChart2(-1, xlHistogram).Select
Selection.Cut
Sheets("Summary").Select
ActiveSheet.Paste
With ActiveSheet.Shapes("Chart 3")
' Reposition the chart
    .IncrementLeft Range("E17").Left
    .IncrementTop Range("E17").Top
End With

What I'm struggling with is that I need to adjust the width of the bins, such that the chart displays the count of items in intervals of 10. I can do this in the un-automated way by

-right-clicking on the chart, then selecting "Format Plot Area",

-in the sidebar that appears, clicking on the "Plot Area Options" drop-down and selecting "Horizontal Axis",

-clicking on the histogram icon, then opening up the drop-down for "Axis Options", then

-clicking on the "Bin Width" item and putting in "10.0".

I tried recording the macro while doing this, but I get nothing in it. Any idea how I can do so using VBA?

Upvotes: 1

Views: 3313

Answers (1)

teylyn
teylyn

Reputation: 35935

The new chart types in Excel 2016, of which the histogram is one, don't yet fully support VBA and not all chart properties are exposed to the VBA object model. That's why you don't see anything returned by the macro recorder.

If you already use VBA, you may as well generate your bins in VBA, aggregate the data with formulas (or VBA) and then plot a standard column chart.

Upvotes: 3

Related Questions