Excel VBA - industrialisation...Part Seven "Central Error Reporting"

Following on from part six we now look at the central repository of error message reporting.

The implementation here is one that uses Microsoft Message Queuing to report to a central hub (not shown here). The beauty of MSMQ is that the error message can be captured even when the computer is not connected to a network, so there is no limp implementation that only works when you are connected to a network.

So, let's look at how this works. The code is, as for all part of this series, on github

Download the code from github

Create a new Excel workbook.

Save the new workbook as an xlsm format workbook

Enter the VBA editor

Add a reference to Microsoft Message Queue 3.0 Object Library (see screenshot 1 below)

Add a reference to Windows Script Host Object Model (see screenshot 1 below)

Import the modules you just downloaded from github

Check that Microsoft Message Queue is running on the computer (see screenshot 2 below)

In the Computer Management snap-in (see screenshot 3 below) in "Private Queues" create a queue called "ExcelIndErrorRep"

In numberStationMQ execute "ExcelIndustrialisationErrorReportingToMSMQ"

Have a look again at "Private Queues" in the Computer Management snap-in. You should see that there is a new message in the queue (see screenshot 4 and 5 below)

(1) MSMQ Reference in Excel VBA IDE

(2) MSMQ Executable shown in Task Manager

(3) Computer Management - Private Queues

(4) New Message Added to MSMQ

(5) New Message Added to MSMQ

Please note that this is not a full implementation, it's a way of showing an integration. So there is not the level of error handling that is needed in a true production setup. Also, the security level and the MSMQ implementation are not production ready. It's cleaner to use an XML format for the message sent by MSMQ rather than the concatenation of data as shown here.

Consider the case where an investment bank or other large firm has a portfolio of '000s of spreadsheets with VBA code. Using this technique it's possible to have a spreadsheet "phone home" to an operational support team to notify them that an error has occurred. This can then allow the firm to instrument the code base, find errors systematically and then conduct rolling improvements to the quality of the code base.

All within the Excel environment, with no need to re-write existing code, just use "code to write code" to add in "error handling" and "central error reporting".

Global Electronic Trading

Search This Blog

Excel VBA - industrialisation...Part Seven "Central Error Reporting"

Comments

Post a Comment