VBA WorksheetFunction.Sum不适用于数组，但适用范围？ [英] VBA WorksheetFunction.Sum doesn't work with array but works with range?
问题描述
我正在开发一个项目，我需要创建一个在电子表格中使用某个单元格的函数，扩展范围，然后对其进行求和。
功能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 =范围（Worksheets（数据）。单元格（r，c - DataWindow + 1），
工作表（数据）。单元格（r，c））
Top_Performers = Application.WorksheetFunction.Sum（MyArray ）
但是，即使对于非零范围，此函数也会输出零值。
然而，代码如下：
函数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声明为范围，然后将其设置为工作表中的范围。
附加信息：参数销售对应于（例如）范围GJ5在工作表。 DataWindow 是一个整数变量，用于确定要求和的范围的长度。在这种情况下，它是12.所以通过使用 Sales 范围的行号和列号创建范围，然后通过DataWindow + 1减少列索引。
该函数在电子表格中输入为= Top_Performers（GJ5，Best_Clients_months）（在此示例中，列HN，单元格HN5），其中Best_Clients_months仅在此示例中对应于12的命名范围。 / p>
表格和列GJ的截图：
输入函数的列HN的截图： p>
我甚至在第一个例子中通过代码，本地窗口显示数组中的正确值（98.32当第一个arg为GJ4，即第四行，以及119.25和42.42当冷杉st arg是GJ5 - 第五行），但是仍然输出0.
我通常在对数组求和时不会有问题，所以我很困惑。
该工作簿是保密的，所以我只能使用这些屏幕截图来显示第二个示例中使用代码的结果，而不是在使用第一个示例时可以找到的零。
问题是范围被格式化为货币，因此您的数组包含Variant / Currency值，因为您隐式使用默认的.Value属性，它将格式化为货币的单元格转换为VBA货币数据类型。
SUM（和其他工作表函数）不明白VBA Currency或Date数据类型，因此您将获得零。
如果您将MyArray作业更改为
MyArray = Range（Worksheets（Data）。Cells（r，c - DataWindow + 1），_
工作表（数据）。单元格（r，c））。Value2
然后它将工作（.Value2不转换到货币和日期VBA数据类型）。
当您使用Range变量而不是variant数组时，转换永远不会发生，因为您将Range对象传递给SUM。
I'm not a VBA newbie but I have a newbie problem.
I was working on a project and I needed to create a function that takes a certain cell in the spreadsheet, expands the range and then sums it.
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)
However, this function outputs the value of zero even for non-zero ranges. The code below, however, works.
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)
The only difference is that in the second example I declare MyArray as range and then set it to the range in the worksheet.
Additional information: The argument Sales corresponds to (for example) the range GJ5 in the sheet. DataWindow is an integer variable that determines the length of the range to be summed. In this case it's 12. So the range is created by using the row and column numbers of the Sales range and then reducing the column index by DataWindow + 1.
The function is entered as "=Top_Performers(GJ5, Best_Clients_months)" in the spreadsheet (in the column HN, cell HN5 in this example), where Best_Clients_months is just a named range corresponding to 12 in this example.
The screenshot of the sheet and column GJ:
The screenshot of column HN where the function is entered:
I even stepped through the code in the first example, and the locals window shows the correct values in the array (98.32 when the first arg is GJ4, i.e. 4th row, and 119.25 and 42.42 when the first arg is GJ5 - fifth row) but still outputs 0.
I usually never have a problem when summing the array so I'm puzzled by this.
P.s. The workbook is confidential so I could only take these screenshots to show the result when using the code in the second example as opposed to zeros that can be found when using the first example.
The problem is that the range is formatted as currency, so your array contains Variant/Currency values, because you are implicitly using the default .Value property, which converts cells formatted as currency to VBA currency datatype.
SUM (and other worksheet functions) do not understand VBA Currency or Date datatypes, so you get zero.
If you change the MyArray assignment to
MyArray = Range(Worksheets("Data").Cells(r, c - DataWindow + 1), _
Worksheets("Data").Cells(r, c)).Value2
then it will work (.Value2 does not do the conversion to Currency and Date VBA datatypes).
When you use a Range variable rather than a variant array then the conversion never happens because you are passing a Range object to SUM.
这篇关于VBA WorksheetFunction.Sum不适用于数组，但适用范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
