Creating a UDF (User Defined Function) in Excel (original) (raw)

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):

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:

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:

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.