自从我使用 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.CalculateFull
或Application.CalculateFullRebuild
仅更新概览表 - 大多数 UDF 都是从命名表/范围内部调用并使用这些表中的值。
- 在某些 UDF 中,我将
set
范围设置为命名范围(set rngA = Range("A1")
或= Range("SomeGlobalName")
)、间接范围或同一张表的列(set rngB = Application.Caller.Worksheet.Range(Range("SomeCell").Value2 & "[SomeColumn]")
)*。
*提及:Range("SomeCell").Value2
包含表的名称(范围)。
编辑:我想我已经搞明白了。问题出在公式或set
UDF 中,我在最后一点提到过,我确实将其放在了“可能重要”列表中:
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 内部set
ting a的问题,无论它是否命名。我还没有弄清楚。当我将范围作为参数传递或从未在 UDF 内部设置范围时,不会发生错误。出于性能原因,我在 UDF 内部range
获得了某些突破点( )。Exit Function
微软在帮助用户调试 UDF 且不出现任何错误消息方面做得非常出色。
編輯2:如果我使用评估:,则不会出现错误Set rngB = Evaluate(Application.Caller.Worksheet.Name & "!" & Range("SomeCell").Value2 & "[SomeColumn]")
。现在概览表已正确更新。