Creating a UDF (User Defined Function) in Excel

September 15, 2016 by Philip Treacy 11 Comments

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.

I’m going to write two blog posts about UDF’s. This is the first and will look at what a UDF is and how to create a simple one.

The 2^nd post will look at some more complex examples and show how code for several UDF’s can be combined into a single more powerful function.

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

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)

=square(Cell Reference)

into a cell.

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

Download the Excel Workbook.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

Free 100 Excel Tips & Tricks eBook – Click here to Download

Comments

SunnyKow says

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 says
  
  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 says
    
    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 says
      
      September 30, 2016 at 11:50 am
      
      Yep, that’s the one in session 5.15 of the Dashboard course.
      
      Reply
John B. Masyas says

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 says
  
  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
matt says

September 18, 2016 at 7:50 pm

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

Reply
amerongen says

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 says
  
  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
Amadou says

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 says
  
  September 16, 2016 at 9:30 am
  
  Great work Amadou, thanks for letting us know.
  
  Phil
  
  Reply