Excel .EventsEnabled Worksheet Function 1

[BruceMutton]

Hi all
I have written a simple user defined function to make it apparent on the worksheet when events are no longer being triggered. (I have an iterative solver type routine that is triggered by the calculate event, rather than using an on sheet button)

In a worksheet cell, I would enter something like;

=IF(bEvents(),"OK","Events are disabled&quot

It kind of works, in that while VBA code is processing, it gives the results one would expect- ie TRUE when events are enabled, and FALSE when not, however once no code is processing, it returns FALSE regardless of the state of EventsEnabled (as confirmed in the VBE watch window)
What is going on?

Am using Excel 97 SR2

Function Code follows:

Public Function bEvents() As Boolean
'To be used from Worksheet
'Returns state of event enabling as boolean
'Bruce Mutton 25 Sept 2001
Application.Volatile (True)
If Application.EnableEvents Then
bEvents = True
Else
bEvents = False
End If
End Function

 

[yakpol]

Bruce,
Any change in the worksheet will trigger defined function to recalculate. I tried your example on Excel 2000 enabling and disabling events from instant panel in VBE (ctr-G). The function value would change when I type any character on the worksheet. Hope it helps.

 

[BruceMutton]

Thankyou yakpol.
I tested the code with Excel 97 as you described, which is how I knew it was not working on my machine.

I used the worksheet with Excel 2000 last night, and noticed that it always returned FALSE (regardless of the state of .EnableEvents) I did not think to test it from the immediate panel though, as I was focusing on something else at the time.

To repeat and expand, to try to clarify what I am observing.

It kind of works, in that while VBA code is processing, (as observed with .DisplayUpdating set to TRUE, and with VBA in Debug mode) it gives the results one would expect- ie TRUE when events are enabled, and FALSE when not,

however once the code has finished processing, the value returned on the worksheet reverts to returning FALSE regardless of the state of EventsEnabled (as confirmed in the VBE watch window)

This means the function is of little use, as most of the time VBA is not running, and so most of the time it returns the wrong value!

 

[yakpol]

Bruce,
Include line

Application.Calculate

in your routine, it should make a difference.

 

[BruceMutton]

Good idea, but no difference, with this line at end, begining, or beginning and end of function.
(The function is only called by excel in response to a 'calculation' in any case, so it is probably just causing a second calculate each time)

On every calculate, it just flashes up TRUE for an instant, and then reverts to FALSE.....?