Excel VBA: Workbook-scoped, worksheet dependent named formula/named range (result changes depending on the worksheet)

The Problem (Q-score 13, ranked #129th of 303 in the Excel VBA archive)

The scenario as originally posted in 2014

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).

Why community consensus is tight on this one

Across 303 Excel VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — niche answer (below median) (+5)

Advisory answer — community consensus with reference links

Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.

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.

When to Use It — classic (2013–2016)

Ranked #129th in its category — specialized fit

This pattern sits in the 99% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.

What changed between 2014 and 2026

The answer is 12 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.