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.