VBA compiler not breaking / trapping on errors and no error message, when using UDF in conditional formatting

Question

See new development.

I have a strange problem in Excel. I've got a Worksheet_Change event that I'm using and I'm trying to debug it. I save the program and open it back up and all of a sudden the compiler is not breaking on an error. In fact, it's not breaking at all!! I'll put a break at the head of the sub (and the next three lines for good measure) and it just does not happen. I thought maybe the events weren't enabled... So, I've put a message box as one of the first lines of code. The message box pops up.... even with the break on it's line.

This happened once before on a particular line of another macro and I tried copying everything into a .txt file and pasting it back into an earlier version of my program. This worked for months, but the problem now came back.

The coding isn't really important but I'll paste it below for kicks and giggles. It's aborting without an error wether I remove all the "on error"'s or not. I've cut and pasted the code into a new sub and it works fine. I've also checked the options and checked "break on all errors." nothing, even an undefined call won't throw an error, will stop the program from aborting.

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Dim aVar() As String
Dim iVar As Integer
On Error GoTo 0

MsgBox "you changed something" 'this is a msgbox that does pop up during execution, verifying that the sub did in fact, run. 
Call iRandomNonsense 'this is a sub that does not exist which the compiler does not tell me about any more.

If target.Columns.Count = 1 Then
    Select Case target.Column
    Case 2
        If target.Count = 1 And Cells(target.Row, 1) = "" Then _
            Cells(target.Row, 1) = Now
    Case 8
        On Error GoTo ExitSub
        aVar = Split(target.Value)
            For Each sVar In aVar
            If IsNumeric(sVar) And Len(sVar) = 5 Then
            If sVar > 30000 Then
            aVar(iVar) = "ALN-" & sVar
            Else
            aVar(iVar) = "DEV-" & sVar
            End If
            End If
            iVar = iVar + 1
        Next
        target.Value = Join(aVar, " ")
    End Select
Else
    On Error GoTo ExitSub
    target.Resize(target.Rows.Count, Cells(target.Row, target.Columns.Count).End(xlToLeft).Column + 1 - target.Column).Select
    Select Case Selection.Columns.Count
    Case 18, 21  'Paste from Scrap report
        Debug.Print "Paste from Scrap report" & Now
        Call purgeCheckboxes
        With Selection
            .Copy
            .PasteSpecial (xlValues)
        End With
        OnSelRow(4, 8).Select
        Selection.Copy Destination:=OnSelRow(1)
        'desc
        OnSelRow(6) = OnSelRow(10)
        OnSelRow(4) = OnSelRow(15)
        With Range(Cells(Selection.Row, 10), Cells(Selection.Row + Selection.Rows.Count - 1, 10))
            .FormulaR1C1 = _
                "=RC[2]&"" ""&RC[3]&"" ""&RC[-3]&"" ""&RC[4]&"" ""&RC[7]&"" ""&RC[11]"
            .Copy
            .PasteSpecial (xlValues)
        End With
        Application.CutCopyMode = False
        Range(Cells(Selection.Row, 7), Cells(Selection.Row + Selection.Rows.Count - 1, 7)).FormulaR1C1 = "TRUE"
        Range(Cells(Selection.Row, 8), Cells(Selection.Row + Selection.Rows.Count - 1, 8)).FormulaR1C1 = "T D Q 9 A Wav DMR"
        Range(Cells(Selection.Row, 9), Cells(Selection.Row + Selection.Rows.Count - 1, 9)).FormulaR1C1 = "2"
        Range(Cells(Selection.Row, 11), Cells(Selection.Row + Selection.Rows.Count - 1, 11)).Select
        Range(Selection, Cells(Selection.Row, UsedRange.Columns.Count)).Select
        Selection.ClearContents
        ActiveWindow.ScrollColumn = 1
    End Select
    Call RefreshCondFormats
End If
ExitSub:
On Error GoTo 0
Application.EnableEvents = True
End Sub

A new development: I followed the advice in one of the comments. "Long shot: do you have any conditional formatting that uses UDFs? – Rory yesterday" It resolved the breaking error when I deleted the User Formula in my conditional formatting. Now the compiler stops like its supposed to and when I comment out "iRandomNonsense" it breaks on my command. When I put the formatting back it screws up again.

Rory, put your comment down as an answer (with a little more description as to how you figured this out) and I'll check it off to you.

If anyone's willing, I'd really like to know a way around this glitch in excel. It seems like a utility I might use in the future, and it really bothers me I can't use a user function in a conditional format. Also, this code has been very useful to me and I don't see any other way to do what I've done without either a user formula in conditional formatting, or a hairy autocorrect code.

Answer 1

If you are using UDFs in conditional formatting, this kind of problem can occur. It is most likely if you don't have error handling, or if you try and access any properties other than .Value or .Formula. It is often possible to work around the issue - e.g. using an alternative calculation, or putting the UDF in a cell - but occasionally you may just be out of luck.