Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Unable to see Global Name Ranges in Module

Posted on 2014-03-24
9
Medium Priority
?
167 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
ID: 39951885
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
ID: 39951889
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
ID: 39951893
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
Independent Software Vendors: 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!

 

Author Comment

by:satmisha
ID: 39954416
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
 
LVL 39

Expert Comment

by:nutsch
ID: 39954432
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
ID: 39957475
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
ID: 39964740
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 430 total points
ID: 39967012
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
ID: 39984559
Thanks a lot, even you answered my question in first attempt only. Thanks for your patience and guiding me.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.
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 Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 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