Microsoft Excel comes with many pre-defined functions which do the maximum job for us. We never need any more functions other than those built-in functions in most of the cases. But, what if you are in need of some functionality which was not being provided by any pre-defined Excel function?
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. I will take you through the steps of creating custom Excel Functions using VBA.
Create Custom Excel Functions
Since we will be creating the Custom Excel Function using VBA, we need to enable the “Developer” tab first. By default, it is not enabled and we can enable it. Open the Excel Sheet and click on the Excel button and then click on “Excel Options”. Then check the box, beside “Show Developer tab in the Ribbon”.
Now, to open the Visual Basic Editor, tap on the Developer tab and click on “Visual Basic” icon to launch the Visual Basic Editor.
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 enabling the Developer tab also.
Now, everything is set to create the Custom Excel Function. Right click on the “Microsoft Excel Objects”, click on “Insert” and then click on “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 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 which 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 the time to save the Excel workbook. Save it with the extension of ‘.xslm’ to use excel sheet with Macro. If you do not save it with this extension, it throws an error.
That’s it!
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 function.
You can see the example below:
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.
These are the simple steps to be followed to create Custom Excel Functions.
