Why doesn't this work...??

[Chris Gorham]

Trying to get this piece of code to compile. All I want to
do is to cycle through the range names in a workbook and
assign details to some variables. It bugs out though...

Dim nm as object

For Each nm In ActiveWorkbook.Names

rng_name = nm.Name // this works ok
rng_sheet = nm.Worksheet.Name // this bugs out
rng_add = nm.Address // this also bugs out

Next nm

Any help much appreciated

Chris

 

[Tom Ogilvy]

Dim rng as Range
Dim nm as Name
Dim rng_sheet as String
Dim rng_add as String
Dim nm as Name
For Each nm In ActiveWorkbook.Names

rng_name = nm.Name '// this works ok
On Error Resume Next
set rng = nm.RefersToRange
On Error goto 0
if not rng is nothing then
rng_sheet = rng.parent.Name '// this bugs out
rng_add = rng.Address '// this also bugs out
' or
'rng_add = nm.Refersto
else
rng_sheet = ""
rng_add = ""
end if
Next nm

Regards,
Tom Ogilvy

Note that you can have defined names that don't refer to ranges. That is
what the error checking is all about.