Search code examples
excelvbahistogramworksheet-function

Using Excel Frequency Worksheet Function in VBA for Histograms

I am trying to generate a histogram using VBA (the histogram will be classified as a clustered column chart to have customizable axis titles). I am currently using the excel worksheet function frequency to generate my counts in each bin. In the actual worksheet this is no problem, but when this is transferred into VBA it has an error as a type mismatch.

I have some coding experience but not a ton. One thing I read online is sometimes when you group cells in a range they get classified as an array and that could be why it is a type mismatch? Another issue I could potentially see occurring is that the excel formula does a spill, will setting the formula in a single cell like in line 3 work? I can attach the whole code if needed but it is semi long and this gives focus to what the problem I am having is I think?

My code line for the formula is long since it is somewhat dynamic. The number of rows in the data array is static (always 22). However, depending on the selected bin range based on some cell inputs in excel, the "bins array" can change in length.

Thank you!

Dim binCount As Integer
binCount = WorksheetFunction.Frequency(ws3.Range(Cells(2, emptycol + 1), Cells(23, emptycol + 1)), ws3.Range(Cells(2, emptycol + 3), Cells(num_bins, emptycol + 3)))
ws3.Cells(2, emptycol + 4).Value = binCount

Bin Range 1

Bin Range 2

Solution

  • WorksheetFunction.Frequency returns a one-based array. Change:

    Dim binCount As Integer

    to

    Dim binCount As Variant 'or just Dim binCount

    then use Ubound and Resize when writing this array to the sheet.

    Side notes:

    • You need to qualify the worksheet before the Cells calls.
    • Additional variables may be helpful here:
    With ws3
    Dim dataRange As Range
    Set dataRange = .Range(.Cells(2, emptycol + 1), .Cells(23, emptycol + 1))

    Dim binsRange As Range
    Set binsRange = .Range(.Cells(2, emptycol + 3), .Cells(num_bins, emptycol + 3))
End With

Dim binCount As Variant
binCount = WorksheetFunction.Frequency(dataRange.Value, binsRange.Value)

ws3.Cells(2, emptycol + 4).Resize(Ubound(binCount)).Value = binCount