Excel keeps creating a hidden _xlfn.IFERROR name - why?

[d0rian]

There's a handful of hidden names in my workbook that are killing my attempt to use VBA to copy 200+ Named Ranges to a new workbook. One of them is named "_xlfn.IFERROR" -- it doesn't show up in Excel's native Name Manager, but the Name Manager 4.3 add-in DOES show it (b/c it has the ability to show 'hidden' names).

I googled _xlfn.IFERROR and pages seemed to suggest that it was caused by trying to use an earlier version of Excel (which didn't support the function you used when creating the file in a later version.)

But that doesn't apply here at all. In fact, that hidden/erroneous range is getting created immediately even in a new 2007 workbook as soon as I type a new =IFERROR formula. E.g.:

i) I open a completely blank workbook
ii) I type in a very basic IFERROR formula in A1
iii) And then I open the Name Manager add-in that shows the below.

That makes no sense. 2007 does support IFERROR, and the formula is calculating correctly...but that _xlfn.IFERROR hidden/erroneous range is getting created anyway. Why?

 

[Fluff]

You can bypass those names like

Sub d0rian()
   Dim Nme As Name
   
   For Each Nme In ThisWorkbook.Names
      If Not Nme.Name Like "_xlfn*" Then
         ' do something
      End If
   Next Nme
End Sub

 

[d0rian]

Thanks, yes I found some suggestions in other threads about how to handle this if my ultimate goal was copying the Named Ranges to a new workbook (which it is)...but in the interest of troubleshooting what's gotten corrupted in my original file, I was trying to understand why these Named Ranges were even getting created in the first place, since the explanation that seemed to be given most often was that it had to do with Excel version incompatibility stuff, when my OP example of typing a simple IFERROR formula in a brand new Excel 2007 book generated the problematic hidden name immediately, so it's clearly not that. FWIW, I found several other people asking about it in the Name Manager 4.3 Comments section , but the developer reply was typically 'yeah, it has something to do with Excel versions but we can't really figure it out either.'

 

[joeu2004]

I found several other people asking about it in the Name Manager 4.3 Comments section , but the developer reply was typically 'yeah, it has something to do with Excel versions but we can't really figure it out either.'

You are asking about an add-in that is not distributed with Excel, nor developed by MSFT. You are correct to discuss this in a forum that is specific to NM 4.3. But if that is the level of support that JKP provides, perhaps you should simply abandon the freeware, and be sure to give JKP feedback (``tell everyone if you like it and to tell me if you don't`` -- actually, I would tell everyone why you don't).

Patient: Nurse, it hurts when I bang my head against the wall.
Nurse: So, don't do that!

 

[MARK858]

joeu2004, I am not so sure it is just down to the 3rd party software as you can make the names appear in the Excel name manager with...

Sub ShowAllNames()
Row = 1
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End Sub

code curtesy of newdoverman

As I can see the same with the 3rd party software (it is also part of FastExcel) and made the names appear in the Excel name manager with the macro above when using SUMIFS,IFERROR and COUNTIFS formula (all on Excel 2010 and created by just typing formula in cells. The formulas have no error).

I'll have to test what happens on a computer without FastExcel to see if it is the addin that creates the names but it won't be until the New Year nYOUow.

P.S. you can delete them once visible.

 

[MARK858]

Just done some more testing and now after deleting the names they do not reappear for me even when using the FastExcel name manager, which makes me think that it is Excel itself that is "remembering" something from the past (which is what Charles Williams speculated on).

So at the moment I believe the names were genuine but needs much more testing so something to do if I get quiet enough to be bored in the New Year

 

[Fluff]

I find that as soon as I close & re-open the workbook they reappear.
As I have both 2003 & 2013 installed, that may be a factor.

 

[MARK858]

Fluff, just for info is that with or without the addin installed?
Btw, still nothing appearing for me but only one version on this laptop these days.

 

[Fluff]

Without the add-in

 

[shg]

Doe you have a template called Book.xlt in your XLSTART folder?