Microsoft Excel is a familiar tool for maintaining lists and tables as well as for performing calculations using its rich spreadsheet function library. Sometimes, however, Excel does not have a pre-built function that suits a specific need. Not to worry, using a working understanding of Visual Basic for Applications (VBA), you can create your own user-defined functions to fill the gap!
Let’s say you have a spreadsheet with a list of tasks and assigned resource(s). For each task you want to enter the duration, namely how many workdays it will take to complete the task. You also want to enter a Start Date and have the spreadsheet calculate the Finish Date. Or you may want to enter the Finish Date and have the spreadsheet calculate the Start Date. Weekends and company holidays are not to be included.
So how do you do this? Here’s how…
Step 1: Setup the spreadsheet with the necessary columns. You may already have a spreadsheet with columns for Task, Assigned to, Duration, Start, and Finish. If not, here’s a good starting point.
- Format the column cells to reflect the kind of data they will contain. For example, Duration should contain whole numbers. Start and Finish should contain dates.
- Fill the Start and Finish cells with a color to indicate they will be calculated.
Step 2: Create a Holidays named range. This is where you will maintain the list of company holidays so that these dates won’t be considered when calculating Start or Finish dates.
- Enter the holiday dates in a blank column on the same spreadsheet where the tasks are maintained. You can hide this column later if you don’t want it displayed. An alternative approach is to maintain the holidays in a column on a separate tab that can also be hidden.
- Select the cells that will contain the holiday dates and enter the range name in the Name Box. The example shows cells A2:A5 selected, corresponding range Holidays, with holidays 8/31/2012, 9/3/2012, and 9/4/2012 entered.
Named ranges are a good way to reference cell ranges within spreadsheet formulas and user-defined functions.
Step 3: Create the user-defined function. This is where the fun begins!
- Open the Visual Basic for Applications Editor via shortcut ALT+F11.
- Insert a new Module via selecting Insert | Module.
- In the Project Explorer, double-click on the Module created (e.g. Module1) so that it is active. Open the Add Procedure dialogue via selecting Insert | Procedure….
- Name = dNewDate: Function Names cannot have a space. ‘d’ as the first character reflects a naming convention that indicates the user-defined function returns a date.
- Type = Function: User-defined functions are ‘Function’ procedures. They calculate and return values to the spreadsheet. They cannot do things that change a spreadsheet such as inserting rows or changing font colors.
- Scope = Public
- Click on OK when done. The dNewDate function shell is created.
- First, important key points to remember about user-defined functions.
- Required: All code is entered between the Public Function and End Function statements.
- Required: A variable whose name is the same as the user-defined function is used to return the calculated value to the spreadsheet (e.g. dNewDate).
- Required if the function will use spreadsheet values: The items within the parenthesis after the function name represent the spreadsheet values used within the function and are called ‘arguments’.‘ByVal’ means a copy of the values are used in the function and not the actual spreadsheet values.‘As…’ indicates to the function the kind of data the spreadsheet’s values represent.
- Optional but Recommended: ‘As…’ after the arguments section indicates the kind of data that will be returned to the spreadsheet.
Next, let’s look at each section of the dNewDate user-defined function.
Text that follows an apostrophe is considered a comment and appears in green. Comments provide helpful information about what a user-defined function does or what a section of code does.
The On Error statement enables basic error-handling code at the end of the procedure.
Variables and constants used within the procedure are declared within this section.
Validating data passed from the spreadsheet to the procedure is performed here. If the values passed are not correct, a message box displays, the Reference Date is returned to the spreadsheet, and the procedure ends.
Tip: This code is not needed if the validation is performed within the spreadsheet via Data Validation. Avoid reinventing the wheel by using native Excel functionality whenever possible instead of writing custom code!
Initializing variables used within the procedure is performed here.
The ‘Multiplier’ section sets the variable to the value that will result in the calculated date being earlier or later than the Reference Date (dDate). The calculated date will be earlier if it will be the Start date and later if it will be the Finish date.
The ‘Holidays’ section builds a temporary String variable containing all of the company holidays defined within the spreadsheet. This variable ensures that calculated Start and Finish dates do not consider defined holidays when derived.
The calculated Start or Finish date returned to the spreadsheet is determined here.
Starting with the Reference Date, the code checks whether it is a weekend or a defined holiday. If it is, the code remembers the day was counted. If it is a work day, the code remembers the day was counted and that it is a workday. The calculation repeats with each calendar day after the Reference Date until the number of work days counted equals or exceeds the number of work days in the spreadsheet. The last date calculated is returned to the spreadsheet.
With the On Error statement at the beginning of the procedure, errors encountered while the code executes are handled here. A message box displays with error information. One benefit of using simple error handling is that errors aren’t as likely to lock up your system.
Step 4: Create the user-defined function spreadsheet formulas. This is where you’ll enter the dNewDate spreadsheet formula and arguments to calculate a Start or Finish date. You can do this in one of two ways:
- Enter the spreadsheet formula and arguments ‘free-style’. The arguments must be entered in the order in which they appear within the user-defined function.
- Create the spreadsheet formula and arguments via the Insert Function and Function Argumentsdialogues.
- Select the cell where the dNewDate spreadsheet formula will be located.
- Click on Formulas | Insert Function.
- Select User Defined from the Or select a category combo.
- Select dNewDate.
- Click on OK.
- Set the function’s arguments.
- Click on OK.
To edit a previously entered spreadsheet formula, select the cell containing the formula and then click on Formulas | Insert Function. The Function Arguments dialogue will appear.
Tip: User-defined functions reside within the spreadsheet in which they were created. To use them within any spreadsheet you open, create the VBA code within a separate workbook and save the workbook as an Excel Add-In. To enable the new Excel Add-In:
- Excel 2007: Click on the Office Button / Excel 2010: Click on File.
- Excel 2007: Click on Excel Options / Excel 2010: Click on Options.
- Click on Add-Ins.
- In the Manage combo, select Excel Add-Ins.
- Click on Go.
- Check the Add-In you created.
- Click on OK.
- Restart Excel.
Additional user-defined functions created for use in multiple workbooks should be done within the Add-In. Creating a Holidays tab within an Add-In is not recommended because the tab is cumbersome to access for maintenance.
Summary: In this article, you learned how to create user-defined functions that supplement Excel’s rich spreadsheet function library. They can be as simple or as complex as needed… the sky is the limit!