VBA WorksheetFunction.Sum doesn't work with ar

问题:

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.

回答1:

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.

回答2:

If you open the Locals Window, I think you'll find that the array being created is two dimensional. You can use Transpose to turn it into a one dimensional array. The code below should work, although sometimes you have to use Transpose twice.

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))

MyArray  = Application.Transpose(MyArray)

Top_Performers = Application.WorksheetFunction.Sum(MyArray)