Creating histogram using arrays as source

Question

I want to build a histogram using arrays of bins and frequences I calculated earlier in the program:

Sub createHistogram(binsArray() As Variant, frequencesArray() As Variant, _
                                                resultWorkbook As Workbook)
    With resultWorkbook("result_values")

    .Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=frequencesArray 'error here
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.FullSeriesCollection(1).XValues = binsArray

    End With
End Sub

getFrequencesArray function:

Function getFrequencesArray(binsArray() As Variant, dataArray() As Double)
    Dim binsNumber As Long
    binsNumber = UBound(binsArray) - LBound(binsArray) + 1

    Dim resultArray() As Variant
    ReDim resultArray(1 To binsNumber)
    resultArray(1) = 1
    ...

    getFrequencesArray = resultArray
End Function

getBinsArray function:

Function getBinsArray(dataArray() As Double)
    Dim binsNumber As Long
    Dim binSize As Double

    binsNumber = Round(VBA.Sqr(UBound(dataArray) - LBound(dataArray) + 1) + 0.5)
    MsgBox ("binsNumber: " & binsNumber)

    binSize = (getMaxValue(dataArray) - getMinValue(dataArray)) / (binsNumber - 1)

    Dim resultArray() As Variant
    ReDim resultArray(1 To binsNumber) As Variant
    ...
    getBinsArray = resultArray
End Function

However I get a type mismatch error when I assign frequencesArray as histogram's source. How do I get around that error and create a histogram from my arrays?

EDIT:

createHistogram is called from StartDataCollect:

  Sub StartDataCollect()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Dim globals As Object
    Set globals = getGlobalVariables()

    Dim dataWorkbook As Workbook
    Set dataWorkbook = 
         ExcelApp.Application.Workbooks.Open(globals("DATA_WORKBOOK_PATH"))

    Dim clientsColl() As Client
    clientsColl = getClients(dataWorkbook, calculation_version)

    dataWorkbook.Close
    Set dataWorkbook = Nothing

    Dim resultWorkbook As Workbook
    Set resultWorkbook = 
        ExcelApp.Application.Workbooks.Open(globals("RESULT_WORKBOOK_PATH"))
    Call clearAll(resultWorkbook)

    'using data to create results for displaying
    ...

    Dim binsArray() As Variant
    binsArray = getBinsArray(sumLossesColl)

    Dim frequencesArray() As Variant
    frequencesArray = getFrequencesArray(binsArray, sumLossesColl)

    Call createHistogram(binsArray, frequencesArray, resultWorkbook)

    ...

    resultWorkbook.Save
    resultWorkbook.Close

    Set resultWorkbook = Nothing

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    Application.StatusBar = False
    ExcelApp.Quit
  End Sub

EDIT2:

Changed code as @user2731076 suggested:

Sub createHistogram(binsArray() As Variant, frequencesArray() As Variant, _
                                                resultWorkbook As Workbook)
    With resultWorkbook.Worksheets("result_values")

    .Shapes.AddChart2(201, xlColumnClustered).Select
    'ActiveChart.SetSourceData Source:=frequencesArray
    ActiveChart.Axes(xlCategory).Select 'error here
    ActiveChart.FullSeriesCollection(1).Values = frequencesArray
    ActiveChart.FullSeriesCollection(1).XValues = binsArray

    End With
End Sub

But get a Object variable or With block variable not set error.

Can we see the code that is calling your `createHistogram()` sub? — K.Dᴀᴠɪs, Jan 18 '18 at 16:39

score 0 · Answer 1 · answered Jan 18 '18 at 17:22

0

From the MSDN help page for the SetSourceData method, the source has to be a range not an array of values. Instead I would suggest setting the Y values using

ActiveChart.FullSeriesCollection(1).Values = frequencesArray

answered Jan 18 '18 at 17:22

user2731076

689
1
6
21

Tried that, but doesn't work properly. Please see edit. – Ans Jan 19 '18 at 08:13
If the error is on the 'ActiveChart.Axes(xlCategory).Select' line, try removing it. That line doesn't seem to actually do anything. Also, using style 201 for the AddChart2 method appears to be specifying a ColumnClustered chart [(See the answer to this question)](https://stackoverflow.com/questions/27889579/what-does-the-number-in-the-addchart2-vba-macro-represents) which you already specify, so maybe there is something there. I have never used AddChart2, I always just use AddChart. – user2731076 Jan 19 '18 at 12:48

Creating histogram using arrays as source

1 Answers1