首页 文章

Excel VBA：工作簿范围，工作表相关的命名公式/命名范围（结果更改取决于活动工作表）

提问于
浏览 次
13

编辑： Headers 已更改为清晰 .

Quick summary: 我想知道工作簿范围，工作表依赖命名公式（我将在下面描述）的行为是否是Excel中的文档功能 . 如果是这样，请指出我在某处某些文档的方向 - 我可以't seem to find ANYTHING about this online (perhaps I'使用不良搜索条件......？）并且不想使用实际上是一个bug的东西并且可能在以后的版本中消失！

严格来说，这不是一个关于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.

当然这很好用，但它具有难以进行未来变化的主要缺点 . 因此，例如，如果公式发生变化（圆形区域当然不会改变！例如，AASHTO LRFD公路设计代码中的公式，几乎每个版本都会改变！），我必须编辑每个实例每个名称公式 . 即使我写了一个VBA程序来为我做这件事，这也很乏味 .

替代方法

我前几天在Excel 2013中发现了以下事故，并且无法在网上任何地方找到任何相关信息 . 这使我开始使用它犹豫不决 .

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

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

这导致以下SINGLE命名范围/公式（作用于工作簿）：

=PI()*!Radius^2 <---公式的范围是工作簿;注意 !Radius ，而不是 Radius .

请注意，这与以下内容不同（没有感叹号）：

=PI()*Radius^2 <---请注意，此处， Radius 的作用域为工作簿 .

现在， AreaCircle 将产生与上面第一种方法完全相同的行为：它将根据本地工作表定义的Radius值生成结果 . 因此，如果有两个命名范围称为 Radius （一个用于 Sheet1 ，一个用于 Sheet2 ）， AreaCircle 将根据 Radius 在其使用的工作表中的值进行计算 . 而且每次添加新工作表时，我都不再需要添加新版本（以及其他所有！）公式的额外好处（这是巨大的！） .

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

  • 在工作簿中，创建两个或多个工作表，然后在Sheet2的单元格 A1 中的单元格 A1 中输入"1"，在Sheet3的单元格 A1 中输入"3"等 .

  • 创建一个名为 CellA1 的命名范围（带有工作簿范围）并为公式输入以下内容： =!$A$1

  • 在任何单元格中输入 =CellA1 将导致"1" Sheet1 ，导致_1768831_ Sheet2 等 .

文档？

嘿，你做到了 - 谢谢你在这里坚持！

所以，正如我上面所说，有人能指出我这个“功能”的文档吗？我希望在一些更复杂的项目中开始实现这一点;如果不出意外，它只会使名称管理器更容易导航20次（没有所有重复的名称） .

excel vba excel-vba named-ranges

2 回答

  • 5

    至于文档，请参阅Evaluating Names and Other Worksheet Formula Expressions

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

    • =!A1 指的是活动工作表上的单元格A1

    与Worksheet References一起

    • 当前是指Excel重新计算的内容......

    • Active是指用户正在查看的内容...

    这就是查尔斯威廉斯所展示的 . 至于你的用例，我推荐用户定义的函数，比如在VBA中 .

    回复于
  • 3

    你需要小心使用！参考名称：
    带引号的名称以=开头！从VBA调用计算时可能会给出不正确的结果 . 它们的计算方式似乎总是引用活动工作表而不是它们正在使用的工作表：

    将1放在Sheet1的单元格A1中
    a）将 =CellA1 放在Sheet1的A2中，其中定义了CellA1作为 =!$A$1
    b）切换到手动计算
    c）激活Sheet2
    d）运行此VBA代码

    Sub Testing()
Worksheets("Sheet1").Range("a1") = 8
Application.Calculate
End Sub

    e）现在切换回Sheet1，您将看到Sheet1！A2包含Sheet2中的所有内容！A1

    使用indirect（）可以避免这个问题
    我们的名称管理器插件检测并警告使用这种语法 .

    回复于

相关问题