Using SUM() in VBA

Question

If I have a set of cells in a worksheet that I want to add up, I can use the formula:

=SUM(Sheet1!A1:A10)

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:

=SUM(Sheet1:Sheet38!B2)

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 Evaluate():

v = Evaluate("Sum(Sheet1:Sheet3!B2)")

Is it possible to use Application.WorksheetFunction.Sum() for this calculation, or should I stick the loop?

Answer 1

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

Application.WorksheetFunction.Sum(Sheet1.Range("A1"), Sheet2.Range("A1"))

The Ranges could be whatever you like.