Creating (Custom) User Defined Functions (UDFs)

This tutorial will explain how to create User Defined Functions in VBA.

VBA is made up of Sub Procedures and Function Procedures. Function procedures return a value and can be called by Sub Procedures, or can be used in the Excel sheet, where the value that the function produces is returned to the Excel sheet.  Excel of course has a range of built-in functions – like the Sum Function or If Function.  The function procedures that you write in VBA are used in a similar fashion to the built-in function and are known as User Defined Functions (UDFs).

Why create a UDF?

Required Function Missing

One of the main reasons for wanting to create a UDF in Excel is that there is not an existing built-in function that will do the task for you.  Writing your own function in VBA is usually the most efficient way to solve the problem.  The function below will convert a value from kilograms to pounds where a variable parameter (dblKilo) is being used to obtain the value of the kilograms in order to do the calculation.

Replace a Sub-Routine (Macro)

You could write a sub procedure (macro) to solve the task for you – but sub procedures do not return a value and they are not dynamic – in other words, if the values in your worksheet change, you would need to re-run the macro in order for the calculations in the macro to update your data.  The sub-procedure below would also convert our kilos to pounds, but every time the data changed in A1, you would need to re-run the macro to update the result.

Replace a Formula

You may have a really complicated formula in Excel which you need to use repetitively – putting the formula into VBA code makes it easier to read and understand – as well as perhaps removing room for user error when typing the formula.

Creating UDFs

To create a UDF, firstly add a module to either your Excel workbook, or, if you have a Personal Macro workbook, you can either use an existing module in there, or add in a new one.  To do this, you need to be in the Visual Basic Editor (VBE).   To get to the VBE, press ALT + F11 or click on the Visual Basic Option in the Developer tab of your ribbon.

TIP: If you do not have the Developer tab enabled in your ribbon, go to File, Options and click on Customize Ribbon.  Make sure the Developer check box is ticked and click OK.

 

To insert a new module, select the VBA Project you wish to insert the module into (either the VBA Project for the current book  you are working in, or the Personal Macro Workbook), click on the Insert Menu, and click Module

Once you have created your module, you can begin to create your UDF.

All UDFs start with Function and then the name of the UDF.   Functions can be private or public but usually you would want a UDF to be public  in order that they appear in the Insert Function dialog box in Excel (see using a function from within an Excel sheet further down in this article). If you do not put the Private keyword in front of the function, then the function is automatically public.

Function TestFunction1(intA As Integer) As Integer
   TestFunction1= intA * 7
End Function

The function above has a single argument (intA).  You can create a function with multiple arguments

Function TestFunction2(intA As Integer, intB As Integer, intC As Integer) As Integer
   TestFunction2 = (intA * intB) + intC
End Function

You can also create a function with optional arguments.  If the argument is omitted, you  can set a default value for the argument in the function.

Function TestFunction3(intA As Integer, intB As Integer, Optional intC As Integer=10) As Integer
TestFunction3 = (intA * intB) + intC
End Function

Using a Function from within an Excel Sheet

The functions that you have created will by default appear in your function list in the User Defined section of the function list.

Click on the fx to show the Insert Function dialog box.

Select User Defined from the Category List

Select the function you require from the available User Defined Functions.

Alternatively, when you start writing your function in Excel, the function should appear in the drop down list of functions.

 

Saving the Functions with your Excel File

As Functions are written in VBA code, it stands to reason that the code needs to be available to the Workbook in order to be available to be used within the Excel sheet.   You can either save your functions in the Workbook that you are using them in, or you can save them in your Personal Macro workbook. Your Personal Macro workbook is a hidden file that is available whenever Excel is open and therefore available for any Workbook in Excel to use.  It is normally created when you record a macro and select the option to store the macro in the Personal Macro Workbook.

If you wish to keep your functions saved within the Workbook you are working on, you will need to make sure that when you save the workbook, it is saved as a ‘Macro Enabled Workbook‘ or an xlsm file.