VBA - Handling events from an indefinite number of event sources

This article describes how to handle the events from a potentially large number of objects of the same type. Even if you do not know at design time how many objects there will exist and need to be observed for events at run time.

The WithEvents declaration and its limitations

In VBA you can use the WithEvents declaration within a class module to declare object variables, which expose events. You can then write your own event procedures to handle those events. This is the same as if you would write an event procedure for the Open-Event of an Access Form or the Click-Event of a Button.

If you need to handle the events from an unknown number of objects in your application, this simple approach does not work. You need to work around the limitation of the WithEvents declaration being limited to the declaration of a distinct variable.

The sample scenario

This article was inspired by a question asked on the UtterAccess-Forums. That question was regarding handling the events of an Outlook ContactItem. So the example is based on this scenario. You will need to add a reference to the Microsoft Outlook (2010 or 2013) object library for the sample code to work.

While this example is partially based on Microsoft Outlook objects, the same approach works with any object that is exposing events. It even works with your own class modules written in Microsoft Access VBA. Furthermore this is not limited to Access, but can be implemented in any Application supporting VBA.

In this sample scenario I assume, we want to handle the Write-event of Outlook contact items the user edits in Outlook.

To achieve this, we need to handle the Outlook Application event ItemLoad to get a reference to any item opened in Outlook. This event passes the loaded item into the event handler we create for it. There we assign this Item-object to our class scope variable m_outlContact. Note that m_outlContact has been declared WithEvents. So we can now handle the write event of the Outlook Contact Item in the event procedure.

Basic sample code

If you want to try out the sample code, there are few simple steps required as a prerequisite.

• Create a new Access database

• Create a new Form in the database and add a button to it

• Open the VBA editor and use there “Tools”->“References” menu to add a reference to the “Microsoft Outlook xx.x Object Library”

• Now paste the sample code below into the VBA class module of your form.

• As final step go back to the form design view, select the button and assign an [Event Procedure] to its click event. Click the “…”-Button to make sure it is linked to the Command0_Click procedure in the sample code.

Here is the sample code:

To try the code, click the button once and then edit and save a ContactItem in Outlook. Be aware that the message boxes might be displayed in the background of your Outlook windows but still will block the Outlook UI.

Spotting the limitation

This works well for a single Contact Item. But if you think about it for a moment, you should notice a significant problem with this code.

Yes, right. This works only as long as the user opens only one contact item in Outlook at a time. If the user opens another Contact before closing the first one, we will lose track of the first ContactItem instance, because we overwrite our class level variable m_outlContact with a new contact item if another item is loaded. The events from previous ContactItems would not be handled in our code any more.

Handling the events from objects within a collection

In VB.net it would be possible to attach an event handler to an object, put that object in a collection and still handle the events the object fires. Even if there is no distinct variable pointing to this instance of the object. – This would very much simplify the implementation. Unfortunately in VBA this is not possible.

The VBA workaround, a wrapper class

In VBA it is only possible to attach event handlers to object variables that are declared WithEvents. If you put the object the variable is pointing to into a collection and reuse the original variable, the events fired by the original object will not be handled any more.

The only way to handle the events of multiple objects is to wrap a custom class that handles the events around them and put the wrapper object together with the event source into a collection.

This wrapper class only needs a variable, declared with WithEvents, to store the reference to the Outlook ContactItem and the actual event procedure.

For better encapsulation I make this variable private and add a property procedure to access the private variable. – This is not required for the sample to work, but I think it is much better programming style and it requires only a few additional lines of code.

Below is the resulting class module. To use this code, create a new Class Module in the VBA editor, paste in the code below and save it under the name ContactItemWrapper.

Now we got a simple wrapper class. To use it, we need to modify the sample code, we began with, in the class module of the form.

Storing our objects in a collection

Instead of just assigning the Outlook ContactItem to a class scope variable, we need to create a wrapper object and set its InnerContactItem-property to the ContactItem. Then we add the instance of the wrapper to a collection.

I implemented a property ContactCollection to access the private m_ContactCollection-Variable. The property procedure is a very simplified (non thread-safe, restricted to our particular ContactCollection) adaptation of the Singleton-Pattern. This property procedure ensures that there will always be an instance of a collection, but one only, when we access it.

The code in our sample form’s class module, except the Command0_Click procedure, should now be replace with this code:

To try the code, click the button once and then open several ContactItems in Outlook. Then edit and save some of them in random order. Observer that for each written (saved) ContactItem our event handling code in the Access application will be called. – Still be aware that the message boxes in the background will block the Outlook UI.

The key point to learn

Notice that we do not declare a variable for the contact item in the form’s class module. The variable for the contact item as well as our wrapper are local only to the m_outlApp_ItemLoad-procedure.

On class level, we only declare the collection to hold our wrapper object instances. As the wrapper object handles the events for each wrapped ContactItem, the event procedure in the wrapper class will still be called. So you can either put the complete event handling code in the wrapper class or you can call any public procedure in your Access application to process the event.

Sensible enhancements

As the sample code intentionally only covers the bare basics, there are several aspects of it that should be improved in a real application.

Putting the collection outside the form class

In this sample, the ContactCollection holding our wrappers and their ContactItems is local to the form class only. If you need this collection to be available beyond the scope of the form, you could just cut and paste the private collection variable and the corresponding property procedure to a normal VBA module. – This does not require any further changes to the code.

Managing the collection

There is only code to add Items to our collection but not to remove them. As soon as a ContactItem is closed in Outlook its instance in our wrapper object will not point to valid object any more.

As we only access this object from within the handler to its own events, this will not cause any errors. Still it is a (small) memory leak, as our wrapper around it will never be disposed. You would need to handle the ContactItems Close- and Delete-events to remove the wrapper object around the Item from the collection and dispose it.

Uncoupling the event handling from the worker code

In a real world application, the handling of the events will involve more code, than just displaying a message box. It might improve the structure of our code, if we don’t put this code inside of the wrapper classes.

Even if we put the worker code in a public procedure in a module and call it from the wrapper object, the code would be tightly coupled to wrapper. One possible way to decouple this would be to create our own custom collection class instead of using the default VB-Collection class. Then we could have our own collection expose custom events. We would handle those events then, instead of calling a function directly from our wrapper class.

This would achieve a loose coupling between the event handling from the contact items and the rest of our code. - But as this covers several other advanced VBA programming topics, I will save these improvements for (a) later article(s).

So that’s it for now. – I hope you enjoyed the article and learned something new about the possibilities of VBA programming.

Drop me a line via email, if you have got any feedback on this article or the whole site.

Share this article: