VBA works... but crashes excel

Question

I am trying to make it so if information is entered into C3, it outputs C3*H2 in E3, and if information is entered into E3, it outputs E3/H2 in C3. My code works perfectly fine, but excel crashes when I change the inputted number and the macro runs.

This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SpotRate As Double
Dim srcSheet As Worksheet

Set srcSheet = ThisWorkbook.Worksheets("Input Data")

SpotRate = srcSheet.Range("H2").Value

    If Target.Address = "$C$3" Then
        If Target.Value <> "" Then
            Range("E3").Value = Target.Value * SpotRate
        Else
            Range("E3").ClearContents
        End If
    ElseIf Target.Address = "$E$3" Then
        If Target.Value <> "" Then
            Range("C3").Value = Target.Value / SpotRate
        Else
            Range("C3").ClearContents
        End If
    End If
End Sub

Note: Excel doesn't crash immediately, so I am able to edit it. It only crashes when I change the numbers in C3 or E3.

score 1 · Accepted Answer · answered Jul 06 '23 at 08:51

The changes you are making within Worksheet_Change cause it to recursively call itself which creates vast numbers of srcSheet objects until you get a "Stackoverflow"(!). You don't need the srcSheet anyway (assuming "Input Data" is the sheet concerned ...) and you can switch off (and back on) 'events' meaning the recursion will not happen. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' ensure events are always enabled when we exit this Sub, even if we have an error
    On Error GoTo errExit
    Application.EnableEvents = False
    
    Dim SpotRate As Double
    SpotRate = Me.Range("H2").Value ' use Me to refer to the Sheet

    If Target.Address = "$C$3" Then
        If Target.Value <> "" Then
            Range("E3").Value = Target.Value * SpotRate
        Else
            Range("E3").ClearContents
        End If
    ElseIf Target.Address = "$E$3" Then
        If Target.Value <> "" Then
            Range("C3").Value = Target.Value / SpotRate
        Else
            Range("C3").ClearContents
        End If
    End If

errExit:
    Application.EnableEvents = True
End Sub

Happy to help, and welcome to Stack Overflow. If you feel an answer solved the problem, please mark it as 'accepted' by clicking the check mark. This helps keep the focus on older questions which still don't have answers. — JohnM, Jul 06 '23 at 09:07

VBA works... but crashes excel

1 Answers1