Forum Discussion
Named Ranges with _xlfn. prefixes
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-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025 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
- 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!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.1191055/
VBA Code:Sub CleanNames() Dim Nm As Name For Each Nm In ActiveWorkbook.Names Nm.Visible = True Next Nm End Sub- 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.
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).
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.
- 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 AnneI 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 SubWhat 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!ErrorRangeNote: 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.
Nobody else on older version opened your file?
- 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
