ActiveX Control Events dropped | lost | blocked - Excel / VBA

[Rick Labs]

I'm trying to track down any info on when VBA code gets halted and/or
ActiveX events are dropped. (See Richard King's comments below).

It’s important that the Excel hosted VBA code I'm working on responds to
EVERY event fired by the ActiveX control. It's O.K. to have them back up a
bit but I must be sure that EVERY event raised in the ActiveX control
gets "sunk" by Excel VBA code.

To that end I made a testing sample spreadsheet located at:
http://clbcm.com/events example.xls

You have to save the file and open it in Excel. It doesn't run correctly
from the IE browser.

The spreadsheet has an internal timer that fires an event every second.
Each of three event sinks (located in three user forms,) “listen†for those
events, and when received, they write a 1 to the spreadsheet, illustrating
they indeed got it.

All you have to do is edit a cell while the program is running to see that
events are dropped, gone forever. However, in the majority cases of moving
windows around, resizing, etc, events are not dropped, they are just cued
back which works fine for me.

If anyone has ANY experience with this please describe what you encountered
and/or how you insured all events were processed and not dropped. Any code
examples would also be most appreciated.

Thanks.

Rick
-------------------------------------------------------------

Richard King’s Conclusions:

1. Events are NOT dropped as a result of Excel executing VBA. My
spreadsheet was processing so much VBA that at the end of the test, the
Excel events were being logged more than 4 minutes behind the VB events,
but not one was dropped.

2. Events are NOT missed as a result of Excel recalculating the
spreadsheet.

3. However, events ARE dropped whenever you type into a cell or the formula
bar. From the time you press the first key to the time you press enter,
Excel simply ignores events from all sources. This is not a problem with
the TWS ActiveX control, it's a problem with Excel itself.

4. Also, events ARE dropped when Excel displays a message box or a modal
dialogue box (such as File > Open).

Recommendations
---------------

I believe you CAN use the ActiveX control successfully with Excel 2000, but
only subject to some serious caveats:

- any required user input must be via controls such as text boxes, check
boxes, combo boxes etc (typing in these does not cause events to be
dropped)

- the user must not type into any Excel cell (unfortunately locking all the
cells does not help, because an attempt to type into a locked cell causes
Excel to display a message box, which itself causes events to be dropped!)

- the user must not use any menu options that display dialogue boxes (such
as File > Open or Tools > Data Analysis)

- VBA code must not use the msgBox function

 

[keepITcool]

Give m some time with 2 extra doevents and it will work.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim yet As Double
Sheet1.Range("A:C").Delete

For i = Val(Me.TextBox1.Value) - 1 To 0 Step -1
yet = Timer + 1
Do While Timer < yet
DoEvents
Loop
TextBox1.Value = i
oRjl.sub1 (i)
DoEvents
DoEvents
Next

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rick Labs wrote :

 

[John.Greenan]

This is something that has been a bit of a bane of my work life over the last
few years. I can state as confidently as possible that it is NOT possible to
ensure that ALL events are processed ALL of the time in a simple ActiveX
control, excel, COM event world.

One of the other posts refers to "DoEvents". This idea is a non-starter -
once the system is running under heavy load you'll see Excel behave badly.

The length of time ActiveX events is queued up for appears to be variable -
so you could find that a heavily processor load on a PC with lots of events
flying around will cause the event queue to miss out.

Just as background, I work for a software company designing Excel based
front ends for investment decision support tools (www.alignment-systems.com).
We've spent a lot of time on this one and the answer really is that you have
to reengineer your software. I don't know the specifics of your control, but
a few ideas are:

1. Build an Excel RTD component as a wrapper around the ActiveX control.

2. Have the events written to a database and have Excel query the database
when it can.

3. Send the events over MSMQ and use the delivery validation mechanisms
therein to ensure that the message is processed.

4. One route we tried was the application.interactive=false path. Create
your excel instance from within a VB component and then have the VB component
try and set application.interactive=false when you want to take control of
Excel and process an event. This worked ok for a while, but did not work out
in the specific environment that we have (other 3rd party components got in
the way)

Good luck

 

[keepITcool]

OP's example:
3 modal userforms + 1 classmodule
1 form modifies the class which then raises the events
to be picked up by the 2 other forms..

in HIS case the doevents is a starter... as it solved his problem in
his example.

It does not solve the problem of buffering events when in "editing
mode","Print Preview" or (other) modal dialogs described in Richard
Kings' quote.

looking back at my earlier post:
insert a DoEvents AFTER every RaiseEvent statement in the RJL
classmodule would be more logical than including the doevents in the
caller.


I agree your solution of RTD server and database looks "solid".
Looks likes it creates a separate thread to catch and buffer the
events, so excel can pick m up when load allows.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John.Greenan wrote :