Tuesday 31 March 2020

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!

Wednesday 25 March 2020

VBA - Persisting Variant Arrays To File

In this post I investigate the undocumented binary representation of variant arrays in files as saved by VBA using the FreeFile, Open For Binary and Put statements. We'll see how each element is preceded by a vbVarType constant followed by a binary representation. This is another attempt to find a good serialization format suited for VBA developers, one that scores higher than XML or JSON because it is native to the VBA/VB6 ecosystem. I believe the undocumented binary persistence format is a good candidate.

Approach

Some Experiments and Investigations

So the following programs are experimental; they write a variable to a binary file for inspection. The first program will write a string array to file and then the reader is invited to inspect the file in Notepad++ or a Hex Editor (I give a screenshot). In the second program I go on to write some code to dump the bytes in hex form to the Immediate Window because I got frustrated with interpreting non printable ASCII characters.

The Goal is VBA as Consumer

I have absolutely no intention of writing VBA code to manually save/load structures to/from a binary files because VBA already supports this. I demonstrate this serialization logic to support writing code in languages other than VBA such as Javascript to generate a file that can be consumed by VBA. The use case scenario could be a web service returning some data to a VBA caller, rather than parse JSON or XML it would be better if VBA loaded the binary file in a variable using the FreeFile, Open For Binary and Get statements. In this way I am shifting the burden of parsing logic to the server-side.

I hope to go on and actually give some JavaScript code that does implement this serialization format in the next post. Because all JavaScript floating point numbers are Double precision (IEEE 754) I'll skip Single as VBA variable type. Also, I will skip the Decimal VBA variable type. Dates are encoded as Doubles so that will be covered.

Investigate arrays first then concentrate on typing

So the first chunk of code will work with strings only to abstract any typology. The program will save a one-dimensional string array; the code then loads the array back into an identically dimensioned one-dimensional string array as a base case. The program then loads the array back into a two dimensional array to investigate if the persistence is row-wise or column-wise.

The second chunk of code then focuses on persisting a single element array, each time with a different variable type to investigate the binary representation of each variable type. I needed to work with one element only here because otherwise the stream of hex bytes is too much to interpret!

The Code

All the code should be placed in a single standard module. You will also need Tools->References->Microsoft Scripting Runtime for some other file handling. The binary files are generated in a sub-directory of the user's temp directory.

First chunk of code saves a string array

So here is our first chunk of code which experiments with array structures. See below for a screenshot of the file in Notepad++.

You should run TestPersistStringTypedArray as the starting point.

Option Explicit

Private Function GetWorkingDir() As String
    Dim fso As New Scripting.FileSystemObject '* Tools->References->Microsoft Scripting Runtime
    Const TemporaryFolder As Long = 2
    
    Dim fldTemp As Scripting.Folder
    Set fldTemp = fso.GetSpecialFolder(TemporaryFolder)

    Dim sPathVBAPersist As String
    sPathVBAPersist = fso.BuildPath(fldTemp, "VBAPersist")

    If Not fso.FolderExists(sPathVBAPersist) Then
        fso.CreateFolder sPathVBAPersist
    End If
    GetWorkingDir = sPathVBAPersist
End Function

Private Function OpenCleanFileForBinary(ByVal sPartialFileName As String, ByRef plFileNum As Long) As String
    
    Dim fso As New Scripting.FileSystemObject '* Tools->References->Microsoft Scripting Runtime
    
    Dim sFullFileName As String
    sFullFileName = fso.BuildPath(GetWorkingDir(), sPartialFileName)

    If fso.FileExists(sFullFileName) Then
        Kill sFullFileName
    End If

    plFileNum = OpenFileForBinary(sFullFileName)
    OpenCleanFileForBinary = sFullFileName

    '* caller needs to close plFileNum!!
    
End Function

Private Function OpenFileForBinary(ByVal sFullFileName As String) As Long

    Dim lFileNum As Long
    lFileNum = FreeFile
    '* caller needs to close plFileNum!!
    
    Open sFullFileName For Binary As lFileNum

    OpenFileForBinary = lFileNum

End Function

Private Sub TestPersistStringTypedArray()

    Dim lSaveFileNum As Long, sFullFileName As String
    sFullFileName = OpenCleanFileForBinary("persistStringTypedArray.bin", lSaveFileNum)
    Debug.Print sFullFileName

    '**** SAVE ****
    Dim asSave(0 To 3) As String
    asSave(0) = "Hi"
    asSave(1) = "Earth"
    asSave(2) = "Goodbye"
    asSave(3) = "Hercules"
    Put lSaveFileNum, , asSave
    Close lSaveFileNum

    '**** LOAD AS ONE DIM****
    Dim asLoadOneDim(0 To 3) As String
    Dim lLoadFileNum As Long
    lLoadFileNum = OpenFileForBinary(sFullFileName)
    Get lLoadFileNum, , asLoadOneDim
    Close lLoadFileNum

    '*** COMPARE ****
    '* Everything as you would expect
    Debug.Assert asLoadOneDim(0) = asSave(0)
    Debug.Assert asLoadOneDim(1) = asSave(1)
    Debug.Assert asLoadOneDim(2) = asSave(2)
    Debug.Assert asLoadOneDim(3) = asSave(3) 'Hercules


    '**** LOAD AS TWO DIM****
    Dim asLoadTwoDim(0 To 1, 0 To 1) As String

    lLoadFileNum = OpenFileForBinary(sFullFileName)
    Get lLoadFileNum, , asLoadTwoDim
    Close lLoadFileNum

    '*** COMPARE ****
    '* Note how loading a one dimensional array into a two dimensions causes the leftmost column to be populated first
    '* i.e. we load columnwise, not rowwise.  Counter-intuitive?
    Debug.Assert asLoadTwoDim(0, 0) = asSave(0) 'Hi
    Debug.Assert asLoadTwoDim(0, 1) = asSave(2) 'Goodbye
    Debug.Assert asLoadTwoDim(1, 0) = asSave(1) 'Earth
    Debug.Assert asLoadTwoDim(1, 1) = asSave(3) 'Hercules

    Stop '* inspect your Locals window
End Sub

The code above will write a file that looks like the following in Notepad++. You can see that Notepad++ is writing non printable ASCII bytes (ASCII code below 32) as control characters; NUL is zero, STX is two, ENQ is five, BEL is seven and BS is eight. So the non-NUL bytes are showing the following string's length. The NUL characters are not string terminators but instead the second byte of the string length, except the peristence format is little endian which means the least significant byte comes first; this implies a maximum string length of 65535.

Because the variable being saved is specifically a string array (and not a variant array) then there is no byte to indicate the following variable type. So this serves a nice plain example to begin with.

(In the second chunk of code I go on to write some hex byte dump logic because wrestling with Notepad++'s control codes became frustrating.)

Another learning point for this first chunk of code is the experiment with loading into a two-dimensional array and discovering that persistence of arrays is column-wise and not row-wise. That is to say, that given a two dimensional array to populate VBA will populate the leftmost column first and then the second column and then onwards moving left to right across the columns. This maybe counterintutive but veterans of VBA will understand this is related to ReDim Preserve only allowing the final dimension to be altered.

Second chunk of code saves a single element variant array with each a different vartype

So the second chunk of code saves a single element variant array with each a different variable type (VarType).

Thankfully, it also comes with some hex byte printing logic so we can ditch Notepad++. The bytes are given first in their raw little endian form and then in big endian which is easier to interpret. A little barrier also shows the two byte variable type (VarType)

We do a single element at a time otherwise we'll get a blizzard of hex!

The entry point this time is the subroutine TestPersistSole().

Private Function PersistSole(ByRef vSingleElement As Variant, ByVal sPartialFileName As String, Optional sValueAsString As Variant) As String
    'Debug.Print 'vbNewLine

    Dim lSaveFileNum As Long, sFullFileName As String
    sFullFileName = OpenCleanFileForBinary(sPartialFileName, lSaveFileNum)
    'Debug.Print sFullFileName

    '**** SAVE ****
    '* use one element of a one dimensional arrays to avoid clutter
    Dim avSave(0 To 0) As Variant
    avSave(0) = vSingleElement
    
    Put lSaveFileNum, , avSave
    Close lSaveFileNum

    '**** FIND FILE LENGTH ****
    Dim fso As New Scripting.FileSystemObject
    
    Dim lLoadFileNum As Long
    lLoadFileNum = fso.GetFile(sFullFileName).Size
    
    
    
    '**** LOAD ****
    ReDim abLoad(0 To lLoadFileNum - 1) As Byte
    lLoadFileNum = OpenFileForBinary(sFullFileName)
    Get lLoadFileNum, , abLoad()
    Close lLoadFileNum
    
    
    Dim sRawBytes As String:
    sRawBytes = RawBytes(abLoad)
    
    Dim sBigEndianBytes As String:
    sBigEndianBytes = BigEndianBytes(abLoad)
    
    Dim sValue As String
    On Error Resume Next
    sValue = VBA.IIf(IsMissing(sValueAsString), CStr(vSingleElement), sValueAsString)
    
    PersistSole = PadLeft(" ", TypeName(vSingleElement) & "(VarType " & VarType(vSingleElement) & ")", 20) & _
                    " of value " & PadRight(" ", sValue, 20) & sRawBytes & sBigEndianBytes
End Function


Private Function RawBytes(ByRef abBytes() As Byte) As String
    Dim sRawBytes As String: sRawBytes = ""
    sRawBytes = HexPad(abBytes, 0) & " " & HexPad(abBytes, 1) & "|"
    
    Dim lLoop As Long
    For lLoop = 2 To UBound(abBytes())
        sRawBytes = sRawBytes & HexPad(abBytes, lLoop) & " "
    Next lLoop
    
    RawBytes = " has raw hex bytes " & Trim(sRawBytes)
End Function

Private Function BigEndianBytes(ByRef abBytes() As Byte) As String
    
    Dim eVarType As VbVarType
    eVarType = abBytes(0)
    
    If eVarType <> vbString And eVarType <> vbByte And eVarType <> vbBoolean Then
    
        Dim sBigEndianBytes As String: sBigEndianBytes = ""
        sBigEndianBytes = HexPad(abBytes, 1) & " " & HexPad(abBytes, 0) & "|"
        
        Dim lLoop As Long
        For lLoop = UBound(abBytes()) To 2 Step -1
            sBigEndianBytes = sBigEndianBytes & HexPad(abBytes, lLoop) & " "
        Next lLoop
        
        BigEndianBytes = " or big endian " & Trim(sBigEndianBytes)
    End If
End Function


Private Sub TestPersistSole()

    '* string
    Debug.Print PersistSole("HELLO", "persistVariantArrayOfSingleString.bin")

    '* integer numbers
    Debug.Print PersistSole(CByte(8), "persistVariantArrayOfSingleByte.bin")

    Debug.Print PersistSole(CInt(511), "persistVariantArrayOfSingleInteger.bin")
    Debug.Print PersistSole(CLng(65535), "persistVariantArrayOfSingleLong.bin")

    '* skip Decimals
    '* https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/decimal-data-type

    '* Booleans
    Debug.Print PersistSole(CBool(True), "persistVariantArrayOfBooleanTrue.bin")
    Debug.Print PersistSole(CBool(False), "persistVariantArrayOfBooleanFalse.bin")
    
    '* skip Singles
    
    '* doubles, 1 sign bit, 11 bit exponent, 52 fraction
    '* https://en.wikipedia.org/wiki/Double-precision_floating-point_format
    Debug.Print PersistSole(CDbl(-1), "persistVariantArrayOfDoubleFloat.bin")
    Debug.Print PersistSole(CDbl(0), "persistVariantArrayOfDoubleFloat.bin")
    Debug.Print PersistSole(CDbl(7.5), "persistVariantArrayOfDoubleFloat.bin")
    Debug.Print PersistSole(CDbl(Application.WorksheetFunction.Pi()), "persistVariantArrayOfDoubleFloat.bin")


    Debug.Print PersistSole(CDbl(1), "persistVariantArrayOfDoubleFloat.bin")
    Debug.Print PersistSole(CDbl(32767), "persistVariantArrayOfDoubleFloat.bin")
    
    '* Dates
    '* dates are implemented as doubles
    Debug.Print PersistSole(CDate(32767), "persistVariantArrayOfDate.bin")

    '* Specials
    Debug.Print PersistSole(Empty, "persistVariantArrayOfSingleEmpty.bin")
    Debug.Print PersistSole(Null, "persistVariantArrayOfSingleNull.bin")
    Debug.Print PersistSole(CVErr(2023), "persistVariantArrayOfSingleError.bin")



End Sub

Private Function HexPad(ByRef abBytes() As Byte, ByVal lIdx As Long) As String
    HexPad = PadRight("0", Hex$(abBytes(lIdx)), 2)
End Function

Private Function PadRight(ByVal sPad As String, ByVal sCore As String, ByVal lNum As Long) As String
    PadRight = Right$(String(lNum, sPad) & sCore, lNum)
End Function

Private Function PadLeft(ByVal sPad As String, ByVal sCore As String, ByVal lNum As Long) As String
    PadLeft = Left$(sCore & String(lNum, sPad), lNum)
End Function

and this prints the following output

String(VarType 8)    of value                HELLO has raw hex bytes 08 00|05 00 48 45 4C 4C 4F
Byte(VarType 17)     of value                    8 has raw hex bytes 11 00|08
Integer(VarType 2)   of value                  511 has raw hex bytes 02 00|FF 01 or big endian 00 02|01 FF
Long(VarType 3)      of value                65535 has raw hex bytes 03 00|FF FF 00 00 or big endian 00 03|00 00 FF FF
Boolean(VarType 11)  of value                 True has raw hex bytes 0B 00|FF FF
Boolean(VarType 11)  of value                False has raw hex bytes 0B 00|00 00
Double(VarType 5)    of value                   -1 has raw hex bytes 05 00|00 00 00 00 00 00 F0 BF or big endian 00 05|BF F0 00 00 00 00 00 00
Double(VarType 5)    of value                    0 has raw hex bytes 05 00|00 00 00 00 00 00 00 00 or big endian 00 05|00 00 00 00 00 00 00 00
Double(VarType 5)    of value                  7.5 has raw hex bytes 05 00|00 00 00 00 00 00 1E 40 or big endian 00 05|40 1E 00 00 00 00 00 00
Double(VarType 5)    of value     3.14159265358979 has raw hex bytes 05 00|18 2D 44 54 FB 21 09 40 or big endian 00 05|40 09 21 FB 54 44 2D 18
Double(VarType 5)    of value                    1 has raw hex bytes 05 00|00 00 00 00 00 00 F0 3F or big endian 00 05|3F F0 00 00 00 00 00 00
Double(VarType 5)    of value                32767 has raw hex bytes 05 00|00 00 00 00 C0 FF DF 40 or big endian 00 05|40 DF FF C0 00 00 00 00
Date(VarType 7)      of value           16/09/1989 has raw hex bytes 07 00|00 00 00 00 C0 FF DF 40 or big endian 00 07|40 DF FF C0 00 00 00 00
Empty(VarType 0)     of value                      has raw hex bytes 00 00| or big endian 00 00|
Null(VarType 1)      of value                      has raw hex bytes 01 00| or big endian 00 01|
Error(VarType 10)    of value           Error 2023 has raw hex bytes 0A 00|E7 07 0A 80 or big endian 00 0A|80 0A 07 E7

So interpreting the results we can see that for a variant array which is polymorphic/heterogeneous the first two bytes are the VarType of the element. We've already met the string representation in the first chunk of code, vartype then string length then one byte for each character. Persisting a single byte is trivial. The Integer, Long and Boolean types are intuitive. The Double (also Date) type is a IEEE 754 floating-point representation and is thus complex to interpret but the specification says that there is 1 sign bit, 11 exponent bits and 52 fraction bits.

I have even added some special use cases for other VarTypes (variant types): Empty and Null which are trivial; and Error which looks like a 32-bit Long (4 bytes).

Final Thoughts

As mentioned above I do not intend to write VBA code to manually load or save to this persistence format because VBA already does that for us. However, I feel I know enough now to attempt some JavaScript code which will persist a two-dimensional grid and return it from a web service to a VBA caller which VBA can then paste directly to a sheet. (Luckily, there is already a JavaScript library for the Doubles.) Watch out for that blog post next.

Monday 23 March 2020

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!