?
Solved

Excel VBA: how to change scope of names to local

Posted on 2010-01-10
15
Medium Priority
?
1,183 Views
Last Modified: 2013-11-25
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

0
Comment
Question by:andy7789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280074
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280080
To further clarify:
    Set wsh = ThisWorkbook.Worksheets("RM")
   
    wsh.Names.Add ("MyLocalName")
 
0
 

Author Comment

by:andy7789
ID: 26280131
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280140

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
 

Author Comment

by:andy7789
ID: 26280153
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280166
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280169
Actually,

ThisWorkbook.Worksheets("own").Range("weights")(4, n + 3).Name = "own!test_approx"
would do in this case I think...
0
 

Author Comment

by:andy7789
ID: 26280171
Yes, it works perfectly, but creates only Global names:

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

Accepted Solution

by:
TomSchreiner earned 2000 total points
ID: 26280177
Preface the name with the [sheet name!] and it will create a sheet level name.  
 
...Name = "own!test_approx"
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280190
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
 

Author Comment

by:andy7789
ID: 26280205
One second - let me try this:

......Name = "own!test_approx"
0
 

Author Comment

by:andy7789
ID: 26280261
Works OK - thank you!
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280267
I feel globally appreciated!  *smiles*
0
 

Author Comment

by:andy7789
ID: 26280275
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
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26280283
Sorry.  I don't have a clue about that and have  not run into that problem before.
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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

764 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