使用数组作为源创建直方图

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?

但是,当我将frequencesArray指定为直方图的源时,我会收到类型不匹配错误。如何解决该错误并从我的阵列创建直方图?

EDIT:

createHistogram is called from StartDataCollect:

从StartDataCollect调用createHistogram:

  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:

将代码更改为@ user2731076建议:

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.

但得到一个Object变量或With block变量没有设置错误。

1 个解决方案

#1

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

在SetSourceData方法的MSDN帮助页面中,源必须是范围而不是值数组。相反,我建议使用设置Y值

ActiveChart.FullSeriesCollection(1).Values = frequencesArray