Willow Tips

Creating a User Defined Function

After you have downloaded the mini-app, you can simultaneously press Alt+ F11 to open the Visual Basic editor and click on the modFunctions module. The lines of code shown below are snippets from the EQDate function. Although functions can be stored in modules with other VBA macros, to keep things simple we chose to put the function in a separate module. The function is well documented and can be copied to a module in another workbook or the modFunctions module can be moved into any Excel workbook. Although similar to VBA macro procedures, VBA functions have some important differences. The most important difference is that a function only returns a value. The syntax for declaring a function is:

Function NameOfFunction (argument list) as Type

Our user-defined function is declared as:

Function EQDate(rngD As Range) As Date

The Scope of a Function

You have the option of using the VBA keywords Public, Private or Static preceding the function. These key words declare a function’s scope. If you don’t declare a function’s scope its default is Public, which means that it is accessible to all other procedures in all modules in all of Excel’s active workbooks. If you declare a function as Private, its name will not appear under the user-defined category in the Insert Function dialog box. You would do this only if you created functions which were being called by VBA procedures and not entered into worksheet cells. Declaring the function as Static indicates the variables in the function procedure are preserved between calls.

The Type is the data type returned by the Function procedure. In this case the EQDate function will return a date.

By default user defined functions are volatile. Volatile functions will calculate when any cell on a worksheet is changed. In large workbooks, with real-time data updating and many calculations, a volatile function could hinder performance and we would advise making user defined functions non volatile with the statement:

Application.Volatile False

This should be the first executable line in your function immediately following your dimensioning of variables.

How the Function Works

The function finds the month and year of the argument date and stores these values in the iMonth and iYear integer variables. We use simple logic within a VBA Case statement to calculate the last business day for the previous quarter.

• If the month is greater than 9 then the month for the previous quarter is 9.

• If the month is between 7 and 9 the month for the previous quarter is 6.

• If the month is between 4 and 6 the month for the previous quarter is 3.

• If the month is less than or equal to 3 the month for the previous quarter is 12.

If you would like a more detailed explanation of how to use VBA’s Case statement or any other VBA key word click on the key word in the VBA editor and press F1. This action opens Visual Basic Help which will provide an in depth explanation and examples of how to use these features. Once the month is identified we create the last date of the month with an If statement and store it in the vEoQuarter variant variable. The logic is:

• If the month is 6 or 9 then the final day of the month is 30. (June or September Quarter)

• If the month is 3 the final day is 31. (March Quarter)

• If the month is 12 the final day of the month is 31 but the year needs to be reduced by 1 (December Quarter)

We then use Excel’s WEEKDAY function within an IF Statement to see if the final day of the previous quarter is a weekday.

If Weekday(vEoQuarter) = 1 Then

Excel’s WEEKDAY function takes a date as an argument and returns a number from 1 to 7 indicating the day of the week. The number 1 indicates the date is a Sunday.

If it is a Sunday, two days are subtracted to calculate the final business day and this date value is assigned to the function:

EQDate = vEoQuarter - 2

If it is a Saturday, one day is subtracted to calculate the final business day and this value is assigned to the function:

EQDate = vEoQuarter - 1

Otherwise the last date is the final business day of the previous quarter.

EQDate = vEoQuarter

Functions should have a process for handling errors. In this function if the user does not enter a date value in the cell used as the argument or some unforeseen error occurs the function will stop calculating and the #N/A error value will appear in the cell.

EQDate = CVErr(xlErrNA)

Powerful and Useful Tool

As you can see, creating a user defined function is not that difficult. Once you have created a function to calculate the end of quarter date business date, you can easily create other functions to find other values. If you find yourself nesting functions several layers deep within IF functions, you should consider writing a user defined function. It will make the formulas in your worksheet easier to understand and follow.