Microsoft Excel has quite a few built-in worksheet functions, such as SUM, AVG, or MAX, but there are a lot of operations that aren’t covered by the standard functions, and that’s where custom functions come into play. For instance, our Microsoft Excel add-in CDXZipStream uses a variety of custom functions for analyzing ZIP Code and address data. Here we’d like to show you how to create your own custom functions, and as an example, we’re going to create a function that formats ZIP code data into a consistent five-digit format.
The first step is to open Excel, then use ALT-F11 to open the Visual Basic for Applications (VBA) editor. All custom functions in Excel must use VBA, the programming language for Office applications. From the Insert menu of the editor, select Module. In the new module window that opens, copy and paste the following:
Function Zipformat (zipcode)
Zipformat = Left(Format(Application.WorksheetFunction.Clean(zipcode), "00000"), 5)
End Function
The custom function is called Zipformat, and the only input parameter required is the ZIP code you wish to format. The line of code that contains the formula removes non-printing characters (using the worksheet function “clean”), and restores leading zeroes to the ZIP code if they are dropped by Excel. It also converts ZIP +4 data to a five digit length.
Now use ALT-F11 again to return to the original worksheet, and let’s try it out. If we have the ZIP code 07869-4222 in cell D2, we simply use our new custom function like this:
=zipformat(D2)
And the result looks like this:
In the case of ZIP code 07869, where Excel drops the leading zero, we can also use the zipformat custom function like this:
And the leading zero is restored.
You can see that developing custom functions is quite easy, and can really help you save time with repetitive tasks. It's also possible to develop more sophisticated functions that use if/then statements, or even a dialog box for user input and messaging. However, custom functions are limited primarily to returning a value to a formula in a worksheet, and cannot do more global tasks like resizing windows or changing cell formatting. For those situations, consider recording a macro to obtain the vba code, then generalizing it to work in different scenarios.
The formula used in the zipformat function above is equivalent to the worksheet formulas used in the video tutorial Find ZIP Codes in a Radius – Update. The formulas are used to reformat ZIP Codes to ensure they are consistent with data used in a VLOOKUP formula; this is important to ensure that the VLOOKUP function works correctly.
For more detailed information on creating custom functions, please see the Microsoft article here.