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.

Answer 1

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