Microsoft Office Pro 2007 Power Tip Use dynamic e

Today’s guest blogger is Joel Graff, Operations Field Engineer with the Illinois Department of Transportation.

Many of the Access projects I’ve developed have used VBA extensively, and the more I became involved in developing user interfaces, the more I saw a need to customize the behavior of the controls that Access provides.  Developing this sort of solution gave me the architecture to be able to develop much more complicated control interactions and aesthetics without getting lost in a mess of spaghetti code, ambiguous function references, and endless form/control qualifications.

A demo database is available here,Windows 7 Professional Product Key, so if you’re interested, download a copy and read on,Windows 7 Professional!

Here’s how the demo database works:

Rather than trapping a control’s event in a form module, I created a class module (referred to as a “control event handler”) which has all the event-handling stubs contained within.  Of course, the main member of this class is a local WithEvents reference to the form’s control.

Once the event is triggered in the control event handler, the handler then uses the Application.Run method to execute a callback function stored in a separate module.  The name of that function is passed to the event handler on initialization and can change at any time in the course of the program.  The control  event handler passes to the callback the reference to the control itself, an enumeration indicating the control’s type (in lieu of the ControlType property, which not all controls support), and a string describing the event which was triggered.  After that is a series of optional parameters containing anything that is passed back from the original event call (e.g., MouseMove has Button, Shift, X, and Y for parameters).

So why do it this way? In most cases, you probably won’t want to.  But here’s why I like it:
Once you’ve pushed the callback to a different module, you have to make sure the parameters you need are passed, and you’ll likely need to reference the original control.  This architecture allows me to standardize my callback declarations (or ‘signatures’).  That is,Microsoft Office Home And Business 2010, every callback has three basic parameters:  a reference to the control, an enumeration describing the control type, and a string of the event name.  Further parameters can be added to the signature for values that are returned by the event, or a blanket series of optional parameters can be added to every callback signature just in case. I don’t have to load a ton of functions when the form loads.  By separating the callbacks into separate modules, I reduce form-loading time. I can reuse my callbacks.  Of course, I can do this the standard way, as well.  But the standard way requires me to manage passing the parameters and control references from the form-level code to the callback.  These classes do all that for me.  I just need to provide a callback with standard signature and make sure I accommodate whatever extra parameters the target event will likely pass to it.  Further, the callback’s standard declaration provides me three things:  a direct reference to the control, an enumeration of the control’s type, and a description of the event, simplifying the logic required to have a common callback process multiple events from multiple controls. Global callbacks can be implemented.  That is, if I want to implement a global callback that gets called anytime one form’s controls get called,Microsoft Office 2010 Professional, or one type of control is called, or perhaps whenever *any* control is called (or any combination of these), I can do that. Type-checking / object validation / error-trapping, etc, that I don’t have to implement in every function that takes calls from an  event.
Some of the drawbacks include:

Increased complexity in the class implementation.  This comes especially because you cannot define one event handler to catch events from multiple controls.  For example, CommandButton, OptionGroup,Microsoft Office Pro 2007, and Form all have Click events, but I’d have to create a separate event handler class for each control in order to trap their separate Click events.  However, the callback for their click events could all be the same function.  I should point out that once the classes are implemented, using the classes isn’t particularly complicated. Additional function calls.  Some would say this would cause performance bottlenecks, which may be the case.  As I see it, however, user-triggered events don’t happen very quickly, so I don’t see the additional overhead of the extra classes or using the *really* slow Application.Run method as really posing any problem.  In fact, in my general experience, even the MouseMove event (which would trigger most frequently) doesn’t really appear to suffer from this overhead. Application failures don’t recover very well.  Debugging is especially problematic as, quite often, when an error is encountered, VBA may let you fix it and continue, but the event handlers will quit responding.
The demo database gives a fair idea of how it works, but you need to explore the code to really understand it.  I have tried to comment it as thoroughly as possible.  Note the relative simplicity of defining an event handler and callback in the Startup module.  Also note the sparseness of code in the Form module.  In fact, the Form module could be completely empty in many cases.  However, you must have, at very least, an empty module for this to work.

The demo demonstrates changing a control’s callbacks on-the-fly, using a global callback, managing events at both the form and the control event handler class level, and managing all events through one common call-back function.


Joel Graff
Send your Power Tips to Mike and Chris at accpower@microsoft.com. <div