VBA For Each求和函数返回0：Sub可用Function不可用问题求助

Hey there! I totally get where you're coming from—switching from Subs to UDFs can trip you up with Excel's built-in restrictions, especially when working across multiple sheets. Let's break down why your function is returning 0 and get it working properly, plus add that input parameter for the target column you wanted.

The Core Issues #

1. UDF Environment Limits: Unlike Subs, User-Defined Functions (UDFs) have stricter rules for interacting with Excel. If your original function wasn't properly referencing data or handling sheet traversal, it could fail silently and return 0.
2. Replacing ActiveCell: Using ActiveCell in a UDF is unreliable (it depends on the user's current selection) and not ideal for a reusable tool—we'll swap this for an explicit input parameter instead.

Working UDF Code #

Here's a revised version that fixes both problems:

Function SumBudgetSheets(targetColumn As Variant) As Double
    Dim ws As Worksheet
    Dim totalSum As Double
    Dim colIndex As Integer
    
    ' Convert input to a column number (works with both "A" or 1-style inputs)
    On Error Resume Next
    colIndex = IIf(IsNumeric(targetColumn), targetColumn, Columns(targetColumn).Column)
    On Error GoTo 0
    
    ' Exit with error if column input is invalid
    If colIndex < 1 Or colIndex > Columns.Count Then
        SumBudgetSheets = CVErr(xlErrValue)
        Exit Function
    End If
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if sheet name starts with "Budget"
        If ws.Name Like "Budget*" Then
            ' Add sum of the target column, handle empty cells/errors gracefully
            totalSum = totalSum + Application.Sum(ws.Columns(colIndex))
        End If
    Next ws
    
    ' Pass the final total back to the cell
    SumBudgetSheets = totalSum
End Function

Key Details Explained #

• Flexible Column Input: The function accepts either a column number (e.g., 3 for Column C) or a column string (e.g., "C"), converting it to a consistent index for easy referencing.
• Error Handling: If the user enters an invalid column (like "ZZZ" or 0), the function returns a clear #VALUE! error instead of 0, which is more intuitive for users.
• Simplified Summing: Using Application.Sum lets Excel handle empty cells, text values, and errors automatically—no need to write extra logic for edge cases.
• Proper Return Logic: We accumulate the total in totalSum and assign it directly to the function name, ensuring the result gets passed back to the Excel cell correctly.

How to Use It #

In any Excel cell, call the function like this:

• With a column number: =SumBudgetSheets(3) (sums Column C across all Budget sheets)
• With a column letter: =SumBudgetSheets("C") (same result as above)

Why Your Original Function Returned 0 #

Chances are, either:

• You forgot to assign the final total to the function name (a common UDF oversight!), or
• Using ActiveCell caused unexpected behavior—UDFs should rely on explicit input parameters instead of the current selection.

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