# 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 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(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:

'

' 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:

'

' 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:

'

' 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].