arrays - VBA WorksheetFunction.Sum 不适用于数组，但适用于范围？

Question

我不是 VBA 新手，但我有一个新手问题。

我正在开发一个项目，需要创建一个函数，该函数采用电子表格中的某个单元格，扩展范围，然后对其求和。

Function Top_Performers(Sales As Range, DataWindow As Integer)

Dim MyArray () As Variant
Dim c As Integer, r As Integer


r = Sales.Row
c = Sales.Column

MyArray = Range(Worksheets("Data").Cells(r, c - DataWindow + 1), 
Worksheets("Data").Cells(r, c))

Top_Performers = Application.WorksheetFunction.Sum(MyArray)

但是，即使对于非零范围，此函数也会输出零值。但是，下面的代码可以工作。

Function Top_Performers(Sales As Range, DataWindow As Integer)

Dim MyArray As Range
Dim c As Integer, r As Integer


r = Sales.Row
c = Sales.Column

Set MyArray = Range(Worksheets("Data").Cells(r, c - DataWindow + 1), 
Worksheets("Data").Cells(r, c))

Top_Performers = Application.WorksheetFunction.Sum(MyArray)

唯一的区别是，在第二个示例中，我将 MyArray 声明为范围，然后将其设置为工作表中的范围。

附加信息:参数Sales对应于(例如)工作表中的范围GJ5。 DataWindow 是一个整数变量，用于确定要求和的范围的长度。在本例中为 12。因此，该范围是通过使用 Sales 范围的行号和列号创建的，然后将列索引减少 DataWindow + 1。

该函数在电子表格中输入为“=Top_Performers(GJ5, Best_Clients_months)”(在本示例中的 HN 列、单元格 HN5 中)，其中 Best_Clients_months 只是与本示例中的 12 相对应的命名范围。

工作表和列GJ的屏幕截图:

输入函数的HN列截图:

我什至单步执行了第一个示例中的代码，局部窗口显示了数组中的正确值(当第一个参数是 GJ4 时为 98.32，即第 4 行，当第一个参数为 GJ5 时为 119.25 和 42.42 - 第五行行)但仍然输出 0。

我在对数组求和时通常不会遇到问题，所以我对此感到困惑。

P.s.该工作簿是保密的，因此我只能在使用第二个示例中的代码时拍摄这些屏幕截图来显示结果，而不是使用第一个示例时可以找到的零。

Answer 1

问题在于范围的格式设置为货币，因此您的数组包含变体/货币值，因为您隐式使用默认的 .Value 属性，该属性会将货币格式的单元格转换为 VBA 货币数据类型。
SUM(和其他工作表函数)不理解 VBA 货币或日期数据类型，因此您得到零。

如果将 MyArray 分配更改为

MyArray = Range(Worksheets("Data").Cells(r, c - DataWindow + 1), _
Worksheets("Data").Cells(r, c)).Value2

然后它将起作用(.Value2 不会转换为货币和日期 VBA 数据类型)。
当您使用 Range 变量而不是变体数组时，转换永远不会发生，因为您将 Range 对象传递给 SUM。