VBS for excel named ranges

[mario]

What will be the vba code to display all the "name" (text) of the named
ranges and its reference range it refers to.

I am trying to create some thing like this

For each rname in Activeworkbook.names.count
msgbox "name is: " & rname.name & "it refers to: " & rname.range
next

Please help.

 

[Gary''s Student]

Sub nameit()
For Each nm In ActiveWorkbook.Names
MsgBox (nm.Name & " " & Range(nm).Address)
Next
End Sub

 

[JLGWhiz]

You don't need the .Range after rname. And drop the .count in the For Each.

For each rname in Activeworkbook.names
msgbox "name is: " & rname.name & "it refers to: " & rname
next

 

[Dave Peterson]

And if you're working with names, get Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You'll find it very valuable.

 

[Mike Middleton]

mario -

It may be an interesting exercise to "reinvent the wheel" using VBA, but
Excel does already have a menu item for this feature.

In Excel 2003 and earlier versions, choose Insert | Name | Paste | Paste
List.

In Excel 2007, choose Formulas | (Defined Names) Use in Formula | Paste
Names | Paste List.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel