I’ve written several articles and done a few presentations about the new functions in Excel, such as Dynamic Arrays, the LET function, and the latest, the LAMBDA function. As I continue to build VBA-based workbooks and add-ins, I realize how much easier these features make the projects I’m working on, and I am planning to release Microsoft 365 versions of these projects.

Someone recently asked me how to test in VBA whether a user’s computer has these features. I realize that this is something I’ll need to do, so I did a little work to develop a VBA test.

I started by putting some new formulas into a worksheet, then I checked what happened to these formulas when the workbook was opened in an older version of Excel (I used Excel 2013). Then I checked what happened when I tried to use VBA to insert such formulas into a worksheet.

## New Functions in Excel

Here is my simple test worksheet in the latest Insiders build of Excel.

I named cell B2 `TestDynamicArray`

for future reference, and entered a Dynamic Array formula in that cell.

Cell B5 is named TestLET and contains a simple LET formula.

Cell B7 is named TestLAMBDA and contains a simple LAMBDA formula.

Cells C2, C5, and C7 (formatted with blue text) use the FORMULATEXT function to show the formulas in column B.

### Dynamic Array: SEQUENCE

The SEQUENCE function develops a list of numbers using this syntax:

`=SEQUENCE(rows,columns,start,step)`

Where `rows`

and `columns`

indicates the size of the resulting array’s spill area, `start`

is the first number in the sequence, and `step`

is how much greater each number is than the previous one. Any missing parameter is assumed to be 1.

In cell B2 I entered this formula:

`=SEQUENCE(2)`

in order to get a spill range of two cells tall and one cell wide.

### LET Formula

My simple LET formula in cell B5:

`=LET(one,1,two,2,one+two)`

basically says, let the value of `one`

be 1 and the value `two`

be 2, then return the result of `one+two`

. And the result is 3, yay arithmetic.

### LAMBDA Formula

My equally simple LAMBDA formula in cell B7:

`=LAMBDA(one,two,one+two)(1,2)`

says input two variables `one`

and `two`

, then return `one+two`

. The values in parentheses after the LAMBDA closing parenthesis, 1 and 2, are used as the inputs `one`

and `two`

. The result again is 3.

### VBA Assessment of the Formulas

When I select a cell and type `?ActiveCell.Formula`

into the Immediate Window of the VBA Editor, the formula is returned. Here are the formulas of my three cells:

These look just like the formulas we entered, and which `FORMULATEXT`

showed us above. Boring, I know.

## New Functions in Old Excel

I booted up a virtual machine that has Windows 10 and Excel 2013. Here is how my modern functions look in an obsolete version of Excel:

The calculated values look okay, but that’s because Excel has preserved them, without recalculating them. Here is what VBA says the formulas are in my test cells:

Note that the new functions have a prefix of `_xlfn`

and the arguments in the LET and LAMBDA formulas have a prefix of `_xlpm`

. I think Excel saves new functions with this prefix to warn old versions of Excel that these are formulas from the future, and old Excel shows the stored cell values without spewing errors.

If you enter a formula with these prefixes in a version of Excel that recognizes the function name after the prefix, the prefixes are stripped away and the formula just works fine.

If you try to re-enter one of these prefix-laden formulas in an old version of Excel, the formulas are entered, but you get a `#NAME?`

error. You get the same error if you try to enter the modern formula in old Excel without the prefix.

## Developing the VBA Test

One way to test for the availability of these functions is to hide a worksheet like this in a workbook or add-in. We can write simple functions in VBA to test whether the formulas are recognized, simply by checking for formulas with a prefix of `_xlfn`

.

Remembering that I named the cells with these formulas TestDynamicArray, TestLET, and TestLAMBDA, the VBA code can examine the formulas. My module looks like this, with the prefix I’m looking for declared in a constant at the top, followed by three test functions.

The functions have the same structure. Find the cell, extract its formula, and return `True`

if it does not begin with the prefix.

```
Option Explicit
Const TestString As String = "=_xlfn."
Public Function TestDynamicArray() As Boolean
Dim rTest As Range
Set rTest = ThisWorkbook.Names("TestDynamicArray").RefersToRange
Dim sFmla As String
sFmla = rTest.Formula
TestDynamicArray = Left$(sFmla, Len(TestString)) <> TestString
End Function
Public Function TestLET() As Boolean
Dim rTest As Range
Set rTest = ThisWorkbook.Names("TestLET").RefersToRange
Dim sFmla As String
sFmla = rTest.Formula
TestLET = Left$(sFmla, Len(TestString)) <> TestString
End Function
Public Function TestLAMBDA() As Boolean
Dim rTest As Range
Set rTest = ThisWorkbook.Names("TestLET").RefersToRange
Dim sFmla As String
sFmla = rTest.Formula
TestLAMBDA = Left$(sFmla, Len(TestString)) <> TestString
End Function
```

You can test for each function as you need it, or you can test for all of them at once:

```
Public Function TestNewFeatures()
Dim TestResult As String
TestResult = "Testing..." & vbNewLine
TestResult = TestResult & "Dynamic Arrays: " & TestDynamicArray & vbNewLine
TestResult = TestResult & "LET Function: " & TestLET & vbNewLine
TestResult = TestResult & "Lambda Function: " & TestLAMBDA
MsgBox TestResult, vbInformation + vbOKCancel, "Testing Excel for New Features"
End Function
```

Here is the resulting message box when tested in the latest Insiders build of Excel:

Here’s the result in Excel 2013:

I wouldn’t necessarily bother my user with this message box, but it’s a good example of how to call the test functions from code.

I might instead show a message explaining that his Excel installation is woefully out-of-date and some of the fabulous features of my most excellent program will not work.

## An Alternative VBA Test

Instead of cluttering my workbook or add-in with a dummy worksheet that contained sample formulas, I can write a VBA procedure that tested for all of the new functions at once.

This VBA function opens a new workbook, inserts each of the new

Public Function TestExcelForNewFunctions(ByRef bDynamicArray As Boolean, _ ByRef bLET As Boolean, ByRef bLAMBDA As Boolean) As Boolean Application.ScreenUpdating = False '[see note 1 below] Dim ws As Worksheet Set ws = Workbooks.Add.Worksheets(1) ' test Dynamic Array With ws.Range("B2") On Error Resume Next '[see note 2] .Formula2 = "=SEQUENCE(2)" '[see note 3] bDynamicArray = .HasSpill On Error GoTo 0 If Not bDynamicArray Then '[see note 4] If .HasFormula Then bDynamicArray = .Text = "#SPILL!" End If End If If Not bDynamicArray Then GoTo ExitFunc '[see note 5] End With ' test LET With ws.Range("B5") On Error Resume Next '[see note 2] .Formula = "=LET(one,1,two,2,one+two)" On Error GoTo 0 bLET = .Text = "3" '[see note 6] If Not bLET Then GoTo ExitFunc '[see note 5] End With ' test LAMBDA With ws.Range("B7") On Error Resume Next '[see note 2] .Formula = "=LAMBDA(one,two,one+two)(1,2)" On Error GoTo 0 bLAMBDA = .Text = "3" End With TestExcelForNewFunctions = bDynamicArray And bLET And bLAMBDA ExitFunc: ws.Parent.Close False Application.ScreenUpdating = True End Function

Here are the notes referenced in the code above:

- Turn off
`ScreenUpdating`

so nobody notices the test workbook being opened and closed. - Use
`On Error Resume Next`

to avoid run-time errors while entering formulas. Important for Dynamic Arrays, but not really needed for LET or LAMBDA. `Formula2`

means enter the formula as a Dynamic Array formula; plain`Formula`

would simply enter a one-cell implicit intersection formula, like`=@SEQUENCE(2)`

.`bDynamicArray`

might be`False`

even in modern Excel, if somehow a value in a cell blocked the spill range, so we test for a spill error.- If
`bDynamicArray`

is`False`

, there’s no need to test for LET;

if`bLET`

is`False`

, there’s no need to test for LAMBDA. - The cell would display 3 if the formula worked, or most likely
`#NAME?`

if it didn’t; I decided to test for 3 in case some other error message appeared.

I would call it as follows, though as before I may decide the change the message to an alert that the user’s Excel version is not up to date.

```
Public Sub TestExcel()
Dim bTestDA As Boolean, bTestLet As Boolean, bTestLambda As Boolean
Dim TestAll As Boolean
TestAll = TestExcelForNewFunctions(bTestDA, bTestLet, bTestLambda)
Dim TestResult As String
TestResult = "Testing..." & vbNewLine
TestResult = TestResult & "Dynamic Arrays: " & bTestDA & vbNewLine
TestResult = TestResult & "LET Function: " & bTestLet & vbNewLine
TestResult = TestResult & "Lambda Function: " & bTestLambda
MsgBox TestResult, vbInformation + vbOKCancel, "Testing Excel for New Functions"
End Sub
```

The message boxes are the same as for the previous VBA test, because I simply reused that bit of the code.

Sandeep Kothari says

Hi Jon, this is amazing! You are a great coder.

xlfn stands for excel function ; what would xlpm stand for?

Mark Proctor says

Good work Jon.

Given the inability to use version numbers for this, it’s great that you’ve already developed a solution.

Jon Peltier says

Sandeep –

Perhaps it means “Excel parameter.”

Jon Peltier says

Mark –

The old numbering system would not have worked for this, since new features are being rolled out monthly. We would need to track build numbers, but what is included in a given build seems to vary with the update channel of the user. Better to test for sure whether the particular feature is available.

TB says

Something interesting I’ve been playing around with regarding LAMBDAs: there are many Excel 4.0 functions available via the Name Manager. Here are a few that I’ve gotten working thus far:

=LAMBDA(formula_text,EVALUATE(formula_text))

=LAMBDA(directory_text,FILES(directory_text))

=LAMBDA(type_num,GET.WORKBOOK(type_num))

=LAMBDA(name_text,info_type,GET.NAME(name_text, info_type))

=LAMBDA(document_text,type_num,match_text,NAMES(document_text, type_num, match_text))

It seems that if the XLM function returns values that the current version of Excel can parse, it will return the value from a LAMBDA instantiated in the Name Manager, and have to have .xlsm file extension. In playing with lambdaEVALUATE, I’ve been able to create a LET/LAMBDA editor/tester in a regular spreadsheet that I’ve found useful in debugging, but has some limitations (EVALUATE only works on strings <256 chars).

In using the lambdaNAMES(,3,"*"), I discovered the "_xlpm…" string for the first time, and that's how I got here! Wondering about the overall implications of this, and if Excel dev team will end up bricking this window to the past over.

Thoughts?

Jon Peltier says

Interesting. I changed the definition of a couple of them, because I like lists in columns:

=LAMBDA(directory_text,TRANSPOSE(FILES(directory_text)))

=LAMBDA(type_num,TRANSPOSE(GET.WORKBOOK(type_num)))