vba - Excel VBA : Workbook-scoped, 工作表依赖命名公式/命名范围(结果根据事件工作表而变化)

Question

编辑:为清楚起见，标题已更改。

快速总结:我想知道工作簿范围的、工作表相关的命名公式(我将在下面描述)的行为是否是 Excel 中记录的功能。如果是这样，请在某处指出一些文档的方向-我似乎无法在网上找到任何关于此的内容(也许我使用了错误的搜索词..？)并且不想使用实际上是的东西一个错误，可能会在以后的版本中消失!

严格来说，这并不是一个关于 VBA 的问题。但是，命名公式是我和其他人use in VBA code all the time ，所以它仍然适用于我认为的主题。

编辑:请注意，下面的 VBA 代码可能并不完全正确——我还没有测试过。

常规方法

对于工程/科学计算，我经常需要在同一个工作簿中多次使用相同的命名公式/范围，但在不同的工作表上。作为一个简化的例子，我可能会为圆形区域实现类似的东西:

Dim sht as Worksheet
For Each sht In ThisWorkbook
    Call sht.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*'" & _
            sht.Name & "'!Radius^2")
Next sht

这会产生以下一组命名范围/公式(范围仅限于每个工作表):

=PI()*Sheet1!Radius^2        <--- scoped to Sheet1
=PI()*Sheet2!Radius^2        <--- scoped to Sheet2
etc. etc.

这当然可以正常工作，但它的主要缺点是难以进行 future 的更改。因此，例如，如果公式发生变化(圆的面积当然不会改变!但是 AASHTO LRFD 高速公路设计代码中的公式，例如，几乎每个版本都会改​​变!)，我必须编辑每个实例每一个名称公式。这很乏味，即使我编写了一个 VBA 程序来为我做这件事。

替代方法

前几天我在 Excel 2013 中意外发现了以下内容，但在网上的任何地方都找不到任何关于它的信息。这让我犹豫是否要开始使用它。

假设我运行以下单行代码:

Call ThisWorkbook.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*!Radius^2")

这导致以下单个命名范围/公式(范围为工作簿):
=PI()*!Radius^2 <--- 公式范围为工作簿；注意 !Radius ，而不是 Radius .

请注意，这与以下内容不同(没有感叹号):
=PI()*Radius^2 <--- 注意这里， Radius范围为工作簿。

现在， AreaCircle将产生与上述第一种方法完全相同的行为:它将根据本地的工作表定义的 Radius 值产生结果。因此，如果有两个名为 Radius 的命名范围(一个用于 Sheet1 和一个用于 Sheet2 )， AreaCircle将根据 Radius 的值进行计算在使用它的工作表中。并且每次添加新工作表时，我不再需要添加此(以及其他所有!)公式的新版本(这是巨大的!)。

这是难以描述的行为；如果您对我的描述感到困惑，可以执行以下步骤来重新创建此行为:

在工作簿中，创建两个或多个工作表并在单元格 A1 中输入“1”的 Sheet1 , 单元格中的“2”A1 Sheet2，单元格中的“3”A1 Sheet3 等

创建一个名为 CellA1 的命名范围(具有工作簿范围)并为公式输入以下内容:=!$A$1

进入=CellA1在任何单元格中都会导致 Sheet1 上的“1” , 在 Sheet2 上产生“2”等等等等

文档？

嘿，你成功了——谢谢你一直陪着我!

那么，正如我上面所说，有人可以指点我这个“功能”的文档吗？我很想在我的一些更复杂的项目中开始实现它；如果不出意外，它只会使名称管理器更容易导航 20 倍(没有所有重复的名称)。

Answer 1

至于文档，见Evaluating Names and Other Worksheet Formula Expressions

=A1指当前工作表上的单元格 A1

=!A1指事件工作表上的单元格 A1

结合 Worksheet References

当前是指 Excel 正在重新计算的...

事件是指用户正在查看的内容...

这就是查尔斯·威廉姆斯所展示的。至于您的用例，我建议使用用户定义的函数，例如在 VBA 中。