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:
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:
in order to get a spill range of two cells tall and one cell wide.
My simple LET formula in cell B5:
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.
My equally simple LAMBDA formula in cell B7:
says input two variables
two, then return
one+two. The values in parentheses after the LAMBDA closing parenthesis, 1 and 2, are used as the inputs
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
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
ScreenUpdatingso nobody notices the test workbook being opened and closed.
On Error Resume Nextto avoid run-time errors while entering formulas. Important for Dynamic Arrays, but not really needed for LET or LAMBDA.
Formula2means enter the formula as a Dynamic Array formula; plain
Formulawould simply enter a one-cell implicit intersection formula, like
Falseeven in modern Excel, if somehow a value in a cell blocked the spill range, so we test for a spill error.
False, there’s no need to test for LET;
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.