Print a list of defined names and what it refers to

[Skinman]

Hi all,
Using excell 2007 on Vista
Would like to print a list of defined names and associated data (value,
refers to, comments) from a worksheet
Have searched the web but come up empty. Closest was
name manager but I can't find a version for excel 2007
When the define name dialog window is open print options don't
seem to me to be accessible
Skinman

 

[CurlyDave]

sometimes its not to complicated..
Try this

Range("A1").ListNames

 

[Skinman]

Thanks Curly. Very grateful
The formula worked for workbook names but not worksheet names
Is it possible to amend the formula to include worksheet names?
Thanks Skinman

 

[BSc Chem Eng Rick]

Hi

Here's VB Code which gets you the name list, reference and values

Sub NamesLister()
For i = 1 To ActiveWorkbook.Names.Count
Sheets("Sheet1").Activate
Cells(i, 1).Value = ActiveWorkbook.Names(i).Name
Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address
Cells(i, 3).Value = ActiveWorkbook.Names(i).RefersToRange.Value
Next
End Sub

 

[Skinman]

Thanks BSc Chem Eng Rick ...

But failed to work for me. As soon as it comes to a #ref comes up with
run time error value 1004 debug comes up for this line....
Cells(i, 2).Value = ActiveWorkbook.Names(i).RefersToRange.Address

I have a macro that defines a range for a filter each time I run the macro
at the end of the macro that range is deleted leaving the defined range with
#ref
till the next time I run the macro.
Thanks for your input though, much appreciated.
Skinman.

 

[Peter T]

Typically you get those #REF! errors after deleting entire rows or columns
of the named range. Attempting to refer to such results in an error.

Simplest (crudest) is simply to add before the loop
On Error Resume Next

If you see REF as part of the address you'll know why you don't get an
address

this line -will error if the named range refers to more than one cell

Try the NameManager addin which you can get from the authors'
sites of Jan Karel Pieterse and Charles Williams:

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T