Excel VBA: workspace dependent workspace, named formula / named range (the result varies depending on the active sheet)

Question

EDIT: name changed for clarity.

Short summary:. I want to know if the behavior of Excel in a workbook-specific work environment is different formulas (which I will describe below) is a documented function in Excel. If yes, please point me somewhere somewhere in the documentation. It seems that I can not find anything about this on the Internet (maybe I use poor search terms ...?) And I do not want to use something that is actually a bug and may disappear in a later version!

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

EDIT: Please note that the VBA code below may not be entirely right - I have not tested it.

Conventional method

For engineering / scientific computing, I often had to use the same Named Formula / Range several times in the same book, but on different sheets. As a simplified example, I could implement something like this for the circle area:

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

This leads to the following set of Named Ranges / Formulas (per sheet):

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

This works great, but it has a major flaw in that it is difficult to make future changes. For example, if the formula changes (the area of ​​the circle will not change, of course, but the formulas of the AASHTO LRFD formula, for example, change almost every edition!), I have to edit every instance of each name formula. This is tiring even if I write a VBA procedure to do this for me.

Alternative method

I found below in case of an accident in Excel 2013 the other day and could not find anything about it anywhere on the Internet. It makes me hesitate to start using it.

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

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

This leads to the following SINGLE Named Range / Formula (in parentheses to the book):

=PI()*!Radius^2 <--- the formula is limited to a workbook; Note !Radius , not Radius .

Please note that this is NOT the same as the following (no exclamation point):

=PI()*Radius^2 <--- Note that here Radius is placed in the workbook.

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

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

• In a workbook, create two or more worksheets and enter "1" in cell A1 from Sheet1 , "2" in cell A1 Sheet2, "3" in cell A1 Sheet3, etc. and etc.
• Create a named range CellA1 (with a workbook area) and enter the following formula: =!$A$1
• Entering =CellA1 in any cell will result in "1" on Sheet1 , will result in "2" on Sheet2 , etc. etc.

Documentation?

Hey, you did it - thanks for being here with me!

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

Answer 1

For 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 the list of worksheets

• Current refers to what Excel recounts ...
• Active refers to user viewing ...

This is what Charles Williams demonstrated. As for your use case, I would recommend user-defined functions, for example in VBA.

Answer 2

You need to be careful! Links in the names:
Names with refer to starting with =! may give incorrect results when calling a calculation from VBA. They are calculated as if they always referred to the active sheet, and not to the sheet on which they are used:

put 1 in cell A1 of sheet1
a) put =CellA1 in A2 of sheet 1, where CellA1 is defined as =!$A$1
b) switch to manual calculation
c) activate Sheet2
d) run this VBA code

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

e) Now go back to Sheet1 and you will see that Sheet1! A2 contains everything that was in Sheet2! A1

Using indirect () eliminates this problem
Our name manager adds and warns about the use of this type of syntax.