C# - Excel Moniker Class to pack cell address to single string

In one single string, I need to encode a path to a block of Excel cells including the correct Excel.exe process (in case there is more than one Excel). I need this for an OLE DB Provider Custom Implementation where one only gets a single string to pack in all the details. As the OLE DB provider is written in C# then I implement an Excel Moniker Class also in C#.

There is an established COM moniker form for an Excel cell address qualified by worksheet, workbook and file location. I use this as the base of my moniker. I then bolt on the Hwnd of the specific Excel session as a prefix.

COM Monikers To Specify Excel Ranges

I do not want to replicate the COM specification here but there is such a thing as a COM moniker. A COM moniker is a bit like a URL (web address) or a file path. COM monikers can be composed of subtype COM moniker such as file moniker. The segments of such composite monikers by convention are frequently separated by the bang (exclamation mark) '!' . It is possible for a composite moniker to be parsed segment by segment passing control of each segment parsing to a separate component. This is emblematic of COM's component design.

An example of a composite moniker is an Excel cell located in another workbook. But first, let's build it up piece by piece. N.B. all the following examples show an equals sign as if in a formula. If on the same sheet a cell address requires no qualification, e.g. =$A$1. A cell address on another sheet requires the sheet name as a qualifier e.g. =Sheet2!$A$1 (note the bang '!' as separator) . A problem arises if the other sheet has a space in its name as this then requires surrounding quotes, e.g. ='Sheet 3'!$E$8 . A cell address in another workbook requires further qualification with the workbook enclosed in square brackets ...

=[TestClient.xlsm]Sheet5!$C$6

If there is a space in the workbook name then this (just like a worksheet name) has to be surrounded in single quotes.

='[Book With Space in Name.xlsx]Sheet1'!$D$6

Moreover, sometimes a workbook needs to be qualified by its full path so a full moniker could look like ...

=[C:\Temp\TestClient.xlsm]Sheet5!$C$6

Then there is the issue of using a named range instead of a cell address. Furthermore, a named range can be either local or global which gives us two extra forms...

=[TestClient.xlsm]Sheet5!LocalName
=[TestClient.xlsm]!GlobalName

So lots of logic to program into a moniker class.

Additionally specifying Excel session with Hwnd

As highlighted in previous post, it is possible to identify and reach a specify Excel session running by means of its Hwnd by using the IAccessible interface. I use this in the code to identify the correct and specific Excel.exe session in case there are two. I bolt on the Hwnd as a prefix separated by a tick ` .

XlMoniker Class Source Code

So the following is C# code to be housed in an assembly that is registered for interop (Visual Studio will need admin rights to register).

Public XlMoniker Class and IXlMoniker

There is more than one class in the code below and so it is required to distinguish in this commentary. To expose functionality to VBA we need to ship a COM interface, that is IXlMoniker and also we need to shiop a COM class, XlMoniker that implements the interface IXlMoniker. There are three methods, GetExcelByHwnd was covered in prior post. ExcelRangeToMoniker takes a range and returns a moniker string. GetExcelRangeFromMoniker takes a moniker string and returns a range.

Internal XlMonikerParser Class

I have separated out the parsing and string handling in a separate class XlMonikerParser. This has no COM interface and so is not exposed to VBA. The code in this class lends itself to unit testing and the Unit test code is given below.

using System;
using System.Runtime.InteropServices;

namespace XlMoniker
{
    [ComVisible(true)]
    public interface IXlMoniker
    {
        bool GetExcelByHwnd(int lhwndApp, ref object app);
        bool GetExcelRangeFromMoniker(string sMoniker, ref object rngRetVal);
        string ExcelRangeToMoniker(object rng);
    }

    public class XlMonikerParser
    {
        public bool ParseMoniker(string sFullMoniker, out string sHwnd, out string sWorkbook,
                    out string sWorksheet, out string sCellAddress)
        {
            bool retval = false; sHwnd = ""; sWorkbook = ""; sWorksheet = ""; sCellAddress = "";

            if (this.ParseLeadingHwnd(sFullMoniker, out sHwnd, out string sFileAndCellAddressMoniker))
            {
                if (this.ParseWorkbookAndSheetFromCellAddress(sFileAndCellAddressMoniker, out string sWorkbookAndSheet, out sCellAddress))
                {
                    if (this.ParseWorkbookFromSheet(sWorkbookAndSheet, out sWorkbook, out sWorksheet))
                    {
                        return true;
                    }
                }
            }

            return retval;
        }

        public bool ParseWorkbookFromSheet(string sWorkbookAndSheet, out string sWorkbook,
                    out string sWorksheet)
        {
            bool retval = false; sWorkbook = ""; sWorksheet = "";
            // if workbook or sheet name contain a space then single quotes wrap them isolating them from the cell address
            // '[Book With Space in Name.xlsx]Sheet1'
            // [TestClient.xlsm]Sheet1


            string[] splitOnSingleQuotes = sWorkbookAndSheet.Split(''');
            string sWithoutSingleQuotes = splitOnSingleQuotes.Length == 3 ? splitOnSingleQuotes[1] : sWorkbookAndSheet;

            char[] squareBrackets = new char[] { '[', ']' };
            string[] splitOnSquareBrackets = sWithoutSingleQuotes.Split(squareBrackets);
            if (splitOnSquareBrackets.Length == 3)
            {
                sWorkbook = splitOnSquareBrackets[1];
                sWorksheet = splitOnSquareBrackets[2];
                return true;
            }
            else if (splitOnSquareBrackets.Length == 1)
            {
                sWorkbook = splitOnSquareBrackets[0];
                sWorksheet = ""; // possibly a global name
                return true;
            }

            return retval;
        }

        public bool ParseWorkbookAndSheetFromCellAddress(string sFileAndCellAddressMoniker, out string sWorkbookAndSheet,
                    out string sCellAddress)
        {
            bool retval = false; sWorkbookAndSheet = ""; sCellAddress = "";

            string[] splitOnBang = sFileAndCellAddressMoniker.Split('!');
            // expecting only one bang to split the workbook and sheet name from the cell address , so two elements
            if (splitOnBang.Length == 2)
            {
                sWorkbookAndSheet = splitOnBang[0];
                sCellAddress = splitOnBang[1];
                return true;
            }

            return retval;
        }

        public bool ParseLeadingHwnd(string sFullMoniker, out string sHwnd, out string sFileAndCellAddressMoniker)
        {
            bool retval = false;
            sHwnd = "";
            sFileAndCellAddressMoniker = "";

            string[] splitOnTick = sFullMoniker.Split('`');
            // expecting only one tick to split the Hwnd from the rest of the moniker, so two elements
            if (splitOnTick.Length == 2)
            {
                sHwnd = splitOnTick[0];
                sFileAndCellAddressMoniker = splitOnTick[1];
                return true;
            }

            return retval;
        }
    }

    [ClassInterface(ClassInterfaceType.None)]
    [ComDefaultInterface(typeof(IXlMoniker))]
    [ComVisible(true)]
    public class XlMoniker : IXlMoniker
    {
        const char separator = '`';

        [DllImport("user32.dll", SetLastError = true)]
        static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);


        [DllImport("oleacc.dll", SetLastError = true)]
        internal static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint id, ref Guid iid,
                                                    [In, Out, MarshalAs(UnmanagedType.IUnknown)] ref object ppvObject);

        bool IXlMoniker.GetExcelByHwnd(int lhwndApp2, ref object appRetVal)
        {
            bool bRetVal = false;

            IntPtr lhwndApp = (IntPtr)lhwndApp2;

            IntPtr lHwndDesk = FindWindowEx(lhwndApp, IntPtr.Zero, "XLDESK", "");
            if (lHwndDesk != IntPtr.Zero)
            {

                IntPtr lHwndExcel7 = FindWindowEx(lHwndDesk, IntPtr.Zero, "EXCEL7", null);
                if (lHwndExcel7 != IntPtr.Zero)
                {
                    Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
                    const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                    object app = null;
                    if (AccessibleObjectFromWindow(lHwndExcel7, OBJID_NATIVEOM, ref IID_IDispatch, ref app) == 0)
                    {
                        dynamic appWindow = app;
                        appRetVal = appWindow.Application;
                        return true;
                    }
                }
            }
            return bRetVal;
        }


        bool IXlMoniker.GetExcelRangeFromMoniker(string sMoniker, ref object rngRetVal)
        {
            bool retval = false;

            XlMonikerParser parser = new XlMonikerParser();
            retval = parser.ParseMoniker(sMoniker, out string sHwnd, out string sWorkbook,
                        out string sWorksheet, out string sCellAddress);
            if (retval)
            {
                if (int.TryParse(sHwnd, out int lHwnd))
                {
                    dynamic xlApp = null;
                    if (((IXlMoniker)this).GetExcelByHwnd(lHwnd, ref xlApp))
                    {

                        if (FindWorkbookByFullName(sWorkbook, xlApp, out dynamic wbFound))
                        {
                            if (sWorksheet.Length == 0)
                            {
                                // perhaps a global name
                                if (TryGetName(sCellAddress, wbFound, out dynamic nameFound))
                                {
                                    rngRetVal = nameFound.RefersToRange;
                                    return true;
                                }
                            }
                            else
                            {
                                if (FindWorksheetByName(sWorksheet, wbFound, out dynamic wsFound))
                                {
                                    // perhaps a local name
                                    if (TryGetName(sCellAddress, wsFound, out dynamic nameFound))
                                    {
                                        rngRetVal = nameFound.RefersToRange;
                                        return true;
                                    }
                                    else
                                    {
                                        // here it can only be a cell address
                                        if (TryGetRange(wsFound, sCellAddress, out dynamic range))
                                        {
                                            rngRetVal = range;
                                            return true;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            return retval;
        }

        bool FindWorkbookByFullName(string sWorkbookFullName, dynamic xlApp, out dynamic wbFound)
        {
            bool retVal = false;
            wbFound = null;
            if (xlApp != null)
            {
                foreach (var wb in xlApp.workbooks)
                {
                    if (wb.FullName == sWorkbookFullName)
                    {
                        wbFound = wb;
                        return true;
                    }
                }
            }
            return retVal;
        }

        bool FindWorksheetByName(string sWorksheetName, dynamic wb, out dynamic wsFound)
        {
            bool retVal = false;
            wsFound = null;
            if (wb != null)
            {
                foreach (var ws in wb.worksheets)
                {
                    if (ws.Name == sWorksheetName)
                    {
                        wsFound = ws;
                        return true;
                    }
                }
            }
            return retVal;
        }

        bool TryGetName(string sNameName, dynamic wsOrWb, out dynamic nameFound)
        {   // this should work for both the Names collection off the workbook (i.e. global) and off each worksheet (i.e. Local)
            bool retVal = false;
            nameFound = null;
            if (wsOrWb != null)
            {
                try
                {
                    nameFound = wsOrWb.Names.Item(sNameName);
                    retVal = true;
                }
                catch
                {
                    return false;
                }
            }
            return retVal;
        }

        bool TryGetRange(dynamic ws, string sCellAddress, out dynamic range)
        {
            bool retVal = false;
            range = null;
            if (ws != null)
            {
                try
                {
                    range = ws.Range(sCellAddress);
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            return retVal;
        }

        bool IsRangeNamed(dynamic rng, out dynamic nameFound)
        {
            bool retVal = false;
            nameFound = null;

            try
            {
                nameFound = rng.Name;
                return true;
            }
            catch
            { }

            return retVal;
        }

        string IXlMoniker.ExcelRangeToMoniker(dynamic rng)
        {
            string retval = "";
            if (rng != null)
            {
                dynamic ws = null; dynamic wb = null; dynamic xlApp = null; int hwnd;
                try
                {
                    ws = rng.Worksheet;
                    wb = ws.Parent;
                    xlApp = wb.Application;
                    hwnd = xlApp.hwnd();
                }
                catch
                {
                    throw new Exception("Error navigating from Range->Worksheet->Parent(Workbook)->Application->Hwnd!");
                }

                try
                {
                    string hwndPrefix = hwnd.ToString() + separator.ToString();
                    string sWorkbookFullName = wb.FullName;
                    string sWorksheetName = ws.Name;

                    if (IsRangeNamed(rng, out dynamic nameFound))
                    {   // range is named, but global or local, presence of ! indicates local
                        string sName = nameFound.Name;
                        bool quoteWorkbook = sWorkbookFullName.Contains(" ");
                        bool quoteWorksheet = sWorksheetName.Contains(" ");

                        if (sName.Contains("!"))
                        {   // it's local 
                            // use single quotes if there is a space in workbook name or sheetname
                            if (quoteWorkbook || quoteWorksheet)
                            {
                                return hwndPrefix + "'[" + sWorkbookFullName + "]" + sName;
                            }
                            else
                            {
                                return hwndPrefix + "[" + sWorkbookFullName + "]" + sName;
                            }
                        }
                        else
                        {   // it's global
                            // use single quotes if there is a space in workbook name
                            if (quoteWorkbook)
                            {
                                return hwndPrefix + "'[" + sWorkbookFullName + "]'!" + sName;
                            }
                            else
                            {
                                return hwndPrefix + "[" + sWorkbookFullName + "]!" + sName;
                            }
                        }
                    }
                    else
                    {   // range is not named just a cell address, so expect a worksheetname
                        if (sWorkbookFullName.Contains(" ") || sWorksheetName.Contains(" "))
                        {
                            return hwndPrefix + "'[" + sWorkbookFullName + "]" + sWorksheetName + "'!" + rng.Address;
                        }
                        else
                        {
                            return hwndPrefix + "[" + sWorkbookFullName + "]" + sWorksheetName + "!" + rng.Address;
                        }
                    }
                }
                catch
                {
                    throw new Exception("Error building moniker string!");
                }
            }
            return retval;
        }
    }

}

VBA Client Code

So this is VBA code to test our class. Though the real test is when I blog an OLEDB Provider that accepts a cell moniker to generate a table, that is upcoming, watch this blog!

Option Explicit
Option Private Module

Private moXlMoniker As SimpleOLEDBProvider1.XlMoniker

Public Function GetXlMoniker() As SimpleOLEDBProvider1.XlMoniker
    If moXlMoniker Is Nothing Then
        Set moXlMoniker = New SimpleOLEDBProvider1.XlMoniker
    End If
    Set GetXlMoniker = moXlMoniker
End Function

Public Sub ResetXlMoniker()
    Set moXlMoniker = Nothing
End Sub

Private Sub Test_XlMoniker_GetExcelRange2()

    Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
    Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
    
    Dim sht1 As Excel.Worksheet
    Set sht1 = ThisWorkbook.Worksheets("Sheet1")
    
    Dim sHwnd As String
    sHwnd = Application.hwnd & "`"
    
    Dim sMonikerPart1 As String
    
    If InStr(1, ThisWorkbook.FullName, " ", vbTextCompare) > 0 Then
        sMonikerPart1 = sHwnd & "'[" & ThisWorkbook.FullName & "]"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A11:B14")) = sMonikerPart1 & "Sheet1'!$A$11:$B$14"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A1:B4")) = sMonikerPart1 & "'!GlobalName"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("LocalName")) = sMonikerPart1 & "'Sheet1!LocalName"
    Else
        sMonikerPart1 = sHwnd & "[" & ThisWorkbook.FullName & "]"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A11:B14")) = sMonikerPart1 & "Sheet1!$A$11:$B$14"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("A1:B4")) = sMonikerPart1 & "!GlobalName"
        Debug.Assert oMoniker.ExcelRangeToMoniker(sht1.Range("LocalName")) = sMonikerPart1 & "Sheet1!LocalName"
    End If
    
End Sub



Private Sub Test_XlMoniker_GetExcelRange()

    Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
    Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
    
    Dim vSetupValues As Variant
    vSetupValues = Application.[{"ColorName","ColorRGB";"Red","FF0000";"Green", "00FF00";"Blue" ,"0000FF"}]

    ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Value2 = vSetupValues

    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!A1") = "$A$1"
    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!A1:b4") = "$A$1:$B$4"
    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet2!B1:C4") = "$B$1:$C$4"

    ThisWorkbook.Worksheets("Sheet1").Range("A1:B4").Name = "GlobalName"
    
    Dim rngGlobalName As Excel.Range
    Set rngGlobalName = ThisWorkbook.Names.Item("GlobalName").RefersToRange
    
    
    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]!GlobalName") = "$A$1:$B$4"
    ThisWorkbook.Worksheets("Sheet1").Range("B1:C4").Name = "Sheet1!LocalName"
    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!LocalName") = "$B$1:$C$4"
    Debug.Assert GetExcelRangeAddress(oMoniker, Application.hwnd & "`[" & ThisWorkbook.FullName & "]Sheet1!$D5:E7") = "$D$5:$E$7"
End Sub

Private Function GetExcelRangeAddress(ByVal oMoniker As SimpleOLEDBProvider1.XlMoniker, ByVal sMoniker As String) As String
    Dim rng As Excel.Range
    Debug.Assert oMoniker.GetExcelRangeFromMoniker(sMoniker, rng)
    GetExcelRangeAddress = rng.Address
End Function

Private Sub Test_XlMoniker_GetExcelByHwnd()

    Dim oMoniker As SimpleOLEDBProvider1.XlMoniker
    Set oMoniker = New SimpleOLEDBProvider1.XlMoniker
    
    Dim obj As Excel.Application
    If oMoniker.GetExcelByHwnd(Application.hwnd, obj) Then
        Debug.Assert obj Is Application
    End If


End Sub

Private Sub GlobalName()
    
    Dim namGlobal As Excel.Name
    Set namGlobal = ThisWorkbook.Names.Item("GlobalName")
    Debug.Assert Not namGlobal Is Nothing
    Debug.Assert namGlobal.RefersToRange.Address = "$A$1:$B$4"
    
    Dim namLocal As Excel.Name
    Set namLocal = ThisWorkbook.Worksheets("Sheet1").Names.Item("LocalName")
    Debug.Assert Not namLocal Is Nothing

    Dim namGlobal2 As Excel.Name
    Set namGlobal2 = ThisWorkbook.Names.Item("GlobalName")

    Stop

End Sub

XlMonikerParser Unit Tests

As promised here is the unit test code for the XlMonikerParser class. I must say I really like the testing support in Visual Studio 2017. Far better than unit tests in VBA!

using XlMoniker;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace UnitTestProject1
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            XlMonikerParser parser = new XlMonikerParser();
            string sHwnd;
            string sFileAndCellAddressMoniker;

            bool parsed = parser.ParseLeadingHwnd("1234`'[foo bar.xlsx]!Sheet1", out sHwnd, out sFileAndCellAddressMoniker);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sHwnd == "1234");
            Assert.IsTrue(sFileAndCellAddressMoniker == "'[foo bar.xlsx]!Sheet1");

            parsed = parser.ParseLeadingHwnd("1234`'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sHwnd, out sFileAndCellAddressMoniker);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sHwnd == "1234");
            Assert.IsTrue(sFileAndCellAddressMoniker == "'[Book With Space in Name.xlsx]Sheet1'!$D$3");

            parsed = parser.ParseLeadingHwnd("1234`TestClient.xlsm!GlobalName", out sHwnd, out sFileAndCellAddressMoniker);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sHwnd == "1234");
            Assert.IsTrue(sFileAndCellAddressMoniker == "TestClient.xlsm!GlobalName");
        }

        [TestMethod]
        public void TestMethod2()
        {
            XlMonikerParser parser = new XlMonikerParser();

            string sWorkbookAndSheet; string sCellAddress;

            bool parsed = parser.ParseWorkbookAndSheetFromCellAddress("'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sWorkbookAndSheet, out sCellAddress);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sWorkbookAndSheet == "'[Book With Space in Name.xlsx]Sheet1'");
            Assert.IsTrue(sCellAddress == "$D$3");

            parsed = parser.ParseWorkbookAndSheetFromCellAddress("TestClient.xlsm!GlobalName", out sWorkbookAndSheet, out sCellAddress);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sWorkbookAndSheet == "TestClient.xlsm");
            Assert.IsTrue(sCellAddress == "GlobalName");
        }

        [TestMethod]
        public void TestMethod3()
        {
            XlMonikerParser parser = new XlMonikerParser();

            string sWorkbook; string sWorksheet;

            bool parsed = parser.ParseWorkbookFromSheet("'[Book With Space in Name.xlsx]Sheet1'", out sWorkbook, out sWorksheet);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sWorkbook == "Book With Space in Name.xlsx");
            Assert.IsTrue(sWorksheet == "Sheet1");

            parsed = parser.ParseWorkbookFromSheet("[TestClient.xlsm]Sheet2", out sWorkbook, out sWorksheet);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sWorkbook == "TestClient.xlsm");
            Assert.IsTrue(sWorksheet == "Sheet2");

            parsed = parser.ParseWorkbookFromSheet("TestClient.xlsm", out sWorkbook, out sWorksheet);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sWorkbook == "TestClient.xlsm");
            Assert.IsTrue(sWorksheet == "");
        }

        [TestMethod]
        public void TestMethod4()
        {
            XlMonikerParser parser = new XlMonikerParser();

            string sHwnd; string sWorkbook; string sWorksheet; string sCellAddress;

            bool parsed = parser.ParseMoniker("1234`'[Book With Space in Name.xlsx]Sheet1'!$D$3", out sHwnd, out sWorkbook, out sWorksheet, out sCellAddress);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sHwnd == "1234");
            Assert.IsTrue(sWorkbook == "Book With Space in Name.xlsx");
            Assert.IsTrue(sWorksheet == "Sheet1");
            Assert.IsTrue(sCellAddress == "$D$3");

            parsed = parser.ParseMoniker("1234`TestClient.xlsm!GlobalName", out sHwnd, out sWorkbook, out sWorksheet, out sCellAddress);
            Assert.IsTrue(parsed);
            Assert.IsTrue(sHwnd == "1234");
            Assert.IsTrue(sWorkbook == "TestClient.xlsm");
            Assert.IsTrue(sWorksheet == "");
            Assert.IsTrue(sCellAddress == "GlobalName");
        }
    }
}

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.