Creating an Excel Custom Function using VBA

Excel includes many different functions that help you complete calculations, but have you ever wished for a function that that doesn’t exist in Excel?  If you have, this tutorial will explain how to create a function DIY style.

You create custom functions in the Visual Basic Editor (VBE) which you can get to by clicking Tools > Macro > Visual Basic Editor or by using the shortcut key ALT F11.  If you are using Excel 2007 click on the Developer ribbon and then click on the Visual Basic Editor button.

Once in the VBE environment you will need to create a module to hold your function.  Click Insert > Module

A function is defined with a name (for the function) and if necessary between 1 and 60 arguments.  For example the Excel worksheet function VLookup has 4 arguments.

A function with no arguments

Several VBA functions such as rand() have no arguments.  In the same way you can create custom functions that have no arguments.   The following function will display the path and filename of the active workbook. 

Function File()
File = ActiveWorkbook.FullName
End Function

Notice the function starts and ends with ‘Function’ rather than sub.

Enter =File() into a worksheet to see the result.

Click on fx and open the User Defined category to see your function listed here

The next function displays the username (as set in Tools | Options | General)

Function User()
User = Application.username
End Function

A Custom Functions with Arguments

The following function simply calculates a value plus VAT. In an empty worksheet create a column of prices.  Then switch to the VBE environment and in a module create the following custom function. Notice that with this function you need to place arguments in the brackets after the function name. The sales argument will require you to select the cell containing the sales value for which you wish to add the VAT to.

Function vat(sales)
vat = sales * 1.15
End Function

Use the VAT function to calculate the VAT inclusive value in your list of prices

We could also add a markup value as part of our function by adding a second argument.

Function retail(sales, markup)
retail = sales * (markup + 1) * 1.15
End Function

Enter a markup percentage on your worksheet and refer to this value in the second argument of the function (separated from the first by a comma).  You can always use the functions argument dialogue box to enter cell references or values.

The following function calculates the amount of time that has elapsed between a start time and the end time.  The function also works for times over two separate days, in other words when the start time is greater than the end time.

Function CalTime(StartTime, EndTime)
    If StartTime > EndTime Then
    CalTime = EndTime - StartTime + 1
    Else
    CalTime = EndTime - StartTime
    End If
End Function