Writing a Custom Function

Using VBA, Excel lets you write custom functions that you can call from any cell in your worksheet (You can also call them from macros you create in VBA, but we’ll revisit this later).  This feature is attractive when you are currently using a complicated formula to do some work in your spreadsheet, especially when you have to re-use that formula multiple times.  I find that it is easier to read through the formula when it written in VBA, rather than a long winding formula in the formula bar of Excel.

So for this post, we’re going to take a look a small custom function I’ve written to find the last day of the month.  So let’s go ahead and take a look at this example.
 
[ad#in-post-ad]

Open the VBA Window
If you want a little  additional help with getting use to the VBA editor, check out this previous post on familiarizing with the Visual Basic editor.  A quick way to get to the VBA editor is to hit the Alt+F11 key sequence (Additional keyboard shortcuts).  Once you get into the VBA editor, from the menu bar – select Insert–>Module.  This is where you are going to place your code whether you are building a Sub or a Function.  

Example Code for the Custom Function
Here is the simple custom function that I wrote to create a custom function:

 Public Function LastDayofMonth(myDate As Date)

Dim tempDate As String
Dim newDate As Date

If Month(myDate) = 12 Then
    tempDate = CStr(“1/1/” & CStr(Year(myDate) + 1))
Else
    tempDate = CStr(Month(myDate)) + 1 & “/1/” & CStr(Year(myDate))
End If

newDate = CDate(tempDate) – 1

LastDayofMonth = newDate

End Function

** If you try to copy & paste this code into your own module of your profect, double check the spacing and double quotes to make sure they are in the correct format.

Let’s take a look at the custom function LastDayofMonth step-by-step:

1. Line 1 – keyword Public  - the keyword public must be used if you want to use the function outside of the current module code.  Which we will do, because we are going to use the function in our worksheet.
2. Line 1 – keyword Function - the keyword function tells the program that this is a function and not a sub.
3. Line 1 – function name – the name of the function must not contain any spaces (Must be written like LastDayofMonth, not Last Day of Month.
4. Line 1 – Input Parameters - any values that need to be passed to the function in order for it to make its calculations are placed in the input parameters on the function.  In this example, we are passing a date which function will refer to as myDate and have a type of Date.
5. Line 2-3 – Dim Variables – as a best practice, it is important to declare any variables you will use in your code at the beginning of the function, using the DIM keyword, then by telling the function what type these variables are.
6. Lines 4-9 – Code for Custom Function – this is where you find all the work for our custom function LastDayofMonth.  All we are doing here is setting the date to the first day of the following month and subtracting 1 to get the last day of the current month (Some extra code had to be created for it to work correctly for all months).
7. Line 10 – Return the value to the calling formula - the line LastDayofMonth = newDate is the line that returns the calculate date back to the cell that called the function in the first place.  Functions must always return a value.
8. Line 11 – End Function - this tells the program that this is the end of the function and there is no more code to evaluate.

[ad#in-post-ad]

Using the Custom Function
Using the new custom function couldn’t be easier.  Now that you have it built, you call the function just like you would any other function.  In the formula bar, just type “=LastDayofMonth(“, enter your date you want to evaluate, followed by a closing parenthesis.  So let’s say your date is in cell A1 (let’s use 2/22/2009), your formula would look like this:

=LastDayOfMonth(A1)

 which will return 2/28/2009.  Isn’t that much cleaner than using an traditional formula where all the logic is in the formula bar?  Once you get the hang of it, custom functions can be a very powerful tool in your Excel projects.

More Excel Tips

• Functions or PivotTable?
• Difference Between Sub and Function