Forum Discussion
Named Ranges with _xlfn. prefixes
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.
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 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.
- 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.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"
