Excel LAMBDA function, converting VBA UDFs to Lambda functions

Content

• Introduction
• The example file
• Arrays of constants
• For...Next loop
• If...Then
• Recursion
• More to follow

Introduction

In March 2021, Microsoft announced the new Excel LAMBDA worksheet function. This new function enables us to define custom functions which may replace UDFs currently written in VBA.

If you're new to the LAMBDA function, best to first read my article Excel LAMBDA function, the basics.

This page shows how to convert various well-known VBA structures into Excel functions.

Please note that for these functions to work you need a Microsoft 365 subscription.

The example file

I started my UDF conversion to Lambda attempts with a file full of UDFs to get the thermodynamic properties of water and steam: Water97-v13.xla. The file was originally developed by a user called bspang as shown on this page which decribes all details about the file.

I have tried reaching out to the authors but have not received any response so far. Authors: Please get in touch if you happen to read this!

You can also use the Microsoft Advanced Formula Environment to import these water97 lambdas from a github gist: https://gist.github.com/jkpieterse/9f2f1d30f188ca20e3311e83b5a2a8a5

Download the Water97 file which contains both the original VBA UDFs and the LAMBDA's used for this article.

Arrays of constants

The UDFs in the Water97 code use a series of constants stored in arrays. They look like this:

Here is one way of mimicking a constant array in a LAMBDA:

=LAMBDA(x,INDEX({0,0,0,0,0,0,0,0,1,1,1,1,1,1,2,2,2,2,2,3,3,3,4,4,4,5,8,8,21,23,29,30,31,32},x))

I've named this Lambda "ireg1". If you need the first value from the array, you simply use ireg1(1), just as you would in VBA!

Of course there's a limitation to this, as the formula in a range name cannot contain more than approximately 250 characters (hopefully Microsoft will increase this limit soon).

For...Next loop

Let's take one of the helper functions in the Water97 files as an example:

Nothing special to it, it contains a For..Next loop in its simplest form. It has a fixed start (1) and end value (34) for the counter.

To get a series of 1 to 34 with a step-size of 1, I used the SEQUENCE function, like so:

SEQUENCE(34, 1, 1, 1)

SEQUENCE thus gives us an array of numbers from 1 to 34. In order to construct a working LAMBDA function from this, the internals of the For...Next structure have to be analysed. In this partcular case the code simply does a SUM over the 34 items the loop runs over.

To tie this together, we need to construct a LET function in which we assign the numbers from the SEQUENCE function to a variable (i) and subsequently let the LET function calculate a SUM using i and the arguments tau and pi:

=LAMBDA(
         tau,
         pi,
         LET(
             i,
             SEQUENCE(34, 1, 1, 1),
             SUM(nreg1(i) * (7.1 - pi) ^ ireg1(i) * (tau - 1.222) ^ jreg1(i))
         )
)

See, that isn't even that complicated!

If...Then

Lets have a look at another construction, the famous If...Then. Here is an example routine from Water97:

As you can see it contains an If statement including the ElseIf and Else clauses. Die-hard formula builders immediately recognize which function we need: the IF function, maybe even a nested one:

=LAMBDA(
        press,
        LET(
            temp,
            tSatW(press),
            IF(
               AND(press >= PSatW(273.15), press <= PSatW(623.15)),
               cvreg2(temp,press),
               IF(
                  AND(press > tSatW(623.15), press <= pc_water),
                  cvreg3(temp, densreg3(temp, press - 0.00001)),
                  -1
               )
            )
        )
)

As you can see I've combined some of the calculations in the VBA routine into one, like for example the calculation of the temperature from the pressure using the TSatW function. As you can see, you can call other Lambda functions in a Lambda.

Recursion

One of the more difficult functions to translate into VBA proved to be this one:

Although it may look like a straight-forward enough function (an If..Then statement and a For loop), if you look a bit more closely the For loop has an "escape". At each pass of the loop, the function calculates how much it deviates from the previous solution (diffdens). If it that difference is beneath a set criterium, the loop is terminated. We're actually solving an equation using the method of successive substitution. As it happens, this method is very easily implemented using a recursion method. If you don't know what recursion is, think of it as if you're creating an Excel model with a circular reference.

I decided to create a dedicated Lambda for that purpose, which is called from the "main" densreg3 lambda

densreg3 as a Lambda

The densreg3 function reduces to this if we fork out the recursive bit to another function:

=LAMBDA(
       temp,press,
       LET(
          tau,tc_water/temp,
          SolveDens3(IF(AND(temp<tc_water,press<PSatW(temp)),100,600),temp,press)
       )
)

The SolveDens3 function

This is the recursing SolveDens3 LAMBDA:

=LAMBDA(
       densin,temp,press,
       LET(
          tau,tc_water/temp,
          delta,densin/dc_water,
          fidel3,fideltareg3(tau,delta),
          ddens,(press*100000-rgas_water*temp*densin^2/dc_water*fidel3)/(rgas_water*temp/dc_water*(2*densin*fidel3+densin^2/dc_water*fideltadeltareg3(tau,delta))),
          IF(
            ABS(ddens)>0.000005,
            SolveDens3(densin+ddens,temp,press),
            densin+ddens
          )
     )
)

the LET function is key. The one-but last argument set is the calculation of the diffdens variable from the VBA routine (which I relabelled to ddens here):

=LAMBDA(
       densin,temp,press,
       LET(
          tau,tc_water/temp,
          delta,densin/dc_water,
          fidel3,fideltareg3(tau,delta),

          ddens,(press*100000-rgas_water*temp*densin^2/dc_water*fidel3)/(rgas_water*temp/dc_water*(2*densin*fidel3+densin^2/dc_water*fideltadeltareg3(tau,delta))),

          IF(
            ABS(ddens)>0.000005,
            SolveDens3(densin+ddens,temp,press),
            densin+ddens
          )
     )
)

Subsequently, the IF function determines whether or not we want the SolveDens3 function to call itself once more (the TRUE part of the IF function), or to return the last calculated value (the FALSE part):

=LAMBDA(
       densin,temp,press,
       LET(
          tau,tc_water/temp,
          delta,densin/dc_water,
          fidel3,fideltareg3(tau,delta),
          ddens,(press*100000-rgas_water*temp*densin^2/dc_water*fidel3)/(rgas_water*temp/dc_water*(2*densin*fidel3+densin^2/dc_water*fideltadeltareg3(tau,delta))),

          IF(
            ABS(ddens)>0.000005,
            SolveDens3(densin+ddens,temp,press),
            densin+ddens
          )
     )
)

Limitations of recursion

There are limitations to recursion. Internally, Excel keeps a call stack in formulas. This call stack has a limited size, which hopefully gets increased before LAMBDA is released to the current channels of Office.

Lets take this example Lambda:

=LAMBDA(
       x,
       IF(
         x<Sheet1!$C$1,
         RecurseMe(x+1),
         x+1
       )
)

By making the criteria value that breaks the chain point to a cell (C2) we can easily test how many times the function can call itself before the call stack overflows. At the moment of writing (2021-6-14), the limit was 512, today (2022-2-11) it is 2048.

More to follow

We've treated some constructions so far, as soon as I have some more time I'll add more. And if you've managed to convert a UDF to a Lambda yourself, why not add your example in the comments below?