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]

Nope, just personal.xlsb
If I remove that I get the same result.

 

[JoeMo]

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.

Same here and I'm running only Excel 2010. No JKP add-in installed, and PERSONAL.XLSB is the lone file in my XLSTART folder.

 

[MARK858]

I do have Book.xtlm in my XLSTART via mysignin/app data/roaming/microsoft/excel/xlstart.

 

[JoeMo]

I do have Book.xtlm in my XLSTART via mysignin/app data/roaming/microsoft/excel/xlstart.

No change after saving Book.xltm to my XLSTART folder, running the post#3 macro, deleting _Xlfn..., saving, closing and re-opening, and running post#3 macro again - _Xlfn... is back.

 

[MARK858]

Just been playing around more, done a full reboot, ran the macro, checked the name manager = nothing, THEN...
Put in a new SUMIFS formula, ran the macro and low and behold _Xlfn... is now back.

 

[Fluff]

I'm guessing they are there so that, if you open the workbook in an earlier version that doesn't support them, it wont destroy the formulae.

 

[Fluff]

Just done a little test with these 2 formulae in 2013
=IFERROR(10/0,"oops")
=COUNTIFS(A:A,"Fluff")

Opened the workbook in 2003 and the formulae became
=_xlfn.COUNTIFS(A:A,"Fluff")
=_xlfn.IFERROR(10/0,"oops")

Saved & closed the workbook & then reopened in 2013 & the formulae reverted to normal

 

[d0rian]

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).

The other responders are correct - this has nothing to do with the 3rd-party add-in. I only referenced it because Excel's native Name Manager doesn't display these weird hidden Ranges by default (so I wanted to make clear that I can't just go into Name Manager and delete them.)

 

[shg]

Does the window caption say Compatibility Mode?

 

[Fluff]

Not with my test file, which I created as an xlsx.