The new Excel Lambda function (see here for more details and links) is currently only available to those signed up to the Beta Preview version of Excel 365, but is has been favourably reviewed by almost all of those who have tried it, being widely described as a “game changer”. I have yet to try it myself, but from the many examples given, I am struggling to see any significant advantage over the use of simple VBA user defined functions (UDFs).

ExcelJet has a detailed post on how to use the new function, including code for several examples. Extracts from two of these examples are shown below, together with VBA code for UDFs with the same functionality.

The screen-shots below show code for a Lambda function to evaluate the volume of a sphere. First by entering the function in a worksheet cell:

Then by entering the function in the Name Manager, and giving it a convenient name:

A slightly more complex example counts the number of words in any text string:

Results for the same two examples are shown below, using VBA UDFs:

Here is the VBA code for these two functions:

Const Pi As Double = 3.14159265358979

Function SphereVol(r As Double)
    SphereVol = 4 / 3 * Pi * (r) ^ 3
End Function

Function CountWords(Text As String)
    If Len(Trim(Text)) = 0 Then
        CountWords = 0
    Else
        CountWords = Len(Trim(Text)) - Len(WorksheetFunction.Substitute(Text, " ", "")) + 1
    End If
End Function

From my brief comparison, I would list the advantages of the alternatives as:

Lambda Functions:

• Can be used in on-line versions of Excel that don’t support VBA
• No knowledge of VBA required

VBA User Defined Functions:

• Useful functions can be created with minimal VBA knowledge
• A good way to learn useful VBA skills, without getting bogged down in the details of the complex Excel VBA object model
• Much easier debugging and documentation for complex functions
• Link to existing VBA libraries
• Link to compiled functions for maths intensive functions
• Save as an add-in to use across other workbooks