UDFs and Conditional Formatting

I came across something in the forums (where else?) this week that I did know, but had completely forgotten. It’s kind of a weird one and not always that easy to track down so I thought I’d post it here as a sort of aide memoire.

If you use a UDF in conditional formatting, you must make sure that it has error handling in it because if an error occurs in the UDF, it can stop any running code dead with no warning whatsoever. For a very good write up on UDFs in general and this issue specifically, have a read of Charles Williams’ page here. In fact, if you do any sort of complex work in Excel and you aren’t familiar with Charles’ site, stop right now and go and read all of his Excel pages. I guarantee it will be worth your time. You should also subscribe to his blog here. And finally, if you can (or can persuade your company to), get his FastExcel V3 add-in. (note to my IT department: still waiting…)

 

Now, back to our story.

The case in the thread in question was a pretty basic UDF to determine whether a cell contained a formula – simple enough code along the lines of:

 

Function CellHasFormula(rngTarget as Range) As Boolean

CellHasFormula = rngTarget.HasFormula

End Function

 

Seems innocuous enough and you wouldn’t, in general use, think it would require error handling. However, it appears that when used in conditional formatting, about the only truly safe properties of a range that you can use are .Value, .Value2 and .Formula. Anything else could be unavailable and if that is the case, your code will just stop. The sample workbook I was provided with had code called in the Workbook_Open event to set various filters on a sheet that used a UDF similar to the above to conditionally format one column of a table, but the Open event didn’t run. No errors, it just didn’t fire.

If you opened the workbook and then manually ran the Workbook_Open code it would start OK, remove filters from the sheet and then simply terminate when trying to reapply an autofilter. Again, no errors, the code just stopped.

Understandably this was proving both very frustrating and difficult to debug. (Great credit goes to Tracy at MrExcel for figuring out what the problem with this workbook was)!

 

So the moral of the story is simply: always put error handling in your UDFs!

 

Until next time.

2 thoughts on “UDFs and Conditional Formatting

  1. Chris Dollar says:

    I’m having an issue in Excel 2010. I’m calling a UDF in my conditional formatting and everything works fine, except that when the user scrolls the sheet down, the first full line displayed is partially blanked out, and when the user scrolls up, the last full line displayed is partially blanked out. This is purely a display issue because when the user selects a different sheet and then reselects the original sheet everything displays properly again. If I clear the rules from the sheet, the scrolling issue goes away. I would welcome any thoughts or advise.

    • romperstomper says:

      What’s the UDF?
      Generally I’d suggest avoiding UDFs in conditional formatting since they are really hard to debug (they can actually cause your code to silently terminate) and can slow things down a lot, so if you can replace it with something else, such as an XLM function or a formula in a helper column, you might be better off doing so.

Leave a Reply

Your email address will not be published. Required fields are marked *