_xlfn.IFERROR Named Range?

**ptmuldoon** · 03-16-2016, 07:36 PM

I'm trying to learn what is causing for 2 named ranges to show up in essentially a blank workbook. By blank, in trying to learn/isolate what is causing this issues, I deleted every worksheet out of the file and resaved it under a new name.

From what I can remember, the original file was made in Excel 2013 and it was also resaved as a 2013 file. The file is an 'xlsm' file as it did originally have a bunch of macros, but i stripped it all down to just the one macro to list out the named ranges.

So a blank file that is giving to strange named ranges?

EDIT. I just found another topic on this issue here. That attached sample probably/may not show any error it appears it may have 'disappeared' when you save close and reopen

http://stackoverflow.com/questions/1...-does-not-work

Now if I only I can find what is causing that in my original, bigger file.

**ptmuldoon** · 03-16-2016, 09:12 PM

Well, in further trying to solve seeing these strange 'errors'. I've found that this hidden namedrange of:

_xlfn.IFERROR with a value of #NAME

is actually being caused by use of the IfError formula of =IFERROR(G9/F9,"") My actual complete workbook makes use of the IfError formula extensively on multiple worksheets as well.

This all seems strange as the formula is valid and the information is all in a 2013 workbook.

Normally, this wouldn't bother me until recently when I started trying loop named ranges in a macro (separate issue) and VBA will return an error with a macro starting with an underscore.

**ptmuldoon** · 03-17-2016, 02:18 PM

I wanted to attach another example here and hoping someone may know the underlying cause issue. I think I may have read that a named range of something like "_xlfn.IFERROR" is actually an built-in name in Excel and there is possibly no way to get rid of it?

In the attached, you'll see the Named Range Error being given. But if you then delete the formula in cell F15. Then save and reopen, that Named Range will actually disappear

**ptmuldoon** · 03-26-2016, 10:08 AM

I wanted to circle back and learn if anyone has any suggestions on why I may be seeing these errors when I list out my named ranges.

There are essentially 3 'hidden' named ranges that I always see, and appears these cause issues when you try and work with and search named ranges in vba. The 3 'hidden' names I see are.

_xlfn.COUNTIFS
_xlfn.IFERROR
_xlfn.SUMIFS

And this is a 2013 workbook. And I've learned If I remove any CountIf, SumIf formulas from the workbook, those named ranges will disappear?

**newdoverman** · 03-26-2016, 10:49 AM

I ran this macro on your workbook and it made the _xlfn.IFERROR name appear in the Name Manager. This article has an explanation that applies to earlier versions of Excel so why it is appearing for you is a mystery.
https://support.office.com/en-US/art...4-9FBB77FD5025

This is the macro that I ran to make the name visible in the Name Manager

Please Login or Register  to view this content.

Upon downloading your file (open in Excel) I saved it to my HD and opened it without any errors showing. I ran the macro and the defined name did appear in the Name Manager.

**shg** · 03-26-2016, 11:41 AM

Those names will be created if you open an Excel 2007+ workbook in a version of Excel (e.g., 2003) before the functions existed.

Even so, Excel 2003 will open such workbooks read-only, and if you save it by another name and then open it in a later version, the name goes away and the normal function re-appears, so I dunno how you managed to do that. I don't have Excel 2013 to test.

**Alphonse68** · 09-21-2017, 06:08 AM

Hi,
I have these four, _xlfn.AGGREGATE, _xlfn.BAHTTEXT, _xlfn.GAMMA.DIST, and _xlfn.IFERROR, though I have only used IFERROR, having never even heard of the other three.
They impede me from looping through the names and copying them to another workbook.
Are they safe to delete?
TIA.
Cheers

**xlnitwit** · 09-21-2017, 06:29 AM

Hi,

You can't delete them if they are in use, so far as I know. You could simply add a test to your code to ignore names whose name begins with "_xlfn."

**Alphonse68** · 09-21-2017, 10:31 AM

Thanks. I will do just that, specially since I have no idea where they would be in use... I had never seen a range name beginning with _xlfn.
Cheers.

**cnoblot** · 05-09-2019, 12:38 PM

Excelente, gracias por el aporte

_xlfn.IFERROR Named Range?

LinkBack

Thread Tools

Rate This Thread

Display

_xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Re: _xlfn.IFERROR Named Range?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compare cells to named range and create new sheets using related named range

[SOLVED] Assign named range to one cell depending on named range in another

[SOLVED] determining if cell is part of named range and what that named range is

Need to make one named range equal to another named range with IF function

_xlfn.AVERAGEIFS convert to 2003

[SOLVED] Determine what Named Range the Target Address is and return Named Range Name

_xlfn.AVERAGEIF ... How do I remove this?

Bookmarks

Bookmarks

Posting Permissions