Forum Discussion
GeorgieAnne
Jul 08, 2022Iron Contributor
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 ...
PeterBartholomew1
Jul 09, 2022Silver Contributor
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.
mtarler
Jul 09, 2022Silver Contributor
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.
The one Name that was mentioned in the OP that stands out is "Single" as that isn't a function I know.
- PeterBartholomew1Jul 09, 2022Silver Contributor
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"
- SergeiBaklanJul 09, 2022MVP
SINGLE() was introduced at very beginning of DA Excel, bit later Microsoft replaced it with the implicit intersection operator "@"
- PeterBartholomew1Jul 09, 2022Silver ContributorI remember it. What I didn't know was whether the SINGE() form still worked. The automatic conversion was something of a surprise.