VBA - Installing and calling Microsoft Message Queue

A question was asked on StackOverflow the solution to which was using a message queue. The questioner had a database and wanted notification to reach his Excel workbook application when the number of rows in a database table changed. On the database side one would use a database trigger to detect insertions but how would the database trigger notify Excel?

Whilst it is possible to open an ADODB Connection and sink events, holding that connection open for however long seemed wrong. Better if the notification was loosely coupled, so the database trigger could send a message to a message queue which would reliable be received by Windows and readable for later. A COM event is not persisted, if the Excel workbook application was closed or not running for some reason then the COM event would be lost.

So in the given scenario a message queue is better than sinking COM events. So for the benefit of that StackOverflower, this post highlights how to install and call Windows Message Queue.

Steps to install Microsoft Message Queue

I have verified these steps on my Windows 10 box

1. Open Control Panel (classic view I loathe Windows 8 app view).
2. Within control panel click on Programs and Features
3. Within Programs and Features on the left hand side click on the blue yellow chequered shield labelled 'Turn Windows features on or off'

4. Within the Windows Features dialog box scroll down until you find Microsoft Message Queue (MSMQ) Server, the check the checkbox and click OK.

5. Wait patiently as progress dialog box appears for two minutes during install. The messages that pass by are 'Searching for required files', 'Applying changes' and finally 'Windows completed the requested changes.'
6. Click Close.

Creating a Queue

So to create a queue we have to start with the control panel again. Here are the steps ...

1. Open Control Panel (classic view I loathe Windows 8 app view).
2. Within control panel click on Administrative Tools
3. Within the Administrative Tools explorer click on Computer Management, this launches the Computer Management Microsoft Management Console (MMC)
4. In the Computer Management MMC expand the Services and Applications tree node, then Message Queueing tree node, then Private Queues tree node
5. On the Private Queues node right-click to get context menu and select New -> Private Queue

6. In the New Private Queue give you queue a name, I named mine myfirstqueue and the code samples below are coded to this name but you can obviously change the names to suit.
7. Click OK and you're done.

Do Add Security

I'm not sitting in an enterprise as I write this so I cannot inform as how to secure your message queue at the enterprise level but obviously you would want to tie down access. As a message queue is a Windows resource then it can be permission-ed like other Windows resources such as directories and printers etc. Indeed the security tab on the queue's properties looks pretty much like a standard Windows permissioning dialog.

Leaving a message queue with no security is a big no-no.

Finding the Message Queue Type Library

So after installing I had thought it would be simple to go to the VBA IDE and find a brand new choice in the Tools->References dialog box but it seems the type library is not registered correctly and so it will not appear in the list. Nevertheless, I have managed to track it down. So in the Tools->References dialog box, click Browse and then go find the file ...

c:\windows\system32\mqoa.tlb

And once checked you should see the following ...

Note how the filename has been resolved to the SysWOW64 directory. Now we can write some VBA code to both send and receive messages.

VBA code to send and receive messages

So here is some client code which was quite straight forward. The only trap was when calling Receive you should definitely supply a timeout otherwise you code will hang which for a single threaded environment such as VBA is bad.

Option Explicit

Private Function GetQueueInfo(Optional ByVal sPrivateQueueName As String = "myfirstqueue") As MSMQ.MSMQQueueInfo
    Dim oQueueInfo As MSMQ.MSMQQueueInfo
    Set oQueueInfo = New MSMQ.MSMQQueueInfo

    Dim sComputerName As String
    sComputerName = Environ$("COMPUTERNAME")
    
    oQueueInfo.FormatName = "direct=os:" + sComputerName + "\PRIVATE$\" + sPrivateQueueName
    Set GetQueueInfo = oQueueInfo
End Function

Sub TestSend()
    Dim oQueueInfo As MSMQ.MSMQQueueInfo
    Set oQueueInfo = GetQueueInfo("myfirstqueue")
    
    Dim oQueue As MSMQ.MSMQQueue
    Set oQueue = oQueueInfo.Open(MSMQ.MQACCESS.MQ_SEND_ACCESS, MSMQ.MQSHARE.MQ_DENY_NONE)
    
    Dim oMessage As MSMQ.MSMQMessage
    Set oMessage = New MSMQ.MSMQMessage
    
    oMessage.Label = "TestMsg"
    oMessage.Body = "Message queues facilitate loose coupling in a distributed component system."
    
    Call oMessage.Send(oQueue)

End Sub

Sub TestReceive()
    Dim oQueueInfo As MSMQ.MSMQQueueInfo
    Set oQueueInfo = GetQueueInfo("myfirstqueue")

    Dim oQueue As MSMQ.MSMQQueue
    Set oQueue = oQueueInfo.Open(MSMQ.MQACCESS.MQ_RECEIVE_ACCESS, MSMQ.MQSHARE.MQ_DENY_NONE)
    
    Dim oMessage As MSMQ.MSMQMessage
    '* IF YOU DO NOT SUPPLY A TIMEOUT THE NEXT LINE WILL HANG!!!
    Set oMessage = oQueue.Receive(ReceiveTimeout:=1000) '1000 milliseconds = 1 second
    If Not oMessage Is Nothing Then
        Debug.Print oMessage.Label
        Debug.Print oMessage.Body
    End If
End Sub

Client C# Code

If in the use case given at the top of the article, the database is SQL Server so the database trigger would be written in C#.

So I have not given any C# code because I think one can easily use a Primary Interop Assembly to deal with a COM library so the above code can be easily translated. However, if writing a .NET language you should use the .NET API and the System.Messaging namespace is where you will find the equivalent .NET objects.

Client Python Code

If the database in the use case is PostgreSQL one can write database triggers in Python and so some Python code would be useful.

So here is a good link to an great article Send MSMQ messages using python and actually that article inspired me to write this one.

VBA - Python - COM Interoperability error when you forget error handler

I have been writing quite a lot of Python using the gateway class pattern. Python does COM inter-operability well including throwing errors with rich error info. However, do remember to provide an error handler or you get a strange error message.

Background: Error Handling in COM

I sketch the briefest of details below on HRESULT and ICreateErrorInfo but for a fuller read see this link.

HRESULT

Consider the following extract from the IDL of the Microsoft Scripting Runtime, specifically the Dictionary's RemoveAll method. (There is no return value to confuse matters). Understand that the HRESULT signals the success or failure of the method invocation. An HRESULT is a 32-bit integer, if it is zero (symbollically S_OK) then the method succeeded but any other value is an error, so 32-bits can support billions or error numbers. You can see how this 'error space' is divided at this link.

interface IDictionary : IDispatch {

        [id(0x00000008), helpstring("Remove all information from the dictionary."), helpcontext(0x00214b41)]
        HRESULT RemoveAll();

ICreateErrorInfo interface

Nevertheless, VBA programmers are used to error description strings and the HRESULT does not carry this information. Instead, an ErrorInfo object is created by calling the ICreateErrorInfo there you can see the error Description being settable. Python supports rich error handling via ICreateErrorInfo.

Code

So some code we illustrate..

Python Class

So the following Python code is a class with two methods, the first method PythonThrowsAnEror results in Python throwing an error because we try to call a method that does not exist. In the second method, we throw a custom error; this could be a business logic error as well as a system error.

class ThrowsErrors(object):
    _reg_clsid_ = "{FD538AF6-6B9C-4E53-8013-93D74665F23E}"
    _reg_progid_ = 'PythonComTypes.ThrowsErrors'
    _public_methods_ = ['PythonThrowsAnEror','ThrowMyOwnError']

    def PythonThrowsAnEror(self):
        a=self.noexist()

    def ThrowMyOwnError(self):
        a=2+2
        raise COMException(description="Throw an error!", scode=winerror.E_FAIL, source = "ThrowsErrors")

if __name__ == '__main__':
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(ThrowsErrors)

VBA Test Client code

So here is some test code but you must run the above Python class first to register it!

Sub TestThrowsErrors()
    On Error GoTo PythonErrHand
    Dim obj As Object
    Set obj = VBA.CreateObject("PythonComTypes.ThrowsErrors")
    Debug.Print obj.PythonThrowsAnEror
    'Debug.Print obj.ThrowMyOwnError
SingleExit:
    Exit Sub
PythonErrHand:
    Debug.Print err.Description, Hex$(err.Number), err.source
    
End Sub

Running the code prints the following (edited) in the Immediate window...

Unexpected Python Error: Traceback (most recent call last):
  File "C:\PROGRA~2\MICROS~4\Shared\PYTHON~1\lib\site-packages\win32com\server\policy.py", line 278, in _Invoke_
    return self._invoke_(dispid, lcid, wFlags, args)
  File "C:\PROGRA~2\MICROS~4\Shared\PYTHON~1\lib\site-packages\win32com\server\policy.py", line 283, in _invoke_
    return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None)
  File "C:\PROGRA~2\MICROS~4\Shared\PYTHON~1\lib\site-packages\win32com\server\policy.py", line 586, in _invokeex_
    return func(*args)
  File "N:\source\repos\ThrowsErrors\ThrowsErrors\ThrowsErrors.py", line 10, in PythonThrowsAnEror
    a=self.noexist()
AttributeError: 'ThrowsErrors' object has no attribute 'noexist'
              80004005      Python COM Server Internal Error

You can see that Python is passing a whole error stack via the Err.Description field. For the error number it is using &H80004005 which is E_FAIL which signifies a general error.

If &H80004005 is the favoured catch all error number for errors then we can do the same for our custom errors. In the Python code above one can see in the ThrowMyOwnError() method we also use E_FAIL. If you uncomment the second method call (and comment the first to suppress it) then the test code now prints

Throw an error!             80004005      ThrowsErrors

So that's fine but there is one last gotcha.

What happens if I forget my error handler?

Of course, if you write production quality code you'd add an error handler for every single Sub and Function! But if you are playing with some test code you may forget to add an error handler, let's simulate this by commenting out the On Error Goto PythonErrHand line of code. If you then run the code you get the following message box...

and so this is devoid of any rich error information. So just be aware.

Command Line - Running Midl.exe from a batch file

Just a quick one. I have had cause to run the MIDL.exe compiler as I am researching types and type libraries. MIDL.exe can be fussy in that it expects some environmental variables to be set, PATH and INCLUDE, so it can pick up all the files it needs. This is a little frustrating if you forget its prerequisites so I have packaged the details into a batch file to be run from the command line.

There is not much to say about this except note how we can alter (append) the PATH and INCLUDE variables. Also we we can pass arguments to the batch file and then reference them with %1 %2 etc.

SET INCLUDE=C:\Program Files (x86)\Windows Kits\10\Include\10.0.16299.0\um\;C:\Program Files (x86)\Windows Kits\10\Include\10.0.16299.0\shared\;C:\Windows\Microsoft.NET\Framework\v4.0.30319
SET PATH=%PATH%C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC\bin\;

"C:\Program Files (x86)\Windows Kits\10\bin\10.0.16299.0\x86\midl.exe" %1 /tlb %2
pause
Exit

Here is an example of how to run it, one supplies the idl file first and the tlb (type library) file second.

n:\midl.bat "n:\MyTypeLib.idl" "n:\MyTypeLib.tlb"

I actually like this little batch file and I am beginning to wonder what else this vintage technology can offer.

VBA - Python Com Class with attributes

Keen readers of this blog will know from time to time I give Python COM gateway classes which are callable from VBA. Up until now I do not think I have given an example of a class that has attributes (or a constructor). Time to rectify this.

Open Visual Studio 2017 and add a new Python Applciation project and then copy and paste in the code below. Save the file, calling it whatever you want mine is called PythonExampleClassWithAttributes.py

1. class ExampleComClassWithAttributes(object):
2.  
3.     _reg_clsid_ = "{EF38F5B8-6E86-4D8E-A93E-C5B6C05CF378}"
4.     _reg_progid_ = 'PythonLib1.ExampleComClassWithAttributes'
5.     _public_methods_ = ['AMethod']
6.     _public_attrs_ = ['Foo','ws','ReadOnlyProp']
7.     _readonly_attrs_ = ['ReadOnlyProp']
8.  
9.     def __init__(self):
10.         self.ReadOnlyProp = "Not writable!"
11.  
12.     def AMethod(self):
13.         return "AMethod returns"
14.  
15. def TestExampleComClassWithAttributes():
16.     # This test routine gives each feature a spin 
17.     # (except ws for which see VBA client example)
18.     test = ExampleComClassWithAttributes()
19.     test.Foo = 54
20.     print(test.Foo)
21.     print(test.AMethod())
22.     print(test.ReadOnlyProp)
23.  
24. def RegisterThis():
25.     print("Registering COM servers...")
26.     import win32com.server.register
27.     win32com.server.register.UseCommandLine(ExampleComClassWithAttributes)
28.  
29. if __name__ == '__main__':
30.     RegisterThis()
31.     TestExampleComClassWithAttributes()

Running the above code from Visual Studio 2017 should give a command window output of the following...

Registering COM servers...
Registered: PythonLib1.ExampleComClassWithAttributes
54
AMethod returns
Not writable!
Press any key to continue . . .

_public_attrs_ and _readonly_attrs_

the new features in this code are the two class level attributes of _public_attrs_ and _readonly_attrs_. _public_attrs_ is an array of attributes that are to be exposed by Python to COM. That's all you need to get attributes up and running; very economical with lines of code!

You may be wondering where are the property procedures? And also wondering that without them how do you make properties read only? To make properties read only you add them to the _readonly_attrs_ array. In the above code the property named "ReadOnlyProp" is read-only, attempting to write to that property will error.

def __init__(self)

I don't think I have given a Python class constructor example on this blog before either. In the above code I give one, it is the block of code headed def __init__(self) on lines 9-10. Because I made "ReadOnlyProp" read-only I somehow need to set the value, in the code above I set it in the constructor.

Client VBA Code

So here is the client VBA code which can be pasted into a standard module.

modExampleComClassWithAttrs Standard Module

1. Option Explicit
2.  
3. Sub VBATestExampleComClassWithAttributes()
4.     Dim objExampleWithAttrs As Object
5.     Set objExampleWithAttrs = VBA.CreateObject("PythonLib1.ExampleComClassWithAttributes")
6.     Debug.Print objExampleWithAttrs.AMethod
7.  
8.     Set objExampleWithAttrs.ws = ThisWorkbook.Worksheets.Item(1)
9.     Debug.Print objExampleWithAttrs.ws.name
10.     objExampleWithAttrs.Foo = 54
11.     Debug.Print objExampleWithAttrs.Foo
12.     Debug.Print objExampleWithAttrs.ReadOnlyProp
13.  
14. End Sub

So one feature above not found in the Python test code is that I am setting one attribute 'ws' to be a object reference to a worksheet and so I need the Set keyword, notice how in the Python code I do not need to give this. Python is less fussy it seems, but with it the responsibility for the programmer to take care and test their code.

Final Thoughts

I have to say I feel quite liberated to be able to define a class that can carry state, i.e. has attributes, without having to add a class module for each class to the VBA project. In my university computer sciences courses I was firmly taught object orientation (OO). And to do good OO can require many classes, some of them potentially quite small. The Python files can hold as many classes as you need. No need to fill your VBA projects up with mini-classes.