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 SubFinal 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!
