Custom Functions in Microsoft Excel - A Piece of Cake

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:

  =CDXZipCode("08034","MedianAge")

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 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, watch the video below showing CDXZipStream custom functions in action:

or see the YouTube version:  Zip Code Finder in Excel

Comments (2) -

  • i follow what you are suggesting about custom UDF's and i have some ideas i want to program. How do i reference the Access database (in my UDF) just like the CDXZipStream UDF's are accessing this information??? -bill
  • You would need set a reference to Microsoft ADO (ActiveX Data Objects) in your VBA project and then use this to connect to the Access database.  You can set up references in Office VBA by using Tools-Reference in the VBA editor.

    Once the connection is set up you can then query your database and then return these results as part of your custom function.  You can Google "ADO and Access Database" to find a variety of technical articles how to do this.  One issue is that you want to make the connection a global variable that so that you connect only once.  This prevents delays in subsequent function calls.




Add comment

Loading