1

I have encountered a strange event behavior, which I do not understand. Please help. Following is an explanation of the problem.

  1. In my Worksheet_Change event, at the beggining I have set Application.events = False.
    1. Then I run main code that changes few things including formulas.
    2. After the main code is done I reset the Application.EnableEvents = True.
    3. Then the Worksheet_Change event ends at Exit Sub.
  2. Right after the code finishes it triggers Worksheet_Calculate event, even when there is no code after Application.EnableEvents = True. I do not want this event to be triggered.

Here is a snapshot of a code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Main code

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Exit Sub

Private Sub Worksheet_Calculate() 'triggered without any reason?
... 'code here
exit sub

So the question is : How is it possible that after the code ends, it immediately triggers the Worksheet_Calculate event? There is nothing after the Application.EnableEvents = True to trigger this event.

Thank you for help. I don't understand this one.

Edit and possible explanation

I've been thinking and testing. There may be nothing wrong with that behavior. Both events are triggered at the same time and it has to pick one code to run before another. The main code inside Private Sub Worksheet_Change(ByVal Target As Range) is NOT triggering the Private Sub Worksheet_Calculate(). The latter is in queue, waiting for it's turn.

kolcinx
  • 2,183
  • 1
  • 15
  • 38

2 Answers2

1

You re-enable automatic calculation at the end of your script, so it calculates when the script is finished.

When calculation is set to automatic, recalculation occurs after every data input and after certain events...

Please see this MSDN article.

Porcupine911
  • 928
  • 9
  • 22
  • Thank you Porcupine911. What you say is true, but I am aware of no reason at all (no data input or any other event) why the Worksheet_Calculate event is triggered. **There is no code or user action between the Application.EnableEvents = True and Exit Sub.** I understand that when I re-enable events, and then make changes that trigger the Worksheet_Calculate event, it must trigger. But I did not do such thing. At least I`m not aware of it right now. – kolcinx Mar 09 '15 at 13:30
  • I'm thinking perhaps the completion of a macro trggers the recalc. Maybe set up a small test? – Porcupine911 Mar 09 '15 at 16:18
  • As it turns out, you were right. I made a mistake in my thinking, thinking that my procedure triggered the calculate event, but it was triggered even before my code ran. It was just waiting in a queue. Thanks for your help and time. – kolcinx Mar 13 '15 at 11:13
0

You could perhaps apply the Skip_update method mentioned here: VBA Worksheet change event bypass? I do find it quite bizarre that the event still fires when you have turned them off.

Community
  • 1
  • 1
Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Thank you for reply Dan. I will use that Skip_update method. Anyway, I still don`t understand why the Worksheet_Calculate event triggers. Must do more testing and debugging. – kolcinx Mar 09 '15 at 13:24