CODEWITHSUNDEEP
excelvba
CDutton
CDutton

Reputation: 43

"Out of Stack Space" Error triggered by simple Worksheet_Calculate

Basically what I'm trying to do is record the value of a particular cell (in this case B26) when the worksheet opens, and then copy the value of B26 to cell B25 if the value of B26 changes.

In Module1 I have the following code:

Public PrevVal As Variant

In ThisWorkbook I have the following code:

Private Sub Workbook_Open()
PrevVal = Sheet13.Range("B26").Value
End Sub

And in Sheet13 I have the following code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

If Range("B26").Value <> PrevVal Then
    Range("B25").Value = Range("B26").Value
    PrevVal = Range("B26").Value
End If

End Sub

The strange thing is that this code seemed to be working fine, then started giving me an "Out of Stack Space" error seemingly out of the blue (error occurs on the If Range("B26").Value <> PrevVal Then line of code.

If it helps, cell B26 is a formula that returns either "low", "moderate", or "high" based on the value of another cell. Here is the formula:

=IF('Source Data'!$C$53=0,"low",IF(AND('Source Data'!$C$53>0,'Source Data'!$C$53<3),"moderate",IF('Source Data'!$C$53>2,"high","N/A")))

Any idea why I'm suddenly getting this error?

Upvotes: 2

Views: 1515

Answers (1)

brettdj
brettdj

Reputation: 55692

Your code is calling itself in a continuous loop when the the PreVail range updates, you can turn off the code loop by disabling Events like so (so the Calculate event runs only once, including any updates if PrevVal is different to B26)

See this MSFT link for general information as to the Out of Stack Space error.

Good question!

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False

If Range("B26").Value <> PrevVal Then
    Application.EnableEvents = False
    Range("B25").Value = Range("B26").Value
    PrevVal = Range("B26").Value
    Application.EnableEvents = True
End If

End Sub

Upvotes: 2

Related Questions