Using SUM() in VBA
Solution 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.
Solution 2
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
Solution 3
I was able to get it to work just by the line:
Cells(x,y) = WorksheetFunction.sum(range(a,b:a,d))
Solution 4
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.
Gary's Student
Retired Engineer, Retired Manager, Retired Baby SitterGrandfatherBionicle CollectorZinfandel SipperMarzipan Addicta.k.a. James Ravenswooda.k.a. Jakobshavn
Updated on July 09, 2022Comments
-
Gary's Student almost 2 years
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? -
airstrike over 7 yearsTo be fair, the range in the screenshot has a typo. There's an extra
!
before the colon. -
L. F. almost 5 yearsPlease include some explanation along with your code.
-
Abayomi Apetu almost 4 yearsThis 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]);