Excel VBA：Workbook-scoped，依赖于工作表的公式/命名范围（结果根据活动工作表而更改） [英] Excel VBA: Workbook-scoped, worksheet dependent named formula/named range (result changes depending on the active worksheet)

问题描述

快速摘要：我想知道工作簿作用域的行为，依赖于工作表的行为公式（我将在下面描述）是Excel中的一个记录的功能。如果是这样，请指出我在某处的某些文档的方向 - 我似乎无法在网上找到任何东西（也许我使用的是不好的搜索字词..），并且不想使用实际的东西一个错误，可能会在以后的版本中消失！

严格来说，这并不是一个关于VBA的问题;然而，命名的公式是我和其他人一直使用VBA代码，所以它仍然适用于主题I认为。

编辑：请注意，下面的VBA代码可能不完全正确 - 我没有测试它。

常规方法

对于工程/科学计算，我经常需要使用相同的命名公式/范围倍数时间在同一个工作簿，但在不同的工作表。作为一个简化的例子，我可能会为这个圈子实现这样的一个方面：

  Dim tt as Worksheet 
对于每个sht在ThisWorkbook 
调用sht.Names.Add（Name：=AreaCircle，RefersTo：== PI（）*'& _ 
 sht.Name&'！半径^ 2）
下一个sht 
  

这导致以下一组命名范围/公式（范围到每个工作表）：

  = PI（）* Sheet1！Radius ^ 2< ---范围到Sheet1 
 = PI（）* Sheet2！Radius ^ 2< ---范围到Sheet2 
等等
  

当然这样做不错，但是它有很大的缺点，难以做出未来的变化。所以例如，如果公式改变（一个圆的面积当然不会改变，但是在AASHTO LRFD高速公路设计代码中的公式，例如，几乎每个版本都会改​​变！），我必须编辑每个单一的实例的每个名称公式。这是乏味的，即使我写一个VBA程序来为我做。

替代方法

我在另外一天在Excel 2013中发现了以下事故，并没有能够在任何地方找到任何东西。这让我犹豫开始使用它。

让我们说，我运行以下一行代码：

  Call ThisWorkbook.Names.Add（Name：=AreaCircle，RefersTo：== PI（）*！Radius ^ 2）
  

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

= PI（）*！Radius ^ 2 < ---公式范围为Workbook;注意！Radius ，而不是 Radius 。

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

= PI（）* Radius ^ 2 < ---请注意，这里， Radius 被限定到工作簿。

现在，code> AreaCircle 将产生与上述第一个方法完全相同的行为：它将根据本地工作表定义的Radius值生成一个结果。所以如果有两个命名范围称为 Radius （一个用于 Sheet1 ，另一个用于 Sheet2 ）， AreaCircle 将根据其中的 Radius 的值计算正在被使用。还有另外一个好处是，我每次添加一个新的工作表（这是巨大的！），我不再需要添加一个新版本（和其他！）公式。

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

1. 在工作簿中，创建两个或多个工作表并在单元格 A1 中的 Sheet1 ，单元格 A1 Sheet2，3在单元格 A1 的Sheet3等。


2. 创建一个命名范围称为 CellA1 （具有Workbook范围），并输入以下公式： =！$ A $ 1


3. 在任何单元格中输入 = CellA1 将导致 Sheet1 中的1，导致 $ / $>
   
   

   文档？

   
   
   

   你做了 - 谢谢你在这里坚持下去！

   
   
   

   所以，正如我上面所说的，有人可以指出我到这个功能的文档？我很乐意在我的一些更复杂的项目中开始实施;如果没有别的，它只会使名称管理器容易导航20次（没有所有重复的名称）。

   解决方案

   有关文档，请参阅评估名称和其他工作表公式表达式

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

   与工作表参考

   
   
   
   
   
   • 到什么Excel正在重新计算...
   
   
   • 活动指的是用户正在查看...
   
   
   
   
   这是查尔斯·威廉姆斯所说的。对于你的用例，我建议用户定义的功能，在VBA中说。
   
   

   EDIT: Title changed for clarity.

   Quick summary: I want to know if the behavior of workbook-scoped, worksheet dependent named formulas (which I will describe below) is a documented feature in Excel. If so, please point me in the direction of some documentation somewhere- I can't seem to find ANYTHING about this online (perhaps I'm using bad search terms..?) and don't want to be using something that is actually a bug and could disappear in a later version!

   Strictly speaking this is not really a question about VBA; however, named formulas are something I and others use in VBA code all the time, so it is still applicable to the subject I think.

   EDIT: Note that the VBA code below may not be exactly right- I haven't tested it.

   Regular Method

   For engineering/scientific calculations I have often needed to use the same Named Formula/Range multiple times in the same workbook, but on different worksheets. As a simplified example, I might implement something like this for the area of a circle:

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

   Which results in the following set of Named Ranges/Formulas (scoped to each worksheet):

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

   This works fine of course, but it has the major downside of being difficult to make future changes. So for example, if the formula changes (the area of a circle isn't going to change of course! But formulas in the AASHTO LRFD highway design code, for example, change almost every edition!), I have to edit every single instance of every single Name Formula. This is tedious, even if I write a VBA procedure to do it for me.

   Alternative Method

   I discovered the below on accident in Excel 2013 the other day, and haven't been able to find anything about it anywhere online. This makes me hesitant to start using it.

   Let's say I run the following single line of code instead:

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

   Which results in the following SINGLE Named Range/Formula (scoped to the workbook):

   =PI()*!Radius^2 <--- formula is scoped to Workbook; note !Radius, not Radius.

   Note that this is NOT the same as the following (there is no exclamation point):

   =PI()*Radius^2 <--- Note that here, Radius is scoped to the Workbook.

   Now, AreaCircle will produce the exact same behavior as the first method above: it will produce a result based on the local, worksheet-defined value of Radius. So if there are two Named Ranges called Radius (one for Sheet1 and one for Sheet2), AreaCircle will be calculated depending on the value of Radius in the sheet in which it is being used. And with the added benefit that I no longer have to add a new version of this (and every other!) formula every time I add a new worksheet (this is HUGE!).

   This is difficult behavior to describe; if you are confused by my description, you can do the following steps to recreate this behavior:

   1. In a workbook, create two or more worksheets and enter "1" in cell A1 of Sheet1, "2" in cell A1 of Sheet2, "3" in cell A1 of Sheet3, etc etc.
   2. Create a Named Range called CellA1 (with Workbook scope) and enter the following for the formula: =!$A$1
   3. Entering =CellA1 in any cell will result in "1" on Sheet1, result in "2" on Sheet2, etc etc.

   Documentation?

   Hey, you made it - thanks for sticking with me here!

   So, as I said above, can someone point me to documentation for this "feature"? I would love to start implementing this in some of my more complicated projects; if nothing else, it will just make the Name Manager about 20 times easier to navigate (without all the duplicate names).

   解决方案

   As for the documentation, see Evaluating Names and Other Worksheet Formula Expressions

   • =A1 refers to cell A1 on the current sheet
   • =!A1 refers to cell A1 on the active sheet

   in conjunction with Worksheet References

   • Current refers to what Excel is recalculating ...
   • Active refers to what the user is viewing ...

   This is what Charles Williams demonstrated. As for your use case, I'd recommend user defined functions, say in VBA.

   这篇关于Excel VBA：Workbook-scoped，依赖于工作表的公式/命名范围（结果根据活动工作表而更改）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！