VBA code to itemize range names and associations?
 

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.

VBA code to itemize range names and associations?
 

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


"Larry A" wrote:

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.

VBA code to itemize range names and associations?
 

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

VBA code to itemize range names and associations?
 

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 :

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.

VBA code to itemize range names and associations? New Wrinkle
 

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" wrote in message
oups.com...
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

VBA code to itemize range names and associations? New Wrinkle
 

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

VBA code to itemize range names and associations? New Wrinkle
 

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

VBA code to itemize range names and associations?
 

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 :

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

VBA code to itemize range names and associations?
 

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" wrote in message
ft.com...


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 :

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

VBA code to itemize range names and associations?
 

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 :

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" wrote in message
ft.com...


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 :

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

VBA code to itemize range names and associations?
 

Thanks, I will definitely give it a look.

"keepITcool" wrote in message
ft.com...

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 :

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" wrote in message
ft.com...


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 :

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

VBA code to itemize range names and associations?
 

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/marketpl...41921033&CTT=4

Regards,

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

VBA code to itemize range names and associations?
 

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.

wrote in message
ups.com...
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/marketpl...41921033&CTT=4

Regards,

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

VBA code to itemize range names and associations?
 

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