2

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 the sheet and column GJ

The screenshot of column HN where the function is entered:

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.

Improve this question
10
  • 1
    Is there a reason why you wouldn't just use the built-in formula =SUM(OFFSET(GJ5,0,1-BestClientMonths,1,BestClientMonths))? (P.S. Your first code works for me.) Commented Sep 5, 2017 at 2:20
  • 1
    Also remember that UDFs are only recalculated if you force them to be, or if there is a change to one of the parameters (in your case, if GJ5 or BestClientMonths changes value) - it won't be recalculated if, for instance, cell GI5 is modified. (Your question states that you were able to see the correct values while stepping through it, and it still gave zero as the answer, so that doesn't explain this. But it will cause you problems later if any of the values not being passed as a parameter are changed.) Commented Sep 5, 2017 at 2:27
  • What version on Excel are you using? I'm testing on Excel 2007, and all the formulae are working, including the current answer. (It's a good idea to tag your Excel questions with the Excel version tag.) Commented Sep 5, 2017 at 2:28
  • Actually, the formula in my first comment should be =SUM(OFFSET(Data!GJ5,0,1-BestClientMonths,1,BestClientMonths)) to replicate what your code is doing. (No matter which sheet you enter the formula on, and therefore which sheet's cell G5 that is passed as a parameter to your function, it is cell G5 on sheet "Data" explicitly that the function uses.) Commented Sep 5, 2017 at 2:35
  • FililP & @YowE3K Or use the non-volative equivalent =SUM(INDEX(5:5,,COLUMN(Data!GJ5)+1-BestClientMonths):Data!GJ‌​5) which is much faster in a large spreadsheet. And a tiny bit shorter ;) Commented Sep 5, 2017 at 9:53

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow, you were right! I thought of this and that's why I looked at which values the array stored in the Locals Window, but they looked normal to me (I thought that maybe the dollar sign would get stored as well). I learned something new. Thanks!
0

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)
Improve this answer

1 Comment

Thank you for your answer. It doesn't work. Yes, the array is of dimensions (1 to 1, 1 to 12). Shouldn't WorksheetFunction.Sum work either way though? I've used the SUM function on 2D arrays before and I even tested it on an example 2D array now. It should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.