How to create Custom Excel Functions using VBA

Microsoft Excel allows us to create Custom Excel Functions or User Defined Functions using VBA. We can create Custom Excel Functions with the functionality we want and they can be accessed in the Excel Sheet as regular Excel Functions using “=” followed by the function name. This will take you through the steps of creating custom Excel Functions using VBA.

How to create Custom Excel Functions using VBA

To create custom Excel functions using VBA, follow these steps:

Open Excel and go to the Developer tab.
Click on the Visual Basic menu.
Select Microsoft Excel Objects > Insert > Module.
Write your code.
Click the Save button.
Enter a name with a .xslm extension.

To learn more about these steps, continue reading.

To get started, you need to enable the Developer tab first. However, if you have already enabled it earlier, you can skip this step.

For that, go to Files > Options > Customize Ribbon.

Choose the Main Tabs option and tick the Developer checkbox.

Finally, click the OK button.

Now, switch to the Developer tab and click on the Visual Basic menu.

You can even use the keyboard shortcut “Alt + F11” to launch the Visual Basic Editor. If you use this keyboard shortcut, then there is no need to enable the Developer tab.

Next, right-click on the Microsoft Excel Objects and select Insert > Module.

It opens the plain window which is the place to write code.

Before, writing the code, you need to understand the sample syntax which needs to be followed to create a Custom Excel Function and here how it is,

Function myFunction (arguments) return type

myFunction = some_calculation

End Function

There is no ‘Return’ statement as we have with normal programming languages.

Insert your code in the plain window that just opened. For example, I will create a function “FeesCalculate” which calculates the ‘8%’ of the value provided to the function. I used the return type as “Double” as the value might be in decimals also. You can see that, my code follows the syntax of VBA.

Now, it is time to save the Excel workbook. Click the Save button and save it with the extension of ‘.xslm’ to use the Excel sheet with Macro. If you do not save it with this extension, it throws an error.

Now, you can use the User Defined Function in the Excel sheet as a normal Excel function using “=”. When you start typing “=” in the cell, it shows you the created function along with other built-in functions.

Excel Custom Functions cannot change the environment of Microsoft Excel and thus they have limitations.

Limitations of Custom Excel Functions

Custom Excel Functions cannot do the following,

Insert, format, or delete cells on the spreadsheet.
Changing the value of another cell.
Adding names to the workbook.
Rename, delete, move, or add sheets to the workbook.

There are many more such limitations and mentioned are some of them.

Read: How to fix VBA error 400 in Excel

How do I create a custom function in Excel VBA?

To create a custom function in Excel VBA, you need to enable the Developer tab and go to the Visual Basic panel. Next, right-click on Microsoft Excel Objects > Insert > Module and enter the code. Then, save it with the .xslm extension.

How do you put an Excel function in VBA?

To put an Excel function in VBA, you need to create a module. For that, open the Visual Basic panel and find the Microsoft Excel Objects. Next, right-click on it and select Insert > Module. Then, write down your code with the function included in it. Finally, save the code or module with the .xslm extension.

Read: Fix Class not registered VBA Run-time error.