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.
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.