如何在VBA中对工作表数组使用Sum函数？代码问题咨询

First, let's unpack the issues in your current code:

• You've got a typo: it's Application.WorksheetFunction, not Application.Worksheet
• You can't pass an array of sheet names directly to the Sum function — it expects a range of values, not a list of sheet names or worksheet objects
• Your syntax for accessing Range("B6") after Sum is backwards; you can't chain that like that

Here are a few solid ways to get the sum of B6 across your target sheets into Totals!B6:

Option 1: Loop Through the Sheet Names (Simple & Easy to Debug) #

This approach iterates over each sheet name in your array, grabs B6's value, and adds it up. Great if you want to handle edge cases (like sheets that might not exist) easily:

Dim wkshtArray As Variant
Dim numYear As Integer ' I'm assuming numYear is defined somewhere
Dim totalSum As Double
Dim sheetName As Variant

numYear = 2024 ' Replace with your actual year value
wkshtArray = Array("Dec" & numYear, "Nov" & numYear)

totalSum = 0
For Each sheetName In wkshtArray
    ' Check if the sheet exists to avoid errors
    On Error Resume Next
    totalSum = totalSum + ThisWorkbook.Worksheets(sheetName).Range("B6").Value
    On Error GoTo 0
Next sheetName

' Write the total to Totals!B6
ThisWorkbook.Worksheets("Totals").Range("B6").Value = totalSum

Option 2: Use WorksheetFunction.Sum with INDIRECT (One-Liner) #

If you prefer a concise approach, you can build a range string and use INDIRECT to reference the B6 cells across your sheets. Just make sure all the sheet names exist:

Dim wkshtArray As Variant
Dim numYear As Integer
Dim rangeString As String

numYear = 2024
wkshtArray = Array("Dec" & numYear, "Nov" & numYear)

' Build a string like "'Dec2024'!B6,'Nov2024'!B6"
rangeString = "'" & Join(wkshtArray, "'!B6,'") & "'!B6"

' Use SUM with INDIRECT to calculate the total
ThisWorkbook.Worksheets("Totals").Range("B6").Value = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Indirect(rangeString))

Option 3: Convert to Worksheet Object Array (Cleaner for Object-Oriented Code) #

If you want to work with worksheet objects instead of names, convert your string array to an array of worksheet objects first:

Dim wkshtArray As Variant
Dim numYear As Integer
Dim wsObjArray() As Worksheet
Dim i As Integer
Dim totalSum As Double

numYear = 2024
wkshtArray = Array("Dec" & numYear, "Nov" & numYear)

' Resize the object array and populate it
ReDim wsObjArray(UBound(wkshtArray))
For i = LBound(wkshtArray) To UBound(wkshtArray)
    Set wsObjArray(i) = ThisWorkbook.Worksheets(wkshtArray(i))
Next i

' Sum each sheet's B6
totalSum = 0
For Each ws In wsObjArray
    totalSum = totalSum + ws.Range("B6").Value
Next ws

ThisWorkbook.Worksheets("Totals").Range("B6").Value = totalSum

All three methods will get you the result you want — pick the one that fits your coding style and whether you need error handling for missing sheets.

内容的提问来源于stack exchange，提问作者WIW Wrestling