User-defined functions in Excel

Excel lets you create your own functions. If you wanted to create a function that returned the distance between two points (x1,y1) and (x2,y2), you can create a function DIST that takes these 4 parameters, and use it as shown below.

To create such a function,

1. press Alt-F11 to open the Visual Basic Editor
2. insert a new module (Alt-I-M)
3. type the following code:

   

Anything you declare as a “Function” in Excel’s Visual Basic automatically becomes visible in the Insert-Function dialog box under the category “User Defined” (see examples). The function is normally saved with the file. This is a good idea if you’re going to distribute the file. You can also save your functions in your personal.xls file and load it on startup.

There are 3 places where I suggest using UDFs.

1. You need to repeat a formula or use an additional cell to get the job done (e.g. replace Excel errors with empty strings)
2. You can’t get the information from a formula (e.g. a cell’s background colour)
3. It’s very cumbersome to get the information using formulas (e.g. regular expressions)

Let’s take the first one: replace Excel errors with empty strings. Normally, you’d store the results in a cell (say A2), and have another cell with the formula =IF(ISERROR(A2),””,A2). Instead, create this function NOERROR:

Now you can enclose any Excel function inside a NOERROR() and it’ll filter out the errors.

Notice that cell E2 would’ve had a #N/A error if you’d just used the VLOOKUP. This function also filters out #REF, #DIV/0!, #NAME? and all other errors.

BTW, you see column F displaying the formula in column E. I didn’t type it out. That’s another UDF.

I will talk about the other two places where you use UDFs tomorrow.