title: Excel VBA: Workbook-scoped, worksheet dependent named formula/named range (result changes depending on the active worksheet)
body: <p>EDIT: Title changed for clarity.</p>
  
  <p><strong>Quick summary:</strong> 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!</p>
  
  <p>Strictly speaking this is not really a question about VBA; however, named formulas are something I and others <a href="https://stackoverflow.com/a/23913882/2437514">use in VBA code all the time</a>, so it is still applicable to the subject I think. </p>
  
  <p>EDIT: Note that the VBA code below may not be exactly right- I haven't tested it. </p>
  
  <h2>Regular Method</h2>
  
  <p>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:</p>
  
  <pre><code>Dim sht as Worksheet
  For Each sht In ThisWorkbook
      Call sht.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*'" &amp; _
              sht.Name &amp; "'!Radius^2")
  Next sht
  </code></pre>
  
  <p>Which results in the following set of Named Ranges/Formulas (scoped to each worksheet):</p>
  
  <pre><code>=PI()*Sheet1!Radius^2        &lt;--- scoped to Sheet1
  =PI()*Sheet2!Radius^2        &lt;--- scoped to Sheet2
  etc. etc.
  </code></pre>
  
  <p>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. </p>
  
  <h2>Alternative Method</h2>
  
  <p>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.</p>
  
  <p>Let's say I run the following single line of code instead:</p>
  
  <pre><code>Call ThisWorkbook.Names.Add(Name:="AreaCircle",RefersTo:="=PI()*!Radius^2")
  </code></pre>
  
  <p>Which results in the following SINGLE Named Range/Formula (scoped to the workbook):</p>
  
  <p><code>=PI()*!Radius^2</code> &lt;--- formula is scoped to Workbook; note <code>!Radius</code>, not <code>Radius</code>.</p>
  
  <p>Note that this is NOT the same as the following (there is no exclamation point):</p>
  
  <p><code>=PI()*Radius^2</code> &lt;--- Note that here, <code>Radius</code> is scoped to the Workbook.</p>
  
  <p>Now, <code>AreaCircle</code> 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 <code>Radius</code> (one for <code>Sheet1</code> and one for <code>Sheet2</code>), <code>AreaCircle</code> will be calculated depending on the value of <code>Radius</code> 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!). </p>
  
  <p>This is difficult behavior to describe; if you are confused by my description, you can do the following steps to recreate this behavior:</p>
  
  <ol>
  <li>In a workbook, create two or more worksheets and enter "1" in cell <code>A1</code> of <code>Sheet1</code>, "2" in cell <code>A1</code> of Sheet2, "3" in cell <code>A1</code> of Sheet3, etc etc.</li>
  <li>Create a Named Range called <code>CellA1</code> (with Workbook scope) and enter the following for the formula: <code>=!$A$1</code></li>
  <li>Entering <code>=CellA1</code> in any cell will result in "1" on <code>Sheet1</code>, result in "2" on <code>Sheet2</code>, etc etc. </li>
  </ol>
  
  <h2>Documentation?</h2>
  
  <p>Hey, you made it - thanks for sticking with me here!</p>
  
  <p>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). </p>