How to Create a Custom Function in Excel Using VBA

08 Jun How to Create a Custom Function in Excel Using VBA

Howdee! Do you ever find yourself wishing “if only there were a function in Excel like this, I could do that”? Then this write-up is for you. Creating a custom excel function can help reduce the reliance on having multiple calculated columns or, what I like to call “middle-man formulas”. These are formulas you enter to be able to use another formula to achieve a desired result.  These not only waste valuable memory resources and increase file size, they also make spreadsheets look disorderly and unnecessarily complex.

The best part about a VBA custom excel function? You don’t need to be a VBA expert to build one. Often, if you understand how to use an “IF” formula in Excel, you can create your own custom excel function.  The example I’ve chosen to use today is something that used to take several middle-man formulas to achieve. It is a very widespread practice in the finance/accounting world to age things.  Whether they be invoices (accounts receivable), accounts payable, or accrued revenue, we are constantly tasked with reporting them based on their age.

In the past, I would use a template like this to age data based on a date input:

It got the job done. However, users who don’t know how to nest four “IF” functions might not understand what is happening in that long formula. Even if you do understand what’s happening, it is time consuming to follow the logic (and time consuming to write). I would constantly find myself saying “there’s gotta be a better way!”

Let’s explore how to make this easier by building a custom excel function using VBA. Open a new workbook and press “alt + F11” to open the code editor.  Right click on the workbook name and insert a module as shown below.

You can rename your module in the properties window if you like.  I usually like to name it something to describe what code will be contained within the module. In this case, I would name it “Function”.

The Code

Now you’ll need to paste this code within that module:

Function AgeDates(DateToAge, AgeFromDate)
    If DateToAge = "Help" Then
        AgeDates = "(Input 1 = Date to Age, Input 2 = Date to Age From)"
    Else
        If IsDate(DateToAge) = False Then
            AgeDates = "Date to Age is Not a Date"
        ElseIf IsDate(AgeFromDate) = False Then
            AgeDates = "Age From Date is Not a Date"
        Else
    If CDate(AgeFromDate) < DateToAge Then
        AgeDates = "Date to Age after Age From Date"
    ElseIf CDate(AgeFromDate) - DateToAge <= 30 Then
        AgeDates = "0-30 Days"
    ElseIf CDate(AgeFromDate) - DateToAge <= 60 Then
        AgeDates = "31-60 Days"
    ElseIf CDate(AgeFromDate) - DateToAge <= 90 Then
        AgeDates = "61-90 Days"
    ElseIf CDate(AgeFromDate) - DateToAge <= 120 Then
        AgeDates = "91 to 120 Days"
    Else
        AgeDates = " Over 120 Days"
    End If
    End If
    End If
End Function

What Does This Custom Excel Function Do?

The first thing you might notice, if you’re familiar with VBA at all, is that there is no “Sub” to start this code off. Instead, because this is a function, we start with simply “Function”.  What immediately follows before the open parenthesis is the name of the function. This is important as this is how you’ll call the function in Excel, so make sure it makes sense with whatever you’re building the function accomplish. Within the parentheses, are the parameters that your function will accept. In this simple case, because I’ve hardcoded so much of this function, we are accepting two parameters. The date we wish to age, and the date we want to age from.

Stepping through the code, if the user inputs the string “Help”, the function will display the string “Input 1 = Date to Age, Input 2 = Date to Age From”.  This line is not necessary, but I like to include it because, unfortunately, Excel will not offer intellisense (the tool tip explain inputs to users) for any custom functions you create within VBA. There is a way I’m researching now that requires some development in VB.NET and C# using a VSTO (Visual Studio Tools for Office) add-in. I’ll put up a blog post on this in the future once I’m familiar enough with it. If the user puts in date values instead of the word “Help”, the function begins to evaluate the parameters.

The first piece of this if statement checks to ensure the inputs are dates. The “IsDate()” function is a VBA built-in function and not one that I’ve created. This tells the user if one of the inputs they selected is not a date. The last check before the evaluations begin is to ensure the date you want to age from is before the date you’re aging (since you wouldn’t age from a future date). If all these checks are passed, then the function checks how old the date is and returns a string telling the user which group this falls into.

In this example, I’ve hard coded the aged ranges (0-30, 31-60, etc.). However, you can have the user define these ranges if that is something you want or need. I’ve included an example of this in my example file. If you’re a free member, you can download this file and check it out. I’ll create a second post on this specifically soon so be sure to check back!

Cheers,

R