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 2nd 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)
or
=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
Enter your email address below to download the sample 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.
Lynne says
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
Catalin Bombea says
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
SunnyKow says
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
Mynda Treacy says
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
SunnyKow says
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
Mynda Treacy says
Yep, that’s the one in session 5.15 of the Dashboard course.
John B. Masyas says
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
Catalin Bombea says
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
matt says
yup! Love to do my function in VBA by my own
amerongen says
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?
Philip Treacy says
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
Amadou says
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
Philip Treacy says
Great work Amadou, thanks for letting us know.
Phil