CODEWITHSUNDEEP
excelvba
Zack E
Zack E

Reputation: 706

Why am I running into Stack Overflow Error

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

Upvotes: 3

Views: 213

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

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

Upvotes: 2

Related Questions