Excel VBA "out of stack space" error

Question

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim b As Integer
    b = 0

    Dim cell As Range
    Dim rgn As Range

    Set rgn = Range("f2:f200")

    For Each cell In rgn
        If IsEmpty(cell) = False Then
            b = b + 1
        End If
    Next

    Range("d2").Value = b
End Sub

Hi, I met a problem when trying to run the following piece of Excel VBA code. A message box will pop out and say there is a

"out of stack space"

problem to line Set rgn = range("f2:f200"), then another message box will pop out and say

"method 'value' of object 'range' failed"

I don't know what is wrong... Thank you very much for helping.

score 9 · Accepted Answer · answered Jun 13 '17 at 07:42

9

The problem is that you are changing cells in a Change event, which will trigger the event again, and again, and again...

You need to disable events temporarily:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim b As Integer
    b = 0

    Dim cell As Range
    Dim rgn As Range

    Set rgn = Range("f2:f200")

    For Each cell In rgn
        If IsEmpty(cell) = False Then
            b = b + 1
        End If
    Next
    Application.Enableevents = False
    Range("d2").Value = b
    Application.Enableevents = True
End Sub

answered Jun 13 '17 at 07:42

Rory

32,730
5
32
35

As a Side note: I recommend not to use `Integer` at all and always use `Long`. If the range `rgn` changes you would easily run into an overflow. Read [here](https://stackoverflow.com/a/26409520/3219613) why it is always a good idea to use `Long` instead of `Integer`. – Pᴇʜ Jun 13 '17 at 07:45
Thank you very much! It solved my problem completely and thank you for the explanation and elaboration as well! They are really useful! – Jiaming Yang Jun 13 '17 at 07:56

Excel VBA "out of stack space" error

1 Answers1