My Online Training Hub

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

You are here: Home / Excel VBA / Creating a UDF (User Defined Function) in Excel
by

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.

More User Defined Function Posts

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
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
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
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
How to create a reference to PERSONAL.XLSB so that using User Define Functions (UDF's) is easier
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
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
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
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
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
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
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
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
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
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
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Tag:

Reader Interactions

Comments

  1. Ebru

    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

      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

    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

      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

    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

  4. John B. Masyas

    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

      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

  6. amerongen

    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

      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

    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

Leave a Reply

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

Current ye@r *