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?

I haven't done any of this in a long time, so I won't add this as an answer until you can try it, but shouldn't you need to also use: Set r = Sheets("Sheet1").Range("Sheet1:Sheet3!B2") and then do Sum on r? Or similar... whatever you need to do to create a range from the sheets. You're probably better off using one of your other approaches though. — gmiley
– gmiley, Commented May 27, 2016 at 20:23
@findwindow I do use Evaluate() and it works fine....but if I have to calculate based on a dynamic range (same on all sheets), there is an extra step making the string I must supply to Evaluate() — Gary's Student
– Gary's Student, Commented May 27, 2016 at 20:27
You can also create two hidden blank sheets "START" and "FINISH" and bookend the sheets you want. Then v = Evaluate("Sum(START:FINISH!B2)") — Scott Craner
– Scott Craner, Commented May 27, 2016 at 20:44
And as you know, but I will put here for future viewers, you can replace the B2 part with a dynamic range reference. For example say you have the range you want set as a variable rng it would be v = Evaluate("Sum(START:FINISH!" & rng.Address(1,1) & ")") — Scott Craner
– Scott Craner, Commented May 27, 2016 at 20:57

mongoose36 · Accepted Answer · 2016-06-13 03:19:47Z

7

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.

answered Jun 13, 2016 at 3:19

mongoose36

7994 silver badges14 bronze badges

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

1 Comment

Abayomi Apetu Over a year ago

This works but that will only be valid for adding values in two cells but to make the function over a range of cells I used this work around

string rng = "D2:D" + totalRows+1;              //calculate and return the average/mean as a float with Excel Worksheetfunction             return (float)app.WorksheetFunction.Average(propertySheet.Range[rng]);

Tabias · Accepted Answer · 2016-06-13 07:22:20Z

1

You need to use the loop to perform the calculation across all of the sheets, it's the most efficient way by the looks of things. As mentioned above you can type each range separately instead.

Might be worth converting the range your adding up to a double (or single, integer, etc) because sometimes VBA reads numbers as text.

v = v + Cdbl(Sheets(i).Range("B2"))

The reason why you're having issues with Application.WorksheetFunction.Sum("Sheet1:Sheet3!B2") is because if you type that formula into Excel, the range 'Sheet1:Sheet3!B2' won't be recognised by excel.

To use a Application.WorksheetFunction it has to work in excel outside of VBA.

Hope that helps.

answered Jun 13, 2016 at 7:22

Tabias

10010 bronze badges

1 Comment

airstrike Over a year ago

To be fair, the range in the screenshot has a typo. There's an extra ! before the colon.

Ganesh Karra · Accepted Answer · 2019-05-14 11:57:10Z

1

 Sub SumWorksheets()
    Dim ws As Worksheet
    Dim v As Variant
    For Each ws In ThisWorkbook.Worksheets
'    If ws.Name <> ThisWorkbook.ActiveSheet.Name Then ' (Sum other sheets only)
    If ws.Name <> "" Then
    Application.DisplayAlerts = False
    v = v + ws.Range("B2")
    Application.DisplayAlerts = True
    End If
    Next ws
    MsgBox v
    End Sub

answered May 14, 2019 at 11:57

Ganesh Karra

111 bronze badge

1 Comment

L. F. Over a year ago

Please include some explanation along with your code.

brasofilo · Accepted Answer · 2019-09-03 20:53:41Z

1

I was able to get it to work just by the line:

Cells(x,y) = WorksheetFunction.sum(range(a,b:a,d))

edited Sep 3, 2019 at 20:53

brasofilo

26.2k15 gold badges96 silver badges188 bronze badges

answered Sep 3, 2019 at 20:17

L DeFrance

293 bronze badges

Comments

Randolph Brock · Accepted Answer · 2023-11-22 20:48:31Z

0

I use a very simple VBA statement:

MSC = Range("MyScores")
MyTotal = WorksheetFunction.Sum(Range(MSC))

In the above MyScores is the address range in a cell named "MyScores" Which is the naormal Excel address formula "Address...."; My formula in as follows, but can be changed to whatever is needed I4 holds the specified row.

=ADDRESS(I4,CELL("Col",R9),1)&":"&ADDRESS(J4,CELL("Col",R9),1)) Works perfect for me.

answered Nov 22, 2023 at 20:48

Randolph Brock

92 bronze badges

Collectives™ on Stack Overflow

Using SUM() in VBA

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related