JavaScript Notes and Queries: JavaScript's Prototypical Inheritance is simpler than TypeScript implies

In other posts on this blog I can write with authority rooted in deep experience. Lately, I have been revisiting JavaScript and cannot write on this topic with such confidence but I am keen to master web development. So I begin a new series called JavaScript Notes and Queries and the first topic is prototypical inheritance...

TypeScript, harbinger of change

I cannot write about all the previous versions of JavaScript nor give key milestones, I can only write as to my previous encounters. Decades ago, I remember JavaScript being the language used in web pages to handle mouse clicks etc. and little more. At my clients (Banks etc.) nobody was writing large scale projects in JavaScript. But Microsoft were and they found it wanting, so Microsoft invented TypeScript. Here's John Papa, Principal Developer Advocate with Microsoft ...

TypeScript is a language for application-scale JavaScript development. It’s a typed superset of JavaScript that compiles to plain JavaScript and was originally created out of a need for a more robust tooling experience to complement JavaScript language developers.

So, Microsoft concluded that ordinary JavaScript couldn't cope with large scale projects. The TypeScript tooling, specifically the online transpilers, that arose could be used to compare and contrast syntax patterns between TypeScript and JavaScript, of most interest to me was classes and inheritance.

TypeScript transpilers reveal JavaScript prototypical equivalence of TypeScript classes

Typescript 'transpiles' to plain JavaScript, and you can find tools on line to demonstrate such as TypeScript playground. I find this to be the best tool because you can change the target version of JavaScript. When I first encountered these JavaScript was still at ECMAScript 5, i.e. before the class keyword had arrived. We can play a game of time travel and restrict the target language to ECMAScript 5 by using the options menu. Then, we can write a TyepScript class and see the old school JavaScript equivalent.

Below is a simple class in Typescript (on the left) and its transpiled into old school JavaScript, specifically ECMAScript 5 (because ECMAScript 6 has its own class keyword) equivalent (on the right). Here is the link which you must paste in full because the source code is encoded in the url.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}

"use strict";
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());

The JavaScript on the right is cryptic compared to the Typescript on the left but if you play around (add some more methods etc.) you'll discover the syntax pattern. The pattern is that methods are appended to the prototype property of the Animal object, this means all instances of Animal created will get a move() method, just like it was a class. Other lines in the JavaScript implement the contructor function. A class's modularity is reproduced in JavaScript by using the 'module pattern', this is established with an IIFE (Immediately Invoked Function Expression).

JavaScript's Prototypical Inheritance is simpler than TypeScript implies

So far so good. Now that we know how to append methods to the prototype we could just skip the Transcript and write the JavaScript directly. But what about inheritance? In Typescript you use the extends keyword. I've given a code listing example below and this link will take you again to Typescript playground but brace yourself for the transpiled equivalent for it is very, very scary.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}

class Dog extends Animal {
  bark() {
    console.log("Woof! Woof!");
  }
}

So the transpiled listing is given below in all its scariness. Don't forget, we're deliberately choosing to target a version of JavaScript before the class keyword arrived. We're doing this in the name of investigation! All the really scary code is in the top code block which defines the __extends function.

"use strict";
var __extends = (this && this.__extends) || (function () {
    var extendStatics = function (d, b) {
        extendStatics = Object.setPrototypeOf ||
            ({ __proto__: [] } instanceof Array && function (d, b) { d.__proto__ = b; }) ||
            function (d, b) { for (var p in b) if (b.hasOwnProperty(p)) d[p] = b[p]; };
        return extendStatics(d, b);
    };
    return function (d, b) {
        extendStatics(d, b);
        function __() { this.constructor = d; }
        d.prototype = b === null ? Object.create(b) : (__.prototype = b.prototype, new __());
    };
})();
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());
var Dog = /** @class */ (function (_super) {
    __extends(Dog, _super);
    function Dog() {
        return _super !== null && _super.apply(this, arguments) || this;
    }
    Dog.prototype.bark = function () {
        console.log("Woof! Woof!");
    };
    return Dog;
}(Animal));

I took this code and then I started to remove lines to see what breaks, you might like to do the same as an exercise. I believe the variable b stands for base, d stands for derived and p stands for property. Much of this code is 'polyfill' code which acts to retofit modern features, so a lot could be removed.

My classes are simpler than others and so I could remove loads of code. My classes hold no state which is my preference these days. Without state, my classes have parameterless constructors and the need to call base class constructors is obviated; this also simplified matters.

I had thought that I had completely boiled down the __extends function to one line that uses the Object.create method ...

Dog.prototype = Object.create(Animal.prototype);

An alternative line of code is to the use

Object.setPrototypeOf( Animal.prototype, Creature.prototype )

I asked a pertinent Stack Overflow question while constructing a deeper class hierarchy. That question links to other relevant SO questions.

All this means we can arrive at a much less scary code listing below.

    var Animal = (function () {

        function Animal() {
            return this;
        }

        Animal.prototype.move = function (distanceInMeters) {
            if (distanceInMeters === void 0) {
                distanceInMeters = 0;
            }
            console.log("Animal moved " + distanceInMeters + "m.");
        };

        return Animal;
    }());

    var Dog = (function () {
        
        Object.setPrototypeOf( Dog.prototype, Animal.prototype )

        function Dog() {
            return this;
        }
        Dog.prototype.bark = function () {
            console.log("Woof! Woof!");
        };
        return Dog;
    }());

    var dog = new Dog();
    dog.bark();
    dog.move(10);
    dog.bark();

If we look in the Console in the Chrome Developer Tools then we can see out program's output. If we also type console.info(dog) and expand the nodes then we can see our desired inheritance tree ...

Our target inheritance tree

Speed Warning

Unfortunately that is not the end of the story because during my research I cam across this MDN article which says that the Object.setPrototypeOf technique is ...

Ill-performing. Should be deprecated. Many browsers optimize the prototype and try to guess the location of the method in memory when calling an instance in advance; but setting the prototype dynamically disrupts all those optimizations. It might cause some browsers to recompile your code for de-optimization, to make it work according to the specs.

Clearly, I need to do more research. I will return to this topic...

Links

Other links that maybe useful...

• JavaScript prototypes. It doesn't have to be complicated.

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");
        }
    }
}