[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Force VBA to use the Global Definition of the named range

Posted on 2011-10-26
5
Medium Priority
?
221 Views
Last Modified: 2012-05-12
I have a workbook with 2 names defined with the same name
One is a Global "RngSales" And the other is local to a Sheet Named "Builders" which is the first sheet in the WorkBook.
See the Picture for the Name Manager Dialog view showing the names are defined

I want to use the Global version but don't seem to be able to reference it

In the immediate window
?Workbooks("Sales Summary11-12.xlsm").Names("RngSales").RefersTo
Gives
=Builders!$A$6 (Which is the local version)
Global Name belonging to the workbook & so expected was
=RV!$A$6

I would have expected the result for
?WorkSheets("Builders").Names("RngSales").RefersTo
=Builders!$A$6 (Which it is)

How do I reference the name so it uses Global which is addressed on a different sheet & I get the result as per the picture
=RV!$A$6 Name Manager Dialog view
0
Comment
Question by:sir plus
  • 3
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 total points
ID: 37037730
playing around with different names I get similar results, whichever appears first in the name manager seems to be the one Excel returns.

This is at odds with what I've read - that Excel will prefer the Global name to the Local name - but does highlight a common theme which is:- DON'T use duplicate names

Is it possible to rename one of them? - excel is good at correcting formulas and so on but any code would have to be hand cranked
0
 
LVL 5

Author Comment

by:sir plus
ID: 37037841
I am converting a massive sheet with the names hard coded in the vba
Safer & hours of work saved if I dont have to which is why I want this if it exists.

I will submit a bug report to MS and see what they say.
0
 
LVL 5

Author Comment

by:sir plus
ID: 37037943
Hmm
Your tip for how the names are selected enabled me to make a work around so you can have this
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 37039069
your a gent :) Thank you
0
 
LVL 5

Author Comment

by:sir plus
ID: 37056268
To Clarify
The first of the local(or possibly many locals) OR the Global if thats first is the one returned when a local is called & both Locals & golbals exist with the same name
So it depends really on the sheet name
I had hundreds of these & was using code to rewrite the names as locals so had to to remap all as local so I remapped the Globals the a sheet names zzz but using the same definitions apart from the sheet name and that forced it to use the local defs
I suspect even if the names were hidden it would work this way but haven't tested
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question