How to Create a Custom Function in Excel

Excel functions are actually shortcuts to more complex calculations. Like a macro is to a series of steps, a function is to a longer equation. While Excel is packed full of functions, you may find that you have proprietary calculation that you use regularly that would be useful as a function shortcut.

Advanced Excel users who are comfortable with creating and editing macros will find creating custom functions a snap. Even those who aren’t that familiar with VBA code can follow the steps below and create useful shortcuts to their calculations. This article will show how to create a very simple custom function (area of a triangle) to demonstrate the steps. You will have to figure out the math on your own!

Save as a Macro-Enabled Workbook

Because custom functions use VBA, your file must be saved as an .xlsm Macro-enabled workbook:

1.     Click File > Save As

2.     Select Excel Macro-Enabled Workbook (*.xlsm) from the dropdown menu below the document title

3.     Click Save

Activate the Developer Tab

If you don’t see the developer tab in your ribbon, you will need to reveal it before you continue:

1.     Click the File tab

2.     Click the Options button

3.     In the Customize Ribbon panel, make sure that the Developer tab has a checkmark beside it in the Customize the Ribbon pane.

4. Click OK

Determine Your Custom Function Variables

A function creates a calculation based on variables input at the time the function is used. To create your custom function, you will first need to identify the arguments needed in your function.

We are going to create a function to quickly calculate the area of a triangle. Our arguments will be the base and the height: Area = Base times Height divided by 2

Open Visual Basic Editor

Hit Alt + F11 or click the Visual Basic button the Developer tab.

Choose Insert > Module in the VBA editor:

Type Code

You will need to be familiar with VBA code to complete this step in your own work. The basic syntax of User Defined Function is:

Function myfunctionname (arguments) return type

Myfunctionname = calculation

End Function

Adding “Public” to Function tells Excel to list your User Defined Function in the Insert Function dialog box.  

Here is simple sample code for our Triangle Area Function.

Public Function TRIAREA(number1, number2)

TRIAREA = (number1 * number2) / 2

End Function

Hint! If your variables are very specific – such as in our example, number1 will always be the Base of our triangle, and number2 will always be the Height – you can give them specific names to help you when you are completing the function in your workbook:

When you are finished writing the code, click the save button or hit Ctrl+S to save your custom function to the workbook. Note that you will only be able to use it in the workbook in which it was saved, it is not a global function.

Close the VBA editor.

Use Your User Defined Function

Back in your Excel workbook, place your cursor in the place where you wish to insert the custom function:

1.     Click Insert Function on the Formulas tab.

2.     In the Insert Function dialog box, select User Defined from the “select a category” dropdown list. You should see the name you gave your function. In this example we see TRIAREA.

3.     Select the function, then click OK.

4.     Fill out the Function Arguments dialog box and click OK.