Solved

Unable to see Global Name Ranges in Module

Posted on 2014-03-24
9
146 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 215 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

772 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