Excel 中存在大量 UDF 值问题,返回 #VALUE,不存储计算值

Excel 中存在大量 UDF 值问题,返回 #VALUE,不存储计算值

自从我使用 UDF 更新我的 Excel 表后,我遇到了大量问题。

我只想在一个地方更改我的公式(即 UDF),而不是更改我的工作表(实际上是 60 个,数字不断上升)中包含相同公式的每一列。

  • 一场表演

Application.Volatile在 UDF 中,我的整个 Excel 应用程序性能非常差。最重要的是,它没有解决问题。

  • b) 存储值

在我的 Excel 表中,我得到了相互构建的不同列,例如,列 A 包含一个返回值的 UDF,该值在列 B 中用于另一个 UDF 的计算。

一个简单的测试不会失败:

细胞 公式 价值
A1 =1 1
A2 =foo(A1) 4
A3 =bar(A1) 3
A4 =foo(A3) 10
Public Function foo(n As Double) As Double
    foo = bar(n) + 1
End Function
Public Function bar(n As Double) As Double
    bar = n * 3
End Function
  • c)获取概览表中的值(#VALUE!

由于其他工作表包含错误,包含所有总额等的概览表失败了#VALUE!

重新计算按钮不起作用。

如果我从概览转到工作表 X,更新所有内容,然后返回概览,则值是正确的。如果我现在在它们之间来回切换,则值是正确的。但是,如果我转到工作表 Y,更新 UDF,然后返回概览,则只有 Y 的值是正确的,X 的值被重置。

这里我遗漏了什么吗?

也许很重要:

  • 我有一个单元格正在改变工作表选项卡的颜色
  • 我使用了很多INDIRECT(),例如获取每张表的表格的唯一名称
  • 工作表打开事件不起作用Application.CalculateFullApplication.CalculateFullRebuild仅更新概览表
  • 大多数 UDF 都是从命名表/范围内部调用并使用这些表中的值。
  • 在某些 UDF 中,我将set范围设置为命名范围(set rngA = Range("A1")= Range("SomeGlobalName"))、间接范围或同一张表的列(set rngB = Application.Caller.Worksheet.Range(Range("SomeCell").Value2 & "[SomeColumn]"))*。

*提及:Range("SomeCell").Value2包含表的名称(范围)。

编辑:我想我已经搞明白了。问题出在公式或setUDF 中,我在最后一点提到过,我确实将其放在了“可能重要”列表中:

  • set rngB = Application.Caller.Worksheet.Range(Range("SomeCell").Value2 & "[SomeColumn]")

我稍微改变了一下: Set rngB = Range(Application.Caller.Worksheet.Name & "!" & Range("SomeCell").Value2 & "[SomeColumn]"),现在值已经被存储了,但是问题是,如果我回到我的概览表并保存或重新打开工作簿,我会#VALUE!再次收到错误。

我创建了一些调试消息,UDF 在我正在set点击时停止(当我在概览表时) rngB。下一个调试消息(在 之后set)没有被打印。

我猜这是UDF 内部setting a的问题,无论它是否命名。我还没有弄清楚。当我将范围作为参数传递或从未在 UDF 内部设置范围时,不会发生错误。出于性能原因,我在 UDF 内部range获得了某些突破点( )。Exit Function

微软在帮助用户调试 UDF 且不出现任何错误消息方面做得非常出色。

編輯2:如果我使用评估:,则不会出现错误Set rngB = Evaluate(Application.Caller.Worksheet.Name & "!" & Range("SomeCell").Value2 & "[SomeColumn]")。现在概览表已正确更新。

相关内容