Using SUM() in VBA
If I have a set of cells in a worksheet that I want to add up, I can use the formula:
To do this in a sub, I would use:
Sub example1() Dim r As Range, v As Variant Set r = Sheets("Sheet1").Range("A1:A10") v = Application.WorksheetFunction.Sum(r) End Sub
If, however, I want to add up a single cell across many worksheets, I use the formula:
In VBA this line fails miserably, as explained in Specify an Excel range across sheets in VBA:
Sub dural() v = Application.WorksheetFunction.Sum("Sheet1:Sheet3!B2") End Sub
I have two workarounds. I can the the sum by programming a loop:
Sub example2() Dim i As Long Dim v As Variant v = 0 For i = 1 To 38 v = v + Sheets(i).Range("B2") Next i End Sub
or by using
v = Evaluate("Sum(Sheet1:Sheet3!B2)")
Is it possible to use
Application.WorksheetFunction.Sum() for this calculation, or should I stick the loop?
I believe the issue with the worksheetfunction.sum is that it needs arguments to evaluate not string. WorksheetFunction.Sum("Sheet1!A1:A3") fails as well. However, this succeeds
The Ranges could be whatever you like.