Writing Your Own First Function in Excel

About User Defined Functions

Excel provides the user with a large collection of ready-made functions, more than enough to satisfy the average user. Many more can be added by installing the various add-ins that are available.
Most calculations can be achieved with what is provided, but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.
A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.
UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.
Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...

A Function to Calculate the Area of a Rectangle

Yes, I know you could do this in your head! The concept is very simple so you can concentrate on the technique.
Suppose you need a function to calculate the area of a rectangle. You look through Excel's collection of functions, but there isn't one suitable. This is the calculation to be done:

AREA = LENGTH x WIDTH

Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).

You will need a module in which to write your function so choose Insert > Module. Into the empty module type: Function Area and press ENTER. The Visual Basic Editor completes the line for you and adds an End Function line as if you were creating a subroutine. So far it looks like this...

Function Area()

End Function

Place your cursor between the brackets after "Area". If you ever wondered what the brackets are for, you are about to find out! We are going to specify the "arguments" that our function will take (an argument is a piece of information needed to do the calculation). Type Length as double, Width as double and click in the empty line underneath. Note that as you type, a scroll box pops-up listing all the things appropriate to what you are typing.

This feature is called Auto List Members. If it doesn't appear either it is switched off (turn it on at Tools > Options > Editor) or you might have made a typing error earlier. It is a very useful check on your syntax. Find the item you need and double-click it to insert it into your code. You can ignore it and just type if you want. Your code now looks like this...

Function Area(Length As Double, Width As Double)

End Function

Declaring the data type of the arguments is not obligatory but makes sense. You could have typed Length, Width and left it as that, but warning Excel what data type to expect helps your code run more quickly and picks up errors in input. The double data type refers to number (which can be very large) and allows fractions.
Now for the calculation itself. In the empty line first press the TAB key to indent your code (making it easier to read) and type Area = Length * Width. Here's the completed code...

Function Area(Length As Double, Width As Double)
    Area = Length * Width
End Function

You will notice another of the Visual Basic Editor's help features pop up as you were typing, Auto Quick Info...

It isn't relevant here. Its purpose is to help you write functions in VBA, by telling you what arguments are required.
You can test your function right away. Switch to the Excel window and enter figures for Length and Width in separate cells. In a third cell enter your function as if it were one of the built-in ones. In this example cell A1 contains the length (17) and cell B1 the width (6.5). In C1 I typed =area(A1,B1) and the new function calculated the area (110.5)...

Sometimes, a function's arguments can be optional. In this example we could make the Width argument optional. Supposing the rectangle happens to be a square with Length and Width equal. To save the user having to enter two arguments we could let them enter just the Length and have the function use that value twice (i.e. multiply Length x Length). So the function knows when it can do this we must include an IF Statement to help it decide.
Change the code so that it looks like this...

Function Area(Length As Double, Optional Width As Variant)
    If IsMissing(Width) Then
        Area = Length * Length
    Else
        Area = Length * Width
    End If
End Function

Note that the data type for Width has been changed to Variant to allow for null values. The function now allows the user to enter just one argument e.g. =area(A1). The IF Statement in the function checks to see if the Width argument has been supplied and calculates accordingly...

Now for a more practical example...

A Function to Calculate Fuel Consumption

I like to keep a check on my car's fuel consumption so when I buy fuel I make a note of the mileage and how much fuel it takes to fill the tank. Here in the UK fuel is sold in litres. The car's milometer (OK, so it's an odometer) records distance in miles. And because I'm too old and stupid to change, I only understand MPG (miles per gallon).
Now if you think that's all a bit sad, how about this. When I get home I open up Excel and enter the data into a worksheet that calculates the MPG for me and charts the car's performance.
The calculation is the number of miles the car has travelled since the last fill-up divided by the number of gallons of fuel used...

MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL

but because the fuel comes in litres and there are 4.546 litres in a gallon..

MPG = (MILES THIS FILL - MILES LAST FILL) / LITRES OF FUEL x 4.546

Here's how I wrote the function...

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)
    MPG = (FinishMiles - StartMiles) / Litres * 4.546
End Function

and here's how it looks on the worksheet...

Not all functions perform mathematical calculations. Here's one that provides information...

A Function That Gives the Name of the Day

I am often asked if there is a date function that gives the day of the week as text (e.g. Monday). The answer is no*, but it's quite easy to create one. (*Addendum: Did I say no? Check the note below to see the function I forgot!).
Excel has the WEEKDAY function, which returns the day of the week as a number from 1 to 7. You get to choose which day is 1 if you don't like the default (Sunday). In the example below the function returns "5" which I happen to know means "Thursday".

But I don't want to see a number, I want to see "Thursday". I could modify the calculation by adding a VLOOKUP function that referred to a table somewhere containing a list of numbers and a corresponding list of day names. Or I could have the whole thing self-contained with multiple nested IF statements. Too complicated! The answer is a custom function...

Function DayName(InputDate As Date)
    Dim DayNumber As Integer
    DayNumber = Weekday(InputDate, vbSunday)
    Select Case DayNumber
        Case 1
            DayName = "Sunday"
        Case 2
            DayName = "Monday"
        Case 3
            DayName = "Tuesday"
        Case 4
            DayName = "Wednesday"
        Case 5
            DayName = "Thursday"
        Case 6
            DayName = "Friday"
        Case 7
            DayName = "Saturday"
    End Select
End Function

I've called my function "DayName" and it takes a single argument, which I call "InputDate" which (of course) has to be a date. Here's how it works...

• The first line of the function declares a variable that I have called "DayNumber" which will be an Integer (i.e. a whole number).
• The next line of the function assigns a value to that variable using Excel's WEEKDAY function. The value will be a number between 1 and 7. Although the default is 1=Sunday, I've included it anyway for clarity.
• Finally a Case Statement examines the value of the variable and returns the appropriate piece of text.

Here's how it looks on the worksheet...

Accessing Your Custom Functions

If a workbook has a VBA code module attached to it that contains custom functions, those functions can be easily addressed within the same workbook as demonstrated in the examples above. You use the function name as if it were one of Excel's built-in functions.

You can also find the functions listed in the Function Wizard (sometimes called the Paste Function tool). Use the wizard to insert a function in the normal way (Insert > Function).

Scroll down the list of function categories to find User Defined and select it to see a list of available UDFs...

    

You can see that the user defined functions lack any description other than the unhelpful "No help available" message, but you can add a short description...

Make sure you are in the workbook that contains the functions. Go to Tools > Macro > Macros. You won't see your functions listed here but Excel knows about them! In the Macro Name box at the top of the dialog, type the name of the function, then click the dialog's Options button. If the button is greyed out either you've spelled the function name wrong, or you are in the wrong workbook, or it doesn't exist! This opens another dialog into which you can enter a short description of the function. Click OK to save the description and (here's the confusing bit) click Cancel to close the Macro dialog box. Remember to Save the workbook containing the function. Next time you go to the Function Wizard your UDF will have a description...

Like macros, user defined functions can be used in any other workbook as long as the workbook containing them is open. However it is not good practice to do this. Entering the function in a different workbook is not simple. You have to add its host workbook's name to the function name. This isn't difficult if you rely on the Function Wizard, but clumsy to write out manually. The Function Wizard shows the full names of any UDFs in other workbooks...

If you open the workbook in which you used the function at a time when the workbook containing the function is closed, you will see an error message in the cell in which you used the function. Excel has forgotten about it! Open the function's host workbook, recalculate, and all is fine again. Fortunately there is a better way.

If you want to write User Defined Functions for use in more than one workbook the best method is to create an Excel Add-In. Find out how to do this in the tutorial Build an Excel Add-In.

Addendum

I really ought to know better! Never, ever, say never! Having told you that there isn't a function that provides the day's name, I have now remembered the one that can. Look at this example...

The TEXT function returns the value of a cell as text in a specific number format. So in the example I could have chosen =TEXT(A1,"ddd") to return "Thu", =TEXT(A1,"mmmm") to return "September" etc. The Excel's help has some more examples of ways to use this function.

Put a Permanently Open Calendar on a Worksheet

One of my most popular tutorials shows you how to build A Pop-up Calendar for Excel. It involves building a VBA UserForm and placing an ActiveX Calendar Control on it. The user right-clicks on any cell and chooses InsertDate from the menu and the calendar appears. Choosing a date from the calendar places that date into the cell.
But you can take a simpler route and have a calendar control embedded into the worksheet itself so that it is permanently visible. This requires very little code and is very easy to do. Here's how...

Display the Control Toolbox

Display the Control Toolbox by choosing View > Toolbars > Control Toolbox. Make sure that the Design Mode button (the first one on the Control Toolbox) is pressed in....

Click the More Controls button (the last one on the Control Toolbox) to display a list of available ActiveX controls and scroll down until you find the Calendar Control. It is marked with a number denoting your current version of Microsoft Office. The illustration below shows Calendar Control 11.0 indicating that it is the control version that came with Microsoft Office 11 (Office 2003). It doesn't matter which version you use. If you don't see the Calendar Control in the list follow my instructions in I Don't Have a Copy of mscal.ocx Calendar Control to obtain it.

Place a Calendar on the Worksheet

Click on the Calendar Control entry in the list then click on the worksheet approximately where you want to place the calendar. When you do this the calendar control will appear and you can move and resize it in the same way you would any drawing object...

Note that Excel has given the calendar a name (Calendar1). If you want to change the appearance of the calendar (e.g. its colour, font or style) right-click on it and choose Calendar Object > Properties and make your choices from the dialog box.

Write the VBA Code

Now you need to write the code that will make the calendar work. Right-click on the calendar and choose View Code from the context menu. This opens the Visual Basic Editor with the current worksheet's code module open and an event procedure ready for you to enter your code...

Excel has created a "Click" event procedure and the code you type here will determine what happens when the user fires the Calendar Control's Click event by choosing a date. Let's assume that you want to put a date in the currently selected cell. Enter the following text (make sure you refer to the calendar by the name that Excel gave it):

ActiveCell.Value = Calendar1.Value

The code window should look like this:

If you want the calendar always to place its date into a particular cell you can specify that cell's address in the code like this:

Range("B3").Value = Calendar1.Value

When you have finished writing your code you can close the Visual Basic Editor by clicking the Close button in the top-right corner of the window or by choosing File > Close and Return to Microsoft Excel.

Activate the Calendar

The calendar will not work until you take Excel out of Design Mode. Click the Design Mode button on the Control Toolbox so that it is no longer depressed. The calendar is now ready to work.
If at any time you want to make changes to the design of your calendar or the way it works simply display the Control Toolbox, place Excel in Design Mode and proceed as before.

Other Options

Synchronise the Calendar

You can have the calendar synchronise with the currently selected cell so that, if that cell contains a date, the calendar will show the same date. The code below uses a VBA If Statement to do this. If it does not find a date in the selected cell it makes the calendar display the current date.
Return to the same same code window as before. At the top there are two drop-down lists. Open the left-hand list (it currently shows the name of the calendar) and choose Worksheet. The Visual Basic Editor should write an empty event procedure for the SelectionChange event. If it displays something else choose SelectionChange from the right-hand list. Then enter code as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        Calendar1.Value = ActiveCell.Value
    Else
        Calendar1.Value = Date
    End If
End Sub

Multiple Calendars

You can add as many calendars as you want. Each will have its own name and a separate event procedure and code (each referring to the relevant calendar by name).

Keeping the Calendar on View

The easiest way to keep the calendar permanently on view when scrolling down the worksheet is to use Excel's Freeze Panes command. Select a cell in Column A a little further down the worksheet than the bottom of the calendar and choose Window > Freeze Panes. Now only the part of the worksheet below that cell will scroll.
But if you want to get really clever with your calendar, you can have it move each time you select a different cell...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calendar1.Left = ActiveCell.Left + ActiveCell.Width
    Calendar1.Top = ActiveCell.Top
End Sub

Hiding the Calendar

You might want to hide the calendar when the selected cell doesn't contain a date (I have combined the code for hiding, moving and synchronising the calendar here)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        With Calendar1
            .Visible = True
            .Left = ActiveCell.Left + ActiveCell.Width
            .Top = ActiveCell.Top
            .Value = ActiveCell.Value
        End With
    Else
        Calendar1.Visible = False
    End If
End Sub

Age Calcualation in Excel

There are many ways to calculate a person's age in Excel. However, most formulas are only valid for birthdates after 1/1/1900, because they rely on serial numbers.

For Birthdates After 1900

To calculate the age of a person on date since their birthdate:

=INT((date-birthdate)/365.25)

To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used to return only the number of complete years.

To calculate age in terms of years, months, and days, DATEDIF is a handy function. The following formula is similar to the one above.

=DATEDIF(birthdate,date,"y")

For Birthdates Prior to 1900

Although it is possible to create a really long formula to do the necessary text-to-date conversions necessary for dates prior to 1900, the simplest solution is to use a custom Excel function. The function AGE() below takes advantage of the Visual Basic DateDiff() function, which can handle all Gregorian dates. See the VBA help for details on DateDiff().

Function AGE(birthdate As Variant, asofdate As Variant, _
             Optional interval As Variant) As Variant    If IsMissing(interval) Then        interval = "yyyy"
    End If    AGE = DateDiff(interval, birthdate, asofdate)End Function

How Excel Works with Dates

Excel considers dates as numbers. Each date is assigned a unique serial number. For example, the 27th September 1999 was date serial 36430. Fortunately, you don't need to know this but the fact that all dates have numerical values can be very useful. Windows uses the 1900 date system in which 1st January 1900 is date serial 1, 2nd January 1900 is date serial 2 and so on.
When you type a date into a cell, Excel shows you a date but is thinking of a number.
To find out the serial number of a date, select the cell containing the date then go to Format > Cells. Go to the Number tab and click General in the Category list. The date's serial number will appear in the Sample box on the right.
You can make use of these numbers in all sorts of ways. You can add a number to a date to give a date that number of days later (or subtract a number to get a date before), you can take one date from another to find out how many days in between. There are lots of ready-made date functions too.

Working Out a Person's Age

A person's age is the amount of time since they were born (I know you know that but the computer doesn't, and we have to start thinking like the computer). So, all we have to do is put today's date in one cell and the person's date of birth in another cell, then take their date of birth away from today and you get their age - right? Well, sort of... you get a number. Because you took a date serial from another date serial you get the number of days in between*[note]. It looks like this...
In this example the formula in cell A3 is:
=A1-A2


We need to convert this number of days into a number of years. Most years have 365 days but every fourth year has 366 days. So the average number of years is 365.25. Let's modify our formula...
In this example the formula in cell A3 is:
=(A1-A2)/365.25


Note the brackets around the first part of the formula. Brackets mean "Work out this bit first...". I've used them here to stop Excel trying to divide A2 by 365.25 before taking it away from A1. Excel formulas work do any multiplying and dividing before it does adding and subtracting, but anything in brackets gets done first.
Now we can see a number of years, but it's still not quite right. We are getting an accurate result but we don't really want to see the fraction. As a last refinement we'll wrap the whole thing inside an INT() function to give us a whole number (an integer). This is better than changing the number of decimal places displayed, which would risk some numbers being rounded up and giving an incorrect result. Here's the finished result...
In this example the formula in cell A3 is:
=INT((A1-A2)/365.25)


*Note: In fact, to start with, you get another date. Confused? Don't be... Excel is trying to help but has misunderstood what we need. In date calculations, the result cell gets automatically formatted the same way as the first cell in the formula. Because the first cell was formatted as a date Excel showed you the result as a date, although you wanted to see a number. Just reformat the cell manually by going to Format > Cells > General. [back]

^ top

---

Inserting Today's Date Automatically

You can save yourself the effort of entering today's date manually. Excel has a function, TODAY(), that creates the current date. All you need to do is place this function into the age calculation formula in place of the reference of the cell that had today's date in it...
In this example the formula in cell A2 is:
=INT((TODAY()-A1)/365.25)

How accurate do you need to be?

This formula yields pretty accurate results but it isn't infallible. Dividing by the average number of days in a year works for most people most of the time, but sometimes it gets it wrong. Supposing the person in question is a child, who hasn't yet lived through a 366 day year, you should be dividing by 365 and not 365.25. So how can we get an exact, guaranteed correct figure? Read on...

^ top

---

Using Nested IF Statements to Tell It Like It Is!

Supposing it's August and you need to know old someone is...

• The person was born in 1975. How old are they? You can't say.
• The person was born in August 1975. How old are they? You still can't say.
• A person was born on 23rd August 1975. How old are they? At last! You have enough information. You can say for certain.

In order to calculate someone's age precisely you need to know the year in which they were born, and whether or not hey have had their birthday.
If the have had their birthday you subtract their birth year from the current year. If they have not yet had their birthday you subtract their birth year from the current year, and then subtract 1. Easy! We do it all the time without thinking about it. But explaining the rules to Excel is a bit more complicated. Here goes...
=IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),
 IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),
 YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1))
I've written this calculation on three lines for clarity but you should write is as a single expression without spaces. It assumes that cell A1 contains the person's date of birth. Here's what it says...

• IF(MONTH(TODAY())>MONTH(A1)
  If this month is later than the month of the persons birthday...
• YEAR(TODAY())-YEAR(A1)
  ...subtract the year in which they were born from this year because they must have had their birthday.

But what if we haven't passed the month in which they were born. We might be in that month, or we might not have reached it yet. Let's find out...

• IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1))
  If we are currently in the month of the person's birthday and it is either their birthday today or we have passed it...
• YEAR(TODAY())-YEAR(A1)
  ...subtract the year in which they were born from this year because they must have had their birthday.

But what if this isn't the month in which they were born. We know we haven't passed their birthday so...

• (YEAR(TODAY())-YEAR(A1))-1
  ...subtract the year in which they were born from this year then subtract 1, because they haven't had their birthday yet.

Phew!