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.

A rather neat piece of plumbing, Chrome extension pushes byte array of jobs data to Excel via Python

Transcript

The United States is suffering from extremely high unemployment and in this post I give an application that harvests job leads from a leading jobs website. The application has numerous technical components, (i) a Chrome extension, (ii) a Python webserver housed as a COM component and (iii) a VBA deserialization component. Taken together they demonstrate transmitting binary data from the browser through to the Excel worksheet.

In the US, initial jobless claims are running at a 4-week average of 3 million and the non-farm payrolls are currently at 20 million. These figures are both depressing and staggering. Europe can expect suffering on similar terms. Hopefully the code in this post can assist some to find work.

Co-browsing vs Web-scraping

Websites depend upon ad revenue to survive and so they need humans to see the adverts placed. Every time a human sees an advert it is known as an impression. Web-scraping is the process of running code to fetch a web page and to scrape data from the HTML; this typically involves the automation of a hidden web browser and as such any adverts on a hidden web page are no longer viewable but rendering ad impression statistics false. Eventually, this means that ad revenue is debased and devalued. As such, I disapprove of web scraping.

Instead, I give a ‘co-browsing’ application where code captures job leads from a web page that a human user is browsing. So this application is only active when a human browses a web page. This means any advert impressions are genuine and website’s revenue is not threatened.

The code

There are three separate parts to this application, (i) the chrome extension, (ii) the Python web server (housed as a COM component) and (iii) the VBA deserialization component. They are all in Github, https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/

The Chrome Extension

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/Chrome%20Extension/

The chrome extension will wait for a jobs page to load and then read the jobs data, it builds a JavaScript array of jobs and when complete it will convert the single dimensioned array of jobs into a two-dimensional grid array where each row is one job and the attributes are spread across the columns.

I convert to a grid because ultimately it will be sent to an Excel session where it is to be pasted onto a worksheet. The grid is then persisted to a byte array instead of JSON to take advantage of a data interchange format native to VB6, VBA that I have re-discovered and that allows a byte array to be deserialized to a VBA (OLE Automation) Variant (two dimensional).

Once converted to a byte array we make an XMLHttpRequest() to the Python web server (see next component). If you are experimenting then you might need to change port number in the code here.

There are two main JavaScript files, content.js and JavaScriptToVBAVariantArray.js. The former houses logic specific to this application whilst the latter is the array conversion code library file which I intend to use across a number of projects.

Python Web Server housed as a COM component

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/PythonWebSeverCallsBackToExcel

I have previously written about and given code as to how to write a Python web server housed as a COM component and instantiable from VBA. I have also previously written about and given code as to how to call back into Excel VBA from a Python class.

But there is something new in this Python web server which needs detailing, in short one cannot simply call back into Excel with an interface pointer passed in a different threading apartment; instead the interface pointer has first to be ‘marshalled’. I have encapsulated the plain vanilla callback code in the Python class CallbackInfo and the special marshalling case in its derived class MarshalledCallbackInfo.

In the context of the application, the Python web server is part of the pipeline that passes the byte array from the Chrome extension into Excel VBA. It calls into Excel VBA by calling Application.Run on a (marshalled) Excel.Application pointer. The name of the procedure called by Application.Run is configurable, and passed in. Time to look into the VBA code.

Excel VBA

https://github.com/smeaden/ExcelDevelopmentPlatform/tree/master/PythonWebSeverCallsBackToExcel/ExcelVBA

I do not check into whole workbooks, I check in the individual code modules instead. Thus to build the Excel VBA workbook code base one needs to import the modules. Luckily, I wrote one module called devBuild to import the rest of them. I intend to follow this pattern when placing code in GitHub. Look at the README.md file for more detail. From here, I’ll assume you’ve built a workbook codebase.

I have written about the serialization and deserialization of Variants to byte arrays and back again so I’ll refer you to that post for the details. In short we take the byte array passed from the Chrome extension via the Python web server and deserialize this to a two dimensional variant array which can then be pasted onto the worksheet.

I guess I could write some more code to build a cumulative list but the point of this project was to show binary data being passed from browser to Excel, to demonstrate (a) the plumbing and (b) the binary data interface format (i.e. no JSON).

VBA, Named Pipes (& JavaScript) - binary serialization revisited

In this post I revisit the matter of the serialization of VBA Variant arrays to bytes and back again. This can be used VBA to VBA or interoperating with other technologies that can be sequence bytes, in this post I demonstrate JavaScript to VBA. I incorporate a better serialization technique code that uses Windows OS Named Pipes as written by VBForums.com contributor Olaf Schmidt.

Background

This is a revisit of an earlier article where I saved and loaded a Variant array to disk using VBA’s Open File For Binary, Put and Get statements. Whilst the code did what I wanted, I had complained that my code required a disk operation which carries a performance penalty.

I am indebted to a commenter (who gives no name) who tipped me off as to some code on VBForums written by Olaf Schmidt; Olaf’s code serializes using Windows OS Named Pipes and not a disk write. The Named Pipes are purely in memory and so this makes his code faster.

Moreover, the Named Pipes serialization yields a leading sequence of bytes that describes the number of dimensions in the array and the size and bounds of each dimension. This was something formally missing from disk write version my code and which I had had to implement manually, something of a nuisance.

I am thus doubly indebted to Olaf Schmidt’s code and to the commenter who tipped me off. Thank you. Do please keep the comments coming.

VBA Class Module - cPipedVariants

So with Olaf Schmidt's code as a starting point I have modified it to handle the use case of VBA variant arrays, i.e. a two dimensional array which is ready for pasting onto a worksheet. Olaf's original code demonstrated the serialization of user-defined types and these data structures are more prevalent in Visual Basic 6 (VB6) whereas Excel developers (I would argue) are more likely to deal with grids drawn from a worksheet or grids to be pasted onto a worksheet.

If you want the original version that deals with the serialization of UDTs it is on this link here to vb6forums.com.

So what follows in cPipedVariants, a modification on Olaf's original class cPipedUDTs. Much of the code is easy to follow but I will comment on the ‘secret sauce’ of the InitializePipe function.

The two key lines of code are the call to CreateNamedPipeW and then the Open "\\.\pipe\foo" For Binary statement. If I switch the order of these two around then the code fails. Internally, in its implementation the Open For Binary Statement must have a special case where it identifies the "\\.\pipe\ " prefix and then looks up in the list of created named pipes. This is not documented in any Microsoft documentation, or indeed StackOverflow. Only the VB6Forums.com users and specifically Olaf Schmidt understand this lore, it must be a throw back to the era of VB6. Anyway, it does work and I am grateful.

Add a class to your VBA project, call it cPipedVariants and then paste in the following code

Option Explicit

'* Pipe-based helper to serialize/deserialize VB-Variants InMemory ... [based on original code by Olaf Schmidt 2015]
'* Based on code by Olaf Schmidt 2015, http://www.vbforums.com/showthread.php?807205-VB6-pipe-based-UDT-serializing-deserializing-InMemory


'* https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-createnamedpipea
Private Declare Function CreateNamedPipeW& Lib "kernel32" (ByVal lpName As Long, ByVal dwOpenMode&, ByVal dwPipeMode&, _
            ByVal nMaxInstances&, ByVal nOutBufferSize&, ByVal nInBufferSize&, _
            ByVal nDefaultTimeOut&, ByVal lpSecurityAttributes&)

'* https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-writefile
Private Declare Function WriteFile& Lib "kernel32" (ByVal hFile&, lpBuffer As Any, _
            ByVal nNumberOfBytesToWrite&, lpNumberOfBytesWritten&, ByVal lpOverlapped&)

'* https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-readfile
Private Declare Function ReadFile& Lib "kernel32" (ByVal hFile&, lpBuffer As Any, _
            ByVal nNumberOfBytesToRead&, lpNumberOfBytesRead&, ByVal lpOverlapped&)

'* https://docs.microsoft.com/en-us/windows/win32/api/namedpipeapi/nf-namedpipeapi-peeknamedpipe
Private Declare Function PeekNamedPipe& Lib "kernel32" (ByVal hNamedPipe&, lpBuffer As Any, _
            ByVal nBufferSize&, lpBytesRead&, lpTotalBytesAvail&, lpBytesLeftThisMessage&)

'* https://docs.microsoft.com/en-us/windows/win32/api/namedpipeapi/nf-namedpipeapi-disconnectnamedpipe
Private Declare Function DisconnectNamedPipe& Lib "kernel32" (ByVal hPipe&)

Private Declare Function CloseHandle& Lib "kernel32" (ByVal hObject&)

Private mhPipe As Long
Private mlFileNumber As Long
Private mabytSerialized() As Byte

Private Enum eOpenMode
    PIPE_ACCESS_INBOUND = 1
    PIPE_ACCESS_OUTBOUND = 2
    PIPE_ACCESS_DUPLEX = 3
End Enum

Private Enum ePipeMode
    PIPE_TYPE_BYTE = 0
    PIPE_TYPE_MESSAGE = 4

    PIPE_READMODE_BYTE = 0
    PIPE_READMODE_MESSAGE = 2
   
    PIPE_WAIT = 0
    PIPE_NOWAIT = 1
End Enum

Private Enum ePipeInstances
    PIPE_UNLIMITED_INSTANCES = 255
End Enum

Public Function InitializePipe(Optional sPipeNameSuffix As String = "vbaPipedVariantArrays") As Boolean
    Const csPipeNamePrefix As String = "\\.\pipe\"
    CleanUp
   
    Dim sPipeName As String
    sPipeName = csPipeNamePrefix & sPipeNameSuffix
   
    '* Must call CreateNamedPipe first before calling Open <<pathname>> For Binary otherwise you get bad file number
    mhPipe = CreateNamedPipeW(StrPtr(sPipeName), PIPE_ACCESS_DUPLEX, PIPE_TYPE_BYTE + PIPE_READMODE_BYTE + PIPE_WAIT, _
            PIPE_UNLIMITED_INSTANCES, -1, -1, 0, 0)
           
    If mhPipe = -1 Then mhPipe = 0 'reset from InvalidHandleValue to "no Handle"
   
    If mhPipe Then
        '* only try to find a free VB-FileNumber when mhPipe is valid (i.e. pipe has been created)
        mlFileNumber = FreeFile
        If mlFileNumber Then
            Open sPipeName For Binary As mlFileNumber  'open only, when we got an mlFileNumber
        End If
    End If
   
    InitializePipe = mhPipe <> 0 And mlFileNumber <> 0
End Function

Public Function SerializeToBytes(ByRef vSrc As Variant, ByRef pabytSerialized() As Byte) As Long

    Dim lBytesAvail As Long

    Debug.Assert IsArray(vSrc)

    If mlFileNumber <> 0 Then
   
        '* this next line writes the Variant array to the pipe
        Put mlFileNumber, 1, vSrc
       
        '* we should now have some bytes to read out of the pipe, use PeekNamedPipe to verify there are bytes available
        PeekNamedPipe mhPipe, ByVal 0&, 0, ByVal 0&, lBytesAvail, 0
       
        If lBytesAvail > 0 Then
           
            '* so now we can dimension the byte array
            ReDim Preserve pabytSerialized(0 To lBytesAvail - 1)
           
            '* and now we can read the bytes out of the pipe and into the byte array
            ReadFile mhPipe, pabytSerialized(0), lBytesAvail, lBytesAvail, ByVal 0&
           
            '* return number of bytes as a courtesy (not actually required)
            SerializeToBytes = lBytesAvail
        End If
    End If
End Function

Public Function DeserializeFromBytes(ByRef abytSerialized() As Byte, ByRef pvDest As Variant) As Long
   
    Dim lBytesWritten As Long
   
    If mhPipe <> 0 And mlFileNumber <> 0 Then

        '* write the byte array to the pipe
        WriteFile mhPipe, abytSerialized(0), UBound(abytSerialized) + 1, lBytesWritten, 0
       
        If lBytesWritten = UBound(abytSerialized) + 1 Then
            '* the pipe contains a byte array serialization of a variant array
            '* we can use VBA's Get statement to read it directly into a variant array variable
            Get mlFileNumber, 1, pvDest
           
            '* report the amount of deserialized Bytes as a courtesy (not actually required)
            DeserializeFromBytes = Loc(mlFileNumber)
        End If
    End If
End Function

Private Sub CleanUp()
    If mlFileNumber Then Close mlFileNumber: mlFileNumber = 0
    If mhPipe Then DisconnectNamedPipe mhPipe
    If mhPipe Then CloseHandle mhPipe: mhPipe = 0
End Sub

Private Sub Class_Terminate()
    CleanUp
End Sub

VBA Standard Module - tstPipedVariants

So now we need some client code. Add a standard module to your VBA project and paste in the following code. I called this module tstPipedVariants.

Sub SamePipeForSerializeAndDeserialize()
    Dim oPipe As cPipedVariants
    Set oPipe = New cPipedVariants
   
    If oPipe.InitializePipe Then
        Dim vSource As Variant
        vSource = TestData

        Dim abytSerialized() As Byte

        Call oPipe.SerializeToBytes(vSource, abytSerialized)

        Stop '* at this point vSource is populated but vDestination is empty

        Dim vDestination As Variant
        oPipe.DeserializeFromBytes abytSerialized, vDestination
   
        Stop
    End If
End Sub

Function TestData() As Variant
    Dim vSource(1 To 2, 1 To 4) As Variant
    vSource(1, 1) = "Hello World"
    vSource(1, 2) = True
    vSource(1, 3) = False
    vSource(1, 4) = Null
    vSource(2, 1) = 65535
    vSource(2, 2) = 7.5
    vSource(2, 3) = CDate("12:00:00 16-Sep-1989") 'now()
    vSource(2, 4) = CVErr(xlErrNA)
    TestData = vSource
End Function

In the module tstPipedVariants run the test code subroutine SamePipeForSerializeAndDeserialize() by navigating and pressing F5 to reach the first Stop statement. On the first Stop statement vSource is populated but vDestination isn’t.

However, the byte array abytSerialized() is populated and we can go inspect this. The first twenty bytes are similar to SafeArray and SafeArrayBounds structures. The first two bytes represent a vbVarType of vbArray+vbVariant in low byte, high byte order. Next, two bytes gives the count of dimensions. Then for each dimension there are 8 bytes, giving a 4 byte dimension size and a 4 byte lower bound. This abridged SafeArray descriptor is most welcome. When VBA code writes a variant array to disk it omits such a descriptor which meant I had to augment the code to manually write in the dimensions. I am very much pleased that the Named Pipes implementation does this automatically for me.

After the first twenty bytes of abridged SafeArray descriptor the rest of the data follows. I wrote this up in the original blog post so I’ll refer you to that and skip the rest.

Returning to the test code, press F5 again to get the second Stop statement and behold in the Locals window the vDestination variable is now populated exactly the same as the vSource variable. Note how we did not need to dimension the vDestination variable before populating it, excellent!

This completes the VBA to VBA demo. We can move onto the JavaScript to VBA use case.

Revisiting the Javascript to VBA use case

JavaScript Changes

In the original article I gave some Javascript code to serialize a Javascript array to a byte array that can then be deserialized to a VBA variant array. This JavaScript code needs modifying to interoperate with the new Named Pipes VBA code given above. The change required is to give a correctly formatted abridged safe array descriptor. This is a simple change found at the top of the JavaScriptToVBAVariantArray.prototype.persistGrid function. All other code remains the same, so no further explanation is required. The JavaScript module remains something loadable into both browser and server JavaScript environments. The Node.js project given in the original blog post can still be used.

I have only included the function that has changed, JavaScriptToVBAVariantArray.prototype.persistGrid; for rest of the JavaScript module listing see the original blog post.

JavaScriptToVBAVariantArray.prototype.persistGrid = function persistGrid(grid, rows, columns) {
	try {
		/* Opening sequence of bytes is a reduced form of SAFEARRAY and SAFEARRAYBOUND
		 * SAFEARRAY       https://docs.microsoft.com/en-gb/windows/win32/api/oaidl/ns-oaidl-safearray
		 * SAFEARRAYBOUND  https://docs.microsoft.com/en-gb/windows/win32/api/oaidl/ns-oaidl-safearraybound
		 */

		var payloadEncoded = new Uint8Array(20);

		// vbArray + vbVariant, lo byte, hi byte
		payloadEncoded[0] = 12; payloadEncoded[1] = 32;

		// number of dimensions, lo byte, hi byte
		payloadEncoded[2] = 2; payloadEncoded[3] = 0;

		// number of columns, 4 bytes, least significant byte first
		payloadEncoded[4] = columns % 256; payloadEncoded[5] = Math.floor(columns / 256);
		payloadEncoded[6] = 0; payloadEncoded[7] = 0;

		// columns lower bound (safearray)
		payloadEncoded[8] = 1; payloadEncoded[9] = 0;
		payloadEncoded[10] = 0; payloadEncoded[11] = 0;

		// number of rows, 4 bytes, least significant byte first
		payloadEncoded[12] = rows % 256; payloadEncoded[13] = Math.floor(rows / 256);
		payloadEncoded[14] = 0; payloadEncoded[15] = 0;

		// rows lower bound (safearray)
		payloadEncoded[16] = 1; payloadEncoded[17] = 0;
		payloadEncoded[18] = 0; payloadEncoded[19] = 0;

		var elementBytes;
		for (var colIdx = 0; colIdx < columns; colIdx++) {
			for (var rowIdx = 0; rowIdx < rows; rowIdx++) {
				elementBytes = this.persistVar(grid[rowIdx][colIdx]);
				var arr = [payloadEncoded, elementBytes];

				payloadEncoded = this.concatArrays(arr); // Browser
			}
		}
		return payloadEncoded;
	}
	catch (err) {
		console.log(err.message);
	}
};

VBA web client code

Turning to the client VBA code we can greatly simplify the code now that the dimensioning is done for us. The resulting code is now trivial, here it is below. Add the following code to the tstPipedVariants module you added earlier. This code below requires you to add a Tools->Reference to Microsoft WinHTTP Services, version 5.1...

Sub TestByWinHTTP()
    '* this calls the Node.js project with the new JavaScript serialization
    Dim oWinHttp As WinHttp.WinHttpRequest '* Tools->References->Microsoft WinHTTP Services, version 5.1
    Set oWinHttp = New WinHttp.WinHttpRequest
    oWinHttp.Open "GET", "http://localhost:1337/", False
    oWinHttp.send
   
    If oWinHttp.Status = 200 Then
        If IsEmpty(oWinHttp.ResponseBody) Then Err.Raise vbObjectError, , "No bytes returned!"
        If UBound(oWinHttp.ResponseBody) = 0 Then Err.Raise vbObjectError, , "No bytes returned!"
       
        Dim oPipedVariants As cPipedVariants
        Set oPipedVariants = New cPipedVariants
        If oPipedVariants.InitializePipe Then
       
            Dim vGrid As Variant
            oPipedVariants.DeserializeFromBytes oWinHttp.ResponseBody, vGrid
           
            Stop '* Observe vGrid in the Locals window
           
            '* vGrid is now ready to paste on a worksheet
        End If
    End If
End Sub

So run this code with the Node.js project running and the vGrid should be populated. That's all folks. Enjoy!

Thanks again

Thanks once again to Olaf Schmidt and the anonymous tipper! Getting help to fix my code is very welcome.

Javascript - Binary representation of VBA's Variant Array

I am delighted to give a world first in this post where I give JavaScript code to convert a JavaScript array into a byte array loadable into a VBA variant array. This gives an efficient binary serialization format for web services and other network calls where the end client is VBA and especially Excel worksheet cells.

Following on from the previous post where I document an undocumented file serialization format used by VBA with the FreeFile, Open For Binary and Put statements. I can now go on to give some JavaScript to convert two dimensional JavaScript arrays containing JavaScript variables therein to a byte array directly loadable by VBA into a variant array.

This now means a VBA client need not consume Xml or JSON when calling a web service. This is a big win because both Xml and JSON are string based and require parsing into data structures which is expensive. The Xml needs further code to interrogate the nodes with XPath etc. If the end destination for the data is the worksheet then the variant array can be directly pasted into cells (some code is give below).

To get VBA client code to do less work requires the server-side code to do more work. A JavaScript module, JavaScriptToVBAVariantArray.js, is given below which converts JavaScript arrays and variables into a byte array directly loadable by VBA (save for an few initial bytes conveying number of rows and columns).

As well the key JavaScript module, JavaScriptToVBAVariantArray.js, I also give some Node.js server-side JavaScript code to implement a web service that demonstrates the byte array being communicated to a VBA client.

JavaScriptToVBAVariantArray.js - module common to browser and server

So the following JavaScript module is usable in both the browser client and a Node.js server. It encapsulates all the logic to convert a two dimensional (block not nested) JavaScript array containing primitives of strings, Booleans, dates and numbers to a byte array loadable by VBA into a Variant array ready to be pasted onto a worksheet. Also included is logic to take a JavaScript Error object and convert this to an Excel worksheet error code so one can transmit #REF!, #N/A! and #NAME? errors.

The code is implemented as a prototyped class, it includes two test methods JavaScriptToVBAVariantArray.testPersistVar and JavaScriptToVBAVariantArray.testPersistGrid to demonstrate the code's usage. The remaining methods contain production logic that utilize ArrayBuffers and collaborating classes Float64Array and Uint8Array.

So the following code to be saved to its own separate file called JavaScriptToVBAVariantArray.js which then should be added to a Node.js project. I am using Microsoft Visual Studio for a development environment.

'use strict';

// module exporting for node.js and browsers with thanks to
// https://www.matteoagosti.com/blog/2013/02/24/writing-javascript-modules-for-both-browser-and-node/

(function () {
    var JavaScriptToVBAVariantArray = (function () {
        var JavaScriptToVBAVariantArray = function (options) {
            var pass; //...
        };


        JavaScriptToVBAVariantArray.prototype.testPersistVar = function testPersistVar() {
            try {
                var payload;
                //payload = "Hello World";
                //payload = false;
                //payload = 655.35;
                payload = new Date(1989, 9, 16, 12, 0, 0);
                var payloadEncoded = persistVar(payload);
                return payloadEncoded;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.testPersistGrid = function testPersistGrid() {
            try {
                var rows = 2;
                var columns = 4;
                var arr = this.createGrid(rows, columns);
                arr[0][0] = "Hello World";
                arr[0][1] = true;
                arr[0][2] = false;
                arr[0][3] = null;

                arr[1][0] = 65535;
                arr[1][1] = 7.5;
                arr[1][2] = new Date(1989, 9, 16, 12, 0, 0);
                arr[1][3] = new Error(2042);

                var payloadEncoded = this.persistGrid(arr, rows, columns);
                return payloadEncoded;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistGrid = function persistGrid(grid, rows, columns) {
            try {

                var payloadEncoded = new Uint8Array(4);
                payloadEncoded[0] = rows % 256; payloadEncoded[1] = Math.floor(rows / 256);
                payloadEncoded[2] = columns % 256; payloadEncoded[3] = Math.floor(columns / 256);
                var elementBytes;
                for (var colIdx = 0; colIdx < columns; colIdx++) {
                    for (var rowIdx = 0; rowIdx < rows; rowIdx++) {
                        elementBytes = this.persistVar(grid[rowIdx][colIdx]);
                        var arr = [payloadEncoded, elementBytes];

                        payloadEncoded = this.concatArrays(arr); // Browser
                    }
                }
                return payloadEncoded;
            }
            catch (err) {
                console.log(err.message);
            }
        };


        JavaScriptToVBAVariantArray.prototype.concatArrays = function concatArrays(arrays) {
            // With thanks to https://javascript.info/arraybuffer-binary-arrays


            // sum of individual array lengths
            let totalLength = arrays.reduce((acc, value) => acc + value.length, 0);

            if (!arrays.length) return null;

            let result = new Uint8Array(totalLength);

            // for each array - copy it over result
            // next array is copied right after the previous one
            let length = 0;
            for (let array of arrays) {
                result.set(array, length);
                length += array.length;
            }

            return result;
        };

        JavaScriptToVBAVariantArray.prototype.createGrid = function createGrid(rows, columns) {
            try {
                return Array.from(Array(rows), () => new Array(columns));
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistVar = function persistVar(v) {
            try {

                if (v === null) {
                    // return a Null
                    var nullVt = new Uint8Array(2);
                    nullVt[0] = 1;
                    return nullVt;

                } else if (v instanceof Error) {

                    return this.persistError(v);

                } else if (typeof v === 'undefined') {
                    return new Uint8Array(2); // return an Empty

                } else if (typeof v === "boolean") {
                    // variable is a boolean
                    return this.persistBool(v);
                } else if (typeof v.getMonth === "function") {
                    // variable is a Date
                    return this.persistDate(v);
                } else if (typeof v === "string") {
                    // variable is a boolean
                    return this.persistString(v);
                } else if (typeof v === "number") {
                    // variable is a number
                    return this.persistNumber(v);
                }

            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistError = function persistError(v) {
            try {
                var errorVt = new Uint8Array(6); // return a vtError
                errorVt[0] = 10; errorVt[4] = 10; errorVt[5] = 128;

                var errorNumber;
                try {
                    errorNumber = parseInt(v.message);
                }
                catch (err) {
                    errorNumber = 2000;
                    console.log(err.message);
                }
                errorVt[2] = errorNumber % 256; errorVt[3] = Math.floor(errorNumber / 256);

                return errorVt;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistNumber = function persistNumber(v) {
            try {
                var bytes;
                if (Number.isInteger(v)) {
                    bytes = new Uint8Array(6);
                    bytes[0] = 3; bytes[1] = 0;  // VarType 5 = Long
                    bytes[2] = v % 256; v = Math.floor(v / 256);
                    bytes[3] = v % 256; v = Math.floor(v / 256);
                    bytes[4] = v % 256; v = Math.floor(v / 256);
                    bytes[5] = v % 256;

                } else {
                    bytes = this.persistDouble(v, 5);
                }
                return bytes;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistDate = function persistDate(v) {
            try {
                // convert JavaScript 1970 base to VBA 1900 base
                // https://stackoverflow.com/questions/46200980/excel-convert-javascript-unix-timestamp-to-date/54153878#answer-54153878
                var xlDate = v / (1000 * 60 * 60 * 24) + 25569;
                return this.persistDouble(xlDate, 7);
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistDouble = function persistDouble(v, vt) {
            try {
                var bytes;
                bytes = new Uint8Array(10);
                bytes[0] = vt; bytes[1] = 0;  // VarType 5 = Double or 7 = Date
                var doubleAsBytes = this.doubleToByteArray(v);
                for (var idx = 0; idx < 8; idx++) {
                    bytes[2 + idx] = doubleAsBytes[idx];
                }
                return bytes;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.doubleToByteArray = function doubleToByteArray(number) {
            try {
                // https://stackoverflow.com/questions/25942516/double-to-byte-array-conversion-in-javascript/25943197#answer-39515587
                var buffer = new ArrayBuffer(8);         // JS numbers are 8 bytes long, or 64 bits
                var longNum = new Float64Array(buffer);  // so equivalent to Float64

                longNum[0] = number;

                return Array.from(new Int8Array(buffer));
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistString = function persistString(v) {
            try {
                var strlen = v.length;
                var bytes = new Uint8Array(strlen + 4);
                bytes[0] = 8; bytes[1] = 0;  // VarType 8 = String
                bytes[2] = strlen % 256; bytes[3] = Math.floor(strlen / 256);
                for (var idx = 0; idx < strlen; idx++) {
                    bytes[idx + 4] = v.charCodeAt(idx);
                }
                return bytes;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptToVBAVariantArray.prototype.persistBool = function persistBool(v) {
            try {
                var bytes = new Uint8Array(4);
                bytes[0] = 11; bytes[1] = 0;   // VarType 11 = Boolean
                if (v === true) {
                    bytes[2] = 255; bytes[3] = 255;
                } else {
                    bytes[2] = 0; bytes[3] = 0;
                }
                return bytes;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        return JavaScriptToVBAVariantArray;
    })();

    if (typeof module !== 'undefined' && typeof module.exports !== 'undefined')
        module.exports = JavaScriptToVBAVariantArray;
    else
        window.JavaScriptToVBAVariantArray = JavaScriptToVBAVariantArray;
})();

Node.js server code

The following code should is for running in Node.js. Start with a new Blank Node.js Web Application project in Microsoft Visual Studio, add the JavaScriptToVBAVariantArray.js module listed above. In the server.js file replace the code with the listing below. Running the project should spawn a new browser and then print something like the following, i.e. a mix of printable and non-printable characters; this is a browser attempting to display the generated byte array.

    Hello World ÿÿ ÿÿ  @  «ªªª® à@  ú  €

'use strict';

{
    try {
        var JavaScriptToVBAVariantArray = require('./JavaScriptToVBAVariantArray');
        var v = new JavaScriptToVBAVariantArray();
        var payloadEncoded = v.testPersistGrid();
    }
    catch (err) {
        console.log('Could not find JavaScriptToVBAVariantArray.js module:' + err.message);
    }
}

require('http').createServer(function (req, res) {
    try {
        var q = require('url').parse(req.url, true);
        if (q.pathname === '/') {
            /* it's the request for our serialized Variant array */
            res.writeHead(200, { 'Content-Type': 'text/html' });

            // https://stackoverflow.com/questions/8609289/convert-a-binary-nodejs-buffer-to-javascript-arraybuffer
            var buffer = Buffer.from(new Uint8Array(payloadEncoded));
            res.end(buffer);
        }
        else {
            var filename = "." + q.pathname;
            var fs = require('fs');
            fs.readFile(filename, function (err, data) {
                if (err) {
                    res.writeHead(404, { 'Content-Type': 'text/html' });
                    return res.end("404 Not Found");
                }
                res.writeHead(200, { 'Content-Type': 'text/html' });
                res.write(data);
                return res.end();
            });
        }
    }
    catch (err) {
        console.log(err.message);
    }
}).listen(process.env.PORT || 1337);

HTMLPage.html (optional)

I intend the end client to be VBA as we are demonstrating how to serialize to a byte array loadable by VBA. Nevertheless, I include an HTML page to prove the JavaScript works in the browser. Also, if one follows the instructions on the web page and one opens the Dev Tools and the Console then one can see the byte array is a more readily viewable form (or at least more viewable that the non-printable characters above). Once this is added to your Node.js project then in a browser you can type the url http://localhost:1337/HtmlPage.html to access the page.

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title></title>
</head>
<body>
    <p>
        <span style="font-family:Courier New, Courier, monospace">
            Look in the console!
            Do this by right-click menu and then Inspect to get the DevTools window
            then click on the Console tab
        </span>
    </p>
    <script src="https://raw.githubusercontent.com/arextar/browser-buffer/master/browser-buffer.min.js"></script>
    <script src="JavaScriptToVBAVariantArray.js"></script>
    <script>
        var v = new JavaScriptToVBAVariantArray();
        var payloadEncoded = v.testPersistGrid();
        console.log(payloadEncoded);
    </script>
</body>
</html>

Client VBA Code

Finally, we get to the VBA client code. Paste this into a standard module, you will need some code from the previous post. You will also need a Tools->Reference to Microsoft WinHTTP Services, version 5.1 to make the HTTP call.

So the listing below will call into the Javascript web service given above, it takes the HTTP response and then reads the rows and columns from the first four bytes, the remaining bytes it will write to a temporary file (this could be speeded with the use of a RAM disk). The temporary file is then read directly into a variant array new dimensioned with the correct rows and columns.

At this point any VBA code can work with this variant array like it was any other as the byte array has been converted to a VBA native data structure! The code below goes on to paste the variant array to a block of cells on a worksheet (please save your work as this overwrites the cells).

In the pasted cells (screenshot given) we can see a string, two booleans, a deliberately empty cell, an integer, a decimal number, a date, and a #N/A worksheet error code. This demonstrates the full range of variable types that can be transmitted via the byte array.

Sub TestByWinHTTP()
    Dim WinHttp As WinHttp.WinHttpRequest '* Tools->References->Microsoft WinHTTP Services, version 5.1
    Set WinHttp = New WinHttp.WinHttpRequest
    WinHttp.Open "GET", "http://localhost:1337/", False
    WinHttp.send
    
    If WinHttp.Status = 200 Then
        If IsEmpty(WinHttp.responseBody) Then Err.Raise vbObjectError, , "No bytes returned!"
        If UBound(WinHttp.responseBody) = 0 Then Err.Raise vbObjectError, , "No bytes returned!"
        
        '**** SAVE ****
        Dim lSaveFileNum As Long
        Dim sFullFilename As String
        sFullFilename = OpenCleanFileForBinary("JavascriptToVBABin.bin", lSaveFileNum)
        
        
        Dim lRows As Long, lColumns As Long
        lRows = WinHttp.responseBody(0) + WinHttp.responseBody(1) * 256
        lColumns = WinHttp.responseBody(2) + WinHttp.responseBody(3) * 256
        
        Dim lByteLoop As Long
        For lByteLoop = 4 To UBound(WinHttp.responseBody)
            Put lSaveFileNum, , CByte(WinHttp.responseBody(lByteLoop))
        Next lByteLoop
        Close lSaveFileNum
        
        '**** LOAD ****
        
        ReDim vGrid(0 To lRows - 1, 0 To lColumns - 1) As Variant
        Dim lLoadFileNum As Long
        lLoadFileNum = OpenFileForBinary(sFullFilename)
        Get lLoadFileNum, , vGrid
        Close lLoadFileNum
        
        '**** WRITE TO WORKSHEET CELLS ****
        
        Stop '* next line will overwrite cells!! please save your work!!
        Sheet1.Range("a11").Resize(lRows, lColumns).Value = vGrid
        Stop '* Observe the cells on the worksheet
    End If
End Sub

Final Thoughts

It is a shame that the serialization format does not natively include the dimensions of the array block/grid as I could shave even more lines of code on the VBA side. It is also a shame we have to write the file to disk instead of being able to load directly into memory; there is the option of using a RAM disk to speed the file operation. In the meantime I feel I can write JavaScript Chrome extensions that could now transmit blocks of cells to an Excel client in VBA's native serialization format. Cool!

Javascript - Serialize Object to XML on both server and in browser

In this post I give some JavaScript code that takes a JavaScript object variable and serializes it to XML. The code works both on Node.js and in the browser. JavaScript developers frown on XML and will advocate JSON. So an XML solution is difficult to find, I have based this code on a StackOverflow answer.

Background

So, I am again contemplating serialization formats. I want to do some web-scraping and I reiterate that Excel Developers should not do this in VBA but instead in a Chrome Extension which is a JavaScript program running in the Chrome Browser. A Chrome Extension can scrape some information and then pass this along as a payload to a web server by calling out with an HTTP POST request. The receiving web server ought to a simple local server dedicated to listening for that particular Chrome Extension. Previously on this blog, I have given code where a C# component running in Excel can serve as a web server; in that example the payload was in JSON format.

A while back on this blog I went crazy for JavaScript once I found the ScriptControl can parse JSON into an object query-able by VBA code. I have since cooled on this JSON/ScriptControl design pattern and have realised I still have a soft spot for XML, mainly for the inbuilt XML parser with which all VBA developers will be familiar. But as mentioned above Javascript developers prefer JSON and so you won't find many examples of serialization to XML out there. So below is some working code based on a StackOverflow answer.

Unifying Browser and Node.js development

I find JavaScript development quite challenging as I'm never sure I'm using the optimal development environment. Using Node.js in Visual Studio is very good but I do not know how to get Visual Studio to attach and debug client side code. To debug client side code I use Chrome's good debugger tools and Notepad++ (I know) or Visual Studio Code. I find I write code in two different styles for each environment. I'd like to write code once for both client and server and for that I have a trick to show you.

By modularizing the code in separate files or modules a JavaScript project can be broken up into more manageable pieces. To control visibility of a module's code to outside callers we use the module.exports construct but this is not available in the browser so we have to add the code to the browser's Window object instead. This is all explained in this article Writing JavaScript modules for both Browser and Node.js by Matteo Agosti.

In the article Matteo Agosti gives a JavaScript class (don't be misled by JavaScript's odd class-by-prototype syntax) in a separate file and it uses the module.exports to make it visible to other files/modules. The code Matteo gives has an encompassing IIFE (Immediately Invoked Function Expression) to determine if running in a browser or not. This IIFE syntax and also the class-by-prototype syntax can be a little confusing and I'd recommend copying, pasting and editing for your own purposes and this is what I did for me.

The code

Node.js dependencies

The code below adds value in that it will run in a Node.js project as well as a browser. Any Node.js project will require the following npm packages installed:

• xmldom
• xmlserializer

Test object

An object called foo is created in JavaScriptObjectToXml.prototype.testJavascriptObjectToXml()

                var foo = new Object();
                foo.prop1 = "bar";
                foo.prop2 = "baz";

                foo.objectArray = [];
                var subObject = new Object();
                subObject.laugh = "haha";
                foo.objectArray.push(subObject);
                var subObject1 = new Object();
                subObject1.greeting = "hello";
                foo.objectArray.push(subObject1);

                foo.numberArray = [];
                foo.numberArray.push(0);
                foo.numberArray.push(1);
                foo.numberArray.push(2);

and a JSON representation of this object would be

{"prop1":"bar","prop2":"baz","objectArray":[{"laugh":"haha"},{"greeting":"hello"}],"numberArray":[0,1,2]}

but an XML reprentation would be

<foo xmlns="null" prop1="bar" prop2="baz">
<objectArray>
<objectArray-0 laugh="haha"/>
<objectArray-1 greeting="hello"/>
</objectArray>
<numberArray numberArray-0="0" numberArray-1="1" numberArray-2="2"/>
</foo>

Code Listings

And so here are the full listings:

JavaScriptObjectToXml.js - this is the serialization logic

'use strict';

// module exporting for node.js and browsers with thanks to
// https://www.matteoagosti.com/blog/2013/02/24/writing-javascript-modules-for-both-browser-and-node/

(function () {
    var JavaScriptObjectToXml = (function () {
        var JavaScriptObjectToXml = function (options) {
            var pass; //...
        };

        JavaScriptObjectToXml.prototype.testJavascriptObjectToXml = function testJavascriptObjectToXml() {
            try {
                // debugger;  /* uncomment this line for a breakpoint for both node.js and the browser */
                var foo = new Object();
                foo.prop1 = "bar";
                foo.prop2 = "baz";

                foo.objectArray = [];
                var subObject = new Object();
                subObject.laugh = "haha";
                foo.objectArray.push(subObject);
                var subObject1 = new Object();
                subObject1.greeting = "hello";
                foo.objectArray.push(subObject1);

                foo.numberArray = [];
                foo.numberArray.push(0);
                foo.numberArray.push(1);
                foo.numberArray.push(2);

                //console.log(JSON.stringify(foo));

                var retval = this.javascriptObjectToXml(foo, 'foo');
                console.log(retval);
                return retval;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptObjectToXml.prototype.javascriptObjectToXml = function javascriptObjectToXml(obj, objName) {
            try {
                var rootNodeName = 'root';
                var xmlDoc = this.createXmlDocumentRoot(rootNodeName);
                this.serializeNestedNodeXML(xmlDoc, xmlDoc.documentElement, objName, obj);
                return this.getXmlSerializer().serializeToString(xmlDoc.documentElement.firstChild);
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptObjectToXml.prototype.createXmlDocumentRoot = function createXmlDocumentRoot(rootNodeName) {
            try {
                var xmlDoc;
                if (typeof document !== 'undefined') {
                    /* for browsers where document is available */
                    xmlDoc = document.implementation.createDocument(null, rootNodeName, null);
                }
                else {
                    /* for node.js code, needs npm install xmldom */
                    var DOMParser = require('xmldom').DOMParser;
                    xmlDoc = new DOMParser().parseFromString('<' + rootNodeName + '/>');
                }
                return xmlDoc;
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptObjectToXml.prototype.getXmlSerializer = function getXmlSerializer() {
            try {
                if (typeof document !== 'undefined') {
                    /* for browsers */
                    return new XMLSerializer();
                }
                else {
                    /* for node.js code, needs npm install xmlserializer */
                    return require('xmlserializer');
                }
            }
            catch (err) {
                console.log(err.message);
            }
        };

        JavaScriptObjectToXml.prototype.serializeNestedNodeXML = function serializeNestedNodeXML (xmlDoc, parentNode, newNodeName, obj) {
            /* based on StackOverflow answer
            /  https://stackoverflow.com/questions/19772917/how-to-convert-or-serialize-javascript-data-object-or-model-to-xml-using-ext#answer-48967287
            /  by StackOverflow user https://stackoverflow.com/users/355272/martin   */
            try {
                if (Array.isArray(obj)) {
                    var xmlArrayNode = xmlDoc.createElement(newNodeName);
                    parentNode.appendChild(xmlArrayNode);

                    for (var idx = 0, length = obj.length; idx < length; idx++) {
                        serializeNestedNodeXML(xmlDoc, xmlArrayNode, newNodeName + '-' + idx, obj[idx]);
                        //console.log(obj[idx]);
                    }

                    return;     // Do not process array properties
                } else if (typeof obj !== 'undefined') {
                    var objType = typeof obj;
                    switch (objType) {
                        case 'string': case 'number': case 'boolean':
                            parentNode.setAttribute(newNodeName, obj);
                            break;
                        case 'object':
                            var xmlProp = xmlDoc.createElement(newNodeName);
                            parentNode.appendChild(xmlProp);
                            for (var prop in obj) {
                                serializeNestedNodeXML(xmlDoc, xmlProp, prop, obj[prop]);
                            }
                            break;
                    }
                }
            }
            catch (err) {
                console.log(err.message);
            }
        };

        return JavaScriptObjectToXml;
    })();

    if (typeof module !== 'undefined' && typeof module.exports !== 'undefined')
        module.exports = JavaScriptObjectToXml;
    else
        window.JavaScriptObjectToXml = JavaScriptObjectToXml;
})();

server.js - this is web server file

'use strict';

{
    try {
        var JavaScriptObjectToXml = require('./JavaScriptObjectToXml');
        var v = new JavaScriptObjectToXml();
        var fooAsXml = v.testJavascriptObjectToXml();
    }
    catch (err) {
        console.log('Could not find JavaScripObjectToXml.js module:' + err.message);
    }
}

require('http').createServer(function (req, res) {
    try {
        var q = require('url').parse(req.url, true);
        if (q.pathname === '/') {
            /* it's the request for our serialized xml */
            res.writeHead(200, { 'Content-Type': 'text/xml' });
            res.end(fooAsXml + 'n');
        }
        else {
            var filename = "." + q.pathname;
            var fs = require('fs');
            fs.readFile(filename, function (err, data) {
                if (err) {
                    res.writeHead(404, { 'Content-Type': 'text/html' });
                    return res.end("404 Not Found");
                }
                res.writeHead(200, { 'Content-Type': 'text/html' });
                res.write(data);
                return res.end();
            });
        }
    }
    catch (err) {
        console.log(err.message);
    }
}).listen(process.env.PORT || 1337);

HtmlPage.html - this is for serving to the client

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title></title>
</head>
<body>
    <p><span style="font-family:Courier New, Courier, monospace">Look in the console!  
        Do this by right-click menu and then Inspect to get the DevTools window 
        then click on the Console tab</span></p>
    <script src="JavaScriptObjectToXml.js"></script>
    <script>
        var v = new JavaScriptObjectToXml();
        var fooAsXml = v.testJavascriptObjectToXml();
    </script>
</body>
</html>

Some VBA test client code to ensure XML does parse in VBA/MSXML parser library.

Option Explicit

Sub Test()

    Dim xhr As MSXML2.XMLHTTP60
    Set xhr = New MSXML2.XMLHTTP60
    xhr.Open "GET", "http://localhost:1337/", False
    xhr.send
    
    Debug.Print xhr.responseText
    Dim xmlDoc As MSXML2.DOMDocument60
    Set xmlDoc = New MSXML2.DOMDocument60
    Debug.Assert xmlDoc.LoadXML(xhr.responseText) '* true means it parsed fine

End Sub

Running the code

So the above files are to be placed into a Visual Studio instance with a blank Node.js project and press F5 and the Xml representation should appear in both the command line window spawned by Visual Studio and in the browser spawned by Visual Studio. This proves it works on the server. To see it working on the client side change the address in the browser to point to http://localhost:1337/HtmlPage.html and then look in Chrome's console (instructions are on the web page).

Final Thoughts

I'm still not totally happy with serialization formats. I'd love to get something from a webservice and paste directly onto an Excel worksheet. I need to think about this.

Unifying browser and server code should be a good win that will pay dividends in the long run so I am happy about that.

In the meantime, enjoy!

Python - Javascript - MutationObserver- Chrome Extension - ClockWatch

So in the previous post I gave code to detect changes in a web page and POST those changes to a Python based web server which in turn writes the payload to a folder, i.e. an HTTP based message queue. But that code was embedded in the same page as that which changed and was unrealistic. Much better if we can write a Chrome extension to sit in the browser and observe changes on other web pages. That is what we do here on this blog post.

Legal Note

Do please get permission before you start web scraping otherwise you fall foul of the law. There are legitimate use cases for this technology: imagine you are at a large company with plenty of IT teams, you've asked for a data feed but other team say you are not a priority but say feel free to web scrape.

Minimal Chrome Extension

The bare minimum to get a Chrome Extension working is one folder containing two files, that's all. The folder can be named anything. The two files are (i) content.js and (ii) manifest.json.

manifest.json

Here is an example manifest.json file

  {
    "name": "Clock Watch",
    "version": "0.1",
    "description": "example of a Mutation Observer",
    "permissions": [],
    "content_scripts": [ {
      "js": [ "content.js" ],
      "matches": [ "http://exceldevelopmentplatform.blogspot.com/2018/06/javascript-dynamic-blog-clock.html"   ] }
    ],
    "manifest_version": 2
  }

So much of the manifest is boilerplate but one thing to note of interest in the matches array which tells what pages to run extension over. I have published the clock code to a separate blog post, http://exceldevelopmentplatform.blogspot.com/2018/06/javascript-dynamic-blog-clock.html and we'll use that as a laboratory test page. In the matches array one can give a selection of pages, here we only have one.

content.js

This is the content.js file with the code pretty much unchanged from the previous post; all that is added is an IIFE (Immediately Invoked Function Expression) which serves as an entry point, i.e. code that runs first. Also, we have a try catch block around our MutationObserver code to help debugging.

~function () {
  'use strict';
  console.log("clock watch iife running");
  setTimeout(startObserving,1000);
  
}();

function startObserving() {
  'use strict';
  
  try {
   
        console.log("entering startObserving");
        var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver;
        if (MutationObserver == null)
            console.log("MutationObserver not available");

        // mutation observer code from https://developer.mozilla.org/en-US/docs/Web/API/MutationObserver
        var targetNode = document.getElementById('clock');

        // Options for the observer (which mutations to observe)
        var config = { attributes: true, childList: true };

        // Callback function to execute when mutations are observed
        var callback = function (mutationsList) {

            for (var mutation of mutationsList) {
                //debugger;
                //console.log(mutation);  //uncomment to see the full MutationRecord
                var shorterMutationRecord = "{ target: div#clock, newData: " + mutation.addedNodes[0].data + " }"

                console.log(shorterMutationRecord);

                var xhr = new XMLHttpRequest();
                xhr.open("POST", "http://127.0.0.1:8000");
                //xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                xhr.send(shorterMutationRecord);

            }
        };

        // Create an observer instance linked to the callback function
        var observer = new MutationObserver(callback);

        // Start observing the target node for configured mutations
        observer.observe(targetNode, config);

        // Later, you can stop observing
        //observer.disconnect();   
  }
  catch(err) {
   console.log("err.message: "+ err.message);
  }
}

Installing the Extension

In the Chrome browser navigate to chrome://extensions/. Click on "Load Unpacked" and navigate to the folder containing your two files. In my case N:\Clock Watch Chrome Extension\. Then your extension is loaded and should appear.

You can go look at the details page if you want but we are pretty much done. All you need do now is to navigate to the clock page. You'll know if you extension is loaded because a new icon appears in the top right of the Chrome browser window, on the toolbar. In the absence of a given icon, Chrome will take the first letter of your extension and use that as an icon, so below ringed in green in the "C" icon, hover over that and it will read "Clock Watch". Click on the icon and one can remove from menu if you want.

Screenshots- The Clock and the Message Queue

As highlighted in the previous post we have some code which runs a Python web server, taking HTTP POST calls and writing the payloads to a folder. Here is a screenshot to show that working

Final Thoughts

So what have we achieved here? We have a Chrome Extension which observes a page and reports the changes to a message queue by calling out with XmlHttpRequest to a Python web server. Cool but do please use responsibly.

Links

• How to install Chrome extensions manually

Javascript - Dynamic Blog - Clock

This page is just to host a clock. This is part of an Chrome Extension blog post to be published soon. This page demonstrates that blogspot can run javascript code making pages more dynamic.

14:27:34

The code to make this clock tick is to be found in a <script> tag....

    <script>
        ~function () {
            'use strict';
            startTime();
  
        }();


        function startTime() {
            var today = new Date();
            var h = today.getHours();
            var m = today.getMinutes();
            var s = today.getSeconds();
            m = padZero(m);
            s = padZero(s);
            document.getElementById('clock').innerHTML =
                h + ":" + m + ":" + s;
            var t = setTimeout(startTime, 1000);
        }
        function padZero(i) {
            if (i < 10) { i = "0" + i };  // add zero in front of numbers < 10
            return i;
        }
    </script>

Python - Javascript - MutationObserver - detecting and POST changes to a page

So in the last post I showed how to write a message queue (I have improved that code, so the latest version is on this page). Next I write code in an HTML+javascript web page which detects changes in the web page and posts those changes to our message queue.

At this point I must confess to using Visual Studio to create new Python projects, it gives me Intellisense, but I still run code from the command window. The VS project is relevant in this post because the Python needs to changed to serve up a web page and also accept POST requests but they must come from the same domain otherwise one gets irritating cross domain errors. So keeping the web page and the Python script in the same project makes sense.

So here is a screenshot of my Visual Studio project explorer window.

Chrome Only Please (No IE)

By the way, I only use Chrome for this project. IE is going away, a fact which prompted me to investigated other ways of web-scraping. So this little project has arisen out of the need to move away from IE.

ClockWithMutationObserver.html

So we need a page, ClockWithMutationObserver.html, that display a clock (with thanks to w3schools.com) . The clock's div has an id of clock. Save it in the same directory as the Python script.

<!DOCTYPE html>
<html>
<head>
    <script>
        function startTime() {
            var today = new Date();
            var h = today.getHours();
            var m = today.getMinutes();
            var s = today.getSeconds();
            m = padZero(m);
            s = padZero(s);
            document.getElementById('clock').innerHTML =
                h + ":" + m + ":" + s;
            var t = setTimeout(startTime, 1000);
        }
        function padZero(i) {
            if (i < 10) { i = "0" + i };  // add zero in front of numbers < 10
            return i;
        }
    </script>

</head>

<body onload="startTime()">

    <div style="font-size:72pt" id="clock"></div>

    <script>

        console.log("entering startObserving");
        var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver;
        if (MutationObserver == null)
            console.log("MutationObserver not available");

        // mutation observer code from https://developer.mozilla.org/en-US/docs/Web/API/MutationObserver
        var targetNode = document.getElementById('clock');

        // Options for the observer (which mutations to observe)
        var config = { attributes: true, childList: true };

        // Callback function to execute when mutations are observed
        var callback = function (mutationsList) {

            for (var mutation of mutationsList) {
                //debugger;
                //console.log(mutation);  //uncomment to see the full MutationRecord
                var shorterMutationRecord = "{ target: div#clock, newData: " + mutation.addedNodes[0].data + " }"

                console.log(shorterMutationRecord);

                var xhr = new XMLHttpRequest();
                xhr.open("POST", "http://127.0.0.1:8000");
                //xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                xhr.send(shorterMutationRecord);

            }
        };

        // Create an observer instance linked to the callback function
        var observer = new MutationObserver(callback);

        // Start observing the target node for configured mutations
        observer.observe(targetNode, config);

        // Later, you can stop observing
        //observer.disconnect();

    </script>
</body>
</html>

So the above page gives a nice large clock (in 72pt), something like this

20:37:01

Javascript MutationObserver

So in the world of Javascript the Mozilla Developer Network is a good source of documentation. Thankfully, they have a good page on MutationObserver which allows us to detect changes to the DOM.

In the above web page, there are two blocks of JavaScript, (i) the one on the head drives the clock itself; (ii) and the one at the base is the MutationObserver logic. We find the element we want to observe then we define a callback function for when it changes.

MutationRecords

When our callback function is called, we loop through the changes, for each change there is a detailed MutationRecord and they are worth investigating. In the code, the line //console.log(mutation); is commented out. Uncomment that line if you want to see the rich detail given for each change in the Chrome console. Because of all the detail, I copy across the details I want to a new object, actually a string because that is what I will POST back.

XHR to same domain avoid cross domain errors

We then use an AJAX XHR call to POST the data. It is helpful (but not strictly obligatory) to POST back to the same domain whence the page came; this helps to avoid cross origin domain errors.

PythonHTTPMessageQueue.py

So I have some updated Python web server message queue code here. The main change is that all GET requests serve up the ClockWithMutationObserver.html file.

# with thanks to https://blog.anvileight.com/posts/simple-python-http-server/#do-get

from http.server import HTTPServer, BaseHTTPRequestHandler, SimpleHTTPRequestHandler
from io import BytesIO
import tempfile
from socketserver import ThreadingMixIn
import threading

class MyHTTPRequestHandler(SimpleHTTPRequestHandler):

    def do_GET(self):
        self.path = '/ClockWithMutationObserver.html'
        return SimpleHTTPRequestHandler.do_GET(self)

    def do_POST(self):
        content_length = int(self.headers['Content-Length'])
        body = self.rfile.read(content_length)
        self.send_response(200)
        self.end_headers()
        response = BytesIO()
        response.write(b'This is POST request. ')
        response.write(b'Received: ')
        response.write(body)


        # added code to write message to tempfile in temp directory
        msgFName = msgFileName()

        with open(msgFName, 'w+') as msg:
            msg.write(body.decode("utf-8"))
            msg.flush()

        self.wfile.write(response.getvalue())

        # finally add to console so we can see it in the command window
        print(body.decode('utf-8'));

class ThreadedHTTPServer(ThreadingMixIn, HTTPServer):
    """Handle requests in a separate thread."""        

def msgFileName():
    # this function uses the date time to generate a filename which hopefully
    # should be unique and allow the files to be sorted
    import datetime
    import time
    ts=time.time()
    timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y%m%d_%H%M%S.%f')
    fileName = queueDir + '\\' + timestamp + '.txt'
    return fileName


def TempDir():
    #this creates a new directory in the temp folder
    return tempfile.mkdtemp(prefix='MsgQueue')

#Main processing starts here
queueDir =TempDir() #queueDir is in global scope
httpd = ThreadedHTTPServer(('localhost', 8000), MyHTTPRequestHandler)

print("Serve forever, message queue dir:" + queueDir)
httpd.serve_forever()  #code will disappear in here

Running the code, screen shots

So if we start the Python script and we open Chrome and its console window and browse to the address http://127.0.0.1:8000 we get to watch the clock running but we also see activity in the Chrome console window, the command window and the message queue folder. Here are the screenshots.

Final Thoughts

What have we achieved here? Well we've written code to detect changes in a web page and then POST those changes to a HTTP based message queue. Next step would be to detect changes in someone else's page.

What has this got to do with Excel? This example is a Python web server but in this post I have demonstrated that it is possible to use Excel as a web server and so Excel could easily have replaced the Python web server. But this is Python month!

VBA - ScriptControl - Predicates in JScript part 2

Summary: So we can add a compact syntax for adding lambda predicates to filter VBA arrays using the FunctionDelegate framework. The latest version of FunctionDelegate framework is given below.

So I am quite pleased with the compact syntax now possible with the FunctionDelegate framework. So I will start with a sample. I hope this sample makes it very clear how we can write a small piece of filtering logic and just pack it into a string "x => x<5" of 8 characters (or a few more for other expressions)! This is compactness afforded by lambda expressions.

Option Explicit
Option Private Module

'* Tools->References
'*   FnFactory.cls
'*   FunctionDelegate.cls


Private Sub TestFilterByLambdaPredicate()

    '* give me only numbers less than 5
    Dim vFiltered2 As Variant
    vFiltered2 = FilterByLambdaPredicate(Array(1, 2, 3, 4, 5, 6), "x => x<5")
    Debug.Assert vFiltered2(0) = 1
    Debug.Assert vFiltered2(1) = 2
    Debug.Assert vFiltered2(2) = 3
    Debug.Assert vFiltered2(3) = 4
    
    '* give me only words that begin with 'b'
    Dim vFiltered3 As Variant
    vFiltered3 = FilterByLambdaPredicate(Array("foo", "bar", "barry", "baz"), "x => x.substring(0,1)==='b'")
    Debug.Assert vFiltered3(0) = "bar"
    Debug.Assert vFiltered3(1) = "barry"
    Debug.Assert vFiltered3(2) = "baz"
    
    
End Sub

Private Function FilterByLambdaPredicate(ByVal vArray As Variant, ByVal sLambdaPredicate As String) As Variant

    Dim fnPredicate As FunctionDelegate
    Set fnPredicate = FnFactory.FnJsLamdaPredicate(sLambdaPredicate)
    
    Dim sPredicateName As String
    sPredicateName = fnPredicate.JavaScriptName
    
    Debug.Assert FnFactory.ScriptControl.Run("predicateExists", sPredicateName) '* should do, here for debugging
    FilterByLambdaPredicate = FnFactory.ScriptControl.Run("filterByPredicate", sPredicateName, vArray)
End Function

So to get predicates into the FunctionDelegate framework required some refactoring. Most importantly, FnFactory now maintains a singleton instance of the ScriptControl (though we still use a separate instance for a compilation test). Also, there is some extra code to warn about calling with too few arguments. We've chosen to add the Excel Application object to the global namespace and this allows callbacks using Application.Run. Also we've added argument type checking not just for the Javascript but for all function delegates which can be helpful for debugging and development.

So here is the code

The FnFactory class

Option Explicit

'* Tools References
'*   MSScriptControl    Microsoft Script Control 1.0    C:\Windows\SysWOW64\msscript.ocx
'*   Scripting          Microsoft Scripting Runtime     C:\Windows\SysWOW64\scrrun.dll


'---------------------------------------------------------------------------------------
' Module    : FnFactory
' DateTime  : 06/02/2018 15:57
' Author    : Simon
' Purpose   : Contains factory methods to frees us from the syntactical constraints
'             around the New keyword.
'
'             Also for the javascript function delegates this class maintains a singleton
'             instance of the ScriptControl so that different javascript functions can
'             collaborate, such as filter by predicate.
'
'
' Deployment: Open a text editor and ensure the line reads "Attribute VB_PredeclaredId = True"
'             so that one will not need to New this module!
'---------------------------------------------------------------------------------------

Private msLastError As String
Private moScriptControl As MSScriptControl.ScriptControl

Private mdicGlobalFunctions As New Scripting.Dictionary

Private mlJavaScriptCompileSavedErrNum  As Long
Private msJavaScriptCompileSavedErrDesc As String


'---------------------------------------------------------------------------------------
' Procedure : ScriptControl
' DateTime  : 16/02/2018 18:55
' Author    : Simon
' Purpose   : creates an instance of ScriptControl with core routines added and also dynamically added function
'---------------------------------------------------------------------------------------
' Arguments :
'    [out,retval]   : an instance of ScriptControl with core routines added and also dynamically added function
'
Public Property Get ScriptControl() As MSScriptControl.ScriptControl
    Dim sProg As String
    If moScriptControl Is Nothing Then
        Set moScriptControl = New MSScriptControl.ScriptControl
        moScriptControl.Language = "JScript"
    
        moScriptControl.AddCode "function isArray(arr) {  return arr.constructor.toString().indexOf('Array') > -1; }"
    
        '* https://docs.microsoft.com/en-us/scripting/javascript/reference/vbarray-object-javascript
        moScriptControl.AddCode "function fromVBArray(vbArray) { return new VBArray(vbArray).toArray();}"
    
        'http://cwestblog.com/2011/10/24/javascript-snippet-array-prototype-tovbarray/
        sProg = "Array.prototype.toVBArray = function() {                                                                        " & _
                "   var dict = new ActiveXObject('Scripting.Dictionary');                                                        " & _
                "   for(var i = 0, len = this.length; i < len; i++)                                                              " & _
                "       dict.add(i, this[i]);                                                                                    " & _
                "   return dict.Items();                                                                                         " & _
                "};                                                                                                              "
        moScriptControl.AddCode sProg
        
        
        
            
        '* add a singleton global variable then add some functions demonstrating how to use the square brackets surrounding
        '* an identifier to reference a function
        moScriptControl.Eval "var predicates={};"
        moScriptControl.Eval "predicates['IsEven'] = function (num) { return ((num % 2) === 0); };"
        moScriptControl.Eval "predicates['IsSmall'] = function (num) { return num < 5; };"
        
        '* add a function that invokes the predicate, this is mainly error handling
        '* I needed it during development to figure out the behaviour, probably too much code now
        sProg = "function runPredicate(predicateName,n) {                                                                        " & _
                "   var pred = predicates[predicateName];                                                                        " & _
                "   if (typeof pred !== 'undefined' && pred) {                                                                   " & _
                "       return pred(n);                                                                                          " & _
                "   }                                                                                                            " & _
                "}"
        moScriptControl.AddCode sProg
        
        sProg = "function predicateExists(predicateName) {                                                                       " & _
                "   var pred = predicates[predicateName];                                                                        " & _
                "   if (typeof pred !== 'undefined' && pred) {                                                                   " & _
                "       return true;                                                                                             " & _
                "   } else { return false ; }                                                                                    " & _
                "}"
        moScriptControl.AddCode sProg
        
        
    
    
        sProg = "function filterByPredicate(predicateName, vbNumbers) {                                                          " & _
                "    var filtered = []; var numbers=fromVBArray(vbNumbers);                                                      " & _
                "    var pred = predicates[predicateName];                                                                       " & _
                "        for (var i = 0; i < numbers.length; i++) {                                                              " & _
                "            if (pred(numbers[i])) {                                                                             " & _
                "                filtered.push(numbers[i]);                                                                      " & _
                "            }                                                                                                   " & _
                "        }                                                                                                       " & _
                "    return filtered.toVBArray();                                                                                " & _
                "}                                                                                                               "
        moScriptControl.AddCode sProg

        moScriptControl.AddObject "Application", Application
                    

        
    End If
    Set ScriptControl = moScriptControl
End Property
 
'---------------------------------------------------------------------------------------
' Procedure : ResetScriptControl
' DateTime  : 17/02/2018 16:18
' Author    : Simon
' Purpose   : Used for debugging, sometimes one needs a fresh start
'---------------------------------------------------------------------------------------
'
Public Sub ResetScriptControl()
    Set moScriptControl = Nothing
    
    Dim objDummy As Object
    Set objDummy = ScriptControl
End Sub
 
 
'---------------------------------------------------------------------------------------
' Procedure : LastError
' DateTime  : 14/02/2018 17:54
' Author    : Simon
' Purpose   : returns a copy of last error
'---------------------------------------------------------------------------------------
' Arguments :
'    [out,retval]   : returns a copy of last error
'
Public Function LastError() As String
    LastError = msLastError
End Function



'---------------------------------------------------------------------------------------
' Procedure : FnJavascript
' DateTime  : 14/02/2018 17:50
' Author    : Simon
' Purpose   : Public method passes on inner core FnJavascript2
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sJavaScriptFunction    : the JavaScript function source
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=Run(...' for returning an object
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Public Function FnJavascript(ByVal sJavaScriptFunction As String, _
                            Optional ByVal bReturnTypeIsObject As Boolean, Optional vArgTypeNames As Variant) As FunctionDelegate
                            
    If IsMissing(vArgTypeNames) Then vArgTypeNames = Empty
    Set FnJavascript = FnJavascript2(sJavaScriptFunction, bReturnTypeIsObject, "", "", vArgTypeNames)
End Function
    
    
'---------------------------------------------------------------------------------------
' Procedure : FnJavascript
' DateTime  : 14/02/2018 17:50
' Author    : Simon
' Purpose   :
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sJavaScriptFunction    : the JavaScript function source
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=Run(...' for returning an object
'    [in] sFunctionName          : the javascript's function name
'    [in] sQualifier             : used to place function in non global namespace, e.g. predicates
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Private Function FnJavascript2(ByVal sJavaScriptFunction As String, _
                ByVal bReturnTypeIsObject As Boolean, ByVal sFunctionName As String, _
                ByVal sQualifier As String, ByVal vArgTypeNames As Variant) As FunctionDelegate


    Dim poProc As MSScriptControl.Procedure
    
    '* attempt compilation in isolated script control instance
    If Not CompileJavascript(sJavaScriptFunction, poProc) Then
        msLastError = "#Failed to create javascript function delegate because compilation failed " & _
                    "with code (" & mlJavaScriptCompileSavedErrNum & ") and message '" & msJavaScriptCompileSavedErrDesc & "'"
        Err.Raise vbObjectError, , msLastError
    Else
        '* now we know the javascript compiles we can proceed confidently
        Dim oSC As MSScriptControl.ScriptControl
        Set oSC = Me.ScriptControl
        
        If LenB(sQualifier) = 0 Then
        
            '* we add to the global namespace
            If Not mdicGlobalFunctions.Exists(sJavaScriptFunction) Then
                oSC.AddCode sJavaScriptFunction
                Call mdicGlobalFunctions.Add(sJavaScriptFunction, 0)
            End If
        
        Else
            '* we added the global variable found in sQualifier
            oSC.Eval sQualifier & "['" & sFunctionName & "'] = " & sJavaScriptFunction
        End If
    
        Dim oNewFD As FunctionDelegate
        Set oNewFD = New FunctionDelegate
        
        oNewFD.JavaScriptFunction = sJavaScriptFunction
            
        oNewFD.HasReturnValue = poProc.HasReturnValue
        oNewFD.JavaScriptName = poProc.Name
        oNewFD.NumArgs = poProc.NumArgs
        
        If IsArray(vArgTypeNames) Then
            oNewFD.ArgumentTypeNames = vArgTypeNames
        End If
        
        oNewFD.ReturnTypeIsObject = bReturnTypeIsObject
    
        Set FnJavascript2 = oNewFD
    
    End If

End Function


'---------------------------------------------------------------------------------------
' Procedure : CompileJavascript
' DateTime  : 16/02/2018 15:57
' Author    : Simon
' Purpose   : Compiles javascript into ScriptControl and detect errors, also returns a
'             reference to the newly created procedure from which the function name
'             can be found
'
'             Uses own instance of ScriptControl for isolation.
'
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sJavaScriptFunction   : a full javascript function expression
'    [in,out] poNewestProc      : the newly created procedure (if compiled)
'    [out,retval]               : boolean, whether or not the function compiles
'
Private Function CompileJavascript(ByVal sJavaScriptFunction As String, _
                        ByRef poNewestProc As MSScriptControl.Procedure) As Boolean

    Static oSC As MSScriptControl.ScriptControl
    Set oSC = New MSScriptControl.ScriptControl
    oSC.Language = "JScript"
    
    On Error Resume Next
    oSC.AddCode sJavaScriptFunction
    msJavaScriptCompileSavedErrDesc = Err.Description
    mlJavaScriptCompileSavedErrNum = Err.Number
    
    On Error GoTo 0
    
    If mlJavaScriptCompileSavedErrNum = 0 Then
        
        Set poNewestProc = oSC.Procedures.Item(oSC.Procedures.Count)
        CompileJavascript = True
    
    End If



End Function

'---------------------------------------------------------------------------------------
' Procedure : FnJsLamda
' DateTime  : 14/02/2018 17:56
' Author    : Simon
' Purpose   : Takes a lambda expression and rewrites it as a javascript function and passes
'             it to create a javascript function delegate
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sJsLamda               : the Lambda expression (uses JavaScript syntax underlying)
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=Run(...' for returning an object
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Public Function FnJsLamda(ByVal sJsLamda As String, Optional ByVal bReturnTypeIsObject As Boolean, _
                    Optional ByVal vArgTypeNames As Variant) As FunctionDelegate
    
    
    Dim sJavascript As String
    sJavascript = RewriteLamdaAsFullJavascriptFunction(sJsLamda, True)
    
    Set FnJsLamda = FnJavascript2(sJavascript, bReturnTypeIsObject, "", "", vArgTypeNames)
    
End Function

'---------------------------------------------------------------------------------------
' Procedure : FnJsLamdaPredicate
' DateTime  : 14/02/2018 17:56
' Author    : Simon
' Purpose   : Takes a lambda expression and rewrites it as a javascript function and
'
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sJsLamda               : the Lambda expression (uses JavaScript syntax underlying)
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=Run(...' for returning an object
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Public Function FnJsLamdaPredicate(ByVal sJsLamda As String, Optional ByVal bReturnTypeIsObject As Boolean, _
                    Optional ByVal vArgTypeNames As Variant) As FunctionDelegate
    
    
    Dim sJavascript As String, psHash As String
    sJavascript = RewriteLamdaAsFullJavascriptFunction(sJsLamda, True, psHash)
    
    Set FnJsLamdaPredicate = FnJavascript2(sJavascript, bReturnTypeIsObject, psHash, "predicates", vArgTypeNames)
    
    
End Function



'---------------------------------------------------------------------------------------
' Procedure : RewriteLamdaAsFullJavascriptFunction
' DateTime  : 14/02/2018 17:56
' Author    : Simon
' Purpose   : Parses a lambda expression and rewrites it as a javascript function
'---------------------------------------------------------------------------------------
' Arguments :
'   [in] sJsLamda       : the Lambda expression (uses JavaScript syntax underlying)
'   [in] bLabelWithHash : whether or not to label the resulting function, predicates differ
'   [in,out] psHash     : if not labelling with hash pass out hash to caller
'   [out,retval]        : an equivalent fully defined Javascript function
'
Private Function RewriteLamdaAsFullJavascriptFunction(ByVal sJsLamda As String, ByVal bLabelWithHash As Boolean, _
                                                Optional ByRef psHash As Variant) As String

    If Len(sJsLamda) = 0 Then Err.Raise vbObjectError, "", "#Error null sJsLamda!"
    
    If InStr(1, sJsLamda, "function", vbTextCompare) > 0 Then Err.Raise vbObjectError, "", _
                        "#Found function keyword suggesting it is a function definition and not a lambda!"
    
    Dim lArrowAt As Long
    lArrowAt = VBA.InStr(1, sJsLamda, "=>", vbTextCompare)
    If lArrowAt = 0 Then Err.Raise vbObjectError, "", _
                            "#Could not find arrow operator '=>' in sJsLamda '" & sJsLamda & "'!"
                            
    If VBA.InStr(lArrowAt + 1, sJsLamda, "=>", vbTextCompare) > 0 Then Err.Raise vbObjectError, "", _
                        "#Found more than one arrow operator '=>' in sJsLamda '" & sJsLamda & "'!"
    
    
    Dim vSplit As Variant
    vSplit = VBA.Split(sJsLamda, "=>")
    
    Static dicHasher As New Scripting.Dictionary
    
    Dim sHash As String
    sHash = "f" & CStr(dicHasher.HashVal(sJsLamda))
    If Not IsMissing(psHash) Then psHash = sHash
    
    Dim sGivenFunctionNameAndArgs As String
    If bLabelWithHash Then
        sGivenFunctionNameAndArgs = "function " & sHash & "(" & Trim(vSplit(0)) & ") "
    Else
        sGivenFunctionNameAndArgs = "function (" & Trim(vSplit(0)) & ") "
    End If
    
    
    
    Dim lSemiColonsAfterArrow As Long
    lSemiColonsAfterArrow = CountOccurrences(vSplit(1), ";")
    
    If lSemiColonsAfterArrow <> CountOccurrences(sJsLamda, ";") Then Err.Raise vbObjectError, , _
                        "#Semicolons should only appear after arrow in sJsLamda '" & sJsLamda & "'!"
    
    
    If lSemiColonsAfterArrow = 0 Then
        
        Dim sFunctionBody As String
        sFunctionBody = "{ return " & Trim(vSplit(1)) & "; }"
    
    Else
        '* we have a multi statement function so we have to splice in the return keyword
    
        Dim vSplitOnSemiColons As Variant
        vSplitOnSemiColons = VBA.Split(vSplit(1), ";")
        
        vSplitOnSemiColons(UBound(vSplitOnSemiColons)) = " return " & vSplitOnSemiColons(UBound(vSplitOnSemiColons)) & ";"
        
        Dim sRejoined As String
        sRejoined = VBA.Join(vSplitOnSemiColons, ";")
    
        sFunctionBody = "{ " & Trim(sRejoined) & " }"
    
    End If
    
    RewriteLamdaAsFullJavascriptFunction = sGivenFunctionNameAndArgs & sFunctionBody


End Function



'---------------------------------------------------------------------------------------
' Procedure : CountOccurrences
' DateTime  : 14/02/2018 17:55
' Author    : Simon
' Purpose   :
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sText     : the string in which to search
'    [in] sChar     : the character(s) for which to search
'    [out,retval]   : the number of occurences of the character(s)
'
Private Function CountOccurrences(ByVal sText As String, ByVal sChar As String) As Long
    
    Dim lCount As Long: lCount = 0
    
    Dim lCharAt As Long
    lCharAt = VBA.InStr(1, sText, sChar, vbTextCompare)
    While lCharAt > 0
        DoEvents
        lCount = lCount + 1
        lCharAt = VBA.InStr(lCharAt + 1, sText, sChar, vbTextCompare)
    Wend

    CountOccurrences = lCount
End Function




'---------------------------------------------------------------------------------------
' Procedure : FnAppRun
' DateTime  : 05/02/2018 14:05
' Author    : Simon
' Purpose   : A factory method to create a FunctionDelegate containing enough info
'             to pass to Application.Run(.. , .. , .. , ...)
'             Using a factory method frees us from the syntactical constraints
'             around the New keyword
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] sMacro                 : the name of macro passed to Application.Run
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=Application.Run(...' for returning an object
'    [in] lNumArgs               : (optional) the expected number of arguments, used to give warnings of 'mis-call'.
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Public Function FnAppRun(ByVal sMacro As String, Optional ByVal bReturnTypeIsObject As Boolean, _
                        Optional ByVal lNumArgs As Variant, Optional ByRef vArgTypeNames As Variant) As FunctionDelegate
    
    Dim oNewFD As FunctionDelegate
    Set oNewFD = New FunctionDelegate
    
    If IsArray(vArgTypeNames) Then
        oNewFD.ArgumentTypeNames = vArgTypeNames
    End If
    
    oNewFD.IsAppRun = True
    oNewFD.AppRunMacro = sMacro
    oNewFD.ReturnTypeIsObject = bReturnTypeIsObject
    
    If Not IsMissing(lNumArgs) Then
        If IsNumeric(lNumArgs) Then
            oNewFD.NumArgs = lNumArgs
        End If
    End If
    
    'oNewFD.mvArgs = vargs
    
    Set FnAppRun = oNewFD

End Function


'---------------------------------------------------------------------------------------
' Procedure : FnCallByName
' DateTime  : 06/02/2018 15:50
' Author    : Simon
' Purpose   : A factory method to create a FunctionDelegate containing enough info
'             to pass to VBA.CallByName(.. , .. , .. , ...)
'             Using a factory method frees us from the syntactical constraints
'             around the New keyword
'---------------------------------------------------------------------------------------
' Arguments :
'    [in] oCallByNameTarget      : the object (class instance) on whom we want to call the method
'    [in] sMacro                 : the name of method we want to call
'    [in] eCallByNameType        : necessary to specify one of {VbSet, VbMethod, VbLet, VbGet}
'    [in] bReturnTypeIsObject    : whether or not we need to say 'Set foo=VBA.CallByName(...' for returning an object
'    [in] lNumArgs               : (optional) the expected number of arguments, used to give warnings of 'mis-call'.
'    [in] vArgTypeNames          : (optional) array of argument typesname , used for type-checking warnings
'    [out,retval]                : returns a create instance of FunctionDelegate containing the passed details
'
Public Function FnCallByName(ByVal oCallByNameTarget As Object, ByVal sMacro As String, _
                        ByVal eCallByNameType As VbCallType, Optional ByVal bReturnTypeIsObject As Boolean, _
                         Optional ByVal lNumArgs As Variant, Optional ByRef vArgTypeNames As Variant) As FunctionDelegate
    
    Dim oNewFD As FunctionDelegate
    Set oNewFD = New FunctionDelegate
    
    If IsArray(vArgTypeNames) Then
        oNewFD.ArgumentTypeNames = vArgTypeNames
    End If

    oNewFD.IsAppRun = False
    Set oNewFD.CallByNameTarget = oCallByNameTarget
    oNewFD.ReturnTypeIsObject = bReturnTypeIsObject
    oNewFD.CallByNameType = eCallByNameType
    
    oNewFD.AppRunMacro = sMacro
    
    If Not IsMissing(lNumArgs) Then
        If IsNumeric(lNumArgs) Then
            oNewFD.NumArgs = lNumArgs
        End If
    End If
    'oNewFD.mvArgs = vargs
    
    Set FnCallByName = oNewFD

End Function



'---------------------------------------------------------------------------------------
' Procedure : ScriptControlsProcedures
' DateTime  : 17/02/2018 16:19
' Author    : Simon
' Purpose   : Used for debugging.  Returns a dictionary with the names of the procedures
'             currently loaded in the ScriptControl
'---------------------------------------------------------------------------------------
' Arguments :
'    [out,retval] : returns a dictionary with the names of the procedures
'
Friend Function ScriptControlsProcedures() As Scripting.Dictionary
    
    Dim dicProcs As Scripting.Dictionary
    Set dicProcs = New Scripting.Dictionary
    
    Dim oSC As MSScriptControl.ScriptControl
    Set oSC = FnFactory.ScriptControl
    
    Dim lProcLoop As Long
    For lProcLoop = 1 To oSC.Procedures.Count
        Dim oProcLoop As MSScriptControl.Procedure
        Set oProcLoop = oSC.Procedures.Item(lProcLoop)
        
        dicProcs.Add oProcLoop.Name, oProcLoop.NumArgs
        

    Next lProcLoop
    Set ScriptControlsProcedures = dicProcs

End Function

The FunctionDelegate class

Option Explicit

'* Tools References
'*   MSScriptControl    Microsoft Script Control 1.0    C:\Windows\SysWOW64\msscript.ocx
'*   Scripting          Microsoft Scripting Runtime     C:\Windows\SysWOW64\scrrun.dll

'---------------------------------------------------------------------------------------
' Module    : FunctionDelegate
' DateTime  : 06/02/2018 16:04
' Author    : Simon
' Purpose   : Contains enough information to call a function either using
'             (a) Application.Run() if function lives in a standard module or
'             (b) VBA.CallByName() if function lives in a class instance (object)
'             (c) MSScriptControl.ScriptControl.Run() is given a javascript function
'             (d) MSScriptControl.ScriptControl.Run() is given a lambda function
'
'             Uses the FnFactory class for instantiation of this class.
'---------------------------------------------------------------------------------------


Private msAppRunMacro As String
Private mobjCallByNameTarget As Object
Private meCallByNameType As VbCallType
Private mbIsAppRun As Boolean

Private mbReturnTypeIsObject As Boolean

Private mvArgumentTypeNames As Variant

'* Added 14th Feb 2018
Private msJavaScriptFunction As String
Private msJavaScriptName As String
Private mbHasReturnValue As Variant     '* Empty signifies unset
Private mlNumArgs As Variant            '* Empty signifies unset

Public Property Get ArgumentTypeNames() As Variant
    ArgumentTypeNames = mvArgumentTypeNames
End Property
Public Property Let ArgumentTypeNames(ByVal rhs As Variant)
    mvArgumentTypeNames = rhs
End Property

Public Property Get NumArgs() As Long
    NumArgs = mlNumArgs
End Property
Public Property Let NumArgs(ByVal lNumArgs As Long)
    mlNumArgs = lNumArgs
End Property

Public Property Get HasReturnValue() As Boolean
    HasReturnValue = mbHasReturnValue
End Property
Public Property Let HasReturnValue(ByVal bHasReturnValue As Boolean)
    mbHasReturnValue = bHasReturnValue
End Property

Public Property Get JavaScriptFunction() As String
    JavaScriptFunction = msJavaScriptFunction
End Property
Public Property Let JavaScriptFunction(ByVal sJavaScriptFunction As String)
    msJavaScriptFunction = sJavaScriptFunction
End Property

Public Property Get JavaScriptName() As String
    JavaScriptName = msJavaScriptName
End Property
Public Property Let JavaScriptName(ByVal sJavaScriptName As String)
    msJavaScriptName = sJavaScriptName
End Property

Public Property Get ReturnTypeIsObject() As Boolean
    ReturnTypeIsObject = mbReturnTypeIsObject
End Property
Public Property Let ReturnTypeIsObject(ByVal bReturnTypeIsObject As Boolean)
    mbReturnTypeIsObject = bReturnTypeIsObject
End Property

Public Property Get IsAppRun() As Boolean
    IsAppRun = mbIsAppRun
End Property
Public Property Let IsAppRun(ByVal bIsAppRun As Boolean)
    mbIsAppRun = bIsAppRun
End Property




Public Property Get CallByNameType() As VbCallType
    CallByNameType = meCallByNameType
End Property

Public Property Let CallByNameType(ByVal eCallByNameType As VbCallType)
    meCallByNameType = eCallByNameType
End Property

Public Property Get CallByNameTarget() As Object
    Set CallByNameTarget = mobjCallByNameTarget
End Property
Public Property Set CallByNameTarget(ByVal objCallByNameTarget As Object)
    Set mobjCallByNameTarget = objCallByNameTarget
End Property

Public Property Get AppRunMacro() As String
    AppRunMacro = msAppRunMacro
End Property
Public Property Let AppRunMacro(ByVal sAppRunMacro As String)
    msAppRunMacro = sAppRunMacro
End Property


'---------------------------------------------------------------------------------------
' Procedure : Run
' DateTime  : 06/02/2018 16:01
' Author    : Simon
' Purpose   : This runs/executes/calls the function.  Deployed correctly one can omit
'             .Run in the calling line see unit tests for example
'
' Deployment: *** need to ensure that this has the line "Attribute Item.VB_UserMemId = 0"
'                 to make it default ***
'---------------------------------------------------------------------------------------
' Arguments :
'    vargs()    : a variable list of arguments which we'll pass on to Application.Run()
'                 or VBA.CallByName()
'
Public Function Run(ParamArray vargs() As Variant)
    Dim lArgCount As Long
    lArgCount = UBound(vargs) - LBound(vargs) + 1
    
    Dim dicWarnings As Scripting.Dictionary
    Set dicWarnings = New Scripting.Dictionary
    If Not IsEmpty(mlNumArgs) Then
        If Me.NumArgs <> lArgCount Then
            dicWarnings.Add "@Warning calling wrong number of arguments, expecting " & _
                        mlNumArgs & " but got " & lArgCount & "!", 0
        End If
    End If
    
    If IsArray(mvArgumentTypeNames) Then
        Dim lNumberOfArgsToCheck As Long
        lNumberOfArgsToCheck = VBA.IIf(Me.NumArgs < lArgCount, Me.NumArgs, lArgCount)
        
        Dim lArgCheckLoop As Long
        For lArgCheckLoop = 1 To lNumberOfArgsToCheck
            If Len(mvArgumentTypeNames(lArgCheckLoop)) > 0 Then
                If StrComp(mvArgumentTypeNames(lArgCheckLoop), TypeName(vargs(lArgCheckLoop - 1)), vbTextCompare) <> 0 Then
                    dicWarnings.Add "@Warning, argument type mismatch for argument " & lArgCheckLoop & _
                            ", expecting '" & mvArgumentTypeNames(lArgCheckLoop) & "' but is '" & _
                            TypeName(vargs(lArgCheckLoop - 1)) & " instead!", 0
                                
                End If
            End If
        Next
    End If
    
    
    If dicWarnings.Count > 0 Then Debug.Print Join(dicWarnings.Keys)
    
    If mbIsAppRun Then
    
        If lArgCount = 0 Then
            If mbReturnTypeIsObject Then
                Set Run = Application.Run(msAppRunMacro)
            Else
                Run = Application.Run(msAppRunMacro)
            End If
        ElseIf lArgCount = 1 Then
            If mbReturnTypeIsObject Then
                Set Run = Application.Run(msAppRunMacro, vargs(0))
            Else
                Run = Application.Run(msAppRunMacro, vargs(0))
            End If
        ElseIf lArgCount = 2 Then
            If mbReturnTypeIsObject Then
                Set Run = Application.Run(msAppRunMacro, vargs(0), vargs(1))
            Else
                Run = Application.Run(msAppRunMacro, vargs(0), vargs(1))
            End If
        Else
            'requires more lines to handle multiple arguments,
            'a bit ugly so will do later
        End If
    ElseIf Not mobjCallByNameTarget Is Nothing Then
                
        If lArgCount = 0 Then
            
            If mbReturnTypeIsObject Then
                Set Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType)
            Else
                Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType)
            End If
        
        ElseIf lArgCount = 1 Then
            
            If mbReturnTypeIsObject Then
                Set Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType, vargs(0))
            Else
                Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType, vargs(0))
            End If
        
        ElseIf lArgCount = 2 Then
            
            If mbReturnTypeIsObject Then
                Set Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType, vargs(0), vargs(1))
            Else
                Run = CallByName(mobjCallByNameTarget, msAppRunMacro, meCallByNameType, vargs(0), vargs(1))
            End If
        
        Else
            'requires more lines to handle multiple arguments,
            'a bit ugly so will do later
        End If
    ElseIf LenB(msJavaScriptFunction) > 0 Then
            
        '* Tools References
        '*   MSScriptControl    Microsoft Script Control 1.0    C:WindowsSysWOW64msscript.ocx
        Dim oSC As MSScriptControl.ScriptControl
        Set oSC = FnFactory.ScriptControl '* use the same global instance
        
        
        
        
        If lArgCount = 0 Then
            
            If mbReturnTypeIsObject Then
                Set Run = oSC.Run(Me.JavaScriptName, msAppRunMacro)
            Else
                Run = oSC.Run(Me.JavaScriptName, msAppRunMacro)
            End If
        
        ElseIf lArgCount = 1 Then
            
            If mbReturnTypeIsObject Then
                Set Run = oSC.Run(Me.JavaScriptName, vargs(0))
            Else
                Dim dic As Scripting.Dictionary
                Set dic = FnFactory.ScriptControlsProcedures
                Debug.Assert dic.Exists(Me.JavaScriptName)
                
                Run = oSC.Run(Me.JavaScriptName, vargs(0))
            End If
        
        ElseIf lArgCount = 2 Then
            
            If mbReturnTypeIsObject Then
                Set Run = oSC.Run(Me.JavaScriptName, vargs(0), vargs(1))
            Else
                Run = oSC.Run(Me.JavaScriptName, vargs(0), vargs(1))
            End If
        
        Else
            'requires more lines to handle multiple arguments,
            'a bit ugly so will do later
        End If
        
    End If
    
    If IsArray(mvArgumentTypeNames) Then
        If LenB(mvArgumentTypeNames(0)) > 0 Then
            If StrComp(mvArgumentTypeNames(0), TypeName(Run), vbTextCompare) <> 0 Then
                Debug.Print "@Warning, return type mismatch, expecting '" & _
                    mvArgumentTypeNames(0) & "' but is '" & TypeName(Run) & " instead!"
                            
            End If
        End If
    End If

End Function

A test module

Option Explicit
Option Private Module

Private Sub TestFNFactory_All()
    Test_Application
    TestFnJsLamda_PassXml
    TestFNJavascript
End Sub

Private Sub Test_Application()
    Dim oFnFactory As FnFactory
    Set oFnFactory = New FnFactory
    oFnFactory.ResetScriptControl
    
    Dim fn As FunctionDelegate
    Set fn = FnFactory.FnJavascript("function Log2( msg) { Application.Run('Log',msg) ; }", False, Array("", "String"))
    
    Call fn("hi")

    Dim fn2 As FunctionDelegate
    Set fn2 = FnFactory.FnJsLamda("x => Application.Run('Log',x)  ", False, Array("", "String"))

'    'Call fn2("log this")
    Call fn2(1)

End Sub

Sub Log(ByVal sMsg As String)
    Debug.Print sMsg
End Sub



Private Sub TestFnJsLamda_PassXml()
    Dim oFnFactory As FnFactory
    Set oFnFactory = New FnFactory
    oFnFactory.ResetScriptControl
    
    Dim xmlDom As MSXML2.DOMDocument60
    Set xmlDom = New MSXML2.DOMDocument60
    
    '* Debug.Print TypeName(xmlDom) prints "DOMDocument60"

    xmlDom.LoadXML "<foo><bar/><bar/><bar/><bar/></foo>"

    Dim fn As FunctionDelegate
    Set fn = FnFactory.FnJsLamda("xml => xml.documentElement.childNodes.length < 4 ", False, Array("Boolean", "DOMDocument60"))
    
    Debug.Assert fn(xmlDom) = False

End Sub




Private Sub TestFNJavascript()
    'End
    Dim oFnDelegate As FunctionDelegate
    Set oFnDelegate = FnFactory.FnJavascript("function foo(bar) { return bar===1; }", False, Array("Boolean", "Integer"))
    Debug.Assert oFnDelegate.JavaScriptFunction = "function foo(bar) { return bar===1; }"
    Debug.Assert oFnDelegate.JavaScriptName = "foo"
    
    'Stop
    Debug.Assert oFnDelegate.Run(0) = False
    Debug.Assert oFnDelegate.Run(1) = True

    Dim oFnDelegate2 As FunctionDelegate
    Set oFnDelegate2 = FnFactory.FnJavascript("function foo2(x) { return Math.pow(x,2)-4*x-5; }", False, Array("", "Integer"))
    
    Debug.Assert oFnDelegate2.Run(5) = 0 'root
    Debug.Assert oFnDelegate2.Run(-1) = 0 'root

    Debug.Assert oFnDelegate2.Run(1) = -8

End Sub