英文:
Using Excel Frequency Worksheet Function in VBA for Histograms
问题
我正在尝试使用VBA生成直方图（直方图将被分类为簇状柱状图以具有可自定义的轴标题）。我目前正在使用Excel工作表函数频率来生成每个箱中的计数。在实际工作表中，这没有问题，但当将其转换为VBA时，它会出现类型不匹配的错误。
我有一些编程经验，但不是很多。我在线上读到一件事，有时当您将一个范围的单元格分组时，它们会被分类为数组，这可能是为什么出现类型不匹配的原因？另一个可能出现的问题是Excel公式可能会产生溢出，像第3行中的单元格中设置公式是否有效？如果需要，我可以附上整个代码，但它有点长，我认为这会聚焦在我遇到的问题上。
我的公式代码行很长，因为它有点动态。数据数组中的行数是静态的（始终为22行）。但是，根据Excel中一些单元格输入选择的箱范围，"箱数组"的长度可以发生变化。
谢谢！
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
英文:
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
答案1
得分: 2
WorksheetFunction.Frequency 返回一个基于一的数组。更改：
Dim binCount As Integer
为：
Dim binCount As Variant '或者只是 Dim binCount
然后在将此数组写入工作表时使用 Ubound 和 Resize。
附注：
- 在
Cells调用之前，您需要对工作表进行限定。 - 在这里，额外的变量可能会有所帮助：
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
英文:
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
Cellscalls. - 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论