See the new development.
I have a strange problem in Excel. I have a Worksheet_Change event that I am using and am trying to debug it. I save the program and open it, and suddenly the compiler does not break the error. In fact, this does not break at all !! I will put a break at the head of the sub (and the next three lines for good measure), and that just won't happen. I thought that perhaps the events were not included ... So, I set the message box as one of the first lines of code. A message box will appear .... even with a gap on it.
This happened earlier than on a separate line in another macro, and I tried to copy everything into a .txt file and paste it back into an earlier version of my program. This worked for months, but the problem has now returned.
Coding is not very important, but I will insert it below for kicks and giggles. It is interrupted without errors, I delete everything "by mistake" or not. I cut and pasted the code into a new sub, and it works great. I also checked the parameters and checked the "break in all errors". nothing, even calling undefined will not cause an error, stop the program from interrupting.
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 = "TDQ 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
New development: I followed the advice in one of the comments. "Long shot: do you have conditional formatting using UDF?" - Rory yesterday "He resolved the splitting error when I deleted the user formula in my conditional formatting. Now the compiler stops as expected, and when I comment on" iRandomNonsense ", it breaks into my command. When I return its formatting, it wraps again.
Rory, put your comment as an answer (with a slightly more detailed description of how you understood it), and I will check it.
If anyone wishes, I would really like to know the path to this failure in excel. This is similar to a utility that I could use in the future, and it really bothers me. I cannot use a custom function in a conditional format. In addition, this code was very useful for me, and I see no other way to do what I did, without a user formula in conditional formatting or a hairy auto-correction code.