VBA code to itemize range names and associations?

[Larry A]

I would appreciate any thoughts on how to set up a macro to build an
inventory list of all range names in a spreadsheet with associated sheet
names and cell ranges. What I have so far is:

sub BuildRangeNameList()
dim i as integer, s as string, n as name
for i = 1 to activeworkbook.name.count
set n = activeworkbook.name(i)
s = n.name
next i
end sub

How do I find the associate sheet name? And asociated cell range in
non-R1C1 format?

Thanks in advance. Larry.

 

[Guest]

Hi Larry,

you can use the property 'RefersTo' of a 'Name' object to return sheet and
cell address.
By the way, there also is a 'Names' collection for worksheets.

Hope this helps,
Herbert

 

[Roman]

Hi Larry, hope this helps:

Sub BuildRangeNameList()
Dim i As Integer, s As String, n As Name
For i = 1 To ActiveWorkbook.Names.Count
Set n = ActiveWorkbook.Names(i)
s = n.Name
MsgBox "Name: " & s & vbCrLf & _
"sheet: " & n.RefersToRange.Parent.Name & vbCrLf & _
"range: " & Range(n).Address
Next i
End Sub

 

[keepITcool]

simply get NameManager addin from
http://www.jkp-ads.com/Download.htm

does it all (and more)!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Larry A wrote :

 

[Larry A]

Herbert, Roman -- Thanks much. But now I have a new wrinkle. The
spreadsheet against which I am running uses Hyperion's Essbase Excel add-in
to query Essbase cubes. Essbase apparently stores/embeds information in the
template via the names collection. So the routine suggested runs fine until
it runs into this Essbase stuff. After which it fails to retrieve any
further valid Excel range names. And what does come back is, for me,
gibberish.

Any thoughts at all on how to distinguish between the gibberish and the
valid, native Excel range names? Or some other route into the collection
that might by-pass the gibberish altogether and, in so doing, also guarantee
that I will find the other remaining range names? I know this is a stretch.
but any ideas would be greatly appreciated. Otherwise, I think I am dead in
the water.

Thanks again. Larry.

=========================

 

[Roman]

Larry, unfortunately no Essbase experiences on my side. Sorry.

 

[Ron Coderre]

Hi, Larry

The range names that Essbase uses all begin with "Ess". If you skip those,
I suspect your code will execute as intended.

Does that help?

Ron

 

[keepITcool]

quoted code assumes that all names refer to ranges.
it will error out when the refersto contains constants or formulas that
do not evaluate to ranges.following should work

Sub ListNames()
Dim v, n&, rng As Range
On Error Resume Next
With ActiveWorkbook.Names
ReDim v(1 To .Count, 1 To 3)
For n = 1 To .Count
With .Item(n)
v(n, 1) = .Name
v(n, 2) = .RefersToR1C1
If Not IsError(.RefersToRange) Then
v(n, 3) = .RefersToRange.Address
End If
End With
Next
End With

Set rng = Application.InputBox("Dump where?", Type:=8)
With rng.Resize(UBound(v, 1), UBound(v, 2))
.NumberFormat = "@"
.Value = v
End With

End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Roman wrote :

 

[Larry A]

Thank you. I will give this a try. May I ask, to what extent do you trust
the Name Manager add-in? I'm generally reluctant to put outside code on my
machine, but this looks pretty legit and could prove extremely useful
Thanks again. Larry.

==========================

 

[keepITcool]

namemanager is totally legit and has thousands of users.
Jan Karel Pieterse is an MVP and has been for ages.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Larry A wrote :

 

[Larry A]

Thanks, I will definitely give it a look.

namemanager is totally legit and has thousands of users.
Jan Karel Pieterse is an MVP and has been for ages.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Larry A wrote :

 

[Larry A]

Very neat. Thanks!

Thanks, I will definitely give it a look.

 

[jkpieterse]

Hi Larry

May I ask, to what extent do you trust
the Name Manager add-in? I'm generally reluctant to put outside code on my
machine, but this looks pretty legit and could prove extremely useful

Very wise not to trust just everything!

An indication that my Name Manager is legit is that it is one of the
utilities listed on Microsoft's own Office Marketplace:

http://office.microsoft.com/marketp...M011266441033&CategoryID=CE010741921033&CTT=4

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Larry A]

Jan, I downloaded and gave a try on my "problem" template and it worked like
a charm. And I have forwarded on to one of my associates. Much appreciated!!
Larry.

 

[Jan Karel Pieterse]

Hi Larry,

I downloaded and gave a try on my "problem" template and it worked like
a charm.

Great. Thanks for letting me know.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com