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:
CellHasFormula = rngTarget.HasFormula
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.