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.

Rory · Accepted Answer · 2017-06-13 07:42:15Z

10

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, 2017 at 7:42

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pᴇʜ Over a year ago

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 why it is always a good idea to use Long instead of Integer.

Jiaming Yang Over a year ago

Thank you very much! It solved my problem completely and thank you for the explanation and elaboration as well! They are really useful!

Collectives™ on Stack Overflow

Excel VBA "out of stack space" error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related