Microsoft Excel Pack comes with many predefined functions that do the best for us.We don’t need any more functions other than the built-in ones in most cases. But what if you want some functionality that was not provided by any predefined Excel function?
Microsoft Excel allows us to create custom Excel functions or custom functions using VBA . We can create custom Excel functions with the functionality we want, and they can be accessed in an Excel sheet as regular Excel functions using “=” and the function name. I will walk you through the steps to create custom Excel functions using VBA.
Create custom Excel functions
Since we will be creating a custom Excel function using VBA, we need to enable the Developer tab first. It is not enabled by default and we can enable it. Open an Excel sheet and click the Excel button, and then click Excel Options. Then check the box next to Show Developer Tab on Ribbon .
Now, to open the Visual Basic Editor, click on the Developer tab and click on the Visual Basic icon to launch the Visual Basic Editor.
You can even use the Alt + F11 keyboard shortcut to launch the Visual Basic Editor. If you use this keyboard shortcut, there is no need to enable the Developer tab.
You are now ready to create your custom Excel function. Right-click Microsoft Excel Objects, click Insert, and then click Module.
It opens a simple window where you can write your code.
Before writing the code, you need to understand the example syntax that you need to follow to create a custom Excel function and learn how to do it.
MyFunction (arguments) returns the type myFunction = some_calculation End Function
There is no “Return” statement here, as we have with conventional programming languages.
Paste your code in the window that opens. For example, I’ll create a “FeesCalculate” function that calculates “8%” of the value supplied to the function. I used the return type as “Double” as the value can also be in decimal numbers. You can see that my code follows VBA syntax.
Now it’s time to save your Excel workbook. Save it with the extension “.xslm” to use the Excel worksheet with the macro. If you don’t save it with this extension, it will throw an error.
This is it!
Now you can use a custom function in an Excel worksheet like a normal Excel function using “=”. When you start typing “=” in a cell, it shows you the created function along with another built-in function.
You can see an example below:
Excel custom functions cannot change the Microsoft Excel environment and therefore have limitations.
Limitations of Excel Custom Functions
Excel custom functions cannot do the following:
- Insert, format, or delete cells in your spreadsheet.
- Then the value of the other cell changes.
- Adding names to the workbook.
- Rename, delete, move, or add sheets to the workbook.
There are many more such restrictions, and some of them are mentioned.
These are the simple steps you need to follow to create custom Excel functions.