Excel VBA UDF 在添加或删除不相关的工作表时返回 #VALUE 错误

Question

第一次发帖，如果我犯了任何错误，请道歉！

所以，我的UDF遇到了一个非常奇怪的问题。在我的工作簿中，我有一个名为"标准阶段表"的不可见"模板"工作表，以及用户可以激活的子例程，该子例程将该模板工作表复制到用户可以使用的新的可见工作表中。整个工作簿中将有许多该模板工作表的副本，但它们都具有唯一的名称。

我的 UDF 位于该模板工作表的多个位置上，因此在用户制作的模板工作表的每个副本上。在其中一个工作表中工作时，UDF 工作得很好，并返回我期望的值。

但是，当用户添加模板工作表的新副本时，有时 UDF 会失控并在使用 UDF 的每个位置返回 #VALUE 错误。

此外，当用户删除模板工作表的一个副本时，UDF 总是会失控，并在使用 UDF 的每个地方返回 #VALUE 错误。

我没有使用ActiveSheet或类似的东西，我相信我正确地给出了我在UDF中使用的范围的完整参考。任何帮助将不胜感激，我在这里陷入困境！UDF 的代码如下。

另外，因为我确信我会被问到这个问题，所以我的代码中的 neColumn 变量是我在几个子例程和 UDF 中使用的公共变量。它是在我的模块开头定义的。此外，我也在模块的开头使用选项显式。

谢谢！

Public Function fSum(ByVal Target As Range, bExtended As Boolean) As Single
'This function returns a sum, based on a range provided in the cell that holds the function.
'It checks to see if that line item has been marked as Non-Extended, based on the NE column
'that can be check marked. If that line item is marked NE, then only the NE sum columns can
'use that line item as part of their sum, and those values are removed from the E columns.
Dim sSum As Single
Dim i As Integer
Dim n As Integer
'This small section is used to determine complete references to the cell calling the function.
Dim sheetName As String
sheetName = Application.Caller.Parent.Name
'Loop through provided range, and sum up the contents based on whether they have been marked NE or not.
i = 1
n = Target.row
sSum = 0
If Sheets(sheetName).Visible = True Then
While i < Target.Rows.Count
If (bExtended = True) Then
If Sheets(sheetName).Range(neColumn.Address).Cells(n, 1) = vbNullString Then
sSum = sSum + Sheets(sheetName).Range(Target.Address).Cells(i, 1).Value
End If
Else
If Sheets(sheetName).Range(neColumn.Address).Cells(n, 1) <> vbNullString Then
sSum = sSum + Sheets(sheetName).Range(Target.Address).Cells(i, 1).Value
End If
End If
i = i + 1
n = n + 1
Wend
End If
fSum = sSum
End Function

Answer 1

总结评论线程，为后人解答：

• 我不确定你为什么会看到这种行为。
• 会有一些方法可以改进这个UDF(包括使用Long而不是Integer，更喜欢Do While...Loop而不是While...Wend，删除.Visible检查...
• 但无论如何，感觉这只是复制SUMIFS的功能，所以你可以考虑走这条路。

Answer 2

原因是您的neColumn变量已变得Nothing，因为Excel是易失性的。

我假设你的模块的开始看起来像这样：

Option Explicit
Public neColumn As Range
Sub Auto_Open()
Set neColumn = Sheet1.Range("A1:B2")
End Sub

打开工作簿时，调用Auto_OpenSub 以SetneColumn变量。 但是 - 当发生某些操作时，Excel 会重新生成 VBA，这会将公共变量(例如neColumn(重置为其默认值(对于Range等Object，默认值为Nothing(。 触发此问题的一种简单方法是故意抛出错误，例如尝试运行以下命令：

Sub ThrowErr()
NotDefined = 1
End Sub

您可以通过在fSum代码中添加以下行来使其对你更可见：

If neColumn Is Nothing Then Stop

你要么需要一种方法来恢复neColumn，当它被重置为Nothing，或者找到一种非易失性的方式来存储它。

我假设这不适合成为Const，因为否则它已经是了，但您可以将其转换为命名范围，或将Address存储在隐藏的工作表/CustomDocumentProperty中。 这些选项还允许您在保存工作簿时存储neColumn，以便在重新打开工作簿时

存储