VBA Test for New Excel Functions

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:

1. Turn off ScreenUpdating so nobody notices the test workbook being opened and closed.
2. 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.
3. Formula2 means enter the formula as a Dynamic Array formula; plain Formula would simply enter a one-cell implicit intersection formula, like =@SEQUENCE(2).
4. 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.
5. If bDynamicArray is False, there’s no need to test for LET;
   if bLET is False, there’s no need to test for LAMBDA.
6. 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.

More About Dynamic Arrays, LET, and LAMBDA

• ISPRIME Lambda Function
• Lambda Moving Average Formulas
• Improved Excel Lambda Moving Average
• Excel Lambda Moving Average
• LAMBDA Function to Build Three-Tier Year-Quarter-Month Category Axis Labels
• Dynamic Array Histogram
• Calculate Nice Axis Scales with LET and LAMBDA
• Dynamic Arrays, XLOOKUP, LET – New Excel Features