How to Create an Excel UDF for Reusing It Like an Excel Built-In Function

A reusable Excel UDF must resemble a built-in function in the Function Wizard; Why? You are so familiar with writing formulas that any bizarre aspect of a function alienates you.

The fact is that even a Robust Excel Custom Function with no information about arguments and function itself is of little use. This is especially true in the long term as you forget what the function is about.

Follow this 5 steps ritual for a better packaging so you can use User Defined Functions like Excel Built-In Functions.

1. Make your Excel UDF Trustworthy (Sine qua non condition)

Because you reuse robust Custom Functions in which you trust.

Here are some tips…

  • Plan Before You Create the Excel UDF. Stay on the right side; spend more time on diagramming/planning than in writing/debugging. Save time in the long run and keep peace of mind about your constructs.
  • Foolproof and test. Check the Math and trap wrong assumptions entered by users. Avoid your function shows wrong results
  • Comment each part of the code for easy debugging and maintaining. Specify the purpose and describe what each chunk does.

2. Name Appropriately the Function Procedure, Arguments and the Workbook

Use names with meaning so that you can depict in your mind what the item is about. Be short and descriptive.

  • The function proceduremust resemble an Excel Function if possible. Add a distinctive text (a suffix may help), see below:
    • SUMIF
    • SUMIFS
    • SUMIF2_UDF
    • SUMIFBYCOLOR_UDF
  • The arguments must receive your attention because a good name increases the usability and the sense of trust in users. A good argument name will transmit the message to the users without an extensive description.
  • The Workbook Add-in name will allow you to pick the correct library from the Add-Ins dialog (a prefix may help). See graphic 3

3. Add Function Description, Arguments descriptions and Append your Excel UDF to a Category of the Function Wizard

This info is a decisive aspect of your User Defined Function; this data really makes it organic. However, it is not so straightforward to complete.

Here’s how to do it…

Add Function description and assign your UDF to a category with MacroOptions Method

If your function has less than three arguments this simplified method may attend your needs.

Put this method inside your function procedure:

Application.MacroOptions Macro:=”COUNTIFCOLOR”, Description:=”This function counts the cells that match the given color”, Category:=”My Functions”

Achieve the results shown in graphic 1.

Graphic 1

Custom Function Description and Category with MacroOptions

Here’s the process in detail: Add Excel UDF/Custom Function to a Category & Add a Description

Important notice: you cannot add arguments descriptions with this method

Add Function description, argument description and set your Excel UDF to a category with the Method of Laurent Longre (MVP), Jan Karel Pieterse (MVP) and Jurgen Volkerink aka keepITcool

It is time to borrow a developer approach to turn your User Defined Function into a Built-In Function.

Read this fantastic article from these Excel Mavens and download the workbook “RegisterUDF6.xls” here: Registering A User Defined Function With Excel

Then…

Follow the explanations contained in the Introduction worksheet. If you have troubles proceed this way…

  1. Rename the workbook RegisterUDF6.xls as suggested in step 2 above
  2. Remove the module MFunctions that contains the custom functions examples
  3. Import the modules that contain your functions
  4. Add Function description and Arguments descriptions by using the sheet FunctionList of “RegisterUDF6.xls” book. Set each UDF to a category (clean the sample data provided)

They assume this method will be used by developers so be cautious.

Here is the result you’ll get…

Graphic 2

Custom Function Help, Arguments Descriptions and Category

4. Keep Custom Functions in Separate Workbooks Grouped by Category

Each Workbook Add-in will be an entry of the Add-ins dialog (see graphic 3). Take into account these two things:

  • Use category names so you can predict what to expect in the given library. For example: Math Functions, Budget Analysis Functions, etc
  • Use a standard naming for easily identification, for example: a prefix may help you group your functions

Graphic 3

Excel UDFS in the Add-Ins Dialog

5. Save your Excel Add-In Workbooks in a Central Location

It is a good practice to store your Excel Workbooks add-ins in your own folder, this way your files are not under risk to be deleted in a system restoration or back-up. This library will increase its reliability through time as you improve it constantly and know it better.