I was recently informed that the Malwarebyte program was raising a warning for the CSpline2 download. On checking I found that the spreadsheet had a VBA module called frmPickwb which was not my code.
The file has now been updated, with the offending module deleted, and I’m told that Malwarebyte no longer raises a warning.
The message is, always check downloaded files, even from trusted sources, and please let me know if you get any malware or other warnings on files downloaded from here.
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
Final example updated 28th Feb 2021, following comment from Craig:
There are many examples of the use of the new Let function on the web (see my previous post on this topic for links). This post compares use of Let with my Eval user defined function (UDF). More details on the Eval UDF can be found at Evaluating text with units and Evaluating Text – Update.
The screenshot below shows the function “FL^3/(3*E*I)” evaluated with the Eval UDF and the Let function:
The Eval function evaluates a function entered as text on the spreadsheet (or entered as a text string within the function), and reads a list of parameters and the corresponding values from the spreadsheet:
Using the Let function, each parameter is entered directly in the function, followed by the value, which may be entered in the function, or refer to a spreadsheet cell (or range). The function to be evaluated must be entered as the last argument of the Let function:
Note that if the final argument of the Let function is a cell reference, the function just returns the text, rather than evaluating it:
A more complex example is shown below. Using the Eval function the parameters are listed in Column A, and the values in Colum I:
The Let function could be used in the same way, but it is also possible to evaluate parameters within the Let function. In the first Let example below (row 41) I used a nested Let function, in this case evaluating the Beta parameter within the function, but that isn’t necessary. Any parameter can be evaluated within the Let function, using the previously defined parameters. This is shown in Row 42, where Beta is defined with … Beta, (K*G/(Cw*E))^0.5. In the third Let example below (Row 43) all of the parameters are defined within the Let function, giving a complete (but not particularly readable) function, requiring no external evaluation of the intermediate parameters on the spreadsheet:
We last heard from Ebony Buckle here nearly 8 years ago, but I noticed her name in an article on the back page of last week’s New Scientist, which noted that her latest work was inspired by the World’s loneliest whale, so here is the song:
Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!
Introducing LET
LET allows you to associate a calculation or value in your formula with a name. It’s names except on a formula level.
In December 2020, we announced LAMBDA, which allows users to define new functions written in Excel’s own formula language, directly addressing our second challenge. These newly defined functions can call other LAMBDA-defined functions, to arbitrary depth, even recursively. With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation in the Excel formula language. LAMBDA is available to members of the Insiders: Beta program. The initial release has some implementation restrictions that we expect to lift in the future. We discussed LAMBDA and some of our research on spreadsheets in a sponsored video presented at POPL 2021.
I will follow up in later posts with my own comments, including comparison with my VBA Eval function, and linking to similar, and better functionality with Python and pyxll.
Newton Excel Bach, not (just) an Excel Blog 