Year: 2006

Sometimes you want to check the colour of a cell, or whether a cell is bold. This can be easily done with user-defined functions (UDFs). (To create a UDF, press Alt-F11, Alt-I-M, and type the code below.)

You can use ISBOLD(cell) to check if a cell is bold, and BGCOLOR(cell) to get the background colour of the cell. This lets you selectively process bold or shaded cells. The two examples below show how you can add only the cells in bold, or only the shaded cells.

Rather than use an additional column for ISBOLD or BGCOLOR, you can use an array formula, like below. (Remember to press Ctrl-Shift-Enter instead of Enter after typing this formula)

But first, you need to change the UDF to return an array rather than a single value. So IsBold will have to be modified as shown.

User-defined functions that process arrays can be very powerful. It can bring the full power of functional programming into Excel. I’ll describe some next week.

PS: In case you’re wondering, Application.Volatile tells Excel to recalculate the function every time the worksheet is recalculated. When a cell is made bold, or shaded, the value of the cell doesn’t change. So Excel doesn’t recalculate any formulas. You’ll have to manually press F9 every time to recalculate these cells. And Application.Volatile ensures that when you press F9, these functions are recalculated.

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.