在 VBA 中使用 SUM()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37491625/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SUM() in VBA
提问by Gary's Student
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 中执行此操作，我将使用：
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:
在 VBA 中，这一行失败了，如在 VBA 中指定跨工作表的 Excel 范围中所述：
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():
或使用Evaluate()：
v = Evaluate("Sum(Sheet1:Sheet3!B2)")
Is it possible to use Application.WorksheetFunction.Sum()for this calculation, or should I stick the loop?
是否可以Application.WorksheetFunction.Sum()用于此计算，还是应该坚持循环？
采纳答案by mongoose36
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
我相信 worksheetfunction.sum 的问题在于它需要参数来评估而不是字符串。WorksheetFunction.Sum("Sheet1!A1:A3") 也失败了。然而，这成功了
Application.WorksheetFunction.Sum(Sheet1.Range("A1"), Sheet2.Range("A1"))
The Ranges could be whatever you like.
范围可以是任何你喜欢的。
回答by Tabias
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.
可能值得将您加起来的范围转换为双精度（或单精度、整数等），因为有时 VBA 会将数字读取为文本。
v = v + Cdbl(Sheets(i).Range("B2"))
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.
您遇到问题的原因Application.WorksheetFunction.Sum("Sheet1:Sheet3!B2")是，如果您将该公式键入 Excel，Excel 将无法识别范围“Sheet1:Sheet3!B2”。
To use a Application.WorksheetFunctionit has to work in excel outside of VBA.
要使用Application.WorksheetFunction它，它必须在 VBA 之外的 excel 中工作。
Hope that helps.
希望有帮助。
回答by Ganesh Karra
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
回答by L DeFramce
I was able to get it to work just by the line:
我能够让它运行起来：
Cells(x,y) = WorksheetFunction.sum(range(a,b:a,d))
