Incorporating VBA-based UDFs (User-Defined Functions) in Excel empowers users to extend the functionality of their spreadsheets beyond the standard set of available functions. By leveraging VBA (Visual Basic for Applications), a versatile programming language native to Microsoft Office applications, users can create custom functions to address unique data processing or calculation requirements that may not be covered by Excel's built-in functions. To create a UDF, users write a VBA script that defines the custom function, and then store it in a dedicated module within the Excel workbook. Once the UDF is established, it can be effortlessly integrated into spreadsheet formulas, just like any other Excel function.
However, there are some notable disadvantages of macro-based UDFs that users should be aware of. First and foremost, macro-based UDFs can pose security risks, as they have the potential to carry malicious code. Consequently, organizations may restrict the use of macros or enforce strict security settings to mitigate such risks. Second, compatibility issues may arise when sharing workbooks containing macro-based UDFs with users who have different versions of Excel. Additionally, macros can lead to performance issues in larger workbooks, as the execution of complex UDFs may consume significant system resources and slow down the application.
In March 2021, Microsoft announced the new LAMBDA worksheet function that allows users to create custom functions without any VBA, macro, or JavaScript knowledge. Unlike UDFs, which require programming knowledge in VBA, LAMBDA functions are built directly within Excel using familiar formula syntax. This makes them more approachable to users without coding experience, while also reducing the security risks associated with macro-based UDFs. Furthermore, LAMBDA functions are compatible across different versions of Excel and are less likely to encounter performance issues, as they operate within the confines of Excel's native formula environment. Although LAMBDA functions may not be as powerful or versatile as VBA-based UDFs for certain complex tasks, they present a user-friendly alternative for those seeking to extend Excel's functionality without the need for programming expertise.
Since SpreadsheetWeb supports LAMBDA, it offers a viable alternative to convert macro-enabled Excel files that contains UDFs into web applications.
In this article, we are going to show you how to convert VBA based UDFs to LAMBDA functions. If you're new to the LAMBDA function, please check out function's tutorial as well.
Simple Formulas
If you have simple, one-line functions in VBA, you can probably copy paste your operation code directly into your LAMBDA function if you set names for arguments.
Here is a user-defined function written in VBA that calculates the Darcy-Weisbach friction factor for pressure loss calculations via explicit equation by Swamee, P.K. and Jain, A.K.:
' Parameters:
' D : Inner diameter of the pipe [mm]
' aRou : Absolute roughness of pipe [mm]
' Re : Reynolds Number [-]
Function SwameeJain(D As Double, aRou As Double, Re As Double) As Double
SwameeJain = 0.25 / (Application.WorksheetFunction.Log(((aRou / D) / 3.7 + 5.74 / Re ^ 0.9), 10)) ^ 2
End Function
It's LAMDBA equivalent will be like this:
=LAMBDA(D,aRou,Re,0.25 / (LOG(((aRou / D) / 3.7 + 5.74 / Re ^ 0.9), 10)) ^ 2)
The obvious difference is the calling of the LOG function that you don't need to use "Application.WorksheetFunction" in the worksheet.
Here you can see both versions generate the same result.
Tip: You can use Excel Labs add-in to be able manage names in your workbook via a more user-friendly interface.
Excel Labs is an add-in for Excel which has been developed by Microsoft Garage team: https://www.microsoft.com/en-us/garage/profiles/excel-labs/
Optional Parameters
The LAMBDA function supports optional arguments like VBA functions. Instead of defining them with "Optional" keyword, you need to type the argument name between square brackets, e.g. [argument_name].
Here is a VBA function that allows us to select between two equations via optional Boolean argument at the end (IsHaaland).
' Parameters:
' D : Inner diameter of the pipe [mm]
' aRou : Absolute roughness of pipe [mm]
' Re : Reynolds Number [-]
Function DarcyWeisbach(D As Double, aRou As Double, Re As Double, Optional IsHaaland = True) As Double
If IsHaaland Then
' Calculates the Darcy-Weisbach friction factor for pressure loss calculations
' via explicit equation by Haaland,S.E.
DarcyWeisbach = (-1.8 * Application.WorksheetFunction.Log((((aRou / D) / 3.7) ^ 1.11 + 6.9 / Re), 10)) ^ -2
Else
'Calculates the Darcy-Weisbach friction factor for pressure loss calculations
' via explicit equation by Swamee,P.K.&Jain,A.K.
DarcyWeisbach = 0.25 / (Application.WorksheetFunction.Log(((aRou / D) / 3.7 + 5.74 / Re ^ 0.9), 10)) ^ 2
End If
End Function
If IsHaaland is True or omitted, the function calculates via Haaland equation. You can check if an argument is missing or not by using ISOMITTED in your Lambda function.
The following function contains an IF statement to check if IsHaaland is omitted or TRUE. If either is correct, the Haaland equation will be used.
=LAMBDA(D,aRou,Re,[IsHaaland],IF(OR(ISOMITTED(IsHaaland),IsHaaland),(-1.8 * LOG((((aRou / D) / 3.7) ^ 1.11 + 6.9 / Re), 10)) ^ -2,0.25 / (LOG(((aRou / D) / 3.7 + 5.74 / Re ^ 0.9), 10)) ^ 2))
Here you can see the function with and without IsHaaland argument return same results.
Loops and recursion in LAMBDA
VBA enables the programming of highly intricate functions that can incorporate iterative loops or recursions, allowing for calculations based on previous steps. While the LAMBDA function lacks dedicated loop structures, such as 'for' or 'do-while' found in VBA, it does support recursive usage. This capability offers users the chance to emulate similar functionality, albeit with some limitations compared to the full potential of VBA.If you are new to recursion concept, please see “How to create recursive functions in Excel with LAMBDA” article first.
In our example, we use a “do-while” loop to calculate an error value (Err) during each iteration, and continues if the error is greater than the given tolerance (fTol).
' Parameters:
' D : Inner diameter of the pipe [mm]
' aRou : Absolute roughness of pipe [mm]
' Re : Reynolds Number [-]
' fTol : Termination Tolerance(Iteration) [-]
' MaxIter : Max. limit (Iteration) [-]
Function ColebrookWhite(D As Double, aRou As Double, Re As Double, Optional ByVal fTol As Double = 0.01, Optional ByVal MaxIter As Double = 1000) As Double
' Initializing the Iteration
Err = 10 ' Iteration error
IterNum = 0 ' Iteration steps number
' Initial estimate
X0 = Rnd 'Random number
Do While (Err > fTol And IterNum < MaxIter)
IterNum = IterNum + 1
X1 = (2 * Application.WorksheetFunction.Log(((aRou / D) / 3.7 + 2.51 / (Re * X0 ^ 0.5)), 10)) ^ (-2)
Err = Abs(X1 - X0)
X0 = X1
Loop
ColebrookWhite = X1
End Function
While the VBA version requires five arguments, the LAMBDA should include each argument that is calculated and reused within the loop, such as MaxIter, Err, IterNum and X0 value. Additionally, the LAMBDA function employs the LET function, which facilitates the defined in-function names for easily referencing or performing repetitive calculations.
=LAMBDA(
D,Re,aRou,[fTol],[MaxIter],[Err],[IterNum],[X_0],
LET(
fTol_, IF(ISOMITTED(fTol), 0.01, fTol),
MaxIter_, IF(ISOMITTED(MaxIter), 1000, MaxIter),
Err_, IF(ISOMITTED(Err), 10, Err),
IterNum_, IF(ISOMITTED(IterNum), 1, IterNum),
X_0_, IF(ISOMITTED(X_0), RAND(), X_0),
X_1, (2 *LOG10((aRou / D) / 3.7 + 2.51 / (Re * X_0_ ^ 0.5))) ^ -2,
IF(
AND(Err_ > fTol_, IterNum_ < MaxIter_),
ColebrookWhiteλ(D, Re, aRou, fTol_, MaxIter_, ABS(X_1 - X_0_), IterNum_ + 1, X_1),
X_1)))
See the recursive usage of the function by calling itself with calculated variables: ColebrookWhiteλ(D, Re, aRou, fTol_, MaxIter_, ABS(X_1 - X_0_), IterNum_ + 1, X_1)
Limitations of the LAMBDA Function
The LAMBDA function in Excel offers a powerful and accessible way to create custom functions without the need for VBA programming. However, it is crucial to be aware of its limitations and best practices to ensure efficient and reliable function development and conversion of UDFs to LAMBDA functions.
- Precedence of LAMBDA Functions Over VBA Functions with the Same Name:
When a VBA function and LAMBDA function share the same name in Excel, the LAMBDA function takes precedence and will be evaluated in the worksheet. This means that if you have a custom VBA function and create a LAMBDA function with the same name, the LAMBDA function will be used in any formulas that reference that name.
- Debugging Limitations of LAMBDA Functions:
One of the most significant disadvantages of using LAMBDA functions is the lack of proper debugging functionality. Unlike VBA, where you have access to robust debugging tools, the LAMBDA function offers no such built-in tools. This limitation can make it challenging to troubleshoot and fix errors in complex LAMBDA functions.
- Naming Conventions for LAMBDA Functions and Parameters:
When naming LAMBDA functions and their parameters, you can follow the standard Excel syntax rules, with one exception: avoid using a period (.) in a parameter name. The period is a reserved character in Excel, and using it in a parameter name may lead to unexpected behavior or errors.
- Recursion Limitations in LAMBDA Functions:
As of the time of writing this article, LAMBDA's recursion capabilities are constrained by Excel's current operand stack limit, which is 1024. This limit is divided by the number of LAMBDA parameters plus one, effectively determining the maximum depth of recursion. This limitation is important to consider when designing complex LAMBDA functions that rely on recursion, as it may impact the function's ability to handle large datasets or perform specific calculations.
