5. November 2010 23:22
Custom functions, otherwise known as user-defined functions or UDF's, are an extremely useful but very often untapped resource in Microsoft Excel. Just like standard functions SUM or AVERAGE, they are used in cells formulas to perform a variety tasks, some quite complex. The difference is that UDF's can be custom designed by and for you. Sounds hard? There is a bit of a learning curve, but if you are performing repetitive calculations throughout a worksheet, it can save you lots of time and frustration, and once you get your feet wet you will come to appreciate how easy it is to apply UDF's in many situations.
For example, our Excel add-in CDXZipStream uses a custom function (designed by us!) to import data from an Access database into a worksheet cell. The custom function to import the median age for the zip code 08034 looks like this:
Don't be too concerned about the fact that this is in a worksheet formula. There is no math involved whatsoever. The formula is simply calling a function that has a specified task to perform, and the parameters within the parentheses are communicating the details of how that task gets done. In this case the custom function CDXZipCode is being called, and we're telling it to get age data for a particular zip code. If we need data like this for a lot of zip codes, it is easy to see how using a custom function can be a great alternative to manually looking up data the old-fashioned way.
So what about that learning curve? Custom functions are created using the programming language Visual Basic, which is relatively easy to learn and use. Visual Basic for Applications, otherwise known as VBA, is specific to MS Office applications like Excel. The cool thing about VBA is that you can "record" a series of actions within your worksheet, and then use the resulting code as the basis of your custom function - you don't have to create code from scratch, although you may have to modify the recorded code somewhat so it can be used in all situations. There are also numerous examples of VBA code within the Help function of the Visual Basic editor in Excel, and even more on the Web and other resources. We guarantee, once you create your own UDF with Visual Basic and see how simple (and powerful) it is, you'll soon envision a whole range of functions that can be used within your worksheets. To get started, here are some great resources:
A Microsoft tutorial for creating UDF's
Step-by-step instructions for macros and UDF's from Marshall Business School, USC
A basic, simple example from ExcelTip.com
Some nice examples of UDF's (and their VBA code) from FontStuff.com
VBA programming reference books from John Walkenbach
And last but not least, one of our YouTube videos showing CDXZipStream custom functions in action:
Zip Code Finder in Excel