Why does a UDF driving Conditional Format stop VBA Execution?

[Col Delane]

In the last few days I have had a problem with VBA (to apply a criterion in a filter on a different column to that with the Conditional Formatting!) not executing in a workbook using the following UDF to drive CF to alert users to cells containing a formula:

Public Function UDFHasFormula(rCell As Range) As Boolean
Application.Volatile
UDFHasFormula = rCell.HasFormula
End Function

See this thread: http://www.mrexcel.com/forum/excel-questions/782589-called-subs-not-executing.html

It took a while and some detective work by Tracy from MrExcel (User = starl) & me to discover the cause. My workaround was to locate the UDF in a helper cell (i.e. one other than the one to which the CF was being applied). Why this resolves the issue I don't know.

I then did another search of the Forum and discovered a number of threads from Forum users wanting to highlight cells containing formula, many with responses from Tom Urtis suggesting the use of the Defined Name method - where a name (say "CellHasFormula") RefersTo =GET.CELL(48,INDIRECT("rc",FALSE)) is then used as the function in the custom CF formula. [I have had this in my Excel toolbox for quite some time but keep forgetting that it's there!!]

So in a test copy of my problem workbook I have now replaced the UDF with the Defined Name as the CF driver (and deleted the helper cell as well) and the VBA works fine.

My question:
Does anyone have any idea (and a plausible explanation) as to why the UDF stops [certain] VBA from executing but the Defined Name method does not?
Is it because:

1. the method uses VBA?
2. the UDF is referring to its host cell within the CF formula rather than a helper cell?
3. the Application.Volatile line in the UDF code?
4. somethin' else?

Cheers

 

[RoryA]

I don't know the underlying reason but I have seen it before. Does it make any difference if you remove the application.volatile line? (It's unnecessary)

 

[Col Delane]

No it doesn't make any difference whether the Application.Volatile line is included or not.


BTW, I forgot to mention that the code only stops when the following intermediate sub (whether called by the WorkbookOpen event or manually) is run (stops after SetAutofilters has executed) :

Sub DoFilters()
    SetAutofilters            ' calls sub to turn off any existing AutoFilters and then applies new autofilters to the database
    CompletionDateFilterToggle           ' calls sub to apply filter criterion to 'hide' any non-blank row (which represent a completed task)
End Sub

However, if I manually run CompletionDateFilterToggle on its own, the code within that sub works fine.
So the problem (at least to me) seems to be in the passing of control (or not) between sub's in the VBE (e.g. from DoFilters to SetAutofilters [problem here] then back to DoFilters so it can call CompletionDateFilterToggle)

Cheers

 

[RoryA]

It appears the the UDF is causing an error when called by the conditional formatting, which is causing all code to halt. If you simply add On Error Resume Next to the start of the UDF it fixes the problem. Really, all UDFs should have error handling of some kind, since you don't really know how and when they may get called in the calculation chain (see here for more information).

Interestingly it only seems to be certain properties of the range that cause an error here: .HasFormula, .HasArray, .CurrentRegion for example. Using .Value or .Formula doesn't appear to be a problem so it would seem that the other properties are not available at the time of execution for some reason.

 

[Col Delane]

It appears the the UDF is causing an error when called by the conditional formatting, which is causing all code to halt. If you simply add On Error Resume Next to the start of the UDF it fixes the problem. Really, all UDFs should have error handling of some kind, since you don't really know how and when they may get called in the calculation chain (see here for more information).

Interestingly it only seems to be certain properties of the range that cause an error here: .HasFormula, .HasArray, .CurrentRegion for example. Using .Value or .Formula doesn't appear to be a problem so it would seem that the other properties are not available at the time of execution for some reason.

Thanks Rory - I've not seen that site before with explanations about UDfs, etc. I will have a good read.

Error Handling is one of my coding weak points - though I'm getting better since installing M-Z Tools which has a tool to add/insert an EH at the click of a button. And in reference to this particular situation, I didn't think an EH was necessary given the simplicity of the code (one line ".HasFormula") - thinking 'what errors could occur?'

I've decided to use the Defined Name method for detecting formulae in future as it is far easier, more transparent and less prone to error than a UDF.

Thanks to all those who helped get to this level of understanding of an intriguing problem.

Cheers