Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA: how to change scope of names to local

Posted on 2010-01-10
15
Medium Priority
?
1,200 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

580 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