Infinite loop and crash when trying to execute this code in Worksheet_Change method

Question

So I have developed an algorithm that breaks down a word into multiple parts based on certain rules about letter occurrences within the word. I have managed to debug the entire algorithm and now for the sake of convenience for the user, I am trying to put it into a Worksheet_Change method like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr() As String
    Dim i As Byte
    Dim j As Byte

    If (Target.Column = 1) Then
        If (Target.Value <> "") Then
            arr() = BreakupWord("perform")
            For i = 1 To 10
                Cells(Target.Row, i + 1) = arr(i)
            Next i
        ElseIf (Target.Value = "") Then
            For j = 1 To 10
                Cells(Target.Row, i + 1) = ""
            Next j
        End If
    End If
End Sub

Basically, the variable arr() stores the broken down components of the word "Target" in its array and via a For loop I try to print out the contents into cells on the worksheet considering the user has made a change only in column A of the worksheet as given by the conditions

Now, the problem is such: I tested multiple words one line after the other and the code runs smoothly. However, if I go to an already entered word in column A and delete it via Backspace or Delete, my code seems to enter an infinite loop and Excel crashes. What is possibly the problem and how can I avoid it?

Here's a good reference for [Worksheet_Change](https://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs) — kulapo, Feb 21 '18 at 12:43

score 2 · Answer 1 · 2018-02-21T12:53:25.280

You need to turn off events before changing values on the same worksheet or you trigger another event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr() As String
    Dim i As Byte
    Dim j As Byte

    If (Target.Column = 1) Then
        APPLICATION.ENABLEEVENTS = FALSE
        If (Target.Value <> "") Then
            arr() = BreakupWord("perform")
            For i = 1 To 10
                Cells(Target.Row, i + 1) = arr(i)
            Next i
        ElseIf (Target.Value = "") Then
            For j = 1 To 10
                Cells(Target.Row, i + 1) = ""    'should i be j?
            Next j
        End If
    End If
    APPLICATION.ENABLEEVENTS = TRUE
End Sub

You are not covering a situation where Target could be more than a single cell.

I do not understand the nested If ... ElseIf ... End If. It reads If not blank ElseIf blank.

Does BreakupWord return a 1-based array? By default a 1-D array is zero-based.

Oh Thanks!! I'm relatively new to VBA so I had no idea you could even turn off events. I dabbled with the If Else construct to make it a nested if without else to see if that made a difference but it didn't. Also, my 1-D arrays are always with Base 1. I use Excel 2010. Does that make a difference? I believe to make it 0-based you need an OPTION BASE 0 command at the start of the module. — Aashay Sukhthankar, Feb 21 '18 at 14:54
Well, if something is not blank then by strict definition is that the else is blank. `ElseIf (Target.Value = "") Then` could be just `Else`. You haven't provided enough detail on purpose and sample data to suggest anything else. — , Feb 21 '18 at 15:01

score 0 · Answer 2 · answered Feb 21 '18 at 15:12

you've already been told about the relevant issue of avoiding recursive call of the same event handler

here's another code that:

manages that issue
adds an error trapping to prevent any error leave you with disabled event handling
check for proper changed range to act on (one-cell range in column A)

simplifies the cell writing

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr As Variant ' a Variant can handle arrays too

    With Target 'reference changed range
        If .Count = 1 Then ' if only one cell changed
            If .Column = 1 Then ' if changed cell in column "A"
                On Error GoTo ExitSafely 'handle possible errors
                Application.EnableEvents = False 'prevent subsequent sheet changing rise and infinite loop by calling recursively thsi very same sub
                If .value <> "" Then
                    arr = BreakupWord("perform")
                    .Offset(, 1).Resize(, UBound(arr) - LBound(arr) + 1) = arr 'write next to changed cell into as many cells as array elements
                Else
                    .Parent.Range(.Parent.Cells(.Row, .Parent.Cells.Columns.Count).End(xlToLeft), .Offset(, 1)).ClearContents 'clear the content of any cell right of changed one
'                    .Offset(, 1).Resize(, 10).ClearContents ' clear 10 cells right of changed one
                End If
            End If
        End If
    End With

ExitSafely:
    Application.EnableEvents = True
End Sub

just be sure that your BreakupWord() function returns a Variant array, like for instance

Function BreakupWord(strng As String) As Variant
    '....
    BreakupWord = Array("1", "2", "3", "4")

    'in the end
    BreakupWord = Array("1", "2", "3", "4") ' or any other way to assign an array to BreakupWord 
End Function

Infinite loop and crash when trying to execute this code in Worksheet_Change method

2 Answers2