VBA stack overflow

Question

Consider the following VBA (whatever the latest version is) Subroutine:

Private Sub Worksheet_Change(ByVal Target As Range)
    'If Not Application.Intersect(Range("H11:H13"), Range(Target.Address)) Is Nothing Then
            Call ThisWorkbook.testEIRR
    'End If
End Sub

Now, if the commented out lines are NOT commented out, then changing the contents of cell H11 does the right thing (]updates the value computed by some function and stores it where it is supposed to). However, with the condition commented out (as above), I get a stack overflow (o, the irony!)

Any idea how to fix it?

It depends on what `testEIRR` does. I assume it updated a cell in this same `Worksheet` based on that error and since a cell is updated that will trigger the `worksheet_Change()` event which will fire this code which will cause that cell to get updated which will cause the `worksheet_change()` event to fire which will update that cell which will... Set `Application.EnableEvents = False` at the top of this subroutine and then toggle it back on when it's complete. — JNevill, Nov 12 '19 at 17:57
Well done @IgorRivin ! I've never seen a stack overflow from VBA! — HackSlash, Nov 12 '19 at 18:33

John Alexiou · Answer 1 · 2019-11-12T18:04:32.137

You get a stack overflow because Worksheet_Change calls Worksheet_Change which calls Worksheet_Change .. etc

You need to disable events before calling your function and enable them afterwards.

Consider

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("H11:H13"), Range(Target.Address)) Is Nothing Then
        Application.EnableEvents = False
        Call ThisWorkbook.testEIRR
        Application.EnableEvents = True
    End If
End Sub

See this answer to a similar question.

VBA stack overflow

1 Answers1