Excel VBA: how to change scope of names to local

Hi x-perts,

Is there n option to make names local when creating new names as

ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = "test_approx"

it always crfeates Global names. I have found te code to redefine gobal names to local (see attached, but I would rather try to find a way of creating local names from the beginning (not deleting and recreating them).

Any sggestions?

Thanks
Sub LocalizeNames()
   
    Dim nm As Name
    Dim wsh As Worksheet
    Dim sName As String, sRefersto As String
   
    Set wsh = ThisWorkbook.Worksheets("RM")
   
    For Each nm In ThisWorkbook.Names
        If nm.Parent.Name = ThisWorkbook.Name Then
            If nm.RefersToRange.Cells(0).Value = nm.Name And _
                nm.RefersToRange.Parent.Name = wsh.Name Then
                sName = nm.Name
                sRefersto = nm.RefersTo
                nm.Delete
                wsh.Names.Add wsh.Name & "!" & sName, sRefersto
            End If
        End If
    Next nm
End Sub

Open in new window

andy7789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TomSchreinerCommented:
Replace your reference to the workbook in:
 For Each nm In ThisWorkbook.Names

With a reference to a worksheet:
For Each nm In Sheet1.Names
 or
For Each nm In Worksheets("Sheet1").Names  
Tom
 
0
TomSchreinerCommented:
To further clarify:
    Set wsh = ThisWorkbook.Worksheets("RM")
   
    wsh.Names.Add ("MyLocalName")
 
0
andy7789Author Commented:
Thank you, but my question was whether it is possible to define Local names from the beginning rather than running an additional sub to redefine them.

Is it possible to define a range name as local?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TomSchreinerCommented:

My second post answers that question.
See the names collection of the worksheet.  This collection contains names that are defined at the worksheet level while the names collection of the workbook contains workbook level names.

ThisWorkbook.names.add "WorkbookScopeName"
Sheet1.names.add "Sheet1ScopeName"

In the Define Name dialog:
Sheet1!SheetScopeName
0
andy7789Author Commented:
Can I define global names without "Add" directive, i.e.

ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = "test_approx"

It creates a GLOBAL name, though I am using ThisWorkbook.Worksheets("own")....
0
TomSchreinerCommented:
If I understand you, this is what you are trying to do:
 
ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = Worksheets("own").Name & "!test_approx"
 
0
TomSchreinerCommented:
Actually,

ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = "own!test_approx"
would do in this case I think...
0
andy7789Author Commented:
Yes, it works perfectly, but creates only Global names:

ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = "test_approx"
0
TomSchreinerCommented:
Preface the name with the [sheet name!] and it will create a sheet level name.  
 
...Name = "own!test_approx"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TomSchreinerCommented:
What is your definition of global?  You only have two levels of scope for defined names.  Worksheet and workbook.  Beyond this, I don't understand what you are after...  :)
0
andy7789Author Commented:
One second - let me try this:

......Name = "own!test_approx"
0
andy7789Author Commented:
Works OK - thank you!
0
TomSchreinerCommented:
I feel globally appreciated!  *smiles*
0
andy7789Author Commented:
Tom,

Hav alook at my other question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25043087.html

Maybe you can think of some possible reasons for that...


Thank you!
0
TomSchreinerCommented:
Sorry.  I don't have a clue about that and have  not run into that problem before.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.