Testing in a macro for bad range name?

[Don Wiss]

I'm working on a project that opens each old workbook in a folder and
extracts data. Some of these workbooks have bad range names, e.g. with #REF
as part of the refers to. A test against the list of workbook range names
passes. You can't test for iserror, as you can't even read it. Setting an
On Error GoTo xxx doesn't kick in for some strange reason. How can I test
for the invalid range name and branch accordingly?

Don <donwiss at panix.com>.

 

[Tom Ogilvy]

Try this:

Dim nm as Name
for each nm in Activeworkbook.Names
if instr(1,nm.Refersto,"#REF",vbTextCompare) then
msgbox nm.name & " " & nm.Refersto
end if
Next

Untested.