Question: Stack Overflow error in Excel VBA code

[montyb]

This code, when executed, crashes Excel with a Run-time '28' Out of Stack Space whenever I make a change to one of the dropdowns in my spreadsheet. I can't figure out what is wrong with it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Factor1, Factor2, factor3, factor4, OverallRisk As Integer

If Sheet4.Range("d6").Value = "Low" Then
Factor1 = 1
ElseIf Sheet4.Range("d6").Value = "Medium" Then
Factor1 = 3
Else: Factor1 = 5
End If

If Sheet4.Range("d23").Value = "Low" Then
Factor2 = 1
ElseIf Sheet4.Range("d23").Value = "Medium" Then
Factor2 = 3
Else: Factor2 = 5
End If

If Sheet4.Range("d38").Value = "Low" Then
factor3 = 1
ElseIf Sheet4.Range("d38").Value = "Medium" Then
factor3 = 3
Else: factor3 = 5
End If

If Sheet4.Range("d50").Value = "Low" Then
factor4 = 1
ElseIf Sheet4.Range("d50").Value = "Medium" Then
factor4 = 3
Else: factor4 = 5
End If

OverallRisk = Factor1 + Factor2 + factor3 + factor4
Sheet4.Range("d76").Value = OverallRisk
End Sub

 

[Eric W]

What's happening is that when you set cell D76 to the Overall Risk in the last line, that counts as a sheet change, so this macro is called again. And again, and . . .

To avoid that, you can temporarily disable Events like so:

Application.EnableEvents = False
Sheet4.Range("d76").Value = OverallRisk
Application.EnableEvents = True

 

[Joe4]

One thing jumps out at me.

A Worksheet_Change event procedure runs whenever a cell on your sheet is changed.
Look at the last line of your code. What is it doing?

Sheet4.Range("d76").Value = OverallRisk

It is changing a value on your sheet.
So guess what that does? It calls your event procedure again.
Depending on what is happening, you could be putting yourself in an infinite loop that never stops!

What you want to do is temporarily disable the events while you make you change so you don't get caught in an infinite loop, i.e.

Application.EnableEvents = False
Sheet4.Range("d76").Value = OverallRisk
Application.EnableEvents = True

See if that fixes your issue.

 

[montyb]

That worked perfectly, thanks!

 

[Eric W]

Glad we could help.