• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Force VBA to use the Global Definition of the named range

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
=Builders!$A$6 (Which is the local version)
Global Name belonging to the workbook & so expected was

I would have expected the result for
=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
sir plus
sir plus
  • 3
  • 2
1 Solution
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
sir plusSales ManagementAuthor Commented:
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.
sir plusSales ManagementAuthor Commented:
Your tip for how the names are selected enabled me to make a work around so you can have this
your a gent :) Thank you
sir plusSales ManagementAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now