VBA - XMLHTTP60 - Tricky event handling

Summary: XMLHTTP60 does not have any standard VBA events but by adding a class and pulling a trick in a text editor we can track events.

So neither MSXML2.XMLHTTP60 nor MSXML2.ServerXMLHTTP60 have any standard VBA events that can be trapped by declaring a variable with the WithEvents keyword. This contrasts with the WinHttp.WinHttpRequest class (see prior blog post for example code). However, we can still trap events but we have to pull a trick or two along the way. The official tutorial from Microsoft is given here, Microsoft - Use the onReadyStateChange Property (Visual Basic)

One needs to create a VBA class to handle the events. I give the source next but this is exported file source to be copied into a text editor such as Notepad, saved and then imported in the VBA IDE. Do not cut and paste directly into the VBA IDE. This is because of a line of hidden source code which is given here

Attribute Item.VB_UserMemId = 0

This line will disappear from view once the class module is imported. [In case you're interested in what it does, setting UserMemId=0 makes it the default method which means you can call it whatever you want because the caller will ask for it by its Dispatch Id (0), this is an IDispatch trick]. The top 9 lines also disappear but that is standard behaviour.

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "XHRSink"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

' In text editor, need to put "Attribute Item.VB_UserMemId = 0" on line underneath next line, 
' save and then import into VBA IDE, this sets the default method of this class
' Then we set an instance of this class to the OnReadyStateChange property of
' MSXML2.XMLHTTP60 or MSXML2.ServerXMLHTTP60 to get events
' The line "Attribute Item.VB_UserMemId = 0" will DISAPPEAR from view once imported
Sub OnReadyStateChange()
 Attribute Item.VB_UserMemId = 0

    Debug.Print goXHR.readyState
    If goXHR.readyState = 4 Then
        Debug.Print "sink code handling result"
        Debug.Print goXHR.responseText
    End If
End Sub

So now that the above class is imported, it should read XHRSink in the project folder, we can use it when setting the OnReadyStateChange [N.B. we don't use the Set keyword, this is not a typo!] of XMLHTTP60 (or ServerXMLHTTP60) . We are calling a slow and chunky web service built in a prior blog post.

Option Explicit

'* Tools->References
'MSXML2      Microsoft XML, v6.0      C:\Windows\SysWOW64\msxml6.dll


Global goXHR As MSXML2.XMLHTTP60

'https://msdn.microsoft.com/en-us/library/ms757030(v=vs.85).aspx


Public Sub HttpGet()
    On Error GoTo ErrHandler

    Randomize
    Debug.Print String(10, vbNewLine)

    Dim bAsync As Boolean
    'bAsync = True
    bAsync = False

    Set goXHR = New MSXML2.XMLHTTP60
    
    '* need random number in query parameters to make url unique and stop caching
    goXHR.Open bstrMethod:="GET", bstrURL:="http://localhost:34957/slowAndChunkyWebService?chunkCount=5&random=" & Rnd(1), varAsync:=bAsync
    
    Dim oSink As XHRSink
    Set oSink = VBA.IIf(bAsync, New XHRSink, Nothing)
    
    goXHR.OnReadyStateChange = oSink
    
    
    goXHR.send
    
    Debug.Print "send called with bAsync=" & bAsync
    If bAsync = False Then
        Debug.Print "main code handling result with bAsync=" & bAsync
        If goXHR.readyState = 4 Then Debug.Print goXHR.responseText
    End If
    
SingleExit:
    Exit Sub
ErrHandler:
    Debug.Print "Error (" & Err.Number & ") " & Err.Description
    Stop
    Resume
    
End Sub

To experiment swap the commenting on bAsync = True and bAsync = False. The code reports to the Immediate window what it is doing. Sadly no option to chunk the response is available (not that I know of). Here is some sample reported output when bAsync = True.

send called with bAsync=True
 2 
 3 
 4 
sink code handling result
foobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobarfoobar...

Ok, so that works but how extendable is it? What happens with multiple requests? So for multiple requests one would need to upgrade the XHRSink class with an identifier to tie it back to the source; this is a bit poor but not impossible.

Final thoughts. The limited event handling for XMLHTTP60 needs to be compared and contrasted with (a) WinHttpRequest (b) that found in modern browsers facilitated by jQuery and (c) that found on the web servers such as Node.js I would be very tempted to write web service client code in Node.js and then allow VBA to call in to the finalised and processed results.