How to overcome "Out of stack space error" in excel?

Question

I am trying to clear the data from cell A5 to A10 if the count in A3 is not equal to 6. I have written an "If statement" but it gives me out of stack error. How do I overcome this error

I have tried an "if statement" but it gives me error.

Dim Count As Integer
Dim BundleDup As Integer
Dim duplicateall As Integer
Dim SAPError As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    Count = Range("A3").Value
    BundleDup = Range("B3").Value
    duplicateall = Range("C3").Value
    SAPError = Range("D3").Value

    If Target.Address = "$A$10" And Count = 6 And BundleDup = 0 And duplicateall = 0 And SAPError = 0 Then

        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 3
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
        Call MoveData
    End If

    If Count <> 6 Then
        Call ClearData
    End If

End Sub

The code works fine if I am not using this

 If Count <> 6 Then
        Call ClearData
    End If

But once I use this and enter value in A5 to A10, it will clear the data but it will get stuck and give me the error.

The ClearData module includes the following code:

Sub ClearData()
'
' ClearData Macro
'

'
    Range("A5:A10").Select
    Range("A10").Activate
    Selection.ClearContents
    Range("A5").Select
End Sub

Answer 1

This is how this situation should be handled:

    Public ClearingData As Boolean  'initializes natively to "False"

    Private Sub Worksheet_Change(ByVal Target As Range)
    If ClearingData Then Exit Sub 'stops the recursive loop
    ClearingData = True

    Count = Range("A3").Value
    BundleDup = Range("B3").Value
    duplicateall = Range("C3").Value
    SAPError = Range("D3").Value

    If Target.Address = "$A$10" And Count = 6 And BundleDup = 0 And duplicateall = 0 And SAPError = 0 Then

        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 3
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
        Call MoveData
    End If

    If Count <> 6 Then
        Call ClearData 'Here's where the recursive loop gets created.
    End If

    ClearingData = False

    End Sub

This prevents a recursive loop from starting and still leaves Events enabled. The code handles the specific situation completely within the function, avoiding unexpected results which could effect other functions.

If the "MoveData" function changed cell selection and you had a "Worksheet_SelectionChange" event being called, disabling events would prevent that event from being called. By using the above logic, the event would still be called. If you wanted to prevent the "Worksheet_SelectionChange" from being called from this function, you simply include the line "If ClearingData Then Exit Sub" at the start of the "Worksheet_SelectionChange" event.

Sorry, I used the term "Global" instead of "Public" in my comments above.

Answer 2

Just to add a bit around Rory's comment - your problem here is that you've created an infinite loop in your code.

From a very high level here is what's happening:

1. You change a value on your worksheet
2. The Worksheet_Change() code is called
3. This code then changes something on the same worksheet
4. This change causes the Worksheet_Change() code to run again
5. This code then changes something on the same worksheet
6. This change causes the Worksheet_Change() code to run again
7. This code then changes something on the same worksheet
8. This change causes the Worksheet_Change() code to run again
9.This code then changes something on the same worksheet

   (you get the picture...)

To get around this you need to disable any further events from being called in your code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False '// stop further events from firing

    Count = Range("A3").Value
    BundleDup = Range("B3").Value
    duplicateall = Range("C3").Value
    SAPError = Range("D3").Value

    If Target.Address = "$A$10" And Count = 6 And BundleDup = 0 And duplicateall = 0 And SAPError = 0 Then

        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 3
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
        Call MoveData
    End If

    If Count <> 6 Then
        Call ClearData
    End If

    Application.EnableEvents = True '// re-enable events

End Sub

Finally, if you're going to change application level settings in your code - I would strongly recommend you write a proper error handler into your code to revert any such settings in the event of a run-time error.