Can we create a user defined function in excel?


1 Answer(s)


-- If you have a complex calculation that you reuse frequently in Excel, you don't have to repeatedly enter a long, complex worksheet formula. Instead, you can create your own worksheet function to perform the calculation.

-- To create your own custom functions, you work in Microsoft Visual Basic for Applications (VBA). VBA is a programming language that's built into Excel.

1) Open the Visual Basic Editor which is built into Microsoft Excel by going to Tools -> Macro -> Visual Basic Editor (or pressing Alt+F11).

2) Add a new Module to your workbook by clicking in the button shown.

3) Create the "header" or "prototype" of your function. It has to have the following structure:

public function TheNameOfYourFunction (param1 As type1, param2 As type2 ) As returnType

4) Add the code of the function making sure you a) use the values provided by the parameters; b) assign the result to the name of the function; and c) close the function with "end function".

5) Go back to your workbook and use the function by starting the content of a cell with an equal sign followed by the name of your function.