How to create Custom Excel Functions

Microsoft Excel Pack comes with the many pre defined functions which does 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”.

Create Custom Excel Functions

Now, to open the Visual Basic Editor, tap on the Developer tab and click on “Visual Basic” icon to launch the Visual Basic Editor.

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”.

Insert Module custom excel function

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

Editor to write VBA 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.

Sample Custom Excel Function

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.

Save with xslm

That’s it!

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

access the excel custom function

You can see the example below:

Create Custom Excel Function

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 then 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.

Posted by on , in Category Office with Tags
Sridhar is a Software Engineer who loves to stay updated with developments in the world of technology. He is fond of writing everything related to Internet, Computers and Mobile and Desktop Operating Systems.

Leave a Reply

Your email address will not be published. Required fields are marked *

7 + 8 =