Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Events dropped | lost | blocked - Excel / VBA
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). Its 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 Kings 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Events dropped | lost | blocked - Excel / VBA
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 : 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). Its 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 Kings 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Events dropped | lost | blocked - Excel / VBA
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 a 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 -- www.alignment-systems.com "Rick Labs" wrote: 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). Its 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 Kings 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Events dropped | lost | blocked - Excel / VBA
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 : 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
How to add an ActiveX control in Excel? | Excel Worksheet Functions | |||
Excel ActiveX causes lost Class Module reference | Excel Programming | |||
ActiveX control in Excel | Excel Programming | |||
Handling Excel Events in a Designer form in an ActiveX DLL | Excel Programming |