Excel-custom Formula

Excel VBA allows you to customize formulas. After the custom formulas are compiled, they are used in the same way as the built-in formulas in Excel.

1. Enable the developer function group

By default, the developer tab is not displayed in Excel. To use VBA, you must first enable the developer function group by selecting File> Options> customize ribbon, then, select the developer function group in the dialog box:

2. Open the VBA editor.

3. Write custom Formulas

In the VBA Editor, Choose Insert> module,User-Defined formulas must be placed in the module rather than in the existing sheet and thisworkbook modules (where VBA Macros/scripts are written ).

Add a UDF to the added module (default name: module1:

 
'Linear interpolation function
 
 
FunctionInterpolation (x1 as double, Y1 as double, X2 as double, Y2 as double, X as double) as double
 
Interpolation = Y1 + (Y2-Y1) * (X-X1)/(x2-X1)
 
 
End Function
 
 
 
 
This is a simple linear interpolation function with five parameters.
 
4. Use custom formulas in Excel
 
After the formula is edited and saved, you can use the custom formula in Excel. The usage method is the same as the built-in formula.
 
 
You can enter the formula directly in the formula column:
 
 
 
 
You can also select fomulas> insert function to open the insert function dialog box, and then select the User-Defined type to find the custom function: