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 ...

Formulas and Functions

Macros and VBA

GeorgieAnne

Iron Contributor

Jul 08, 2022

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

PeterBartholomew1

Silver Contributor

Jul 09, 2022

GeorgieAnne

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.

Forum Discussion

Named Ranges with _xlfn. prefixes