Jul 08 2022 08:45 AM
Jul 08 2022 08:45 AM
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.
Jul 08 2022 01:08 PM
Jul 08 2022 02:19 PM
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.
Jul 08 2022 06:46 PM
Jul 08 2022 07:33 PM
Jul 09 2022 02:46 AM
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.
Jul 09 2022 03:31 AM - edited Jul 09 2022 03:32 AM
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).
Jul 09 2022 08:54 AM
Jul 09 2022 09:45 AM
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
Excel will replace it by
Jul 09 2022 12:57 PM
Aug 05 2022 06:56 AM
Aug 24 2022 03:47 AM
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...
Sub CleanNames() Dim Nm As Name For Each Nm In ActiveWorkbook.Names Nm.Visible = True Next Nm End Sub
Aug 24 2022 05:43 AM