Force VBA to use the Global Definition of the named range

Posted on 2011-10-26
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
=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
Question by:sirplus
    LVL 19

    Accepted 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
    LVL 5

    Author Comment

    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.
    LVL 5

    Author Comment

    Your tip for how the names are selected enabled me to make a work around so you can have this
    LVL 19

    Expert Comment

    your a gent :) Thank you
    LVL 5

    Author Comment

    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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now