Search code examples
excelvbaexcel-365

`_xlfn`-prefix issue with current worksheet functions in MS 365?

I'm using the new dynamic MS 365 worksheet functions, such as e.g. FILTER, CONCAT and TEXTJOIN (together with a spill range reference indicated by the "#" suffix) These worksheet functions work and don't show any apparent error.

I know that in MS 365 a _xlfn. prefix would be displayed in front of formulae that are not supported in the current version. c.f. _xlfn-prefix issue

Displaying the workbook's Names collection e.g. via

Function GetNamedRanges()
Dim nm As Name
For Each nm In ThisWorkbook.Names
    Debug.Print _
        Left(nm.Name & String(20, " "), 20), _
        nm.ValidWorkbookParameter, _
        nm.Visible, _
        nm.RefersTo
Next
End Function

...VBA does not only list the availabe variable names of a workbook, but also (invisible) _xlfn. prefixed names related to the used dynamic functions (including apparently a spill range _xlfn.ANCHORARRAY):

Named Range                 ValidParam   Visible      RefersTo
-----------------------------------------------------------------
_xlfn._xlws.FILTER          False        False        =#NAME?
_xlfn.ANCHORARRAY           False        False        =#NAME?
_xlfn.CONCAT                False        False        =#NAME?
_xlfn.TEXTJOIN              False        False        =#NAME?
SomeVariable                True         True         =Sheet1!$C$1

Question

Am I missing something when I apply the new dynamic functions and does this actually indicate an error? - Would appreciate any valid background information.

Side note

If not only a temporary design this could be used also to check if the current version is run as MS 365.

Solution

  • Excel uses "_xlfn." prefix with future functions. Future functions refers to those functions which were not available in the original Excel 2007 version. This prefix is attached to retain backward compatibility. Usually, all the functions are reevaluated when a workbook is opened but future functions are avoided in case they are not supported by that version of Excel.

    Ex: SEQUENCE() - This function was introduced for Excel 365 and Excel 2021. If a workbook is created from one of these versions and then opened in a lower version such as Excel 2019, the result for this function wouldn't be recalculated as the function is not supported by Excel 2019 but still the same text will be displayed in the application. However, it would not allow you to make any changes to the cells related to future function.

    Documentation for Future Functions: https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/5d1b6d44-6fc1-4ecd-8fef-0b27406cc2bf