Solved

Unable to see Global Name Ranges in Module

Posted on 2014-03-24
9
140 Views
Last Modified: 2014-04-07
Hi Team,
I am unable to see any Global Name ranges in Module Section.

if I loop within in module like given below I could not get my global name ranges whereas if i declare local name ranges I do get:

WorkSheet
'from here I am calling module level function
GetAllGlobalNamRanges(Activeworksheet)

Module 1
'passing active worksheet object

public sub GetAllGlobalNamRanges(ActiveSheet as Worksheet)
'looping through the activesheet object to get name ranges

for each NameRanges in ActiveSheet.Name
      'Do not get Global Name Ranges - That is waht I want .. Do not want to create local nameranges
      'Do get local name ranges
End Sub

Hopefully looking forward expert comments...
0
Comment
Question by:satmisha
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
The global named ranges are at the workbook level, not the worksheet level.

so to loop, you'd have to loop in the activeworkbook.names collection.

Thomas
0
 

Author Comment

by:satmisha
Comment Utility
But that ways.. I need to loop through workbook level which unnecessary  increase looping.. Is there any better solution ?
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
If you want the global named ranges, loop in the workbook once, if you want the local named ranges, loop through all names of all worksheets:
dim nmName as name, sht as worksheet

for each nmName in activeworkbook.names
'do stuff
next nmName

for each sht in activeworkbook.sheets
for each nmName in sht.names
'do stuff
next nmName
next sht

Open in new window


Makes sense?
0
 

Author Comment

by:satmisha
Comment Utility
Yes but issue is .. Lets say you have a workbook having 15 worksheet and each worksheet have 400 Name Ranges i.e.

1*15*400 = 6000 Name Ranges

So if I go with the approach suggested then pbobabely I need to loop through traverse all these name ranges which is not efficient.

Lets say I want to traverse Name Ranges of worksheet1 (only 400 name ranges )  only rather all the name ranges define in the workbook .

Is it possible ?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 39

Expert Comment

by:nutsch
Comment Utility
What are you trying to do with those name ranges?

Here's two macros, one for the local only on sheet1, one for the locals + globals for sheet1

sub LocalOnly
dim nmName as name, sht as worksheet, shtDest as worksheet
dim lLoop as long

set sht=activesheet
set shtdest=sheets.add

lLoop=1
for each nmName in sht.names
shtdest.cells(lloop,1)=nmName.name
lloop=lloop+1
next nmName
 end sub

Open in new window



sub LocalAndGlobals
dim nmName as name, sht as worksheet, shtDest as worksheet
dim lLoop as long

set sht=activesheet
set shtdest=sheets.add

lLoop=1
for each nmName in sht.names
shtdest.cells(lloop,1)=nmName.name
lloop=lloop+1
next nmName

lLoop=1
For Each nmName In ActiveWorkbook.Names
    
    If Not TypeOf nmName.Parent Is Worksheet Then
        shtDest.Cells(lLoop, 3) = nmName.Name
        lLoop = lLoop + 1
    End If
    
Next nmName


 end sub

Open in new window

0
 

Author Comment

by:satmisha
Comment Utility
Thanks  a lot for your prompts reply. But pardon me.. I'll try your suggested approach this wkend n let you know.
0
 

Author Comment

by:satmisha
Comment Utility
Thanks a lot for your reply and apologies for my late response.

I declare two global name ranges on Sheet 1 lets say NM11 and NM12 and similarly declare on sheet 2 i.e. NM21 and NM22. When I used snippet provided above it gave me all the name ranges whereas  I want only Name ranges of Sheet 1. Is there any possibility to do that ?

My requirement is just to avoid looping through other global name ranges defined on other sheets. I know Name-ranges are defined at workbook-level rather worksheet level. But is there any way to twist the code ?

Looking forward to hearing from you.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 215 total points
Comment Utility
I think I understand what you need: the following code will pull all the names that refer to the current sheet, using a reference to the parent of the range (nmName.RefersToRange.Parent.Name).



Sub LocalAndGlobals()
Dim nmName As Name, sht As Worksheet, shtDest As Worksheet
Dim lLoop As Long

Set sht = ActiveSheet
Set shtDest = Sheets.Add

lLoop = 1
For Each nmName In sht.Names

If nmName.RefersToRange.Parent.Name = sht.Name Then shtDest.Cells(lLoop, 1) = nmName.Name

lLoop = lLoop + 1
Next nmName

lLoop = 1
For Each nmName In ActiveWorkbook.Names
        
    If nmName.RefersToRange.Parent.Name = sht.Name Then shtDest.Cells(lLoop, 3) = nmName.Name
    lLoop = lLoop + 1
    
Next nmName


 End Sub
                                            

Open in new window

0
 

Author Closing Comment

by:satmisha
Comment Utility
Thanks a lot, even you answered my question in first attempt only. Thanks for your patience and guiding me.
0

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

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…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

762 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

6 Experts available now in Live!

Get 1:1 Help Now