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?