excel - 当打开另一个具有相似工作表的工作簿时，VBA 用户定义函数返回#VALUE

Question

我有一个包含多个用户定义函数的工作簿，有时会出现 #VALUE 错误。我已经确定，当另一个具有相似工作表名称/选项卡颜色的工作簿与具有 UDF 的工作簿同时打开时，这些 UDF 会产生 #VALUE 错误。 UDF 引用工作簿中使用制表符颜色的其他工作表来计算其值。所以我的猜测是这些 UDF 给出了 #VALUE 错误，因为它无法区分不同打开的工作簿中具有相似名称/颜色的工作表的差异。

例如，两个打开的具有相同选项卡颜色的工作簿会给这些 UDF 一个#VAULE 错误。我通过打开一个具有通用工作表名称和白色标签颜色的新工作表来确认这一点，并且 UDF 没有给出此错误。这些 UDF 以其他方式完美地按预期工作，除了这种情况。下面只是在这种情况下给出此错误的 UDF 之一的示例。我试图通过引用“ThisWorkbook”来修复此错误，但这似乎无法更正错误。我什至尝试将函数移动到“ThisWorkbook”模块，但显然函数不会填充到该模块的 excel 中。我不知道如何解决这个问题。非常感谢您的帮助!

Function ExpenseActualSum(Month)

Application.Volatile
ColumnNumber = Month.Column - 1
ExpenseMonthSum = 0
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    If WS.Tab.Color = 255 Then

        For Each Tbl In WS.ListObjects
            If Tbl.Name Like "Actual*" Then
            TableName = Tbl.Name
            Exit For
            End If
        Next Tbl

    ColumnSum = Application.WorksheetFunction.Sum(Range(TableName & "[[#All],[Column" & ColumnNumber & "]]"))
    ExpenseMonthSum = ExpenseMonthSum + ColumnSum
    End If
Next WS

ExpenseActualSum = ExpenseMonthSum

End Function

Answer 1

你已经被隐式限定符咬伤了。

当 Range 没有明确限定特定的 Worksheet 对象实例时，它成为对 [_Global] 对象的隐式成员调用.

因此，未限定的 Range 调用隐式引用当前处于事件状态的任何工作表，无论工作簿是否处于事件状态。

当你打算针对特定的 Worksheet 对象工作时，比如你的情况下的 WS，那么你应该限定 Range， Rows、Columns、Names 和 Cells 使用该工作表对象调用。

这就是 ColumnSum = Application.WorksheetFunction.Sum(WS.Range(...)) 修复它的原因。

---

ThisWorkbook 是指写VBA代码的具体工作簿，可能是也可能不是WS所属的工作簿。这就是它不起作用的原因。