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 ...
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.
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"
SINGLE() was introduced at very beginning of DA Excel, bit later Microsoft replaced it with the implicit intersection operator "@"
- I remember it. What I didn't know was whether the SINGE() form still worked. The automatic conversion was something of a surprise.
