Application.EnableEvents best practice

[Yard]

Hi,

Am using a number of checks on Worksheet_Change and Worksheet_Calculate events and, depending on the result of the change, resetting a cell value.

For each of these I am using

Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True

to avoid that the change of A1's value triggers a calculation event and gets me into a loop.

However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error

The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!?!

Is there something I need to know about this technique?

 

[Richard Schollar]

Are you sure it's not happening because you are eg stepping thru code and terminate the code before the true statement is reached (I always do this)?

I usually place an error handler in the code with a line reference so that if errors occur, events are always enabled eg:

On Error Goto Exit_Here
 
'functional code
 
 
Exit_Here:
  Application.EnableEvents = True
End Sub

 

[Yard]

Not sure, it's like trying to nail jelly to a wall.

Most of my code does have something similar. However, I have two Subs which contain the code:

Sub ApplicationOFF()
With Application
     CurrentCalc = .Calculation
     .EnableEvents = False
     .ScreenUpdating = False
     .Calculation = xlCalculationManual
End With
End Sub

Sub ApplicationON()
With Application
     .EnableEvents = True
     .ScreenUpdating = True
     .Calculation = CurrentCalc
End With
End Sub

Where CurrentCalc is defined as

Public CurrentCalc As XlCalculation

At the start of any manipulations in the code I use:

Call ApplicationOFF

then at any exit point I use

If Whatever=True Then Goto Resets
...
Resets:
Call ApplicationON

I'm still auditing all the modules to find where it is (I put a Watch on, but I find that confusing as it sometimes just breaks at the header of the Sub).

I like your On Error method, but I also have a lot of individual error handling bits which change during the code. Maybe that's bad practice then?

 

[Yard]

bump

There's something unknown (to me) about the Worksheet_Calculate event which I think may be contributing to my problem.

On Worksheet "Demand" I have cells DemandChecksum1 and DemandChecksum2

DemandChecksum1=SUM(DemandTable)
DemandChecksum2=value entered by following code:

Private Sub Worksheet_Calculate()
 
If Range("DemandDateCheck1") <> Date Then
    Application.EnableEvents = False
    Range("DemandDateCheck1") = Date
    Range("DemandChecksum2") = Range("DemandChecksum1")
    Application.EnableEvents = True
    Exit Sub
End If
 
If Range("DemandChecksum1") <> Range("DemandChecksum2") Then
    If MsgBox("The demand numbers have changed!" & vbCrLf & vbCrLf &
             "Do you want to see this reminder again?", _
                vbInformation + vbYesNo, "Information") = vbNo Then
        Application.EnableEvents = False
        Range("DemandChecksum2") = Range("DemandChecksum1")
        Application.EnableEvents = True
    End If
End If
 
Application.EnableEvents = True
 
End Sub

If the code is triggered while Demand is the active sheet, then it sees the value of DemandChecksum1 as equal to that in DemandChecksum2 (which is true).

HOWEVER, if the code is triggered when the Demand sheet is not the active sheet, then DemandChecksum is not yet evaluated and therefore has no value, and therefore is not equal to the value in DemandChecksum2!!

Should I be approaching this differently, or should I store the check values in a Public variable somewhere?

Would really appreciate some guidance on this.

Thanks

 

[RoryA]

HOWEVER, if the code is triggered when the Demand sheet is not the active sheet, then DemandChecksum is not yet evaluated

I don't understand what you mean by that.

As far as EnableEvents goes, any procedure that disables events should always exit via a procedure that re-enables them, which your error handler needs to ensure too. Like Richard I tend to use something of the form:

On error goto Err_handle
...
 
clean_up:
...
   Application.EnableEvents = True
   Exit Sub
 
err_handle:
   msgbox err.description
   resume clean_up
End Sub

You should also be particularly careful if you have any statements of the form:

If .... Then Exit Sub

where you must ensure that you didn't disable events prior to that.
FWIW.

 

[Yard]

Hey, thanks for taking the time to follow this one through with me!

I don't understand what you mean by that.

I mean, if I set a breakpoint at the beginning of the event code (or Debug.Print the relevant values), then the Sum function returns a zero if the active cell is not in the Demand Sheet. If the Activecell is in the Demand sheet then the Sum function returns the value I would expect.

i.e. in my logic, at the very start of the Worksheet_Calculate event, all values are reset to zero until the Calculate event is complete?

I guess a valid question would be "does Worksheet_Calculate actually mean Worksheet_BEFORECalculate or Worksheet_AFTERCalculate"?

This is starting to get too weird.

As far as EnableEvents goes, any procedure that disables events should always exit via a procedure that re-enables them, which your error handler needs to ensure too.

...

You should also be particularly careful if you have any statements of the form:

If .... Then Exit Sub

where you must ensure that you didn't disable events prior to that.

I've audited the code now and any exits include a reference to the subroutine which sets the Application variables back to True.


I'm going to take a look at the Application Events (AfterCalculate) possibilities in a Class Module, but I still think there's something I'm missing.

 

[RoryA]

The Calculate event occurs after the sheet has calculated.

 

[Yard]

That's even weirder then. If the sheet has calculated, the code is reporting that the cell containing =SUM(DemandTable) is equal to zero when it isn't.

I've just tested a few different combinations. The SUM was summing a dynamic named range, which I thought might be throwing it, so I reverted to a fixed range, and the sum of that range STILL = 0.

I'm at a loss.

Is it something to do with disabling events WITHIN an event procedure??

What's really bugging me is that in this section:

If Range("DemandChecksum1") <> Range("DemandChecksum2") Then
    If MsgBox("The demand numbers have changed!" & vbCrLf & vbCrLf &
             "Do you want to see this reminder again?", _
                vbInformation + vbYesNo, "Information") = vbNo Then
        Application.EnableEvents = False
        Range("DemandChecksum2") = Range("DemandChecksum1")
        Application.EnableEvents = True
    End If
End If

This line runs OK
This line runs OK
This line just does not run, it doesn't error, it just doesn't happen!

Going for a walk...

 

[RoryA]

Have you compiled the project recently? Do you have an error handler in that code? (you should) Also, are there any Volatile UDFs in your workbook or do you have any activex controls linked to worksheet ranges??

 

[kelmo]

Should I be approaching this differently, or should I store the check values in a Public variable somewhere?

Would really appreciate some guidance on this.

Two suggestions that worked for me when I had a similiar issue.

1) Have you considered specifying an absolute range reference inlcuding the sheet name? i.e. Sheets("YOUR Sheet Name").Range("Your Range Name") ?

2) The other was what you suggested, which is to declare a public variable incase your code is being executed in a different module from the sheet module that triggered the event.

Not sure if it will fully help your situation, but that is how I worked around this.

Regards,
Kelmo