VBA - Use Square Brackets trick to prettify strings, especially code fragments

So I learnt all about using a trick to get square bracket syntax in VBA and how the argument is really a string but without the enclosing double quotes or the doubling of internal double quotes as is normally the case with VBA strings.

The following VBA code fragments below should demonstrate where and where not one could use this technique. The code relies on a ATL/C++ component the key sections of which are given at the bottom.

Sub TestSourceCode()

    '* SourceFragmentLib.Src is defined as Global


    '* plain string, note no doubling of double quotes
    Debug.Print Src.[he said "no", she said "yes"]
    
    '* sql statement much more readable without doubling of double quotes
    Debug.Print Src.[SELECT * FROM fooTable WHERE lastName="Devolio"]


End Sub

Let us not forget that Excel's Application object processes expressions in strings but one can still see the advantages of not having to double up double quotes, see this code which allows the evaluation of a string literal to be parsed into a variant array ready for pasting into a block of cells.

Sub XlSerialization1()
    Dim v
    v = [{1,2;"foo",4.5}]    '* this calls Application.Evaluate and parses to 2x2 variant array, note no doubling of double quotes

    '* write all cells in one line
    Sheet1.Cells(1, 1).Resize(2, 2).Value2 = v
End Sub

For regular expressions, this trick is less effective because square brackets are part of the regular expression symbol vocabulary. Here is an example, if only to illustrate that perhaps this is not a good use of this technique.

Sub TestRegularExpression()

    Dim oRE As VBScript_RegExp_55.RegExp    '* Tools->References: Microsoft VBScript Regular Expressions 5.5
    Set oRE = New VBScript_RegExp_55.RegExp
    
    '* following line avoids doubling double quotes
    '* (but regular expressions are cryptic anyhow!)
    oRE.Pattern = Src.["([^"]*)"]
    
    '* here is what it the expression looks like with doubling double quotes
    '* yuck!
    Debug.Assert oRE.Pattern = """([^""]*)"""
    
    oRE.Global = True
    
    Dim sTest As String
    sTest = """Foo Bar"" ""Another Value"" something else"

    Debug.Assert oRE.test(sTest)

    Dim oMatchCol As VBScript_RegExp_55.MatchCollection
    Set oMatchCol = oRE.Execute(sTest)
    Debug.Assert oMatchCol.Count = 2
    
    Dim oMatch As Match
    For Each oMatch In oMatchCol
        Debug.Print oMatch.SubMatches(0)
    
    Next oMatch

End Sub

The ATL source

Not only am I pulling the square bracket trick, using DispId(-5) I am also pulling a trick to make a class global by using appobject idl attribute. Here is the ATL source code for the class SourceFragmentLib.Src

import "oaidl.idl";
import "ocidl.idl";
import "shobjidl.idl";

[
 uuid(45e7ef27-47c2-40cd-8be7-7a943549469d),
 version(1.0),
]
library SourceFragmentLib
{
 importlib("stdole2.tlb");

 [
  odl,
  uuid(f103332b-4ba0-4c4e-b846-020bc694a85b),
  version(1.0),
  dual,
  oleautomation
 ]
 interface ISrc : IDispatch
 {
  [id(0xfffffffb)]
  HRESULT Line([in]BSTR sIn, [out, retval] BSTR* outRetVal);
 };

 [
  uuid(8d8e4c03-9397-4d07-b171-e6aa2ec61aae),
  version(1.0),
  appobject
 ]
 coclass Src
 {
  [default] interface ISrc;
 };
};

For the header file Src.h add the following line

 STDMETHOD(Line)(BSTR sIn, BSTR* outRetVal);

For the source file Src.cpp add the following implementation which simply copies the in string to the return string.

STDMETHODIMP CSrc::Line(BSTR sIn, BSTR* outRetVal)
{
 HRESULT hr = S_OK;
 CComBSTR bstrIn(sIn);

 bstrIn.CopyTo(outRetVal);
 return S_OK;
}

C# Source

Currently working on a C# source alternative to the ATL/C++ and whilst I can style the method with DispId(-5) getting a C# object to be global in VBA client code is problematic but hopefully I will post a solution soon. UPDATE, this involves launching midl after adjusting the idl source code.

VBA - COM - DispId(-5) gives VBA Square Brackets but the argument is passed as a string

So the flurry of posts around DispIds is because of a very interesting question on SO How to use square brackets for a string evaluation in VBA? . I had read in some old COM books that it is an ActiveX Control standard to allow a square bracket syntax, that it requires setting the method's DispId to -5 (hex 0xfffffffb) and that by convention the method name is usually called Evaluate.

I think it's best to start off with some code and then talk about it. We can enable the square brackets for a C# authored component, the code below is a .NET class library with ComVisible(true) is AssemblyInfo.cs and with checkbox 'Register for Interop' checked. You also need to run Visual Studio with admin rights.

using System.Collections.Generic;
using System.Runtime.InteropServices;

namespace SquareBracketsCSharp
{
    public interface IList
    {
        [DispId(-5)]
        object Foo(object idx);
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IList))]
    public class CList : IList
    {
        private List m_List = new List();

        public CList()
        {
            for (int i = 1; i < 5; i++)
            {
                m_List.Add(i * i);
            }
        }

        object IList.Foo(object idx)
        {
            int idx2;
            if (int.TryParse((string)idx, out idx2))
            {
                return m_List[idx2];
            }
            else
            {
                return 0;
            }
        }
    }

So the code above creates a short list of square numbers. The method Foo has DispId(-5) which makes it the square brackets method for that class. Note well, that the method does not have to be called Evaluate as written some places. You can change the method's name to Evaluate but it won't help.

Now let's write some client VBA code. Below the code attempts to loop through the list but it fails returning zero for each iteration. Why? Because lLoop gets passed as a string! And you can see that happening if you put a break point in the C# code. So you'll see "lLoop" instead of an integer.

Sub TestFibonacci()
    Dim oList As SquareBracketsCSharp.CList
    Set oList = New SquareBracketsCSharp.CList
    
    Dim lLoop As Long
    For lLoop = 0 To 2
        Dim lTerm As Long
        lTerm = oList.[lLoop]
        Debug.Print lTerm
    Next
End Sub

Wherefore Square Brackets

In the code given above the square brackets are useless for simulating C++/C#/Java square bracketed access to array. They will never be indexers for VBA. So, is there any value at all in this syntax then?

Well there might be use case when an argument itself has many quotation marks; in VBA quotes in strings have to be doubled up making it look a bit ugly so in the Immediate Window we type

?"""He said"",""She said"""
"He said","She said"

To illustrate how we can be liberated from the double quote we can insert the following line of code into the above VBA program...

oList.["He said","She said"]

This isn't a trivial nothing, on many occasions when writing code I have had a need to embed expressions with double quotes, often these expressions are in fact fragments of other programming languages such as SQL or JavaScript. Perhaps another post might illustrate the usefulness of being liberated from doubling double quotes.

DispID(-5) does not work for VBA source

The previous posts on DispID assume the ability to export a class from VBA, and add hidden attribute using Attribute <MethodName>.VB_UserMemId = [0|-4] where 0 denotes the default method and -4 denotes an enumeration factory. Sadly, I could not get DispId -5 to work for a VBA class. So you'll need C# (or C++ ATL) to author components with square brackets enabled.

COM - Magic DispIds

So the Microsoft webserver for this page is really slow so I'm recreating this table on this blog post for quick reference. The original is at https://msdn.microsoft.com/en-us/library/windows/desktop/ms221242(v=vs.85).aspx .

Constant/value	Description
DISPID_COLLECT -8	The Collect property. You use this property if the method you are calling through Invoke is an accessor function.
DISPID_CONSTRUCTOR -6	The C++ constructor function for the object.
DISPID_DESTRUCTOR -7	The C++ destructor function for the object.
DISPID_EVALUATE -5	The Evaluate method. This method is implicitly invoked when the ActiveX client encloses the arguments in square brackets. For example, the following two lines are equivalent: x.[A1:C1].value = 10 x.Evaluate("A1:C1").value = 10
DISPID_NEWENUM -4	The _NewEnum property. This special, restricted property is required for collection objects. It returns an enumerator object that supports IEnumVARIANT, and should have the restricted attribute specified.
DISPID_PROPERTYPUT -3	The parameter that receives the value of an assignment in a PROPERTYPUT.
DISPID_UNKNOWN -1	The value returned by IDispatch::GetIDsOfNames to indicate that a member or parameter name was not found.
DISPID_VALUE 0	The default member for the object. This property or method is invoked when an ActiveX client specifies the object name without a property or method.

VBA - Enumeration Methods (DispId(-4))

So magic DispIds (short for Dispatch IDs) have been on my mind today, a clutch of posts earlier about using bang syntax once you set a method dispid to zero. Here we talk about another magic number -4 which denotes an enumeration factory method that VBA will call on your object if found in a For Each .. In .. construct.

Other bloggers usually illustrate this with a canonical example about collection classes. Instead, I'll give a twist on Linq's Where filter function. We'll need a class, but first here is the client (standard) module. This module also houses the predicate SheetIsOdd used to tell if a sheet gets added to the collection or not.

Option Explicit

Sub Test()
    Dim oWhere As New Where
    
    Dim wsOdd As Variant
    For Each wsOdd In oWhere.Init(ThisWorkbook.Worksheets, "SheetIsOdd")
        Debug.Print wsOdd.Name
    Next
End Sub

Function SheetIsOdd(sh As Excel.Worksheet) As Boolean
    Dim lSuffix As Long
    If IsNumeric(Right$(sh.Name, 1)) Then
        lSuffix = Right$(sh.Name, 1)
    End If
    
    If lSuffix Mod 2 = 1 Then
        SheetIsOdd = True
    End If
End Function

Where class

The Where class has an Init method that returns an instance of itself to allow chaining methods. The NewEnum method delegates the enumeration to a VBA.Collection populated during Init. The NewEnum method cannot take parameters because it is defined as parameterless. We supply parameters via Init and use chaining methods so it appears seamless.

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

Private mcol As VBA.Collection

Private mSrcCol As Object
Private msCallbackPredicate As String

Public Function Init(ByVal col As Excel.Sheets, ByVal sCallbackPredicate As String) As Where
    Set mSrcCol = col
    msCallbackPredicate = sCallbackPredicate
    Set Init = Me '* allows chaining
End Function

Public Function NewEnum() As IEnumVARIANT
Attribute NewEnum.VB_UserMemId = -4
    'Attribute NewEnum.VB_UserMemId = -4
    
    Set mcol = New VBA.Collection
        
    Dim oLoop As Object
    For Each oLoop In mSrcCol

        If Application.Run(msCallbackPredicate, oLoop) Then
            mcol.Add oLoop
        End If
    Next
    
    Set NewEnum = mcol.[_NewEnum]
End Function

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.