×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel .EventsEnabled Worksheet Function

Excel .EventsEnabled Worksheet Function

Excel .EventsEnabled Worksheet Function

(OP)
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")

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

RE: Excel .EventsEnabled Worksheet Function

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.

RE: Excel .EventsEnabled Worksheet Function

(OP)
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!
:)

RE: Excel .EventsEnabled Worksheet Function

Bruce,
Include line Application.Calculate in your routine, it should make a difference.

RE: Excel .EventsEnabled Worksheet Function

(OP)
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.....?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources