ActiveWorkbook.Names returns "=#NAME?"

Hey All,

I am trying to set up some code that loops through the named ranges in a workbook and am encountering some errors. Some digging led me to discover that the the first named range the code finds is "=#NAME?"

Example:

Sub test()


Dim nameDrawing As Name




For Each nameDrawing In ActiveWorkbook.Names




Debug.Print nameDrawing.Value




Next nameDrawing




End Sub

returns

=#NAME?
='Cut List'!$AU$1:$BF$63
='Cut List'!$AU$65:$BF$127
='Cut List'!$AF$82:$AP$107
etc....

in the immediate window.

The name manager window does not show any named ranges with any errors that I can see.

If anyone has any ideas on what could be causing the errant named range, I would appreciate the help.

Thanks, Don

Re: ActiveWorkbook.Names returns "=#NAME?"

Thanks for the reply.

Here was the output for the errant range:

_xlfn.IFERROR =#NAME? False

I didn't think of the possibility of a hidden range, as I have never used them. I ran the following code:

Sub test()


Dim nameDrawing As Name




For Each nameDrawing In ActiveWorkbook.Names




nameDrawing.Visible = True




Next nameDrawing




End Sub

then deleted the offending range from the "Name Manager".

Hopefully, my other code will work now that there are no errant ranges.

Thanks, Rory!

Don

Re: ActiveWorkbook.Names returns "=#NAME?"

Quote from rory;773158

IIRC whenever you use a new (i.e. post 2003) function, a hidden name is created. I assume it has something to do with backwards compatibility - possibly how they made the compatibility pack work.

Just an update: You turned out to be 100% right. I am using the iferror() function and it is causing excel to add the named range specified earlier. I just wrote a simple if to skip over any hidden named ranges since it appears sumifs() and other new functions can cause similar errors.

Thanks!