Featured post

Fixed Income Trading: New venues

A simple question came up recently in a conversation – how many new Fixed Income trading venues are there?  I could not think of anywhere th...

Monday, 30 March 2015

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
  1. Download the code from github
  2. Create a new Excel workbook.
  3. Save the new workbook as an xlsm format workbook
  4. Enter the VBA editor
  5. Add a reference to Microsoft Message Queue 3.0 Object Library (see screenshot 1 below)
  6. Add a reference to Windows Script Host Object Model  (see screenshot 1 below)
  7. Import the modules you just downloaded from github
  8. Check that Microsoft Message Queue is running on the computer (see screenshot 2 below)
  9. In the Computer Management snap-in (see screenshot 3 below) in "Private Queues" create a queue called "ExcelIndErrorRep"
  10. In numberStationMQ execute "ExcelIndustrialisationErrorReportingToMSMQ" 
  11. 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".

See also
Excel industrialisation...Part One "Code to write code"    
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"  
Excel industrialisation...Part Ten "Enumerating COMAddins"

No comments:

Post a Comment