Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Problem with Application Events Class

I've done this with my own code and several examples, online.  I set up a class module with events at the application level. I have, for example, a worksheet activate event.

Everything works fine, HOWEVER, if at the spreadsheet, I hit the DESIGN icon to go into design mode (e.g., to add active-X controls, or whatever) and then exit out of design mode, the application level events don't work, unless I reinitialize them.

Is this supposed to happen?  What could be the cause of it happening, if not?  I can pick any of a number of examples online and make this happen - even the simple example from Chip Pearson's website.

Any thoughts?  This is really cramping my style, lol!

Thanks!

Dave
0
dlmille
Asked:
dlmille
3 Solutions
 
smartchapsCommented:
I think upto Excel 2007 it was not the problem,i.e. reinitialization was not required. But with Excel 2010 it is required.
0
 
JonMnyCommented:
I could only duplicate the issue by making the appevent class declaration a local varaiable, Global scope always fired  (using office 2007)
0
 
dlmilleAuthor Commented:
Here's a simple example we can work from (Excel 2007).

This happens in Excel 2003 as well as Excel 2007 for me.

Dave
App-Events-Demo-r1.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zorvek (Kevin Jones)ConsultantCommented:
Dave,

This is not a bug or an anomaly. When you enter design mode the VBA run-time is reset. Same as when you hit the reset button on the Debug toolbar or click "End" on a debugger dialog box. When the VBA run-time is reset all variables are reset which means any object variables are set to nothing. This has been the case since VB was integrated into Office.

There are only workarounds. What I do is capture a few common events such as Workbook_SheetCalculate and Workbook_SheetSelectionChange and check if the environment has been reset and needs to be reinitialized. I use a single global object variable to determine the VBA run-time state. In your case I would use ExcelEvents in the module EventsSetup. Either make it public or add a public function that checks it and returns a Boolean indicating the state of the VBA run-time. I have also tried Excel and Windows timers in the past but the downsides outweigh the positives so I decided some time ago it isn't worth the trouble.

As you process this information and consider calling your therapist, keep in mind that users are not going to be switching in and out of design mode so this is really just a convenience for you while you build. Also consider that Active-X controls were given to Bill by the Devil and they have been wrecking havoc on the Excel development community for decades. Shun them! They are not worthy of our attention!

Kevin
0
 
dlmilleAuthor Commented:
@JohMny - check out what I posted.  What variable should be "global" if its not already?

@Zorvek - I hear you on having some local events to hit the Application level event initialization - seems a pain that this goes down when in/out of design mode.  It doesn' trash local events, just the Application level events in the class module.  There doesn't seem to be a draw-back to reinitialize at any time, so not sure I need the global variable to understand the state of the VBA run-time.  Sounds like you may not agree, and if that's the case, what's the drawback to reinitialize (versus checking global variable, first)?

The controls are here to stay - at least with the users out there, so I'm trying to make life easier using them, regardless.  I've not had the train wrecks you portend, but then I personally don't "load up" my sheets with lots of them so perhaps I've been lucky.

Dave
0
 
zorvek (Kevin Jones)ConsultantCommented:
"Local events" and application events are going through different paths. When local events are generated Excel looks for event handles in the various worksheet and workbook modules. When application events are generated Excel looks only for instantiated Application objects and then looks for event handlers in those instantiated objects. No instantiated objects, no triggered event code.

When you switch to design mode Excel resets the VBA run-time which destroys any instantiated objects. The same thing happens when you edit the conditional compiler arguments line in the VBA settings. The reason is that those actions are signals that some big ass changes are being made and any compiled p-code will probably get out-of-sync with the VBA code. It's a safety precaution to prevent other problems that would annoy you more.

There is no problem to re-initializing every chance you get but it's generally bad programming practice if you can easily prevent it. You only want to run code when you need to run it. It's considered bad practice to consume resources if you don't need to in order to get the job done.

The wreckage I have seen in the past with Active X controls were with only one control on one sheet in the entire workbook. It starts out as a control with a mind of it's own such as a font size that increases with every click on the control. It ends with a corrupted workbook. Disappearing controls and controls that appear on a sheet other than the one on which I placed them are two other symptoms. I have seen it happen enough to conclude it is not worth the risk, especially the risk of workbook corruption. And, of course, the problem you are now experiencing - you don't have that problem with Forms controls. This is a common topic among the Excel MVPs and the Excel PMs at the MVP summits and Microsoft acknowledges that Active X controls are a little dicey but they have yet to do anything about them - they don't want to remove them because too many yahoos are using them and they don't want to fix them because they have much bigger fish to fry. Like being able to re-size the Evaluate Formula dialog...oh, wait, they haven't fixed that one either!

Kevin
0
 
dlmilleAuthor Commented:
I just had an epiphany, when I was wrapping my code into an Add-in and the PROBLEM DID NOT HAPPEN.

epiphany - Perhaps Application level events - which are designed for all workbooks - are really meant to be used in add-in type cases?

Not sure, but everything's working now as an add-in...

Dave
0
 
zorvek (Kevin Jones)ConsultantCommented:
>when I was wrapping my code into an Add-in and the PROBLEM DID NOT HAPPEN.

That's because the add-in is a separate VBA project (and hidden) so it will not be reset when you flip the active workbook into and out of design mode. There is no risk to it so Excel doesn't reset it.

>Perhaps Application level events - which are designed for all workbooks - are really meant to be used in add-in type cases?

That has nothing to do with why Application-level events were created or why they do or don't work. Application events were created to give workbooks in general access to events generated by other workbooks. You can also "subclass" other objects like controls.

Kevin
0
 
dlmilleAuthor Commented:
Thanks for the validation on the first, and the learning on the second.

Your thoughtful responses are always appreciated!

Cheers,

Dave
0
 
dlmilleAuthor Commented:
Do you have a link on subclassing controls?

Thanks

Dave
0
 
zorvek (Kevin Jones)ConsultantCommented:
No. I've just done it. It's the same basic approach as with the Application object. But know that you will not get all the control events that are generated when the control is on a form. I've used it with disappointing results. Here is a case where I did manage to get it to do what I wanted: display help when the mouse moved over the control on a form.

Option Explicit

Public WithEvents control As MSForms.CheckBox

Private moHelpControl As Object
Private msHelpText As String

Private Sub Control_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

' Handle the key press event for the check box control.

    DisplayHelp

End Sub

Private Sub Control_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

' Handle the key up event for the check box control.
   
    If KeyCode = 9 Then DisplayHelp

End Sub

Private Sub Control_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

' Handle the mouse move event for the check box control.
   
    DisplayHelp

End Sub

Private Sub DisplayHelp()

' Display the help for this control.
'
' Syntax
'
' DisplayHelp()

    moHelpControl.Caption = msHelpText

End Sub

Public Sub InitializeHelp( _
        ByVal oHelpControl As control, _
        ByVal sHelpText As String _
    )

' Initialize the class.
'
' Syntax
'
' InitializeHelp(oHelpControl, sHelpText)
'
' oHelpControl - The control in which to display the help text.
'
' sHelpText - The help text to display for this control.

    Set moHelpControl = oHelpControl
    msHelpText = sHelpText

End Sub
0
 
dlmilleAuthor Commented:
Last question.

Thanks for the tip on the Forms controls - I've done that before, but didn't remember it as "subclassing".  Will read up...

I assume this is not needed for Active-X controls, and I know you're opinion on them, lol...  but I was wondering if you could do something like this with them at the application level...

Dave
0
 
dlmilleAuthor Commented:
Impeccable responses.

Thanks!

Dave
0
 
zorvek (Kevin Jones)ConsultantCommented:
No. Each object generates it's own events. And it isn't true sublclassing. VB is not a true OOPL and so some object oriented features are not available. But they can be simulated as I have illustrated.

Kevin
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now