Create Custom Worksheet Formulas in Excel with VBA or LAMBDA
Question details
The user wants to create reusable custom formula logic that can be used in Excel worksheets.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a formula-like function for calculations that are not covered by Excel's built-in worksheet functions.
- Observed behavior
- Excel does not let users add new native worksheet functions directly, but custom logic can be created with LAMBDA, VBA UDFs, or add-ins.
Check which Excel version you use before choosing a method: named LAMBDA functions require a supported Microsoft 365 or newer Excel version. To use VBA, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and ensure macros are permitted by your organization.
Create a reusable custom formula with Excel LAMBDA
Use this method when you have a supported Excel version and want to reuse formula logic without writing VBA code.
A named LAMBDA stores a formula under a custom name, so you can call it in cells like a built-in Excel function. The LAMBDA is saved in the workbook unless you store it in a reusable template or add-in.
In Excel, go to the Formulas tab and select Name Manager, then click New.
Enter a descriptive name, such as AddTax, in the Name box. In the Refers to box, enter a formula such as =LAMBDA(value,value*1.1).
Click OK, then close Name Manager. The function name is now available in the current workbook.
Select a cell and enter =AddTax(A1), replacing A1 with the value or cell reference you want to calculate.
Create an Excel user-defined function with VBA
Use a VBA user-defined function (UDF) when the custom calculation needs VBA logic or must behave like a worksheet function in a macro-enabled workbook.
Use WPS Spreadsheet for Everyday Excel Formula Work
WPS Office is a free, lightweight office suite with a familiar spreadsheet interface for working with formulas, tables, and Microsoft Excel files. You can move existing workbooks to WPS Spreadsheet seamlessly for standard calculation and worksheet tasks while retaining common Excel file formats.
Frequently Asked Questions
Can I add a completely new built-in function to Excel?
No. Excel does not allow users to permanently add a new native function to its built-in function library. You can instead create a named LAMBDA, a VBA user-defined function, or an Excel add-in.
Where do I create a LAMBDA function in Excel?
Go to Formulas > Name Manager > New. Enter a function name and define the logic in the Refers to box with the LAMBDA syntax, such as =LAMBDA(x,x*2).
Why does my VBA custom function return #NAME? in Excel?
Confirm that the function is in a standard module under Insert > Module, the workbook is saved as an .xlsm file, macros are enabled, and the formula uses the exact VBA function name.
Can a VBA UDF change another cell's value?
No. A VBA UDF called from a worksheet cell should only return a result. To edit cells, apply formatting, or perform other actions, run a VBA Sub macro from Developer > Macros or a button instead.
