Why am I running into Stack Overflow Error

Question

I've run into a Stack Overflow issue with the below code. The error happened on Set rngToCheck = Range("GenLoanAmount") and I'm not really sure why since there doesn't seem to be enough happening that would cause that issue. This code does work, so if deemed necessary by the community I will post it on Code Review. Thank you for taking a look.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngToCheck As Range
    Set rngToCheck = Range("GenLoanAmount")


    If Not Intersect(Target, Me.Range("FloodIns")) Is Nothing Then
        If Intersect(Target, Me.Range("FloodIns")) = "Y" Or Intersect(Target, Me.Range("FloodIns")) = "y" Then FloodEmail.Show
    End If

    If Not Intersect(Target, Me.Range("FloodInsAct")) Is Nothing Then
        If Intersect(Target, Me.Range("FloodInsAct")) = "Y" Or Intersect(Target, Me.Range("FloodInsAct")) = "y" Then FloodActEmail.Show
    End If

    If Not Intersect(Target, rngToCheck) Is Nothing Then
        If Intersect(Target, rngToCheck) Then
            rngToCheck.NumberFormat = "$#,##0.00"
        End If
    End If

    If Not Intersect(Target, Me.Range("genCloseDate")) Is Nothing Then
        If Intersect(Target, Me.Range("genCloseDate")) <> vbNullString Then FundDateCalc
    End If


End Sub

When you say the code does work, do you mean it only works when you don't get the error, or that it works whether or not you get the error? — Mistella
– Mistella, Commented Apr 3, 2019 at 20:11
Does this code, specifically FundDateCalc, trigger a change in the worksheet? To be safe, events should be disabled and then enabled at the end. — BigBen
– BigBen, Commented Apr 3, 2019 at 20:13
@Mistella, I added the line of code that the debugger highlighted, and it works unless i get the error then Excel just crashes. — Zack E
– Zack E, Commented Apr 3, 2019 at 20:15

Mathieu Guindon · Accepted Answer · 2019-04-03 20:15:44Z

2

This procedure handles the Worksheet.Change event, which Excel fires whenever a cell changes on that worksheet.

The handler changes cells on that sheet (or invokes code that makes changes on that sheet), and is therefore re-entrant. Disable events at the start of the procedure, and re-enable them before the end of it, to avoid recursing until the call stack can't take it anymore (i.e. until you get a "stack overflow" error).

Dim wasEnabled As Boolean
wasEnabled = Application.EnableEvents

Application.EnableEvents = False

'...code...

Application.EnableEvents = wasEnabled

answered Apr 3, 2019 at 20:15

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zack E Over a year ago

Thank you! Whats sad is that i have used Application.EnableEvents so many times before.

BigBen Over a year ago

@ZackE on the bright side, you've contributed a "stack overflow" question to Stack Overflow, pretty legit if you ask me.

Collectives™ on Stack Overflow

Why am I running into Stack Overflow Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related