Named Ranges with _xlfn. prefixes

[GeorgieAnne]

Hello,

I have some VBA code to makes a list of Named Ranges in a workbook. It then visits each of these named ranges and grabs some data from them for further processing.

Today, I had this code fail, while it has been working perfectly for months.

The reason is there seems to be 3 new Named Ranges named: _xlfn.TEXTJOIN, _xlfn.SINGLE, and _xlfn.CONCAT. Based on this web page https://support.microsoft.com/en-us/office/issue-an-xlfn-prefix-is-displayed-in-front-of-a-formula-8... it seems to me that some unsupported functions were introduced.

A) How was this done? I know this is a very vague question, but I need to prevent it from happening again so that my VBA code will run

B) The three functions should be supported I have Office 365 updated to the latest version and build:

Microsoft® Excel® for Microsoft 365 MSO (Version 2206 Build 16.0.15330.20216) 64-bit. I checked and no updates were available?!

C) I searched all my worksheets looking for these functions, and did not find any of them? Other than the sheet that contains the Named Range List of names! I do not recall using any of these functions in my workbook either!

Thanks for any help and insight.

Georgie Anne

[#2]

@GeorgieAnne 

Nobody else on older version opened your file?

[#3]

Hello @SergeiBaklan,
Thanks for your question.
No, same version of MS-Office installed on all Windows 10 PCs. And the most puzzling thing is that none of the functions are used anywhere in the workbook.

But your question may point to something interesting. Do you think that opening a workbook developed using an older version of MS-Excel could trigger this behavior, even though as I said none of these functions are used?
Georgie Anne

[#4]

@GeorgieAnne 

My recollection is that the hidden system names appear when a worksheet containing new functions is opened in an older Excel version that does not support them.  You could modify your VBA to test for names starting "_xlfn" and exit the loop whenever it finds one.  It may also be useful to skip names that refer to valid formulas but are not range references.  They will have a '.RefersTo' property but there will be no 'RefersToRange' property.

[#5]

Thanks @PeterBartholomew1,
I am stumped. As I mentioned none of these formulas are being used. All the machines that opened the workbook has the same version of MS-Excel, and as you suggested I have added code to check for "_xlfn." in the Range Name and not to process it. That is a solution, but I am still curious as to what happened. I ran the repair utility on my development machine as I opened the workbook, and nothing changed. So???

Would you happen to know a way to get the Scope property of a Named Range?
Georgie Anne

[#6]

what is the named ranges? are they just absolute references like Shee1!$A$1:$B$5 or a formula?
The other oddity is that the named ranges are also actual excel functions. I tested if that causes any issue but it didn't for me.
My guess is more along the lines that the names are using formulas that when opened on an older version that version didn't recognize those formula/functions. can you paste a screenshot of your Name Manager?

[#7]

@GeorgieAnne

I think these system generated names are normally hidden but they will be seen by VBA.  I have just checked the names from a workbook I am planning to post here, using the VBA

Sub Namelist()

For Each n In ThisWorkbook.Names
Debug.Print n.Name
Next n

For Each n In Sheet1.Names
Debug.Print n.Name
Next n

End Sub

What I get is

"System names showing functions - hidden"
_xlfn._xlws.SORT
_xlfn.BYROW
_xlfn.CONCAT
_xlfn.FORMULATEXT
_xlfn.HSTACK
_xlfn.LAMBDA
_xlfn.LET
_xlfn.MAKEARRAY
_xlfn.REDUCE
_xlfn.SEQUENCE
_xlfn.TEXTJOIN
_xlfn.UNIQUE
_xlfn.VSTACK
_xlfn.XMATCH
"System names holding LET local names - hidden"
_xlpm.a
_xlpm.acc
_xlpm.clrs
_xlpm.colours?
_xlpm.ctry
_xlpm.distinct
_xlpm.f
_xlpm.flag
_xlpm.headers
_xlpm.joined
_xlpm.k
_xlpm.m
_xlpm.n
_xlpm.ptr
_xlpm.r
_xlpm.res
_xlpm.sorted
_xlpm.sp
_xlpm.str
_xlpm.x
_xlpm.?
"Named Ranges"
colours
country
data
DoB
Sheet1!ErrorRange
"Named formulas - Lambda functions"
Pack?
Thunk?
Unpack?
"Named Range within Sheet1 Scope"
Sheet1!ErrorRange

Note: the named Lambda function was actually 'Thunkλ' but the 'λ' does not show because VBA is limited to the ASCII character set.  Also, the green text was added by me.

The names .RefersTo property is also important but I did not get much from the .RefersToReference property.  Some functionality equivalent to the worksheet function ISREF() would be useful and, no doubt, there is some trick to implementing such a test.

[#8]

I've seen a similar issue where those functions are missing in a fully updated 365 build (Current Channel). I've had it happen to my work computer, too. For your issue, I'd approach it the same way by trying a Quick Repair of Office. If the Quick Repair fails, try the Online Repair (Essentially a re-installation).

[#9]

Peter that is very interesting list. It is almost like excel creates a hidden name for each of the new functions that might have backward compatibility issue. If you look at your list they are all new functions but not all of them. For example ByCol, Scan, and Map aren't listed. So my guess is that those are the ones that you've used. Then in the background it actually puts those _xlfn versions in the formula so when an old version open it is sees a "name" object and doesn't just break but newer excel knows to swap them out for the function.
The one Name that was mentioned in the OP that stands out is "Single" as that isn't a function I know.

[#10]

@mtarler 

I think your assessment is along the correct lines, though I too am working by experimentation rather than any authoritative information.  'Single' comes from use of the '@' implicit intersection operator.  If you attempt to enter 

"= SINGLE(ColumnA)"

Excel will replace it by

"= @ColumnA"

[#11]

@PeterBartholomew1 

SINGLE() was introduced at very beginning of DA Excel, bit later Microsoft replaced it with the implicit intersection operator "@"

[#12]

I remember it. What I didn't know was whether the SINGE() form still worked. The automatic conversion was something of a surprise.

[#13]

Hello All,
Sorry for the late update, but I got to the bottom of what was going on with the Named Ranges with names starting with _xlfn. To solve the problem, I filtered so that the code will skip these ranges. But it only took a coworker to choose one of these functions, to make these named appear. He did not even use the function, merely was checking to see what was out there. I was at his machine the other day, and in the list of recently used functions, there it was!!! Wow. Thanks for all your input I used some of them and learned from the others. Great Job All!

[#14]

 You could run this code, which will make all names visible, after which you can delete them from the name manager. See https://www.mrexcel.com/board/threads/cant-swap-reference-style-between-r1c1-and-a1-due-to-_xlpm.119...

VBA Code:

Sub CleanNames()
   Dim Nm As Name
   For Each Nm In ActiveWorkbook.Names
      Nm.Visible = True
   Next Nm
End Sub

@GeorgieAnne

[#15]

@LittleHelper that is a great tip BUT be careful you are 'breaking' a functionality excel has built in to prevent problems when opening in older sheets (and maybe other cases?). Also I was doing some testing and it appears that once you delete it, it doesn't seem to come back. So I had parameter list:
abc, abcd, abcde, abcdef, abcdefg
I started with a LET statement with abcdefg and then editted it and used the backspace key delete the letters one at a time creating all those intermediate parameters until I ended with abc.
so I used this trick and deleted all except abc
then I edited the LET statement again and made it abcdef and neither that final parameter nor the intermediates were re-created.
so although you can 'CLEAN' up those variables I would be very careful as there is still more going on (or not going on) that we don't know.