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