How to Write User Defined Functions (UDFs) in Excel with VBA

Bottom Line: Learn how to create your own User Defined Function (UDF) when the function you need doesn't exist in Excel.

Skill Level: Intermediate

Video Tutorial

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

I've attached my UDF examples from the video in the file below. You can view them by opening the VB Editor from the Developer tab.

UDF Examples.xlsm (29.6 KB)

Create Custom Functions in Excel

Excel is a fantastic tool with SO many capabilities and functions. But what do you do if the function you need isn't already built in?

You write your own. When you create your own custom function for Excel, it's called a UDF, a User Defined Function.

UDFs Write Custom User Defined Functions in Excel with VBA

One example of a function that is not included in Excel is a function that returns the fiscal year. There are many ways to calculate the fiscal year from a date with existing functions in Excel. Here is one example for a fiscal year starting in October.

=IF(MONTH(E2)>=10,1,0)+YEAR(E2)

However, if this is a calculation you do often, you might want a shorter formula that is easier to write. So let's write a UDF to create our own function that returns the fiscal year.

Example of a UDF for Excel

We're going to create a Fiscal Year UDF for a company that has a fiscal year beginning in October.

Like any other function, the UDF can be called from a cell formula. In this image, you can see that I've called a function named FiscalYear in order to calculate the fiscal year based on the date found in cell E2. If that date falls before October, it will return one value, and if it falls in or after October, we will get a different value.

UDF for Fiscal Year shown in cell

Writing the Function in VBA

To write the function, go to the VB Editor and begin with the word Function. This will be followed by the function name, which you designate. In my example, I've called it FiscalYear. It can be any function name that isn't already in use.

Next, open the parenthesis and specify the arguments. In my example I use dDate and then the data type is Variant because we might pass through text or other data that is not in date format. Then close the parenthesis.

Next, type “as” and specify the return data type. In this case, I use Long as the data type.

On the next line, we are going to set our function name. The function name always needs to be set somewhere within the function, usually at the end. For my example, I set the function like this: IIf (Month(dDate) >=10, 1, 0) + Year(dDate). This code basically tells Excel that if the date month is on or after October, use the next year as the fiscal year, and if not, leave it as the year that is found in the date.

So the entire function looks like this:

Fical year UDF code

Here is the code that you can copy and paste:

Function FiscalYear(dDate As Variant) As Long

  FiscalYear = IIf(Month(dDate) >= 10, 1, 0) + Year(dDate)

End Function

Syntax Guide

I've created a syntax guide to help you remember the order of the function's components.

Function Statement Syntax Guide

Advanced Options for UDFs

The example we just looked at is fairly straightforward. However, User Defined Functions in VBA can have multiple lines of code.

For example, if we want to add a bit of error-handling to our code so that we get the words “Date not specified” anytime the function passes through data that isn't in date format, the code would look something like this:

Private Function FiscalYear2(dDate As Variant) As Variant

Dim vTemp As Variant

  If IsDate(dDate) Then
    vTemp = IIf(Month(dDate) >= 10, 1, 0) + Year(dDate)
  Else
    vTemp = "Date not specified"
  End If
  
  FiscalYear2 = vTemp
  
End Function

Multiple and Optional Parameters

UDFs that you create can also have multiple arguments as well as optional arguments.

For example, in the code below, I've added the Optional keyword so that the iStartMo argument becomes optional and does not need to be specified.

I also added an If statement to handle the scenario when the iStartMo argument is not specified. In this case the value of the variable will be zero if it is not specified since that is the default for the Long integer data type.

The If statement will change the value to 13 if it is not specified and this will calculate a fiscal year that starts in January.

The point is that you will typically need to handle the cases when the user doesn't specify a variable if you have Optional arguments.

Function FiscalYear4(dDate As Variant, Optional iStartMo As Long) As Long

  If iStartMo = 0 Then
    iStartMo = 13
  End If
    
  FiscalYear4 = IIf(Month(dDate) >= iStartMo, 1, 0) + Year(dDate)

End Function

Private Functions

You can also add the word “Private” before “Function” if you don't want your UDF to be shown in the autofill dropdown menu that Excel provides when you start to type a function name.

Making a function private doesn't mean other users cannot use it; it just means that they won't see it as an option in the list.

Stepping Through the Code

You can add a Breakpoint anywhere in the function to pause the code and step through it.

Breakpoint in UDF to Pause and Step Through VBA Code

After adding the Breakpoint (keyboard shortcut: F9), go to any cell in Excel that calls the function, edit the cell, then press Enter. This will run the VBA code and pause at the line where you placed the Breakpoint.

You can then step through the code line-by-line with the F8 key to evaluate it.

Pros and Cons of UDFs

Pros

  1. Custom Functions in VBA help to simplify complex formulas. It's much easier to call a function than to write, rewrite, or copy and paste entire formulas, especially when they are frequently used.
  2. UDFs can easily be copied to other workbooks.
  3. We are able to do some advanced error handling within our functions since we are customizing them to be exactly how we want them.
  4. The UDFs can be called from other macros, if needed.

Cons

  1. The function code has to be saved in the workbook that it is being used in. If you wanted to use the code in another workbook, you would have to copy the code into that workbook and save it as a macro-enabled file. If you don't know how to copy or import code into other workbooks, you can reference this tutorial: How to Copy or Import VBA Code to Another Workbook.
  2. While it is possible to store the UDF in your Personal Macro Workbook or an Excel add-in, any other users who are trying to run those UDFs would have to have the same code in their Personal Macro Workbook or have the add-in installed. You also have to reference the Personal Macro Workbook when you call the function, which makes the process a little less simplified. Here is the syntax if the function is in your PMW.
    =Personal.XLSB!FiscalYear(E4)
  3. For predefined functions, Excel provides a handy tool called a ScreenTip, which helps to walk you through each arguemnt of the function. When you create your own UDFs, you do not have the ScreenTip feature.
UDFs do not have ScreenTips like built-in functions

Conclusion

User Defined Functions are a great tool for simplifying formulas in Excel. They can also allow us to write simply functions for complex formulas, and perform calculations that can't be achieved with built-in functions.

I hope this overview has been helpful in getting you started creating your own UDFs. In this tutorial, we only looked at creating a UDF for the purpose of defining a fiscal year, but as you can imagine, the applications are really limitless.

I'd love to hear how you plan to use (or are already using) UDFs for your projects in Excel. Write your thoughts in the comments so that we can all learn from each other. Please leave a comment with questions as well.

Thanks! 🙂

14 comments

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

  • Hi Jon,

    Thanks for yet another great lesson! I have a problem when I try to count cells with different attributes related to there apperances. I understand that using the ColorIndex property, I should be able to return the color of the border, font, or interior fill of a cell.

    It works for fill:
    =CountFillColor(range, reference cell) with the following UDF:

    Function CountFillColor(range_data As Range, criteria As Range) As Long
    Dim datai As Range
    Dim icolor As Long
    icolor = criteria.Interior.ColorIndex
    For Each datai In range_data
    If datai.Interior.ColorIndex = icolor Then
    CountFillColor = CountFillColor + 1
    End If
    Next datai
    End Function

    But I get a value error for border function
    =CountBorderColor(range, reference cell) with the following UDF:

    Function CountBorderColor(range_data As Range, criteria As Range) As Long
    Dim datab As Range
    Dim bcolor As Long
    bcolor = criteria.Border.ColorIndex
    For Each datab In range_data
    If datab.Border.ColorIndex = bcolor Then
    CountBorderColor = CountBorderColor + 1
    End If
    Next datab
    End Function

    And for font color the result is 0 regardless of the font color in the reference cell, for
    =CountFontColor(range, reference cell) with the following UDF:

    Function CountFontColor(range_data As Range, criteria As Range) As Long
    Dim dataf As Range
    Dim fcolor As Long
    fcolor = criteria.Font.ColorIndex
    For Each dataf In range_data
    If dataf.Font.ColorIndex = xcolor Then
    CountFontColor = CountFontColor + 1
    End If
    Next dataf
    End Function

    Where did I go wrong?

  • I wonder if there is a way to make an alias for an Excel function?

    Principal statistical functions are MAX, MIN, MEDIAN and AVERAGE. I would prefer to use the alias MEAN in place of AVERAGE.

    MEAN could be defined via the parameter PARAMARRAY, but this is not a trivial exercise.

  • Hi Jon,

    Another great video from an excellent teacher.

    Can I ask; would it be possible to have a video and/or companion file with the macros briefly showcased in your own PMW? It does look extensive.

    Kind regards,
    Colin

    • Hi Uffe,
      Thank you for the suggestion! Just to be clear, that technique will show the function arguments in the Function Arguments window. That is different from the screentip that displays under the cell or formula bar. If you or your users use the Function Arguments window then that can be a good place for those argument descriptions.

      Thanks again!

  • Hi Jon,
    Thank you for the tutorial on UDF. I had written one to enable several columns to be updated when a key column was updated. I was predicting the next two terms of a science module based on the first term. There are different starting points and different routes through the 12 modules. My formula originally had lots of IF statements and was very long. The UDF made it very simple to write and update.
    However, I had to regularly recalculate the sheet to keep the cells filled with the correct information, otherwise the had #VALUE in them. I have gone back to the long formula to make the sheet data stable.
    Should I be doing something else to enable the UDF to be stable?

    I hope that all makes sense.

    • Hi Jan-Robert,
      Great question! Yes, it makes sense. There are several things that can cause this.

      I believe the most common reason is that the UDF will only recalc if changes are made to cells that are directly referenced in the formula. The dependencies are not as deep as they are with built-in functions. So this can force you to have to recalc, depending on the other inputs that you might be using in the model.

      There is a great answer in this Stack Overflow post that covers other reasons. I hope that helps.

  • I have seen code for drop down boxes that brings up items as the cell is filled. I.E. “Fr” brings up “Fred”, “Fanny,” “Felicity.” This is very helpful for long drop down lists. Do you have such a video?

    Thanks for all you do.

    Cliff

      • Hi Jon,

        I enjoyed the video. It was interesting. It did occur to me, that it would be designed more for advanced users. You need to know the definitions of the codes in functions. In order to use the correct terminology to write the codes.
        Thanks,
        Betty

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar