Excel LAMBDA function, converting VBA UDFs to Lambda functions

Content

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 Office 365 and (at the moment of writing) you need to be on the so-called Office Insider program.

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:

    ireg1(1) = 0
    ireg1(2) = 0
    ireg1(3) = 0
    ireg1(4) = 0
    ireg1(5) = 0
    ireg1(6) = 0
'   more code here...
    ireg1(31) = 29
    ireg1(32) = 30
    ireg1(33) = 31
    ireg1(34) = 32
    

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:

Private Function gammareg1(tau, pi)
'
' Fundamental equation for region 1
'
    Call InitFieldsreg1
    gammareg1 = 0
    For i = 1 To 34
        gammareg1 = gammareg1 + nreg1(i) * (7.1 - pi) ^ ireg1(i) * (tau - 1.222) ^ jreg1(i)
    Next i
    '
End Function
'

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:

Public Function cvSatVapPW(pressure)
'
' specific isochoric heat capacity of saturated steam as a function of pressure
' cvSatVapPW  in kJ/(kg K)
' pressure in bar
'
' cvSatVapPW  = -1: pressure outside range
'
    If pressure >= pSatW(273.15) And pressure <= pSatW(623.15) Then
        '  region 2
        temperature = tSatW(pressure)
        cvSatVapPW  = cvreg2(temperature, pressure)
    ElseIf pressure > pSatW(623.15) And pressure <= pc_water Then
        '  region 3
        temperature = tSatW(pressure)
        pressure = pressure - 0.00001
        density = densreg3(temperature, pressure)
        cvSatVapPW  = cvreg3(temperature, density)
    Else
        '  outside range
        cvSatVapPW  = -1#
    End If
    '
End Function
'

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:

Private Function densreg3(temperature, pressure)
'
' Determine density in region 3 iteratively using Newton method
' densreg3 in kg/m^3
' temperature in K
' pressure in bar
'
' densreg3 = -2: not converged
'
    If temperature < Tc_water And pressure < pSatWVBA(temperature) Then
        densold = 100#
    Else
        densold = 600#
    End If
    tau = Tc_water / temperature
    '
    For J = 1 To 1000
        delta = densold / dc_water
        derivprho = rgas_water * temperature / dc_water * (2 * densold * fideltareg3VBA(tau, delta) + densold ^ 2 / dc_water * fideltadeltareg3VBA(tau, delta))
        densnew = densold + (pressure * 100000# - rgas_water * temperature * densold ^ 2 / dc_water * fideltareg3VBA(tau, delta)) / derivprho
        diffdens = Abs(densnew - densold)
        If diffdens < 0.000005 Then
            densreg3 = densnew
            Exit Function
        End If
        densold = densnew
    Next J
    densreg3 = -2#
    '
End Function

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?


Comments

All comments about this page:


Comment by: SebW (10-12-2021 11:04:00) deeplink to this comment

Thanks for the great explanations. I'm so eager for this to finally come to the Current Channel and onto my work PC.


Comment by: Jan Karel Pieterse (10-12-2021 11:36:00) deeplink to this comment

Hi Seb,

Yes we've been waiting for this for too long, haven't we.


Comment by: Peter Bartholomew (1-2-2022 21:49:00) deeplink to this comment

This function takes line items running left to right along an evenly spaced timeline and adds forecast periods at the end. The intent is to further demonstrate Excel formulas as a programming language. The function is
= RollingForecastλ(Actuals, forecastCount)
and is defined by
= LAMBDA(actuals,n′,
     LET(
         m,    ROWS(actuals),
         n,    COLUMNS(actuals),
         p,    SEQUENCE(1,n),
         Actλ, LAMBDA(ℓ,[p],INDEX(actuals,ℓ,IF(ISOMITTED(p),0,p))),
         Fcstλ, LAMBDA(ℓ,p′,FORECAST.ETS(p′,INDEX(actuals,ℓ,),p)),
         MAKEARRAY(m,n+n′,
            LAMBDA(ℓ,p′,
             IF(p′<=n, Actλ(ℓ,p′), Fcstλ(ℓ,p′))
            )
         )
     )
)


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].