WorksheetChange Event triggers 47 times?

[KKaren]

Hello,

I am a newbie in VBA and I have a question regarding the Worksheet_Change event. Let's say, anytime something changes in the worksheet, I want to write something in the next empty cell in column A. So I have the following simple code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("A" & Rows.Count).End(xlUp).Value = "" Then
        Range("A" & Rows.Count).End(xlUp).Value = "Beer"
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Beer"
    End If
    
End Sub

In the beginning, all cells in column A are empty, so I when I trigger this event, all cells from A1 to A47 have the value of "Beer". When I trigger it one more time, all cells to A94 have the value of "Beer".

Obviously, when Excel updates cell A1 with "Beer", this triggers the event once again and then it executes the part in the Else block and this process is being repeated, but my question is why exactly 47 times?!

I will be glad if somebody answers as I have tried searching here and at other forums as well but without any luck.

Thanks in advance and cheers

 

[Joe4]

Welcome to the Board!

You are lucky it was just 47 times, and you didn't get caught in an endless loop and crash Excel!

There are two very important things to know when using Event Procedures:

1. Limit it to just run on the cells you want, so it doesn't run needlessly.
In Worksheet_Change event procedures, the cell that was just updated that triggered it is defined as "Target". So you can use this to make sure that it meets certain criteria before continuing on.
For example, if we wanted it to only run on the first 10 rows, we could do this:

If Target.Row > 10 Then Exit Sub

2. If you are updating cells with the VBA code, you usually want to disable events while you are making those changes so your changes don't keep triggering more events to run (and you get caught in endless loops!). You do that by adding this line in before updating any cells in your code:

Application.EnableEvents = False

You just need to remember to turn it back on after the lines of code that make cell updates, otherwise it probably won't run the next time you make a change. So you just add a line of code after all that like:

Application.EnableEvents = True

 

[mikerickson]

You should put Application.EnableEvents = False before writing to the sheet so that the writing doesn't trigger another Change event.
Note that it is set back to True.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Range("A" & Rows.Count).End(xlUp).Value = "" Then
        Range("A" & Rows.Count).End(xlUp).Value = "Beer"
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Beer"
    End If
    Application.EnableEvents = True
End Sub

 

[KKaren]

Hello and thank you for your answers .

I know about the Target parameter and the EnableEvents property. Maybe I didn't specify my question well, sorry about that. I don't need that much to populate the next empty cell as this can be done in many ways.

My question is, why this process has been repeated exactly 47 times? Because as @Joe4 mentioned...

Welcome to the Board!

You are lucky it was just 47 times, and you didn't get caught in an endless loop and crash Excel!

...I expected that Excel will crash and I will have to close it with the Task Manager (clicking CTRL+Break and ESC do not work for some reason on my laptop).

So this is what I would like to know as I wasn't able to find any info online - why 47 times exactly? And if I amend the code a little by using a variable to count how many times this will be repeated...

Dim i As Integer
Private Sub Worksheet_Change(ByVal Target As Range)

    i = i + 1
    If Range("A" & Rows.Count).End(xlUp).Value = "" Then
        Range("A" & Rows.Count).End(xlUp).Value = "Beer"
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Beer"
    End If

End Sub

...I can see in the Locals Window that as soon as i is 47, this whole process stops...

Thanks in advance

 

[Norie]

Have you stepped through the code to see what's happening?

 

[KKaren]

Hello Norie,

Yes...basically anytime I click F8 the next empty cell in Column A is being updated with "Beer"...And this is being repeated exactly 47 times which is the mystery for me...

I'm not sure if this is only for me because as I said, I expected that this will be endless process which should crash Excel and I haven't tried this on other PCs. And I am using Excel 2016, I'm not sure if it matters.
By the way, here is a screenshot:

 

[KKaren]

Sorry for posting twice, I tried to upload the screenshot in the post but it doesn't work, so here is a link...as I as said I am a newcomer here :D

https://ibb.co/grL3Lc

 

[Norie]

So when you step through the code line by line using F8 it's getting repeated exactly 47 times?

Is there any other data on the sheet?

 

[KKaren]

So when you step through the code line by line using F8 it's getting repeated exactly 47 times?

Is there any other data on the sheet?

No, I only typed in some gibberish in some cell to trigger the event.

 

[wideboydixon]

Ran 75 times for me before I ran out of stack space. Because you're essentially calling this recursively, each time the _Change() event is triggered it saves the current "state" on the stack. Are you getting the stack space error?

WBD