• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Creating a UDF (User Defined Function) in Excel

You are here: Home / Excel VBA / Creating a UDF (User Defined Function) in Excel
September 15, 2016 by Philip Treacy

Any user of Excel will have used its functions.  From the humble SUM to VLOOKUP, INDEX and MATCH, OFFSET and myriad others.

Excel also allows you to create your own functions, these are known as user defined functions, or UDF’s.

By creating a UDF you can expand the functionality of Excel and get it to do things it just doesn’t do out of the box.

You may want to read another post I’ve written looking at a more complex example showing how code for several separate UDF’s can be combined into a single multi-function UDF.

 

Does This Have Something to do with VBA?

Yes, it does, UDF’s can be written in VBA (and other languages like C++), but we’re just going to look at VBA.

Often we refer to VBA as a macro, but there are some important distinctions between what we’d ordinarily call a macro, and a UDF.

The primary distinction is that a function returns a value.  Put another way, it carries out some form of calculation and returns the result to the worksheet.

A macro can of course carry out calculations, but the results are not returned to Excel in the same way as a function.

In addition, macros can do things that functions can’t (this isn’t an exhaustive list):

  • Affect other cells
  • Change cell formatting
  • Add sheets
  • Change sheet names
  • Add workbooks

A macro can alter the structure and formatting in a sheet or workbook.  A function can only return the result of its calculations to the cell in which it is called from, (except for the HYPERLINK exception).

You can also record macros, functions have to be written by hand.

But of course there are advantages to using your own functions.  With UDF’s you can:

  • Create your own custom maths/engineering/stats/string functions
  • Simplify complex, nested formulae

In addition, a UDF automatically recalculates when you change the input value(s), macros have to be run again manually, unless you are using events.

So if you are looking for a specific answer, a result, then you can consider writing a UDF.

Writing a UDF

Before we actually write the UDF, let’s look at writing a macro that does the same job.  This will help us understand some of the main differences between an ‘ordinary’ macro and a UDF.

Our macro is going to do something very simple, it will give us the square of a number, the code looks like this


Sub SquareMacro()

  Selection.Offset(, 1).Value = Selection.Value ^ 2

End Sub

We select a cell, and then run the macro.  The value of the selected cell Selection.Value is squared, and the result goes into the cell to the right of our selected cell Selection.Offset(,1).Value

run a macro

So in order to run this we have to:

  • Select a cell
  • Manually run the code

If we have a series of numbers we want the squares for, we have to do this for each of those numbers.

You could of course rewrite the macro to work for a range of numbers, but that isn’t the object of this post and depending on what you are trying to do, that might not be the best solution in your case.

If we change this code into a UDF, we can use it in our workbook like any other function.  We don’t need to manually run the code each time we want an answer.  We can copy and paste the formula in the sheet, and we can nest the function with other functions.

Here’s the code modified to work as a UDF


Function Square(MyNum As Single) As Single

   Square = MyNum ^ 2

End Function

In the macro we referenced the value we wanted to square using Selection.Value which just means the value in the currently selected cell.

We assigned the result of that calculation to    Selection.Offset(, 1).Value, so put the result into the cell that is one column to the right of the selected cell.

In our function, we are able to pass in an argument (value) and I’ve called this MyNum.  I’ve specified that MyNum will be of the data type Single, MyNum As Single, so when we use this function, we must pass in a single value.

The argument we pass in can be a number, or a cell reference.  Exactly the same way a built-in Excel function works.

The result is sent back to Excel by assigning the result of this calculation to the name of the function, which is Square.


Square = MyNum ^ 2

To use my new UDF in a worksheet I type in

=square(number)

or

=square(Cell Reference)

into a cell.

user defined function

Write Your Own UDF’s

So what functions can you write for yourself?  Please let me know, I’d love to hear what you are up to.

Distributing Workbooks with UDF’s

Because a UDF is VBA that you have written yourself, the function won’t be available to anyone else unless you include the function code with the workbook you distribute.

This also means that when someone opens a workbook with a UDF, they will get a security warning that the workbook contains macros, and be asked to enable them (or not).  Of course, if they don’t enable macros, your UDF won’t work for them.

Download the workbook and try it yourself

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.

More User Defined Function Posts

writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Return an array from a udf

Return An Array From A UDF

If you can return an array from a udf you are able to return multiple pieces of data and place them into multiple cells
Return a range from a UDF

Return a Range from a UDF

If you return a range from a UDF this allows you to use that range in other functions and calculations.
create an excel add-in for user defined functions udfs

Create an Excel Add-In for User Defined Functions (UDF’s)

How to create an Excel add-in for user defined functions (UDF's) to make them easier to use.
creating a reference to personal.xlsb for user defined function udf

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

How to create a reference to PERSONAL.XLSB so that using User Define Functions (UDF's) is easier
Creating Multi-Function UDF's

Creating Multi-Function UDF’s

Combine separate functions into one, multi function UDF (user defined function)

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Excel VBATag: user defined function
Previous Post:Excel PivotTable Show Values As
Next Post:Creating Multi-Function UDF’sCreating Multi-Function UDF's

Reader Interactions

Comments

  1. Ebru

    January 17, 2021 at 6:41 pm

    How can I do that? Can you help me?

    The name of the function(s): snowball, detailed_snowball, most_snowball
    The objective(s): (Snowbball) Finding how many steps of iterations does it take for a number to decrease to 1
    (detailed_snowball) Which number is found at each iteration
    (most_snowball) Which number in a range has the most snowball number.

    Reply
    • Philip Treacy

      January 19, 2021 at 11:51 am

      Hi Ebru,

      I’ll need more info to help you. For example, by how much does each iteration reduce the number?

      I’d write one function that does all 3ofthe things you want, otherwise you are duplicating code.

      With regards most-snowball – won’t the largest number in the range have the most snowball number?

      Please start a topic on our forum and attach your file with a bit more info and some examples of data.

      Regards

      Phil

      Reply
  2. Lynne

    November 24, 2017 at 10:55 pm

    Good afternoon, I have downloaded the workbook for Creating User Defined Functions but all I see is a heading – Creating User Defined Functions – next to it – click to read the tutorial. But there is nothing showing apart from – Other Resources and Our Courses.
    Am I missing something?

    Kind Regards

    Reply
    • Catalin Bombea

      November 25, 2017 at 1:38 am

      Hi Lynne,
      It’s about User Defined Functions, so you have to open the Visual Basic Editor, that is the place for macros. Press Alt+F11 to open the VB Editor, then in Module1 you should see the codes described in this article.
      Catalin

      Reply
  3. SunnyKow

    September 29, 2016 at 6:17 pm

    Hi Philip
    Nice article. Hope you can write one on the HYPERLINK as it is very interesting and can really do some Excel “magic”.

    Sunny

    Reply
    • Mynda Treacy

      September 30, 2016 at 10:09 am

      Hi Sunny,

      The HYPERLINK function or the use of HYPERLINK in VBA? The HYPERLINK Function is covered here:

      https://www.myonlinetraininghub.com/excel-factor-18-dynamic-hyperlinks-and-the-magic

      And in the Dashboard course I use it in session 5.15 Rollover Technique.

      Mynda

      Reply
      • SunnyKow

        September 30, 2016 at 11:03 am

        Hi Mynda

        I am referring to the Hyperlink function that allows the mouse to move over (mouse-over) that hyperlinked cell and trigger a change (VBA) to another cell’s value. I will take at look at Lesson 5.15 as I have not reached there yet.

        Thanks

        Sunny

        Reply
        • Mynda Treacy

          September 30, 2016 at 11:50 am

          Yep, that’s the one in session 5.15 of the Dashboard course.

          Reply
  4. John B. Masyas

    September 20, 2016 at 5:17 pm

    Hi Philip,

    Thanks for this article, you made it real easy to be read and try in the future 🙂

    I’m just wondering how to copy or send the file through e-mail to another user and make the UDF works as desired!!! you said I must include the function code with the workbook, where do I put it?

    It might be a naive question, but I’m not familiar with macros and VBAs…

    Thanks again

    Reply
    • Catalin Bombea

      September 20, 2016 at 6:09 pm

      Hi John,
      Press Alt+F11 to open the Visual Basic Editor.
      On the left side, you should be able to see the Project explorer window, you will be able to identify the names of the sheets from that workbook.
      Right click on any sheet module and select Insert>Module. Double click on that module to open it in Editor window, here is the place to paste the code you have for your UDF’s.
      Let us know if you managed to make it work.
      Cheers,
      Catalin

      Reply
  5. matt

    September 18, 2016 at 7:50 pm

    yup! Love to do my function in VBA by my own

    Reply
  6. amerongen

    September 16, 2016 at 10:27 pm

    Hi, Nice article. I hope you wil shows us also a C-version. I didn’t know that they called this UDF.
    I use this now for several years, but I found it a little slow on the mac with the as of today the latest 2016 version. In the 2011 version it was much faster. Do you have suggestions to make it run quicker?
    I got in 13 Tab’s a table, where this calculates the used/needed number of units, depending if it is m1, m2, m3, pieces or some other kind of unit.

    =CALCULATEUNIT(R7;0,001;VasteData!$E$2:$E$4;F7:H7)*[@[corr/ reken factor]] <-= is inside a cell row in the table. The 'VasteData' part is for getting data from a table with the units in the same order as in the popup cells in the tables. The last part is a correction factor available in the same table row.

    '***********************************************************
    ' Date 25 mrt 2011 – CalculateUnit
    ' RvA
    ' Creates the total of measurements
    ' supplied from a range of an unit
    '
    ' Function CalculateUnitTotal(
    ' reqUnit As String, – the unit that must be used to calculate the totals
    ' scaledTo As Integer, – the scale that must return e.g. -1000 means divide it with 1000, 20 means multiply it by 20
    ' orderedUnits As Range, – the range units supplied as a range
    ' orderedValues As Range) – a range, cell or a number tot calculate with. Items count should be the same number as the count of units
    ' As Double
    '***********************************************************
    Public Function CALCULATEUNIT(reqUnit As String, scaledTo As Double, orderedUnits As Range, orderedValues As Range) As Double

    'Application.ScreenUpdating = False
    'Application.DisplayStatusBar = False
    'Application.EnableEvents = False
    'ActiveSheet.DisplayPageBreaks = False

    'Debug.Print "sheet – : " & ActiveSheet.Name

    ' This function accepts a single orderedListOfUnits range and one or more orderedListOfValues
    Dim i As Long, j As Long
    Dim unitElem As Variant
    Dim valueElem As Variant
    Dim cntUnits As Long
    Dim cntValues As Long
    cntUnits = orderedUnits.Cells.Count
    cntValues = orderedValues.Cells.Count

    Debug.Print "****** CalculateUnit"

    If (cntUnits cntValues) Then
    CALCULATEUNIT = 0
    Debug.Print “#VALUE error: Range orderedListOfUnits size not equal with size of orderedListOfValues.”
    Exit Function
    End If
    Debug.Print “reqUnit :” & reqUnit & ” scaledTo :” & scaledTo
    Debug.Print “The array is: orderedUnits – orderedValues”
    For i = 1 To UBound(orderedUnits())
    arrString = “Unit :” & orderedUnits(i) & ” Value :” & orderedValues(i)
    Debug.Print arrString
    Next i

    Dim Units(1 To 12) As String
    Dim UnitValue(1 To 12) As Double

    ‘ popup validation list order
    ‘ kg
    ‘ b
    ‘ h
    ‘ l
    ‘ omtrek-lb
    ‘ omtrek-lh
    ‘ m2-lb
    ‘ m2-lh
    ‘ m3
    ‘ set
    ‘ st

    Units(1) = “KG”
    Units(2) = “B”
    Units(3) = “H”
    Units(4) = “L”
    Units(5) = “OMTREK-LB”
    Units(6) = “OMTREK-LH”
    Units(7) = “M2-LB”
    Units(8) = “M2-LH”
    Units(9) = “M3”
    Units(10) = “SET”
    Units(11) = “ST”
    Units(12) = “ZAK”
    reqUnit = UCase(reqUnit)
    ‘ check if reqUnit is in the Units arr.
    ‘IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
    If (IsError(Application.Match(reqUnit, Units, 0))) Then
    CALCULATEUNIT = 0
    Debug.Print “#VALUE error: requested Unit is not available in a calculation method.”
    Exit Function
    End If

    i = 0
    For Each unitElem In orderedUnits
    unitElem = UCase(unitElem)
    i = i + 1
    j = 0
    valueElem = 0
    valueElem = orderedValues(i)
    ‘ Debug.Print ” search for Item: ” & i & ” unitElem: ” & unitElem & ” with valueElem: ” & valueElem
    For Each UnitsItem In Units
    j = j + 1
    Debug.Print ” Unit ” & j & ” UnitsItem: ” & UnitsItem
    If (UnitsItem = unitElem) Then
    UnitValue(j) = valueElem * scaledTo ‘ save the the scaled value corresponding to unit place in the UnitValue Array
    Debug.Print ” Unit vs UnitsItem found :” & j & ” UnitsItem: ” & UnitsItem
    Exit For
    End If
    Next
    Next unitElem
    j = 0
    For Each UnitsItem In Units
    j = j + 1
    ‘Debug.Print ” CalcAction Unit search : ” & j & ” UnitsItem: ” & UnitsItem
    If (reqUnit = UnitsItem) Then
    CalcAction = j
    Debug.Print ” Unit CalcAction found :” & j & ” CalcAction: ” & CalcAction
    Exit For
    End If

    Next

    result = 0
    ‘Debug.Print “CalcAction: ” & CalcAction
    Select Case CalcAction

    Case 1: ‘kg
    ‘ Debug.Print “Unitvalu 3:” & 1
    result = 1

    Case 2: ‘br
    ‘ Debug.Print “UnitVal 2:” & UnitValue(2)

    result = UnitValue(2)
    Case 3: ‘h
    ‘ Debug.Print “Unitvalu 3:” & UnitValue(3)
    result = UnitValue(3)
    Case 4: ‘l
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    result = UnitValue(4)
    Case 5: ‘omtrek-l x b
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    ‘ Debug.Print “Unitvalu 2:” & UnitValue(2)
    result = (UnitValue(4) + UnitValue(2)) * 2
    Case 6: ‘omtrek-l x h
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    ‘ Debug.Print “Unitvalu 3:” & UnitValue(3)
    result = (UnitValue(4) + UnitValue(3)) * 2
    Case 7: ‘m2 l x b
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    ‘ Debug.Print “Unitvalu 2:” & UnitValue(2)
    result = UnitValue(4) * UnitValue(2)
    Case 8: ‘m2 l x h
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    ‘ Debug.Print “Unitvalu 3:” & UnitValue(3)
    result = UnitValue(4) * UnitValue(3)
    Case 9: ‘m3
    ‘ Debug.Print “Unitvalu 4:” & UnitValue(4)
    ‘ Debug.Print “Unitvalu 2:” & UnitValue(2)
    ‘ Debug.Print “Unitvalu 3:” & UnitValue(3)
    result = UnitValue(4) * UnitValue(2) * UnitValue(3)
    Case 10: ‘set
    Debug.Print “Unitvalue 3:” & 1
    result = 1
    Case 11: ‘st
    ‘ Debug.Print “Unitvalue 3:” & 1
    result = 1
    Case 12: ‘zak
    ‘ Debug.Print “Unitvalu 3:” & UnitValue(3)
    result = UnitValue(3)
    Case Else
    ‘ Debug.Print “Unitvalu” & 1
    result = 0
    End Select

    If (scaledTo > 0) Then
    CALCULATEUNIT = result * scaledTo
    Else
    CALCULATEUNIT = result
    End If
    Debug.Print “return value: ” & CALCULATEUNIT
    End Function

    Again, it works but it is sloooow. Any suggestions?

    Reply
    • Philip Treacy

      September 18, 2016 at 10:52 am

      Hi,

      If you can provide your workbook with sample data and the UDF/VBA then it will be easier to see if we can speed it up.

      Please create a post on the forum and attach your workbook there.

      Thanks

      Phil

      Reply
  7. Amadou

    September 15, 2016 at 9:47 pm

    Hi, in the context of project estimates I use an UDF to compute an estimation of project workload. I first perform 3 estimations (optimistic, realist and pessimistc) then my UDF compute an average as (worst estimation + 4*average esimation + best estimation) /6

    Reply
    • Philip Treacy

      September 16, 2016 at 9:30 am

      Great work Amadou, thanks for letting us know.

      Phil

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x