在VBA中使用SUM（） [英] 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:

=SUM(Sheet1!A1:A10)

要在sub中执行此操作，我将使用： / p>

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)

在VBA这行失败了，如在VBA中的表格中指定Excel范围：

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

或使用 Evaluate（）：

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

可以使用 Application.WorksheetFunction.Sum（）进行此计算，或者我应该坚持循环？

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

推荐答案

我相信workheetfunction.sum的问题是它需要参数来评估不是字符串。 WorksheetFunction.Sum（Sheet1！A1：A3）也失败。但是，成功

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.

这篇关于在VBA中使用SUM（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！