Solved

Unable to see Global Name Ranges in Module

Posted on 2014-03-24
9
153 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
[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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

634 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