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.