Использование SUM() в VBA

Если у меня есть набор ячеек на листе, который я хочу добавить, я могу использовать формулу:

=SUM(Sheet1!A1:A10)

Чтобы сделать это в суб, я бы использовал:

Sub example1()
    Dim r As Range, v As Variant

    Set r = Sheets("Sheet1").Range("A1:A10")
    v = Application.WorksheetFunction.Sum(r)
End Sub

Если, однако, я хочу добавить одну ячейку во многие листы, я использую формулу:

=SUM(Sheet1:Sheet38!B2)

В VBA эта строка терпит неудачу, как объясняется в Укажите диапазон Excel по листам в VBA:

Sub dural()
    v = Application.WorksheetFunction.Sum("Sheet1:Sheet3!B2")
End Sub

У меня есть два метода обхода. Я могу получить сумму, программируя цикл:

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() для этого вычисления, или я должен придерживаться цикла?

Ответы

Ответ 1

Я считаю, что проблема с workheetfunction.sum заключается в том, что ей нужны аргументы для оценки не строки. WorksheetFunction.Sum( "Sheet1! A1: A3" ) также не работает. Однако это удается

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

Диапазоны могут быть любыми, что вам нравится.

Ответ 2

Вам нужно использовать цикл для вычисления всех листов, это наиболее эффективный способ взглядов. Как упоминалось выше, вы можете вводить каждый диапазон отдельно.

Возможно, стоит преобразовать диапазон сложения в двойное (или одно целое и т.д.), потому что иногда VBA читает числа как текст.

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

Причина, по которой возникают проблемы с Application.WorksheetFunction.Sum("Sheet1:Sheet3!B2"), заключается в том, что если вы введете эту формулу в Excel, диапазон "Sheet1: Sheet3! B2" не будет распознан excel.

Чтобы использовать Application.WorksheetFunction, он должен работать в excel вне VBA.

Надеюсь, что это поможет.

Ответ 3

 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

Ответ 4

Я смог заставить его работать только по линии:

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