Excel JavaScript API Part 2: Benchmark of Read/Write Range Performance

As an Excel Office Developer many of the Excel based applications I develop depend on reading data from Excel worksheet ranges, processing it and then writing the processed data back to a worksheet.

In VBA the way to do this is to read the ranges into variant arrays, loop through the arrays and then write them back. Done efficiently this kind of process can be reasonably fast even with large amounts of data.

So how do you do this kind of operation using the Excel JS-API and how does performance compare with VBA?

Note: these tests were done using JS-API release 1.4 with Office Insider Slow (1702.7870.2020). Further performance improvements may be introduced in later versions.

The Test Workbook

The test workbook has 500000 random numbers hard-coded into column A on Sheet1. The benchmark will read subsets of these numbers and then write them back to column D on Sheet1.

The set of subset volumes to be used in the benchmark are on Sheet2!B19:B25.

The VBA Benchmark Code

The VBA benchmark code reads the subset volumes to be used and then times reading and writing the data.

Option Explicit
Option Base 1
Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
' Initialize MicroTimer
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds = Ticks (or counts) divided by Frequency
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Sub VarBench()
Dim dT1 As Double
Dim dT2 As Double
Dim dT3 As Double
Dim rng1 As Range
Dim rng2 As Range
Dim var As Variant
Dim j As Long
Dim varIn As Variant
Dim varOut() As Variant
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
varIn = ThisWorkbook.Worksheets("Sheet2").Range("B19:B25").Value2
ReDim varOut(1 To UBound(varIn), 1 To 2)
'
' initialise timer etc
'
dT1 = MicroTimer
'
For j = 1 To UBound(varIn)
dT1 = MicroTimer
Set rng1 = Worksheets("Sheet1").Range("A1").Resize(varIn(j, 1), 1)
Set rng2 = Worksheets("Sheet1").Range("D1").Resize(varIn(j, 1), 1)
var = rng1.Value2
dT2 = MicroTimer
rng2.Value2 = var
dT3 = MicroTimer
varOut(j, 1) = (dT2 - dT1) * 1000
varOut(j, 2) = (dT3 - dT2) * 1000
Next j
'
ThisWorkbook.Worksheets("Sheet2").Range("F19:G25").Value2 = varOut
Application.Calculation = xlCalculationAutomatic
End Sub

The JS API Code

Here is some basic JS (well actually its TypeScript) code to read 500000 cells into a JS range and then write it back to a different range:

$('#run').click(run);

async function run() {
try {
await Excel.run(async (context) => {
let rngFrom = context.workbook.worksheets.getItem("Sheet1").getRange("A1:A500000").load("values");
await context.sync();
let rngTo = context.workbook.worksheets.getItem("Sheet1").getRange("D1:D500000");
rngTo.values = rngFrom.values;
await context.sync();
});
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

This code is using the Typescript Async – Await pattern. The first Let statement identifies the sheet and range and queues the load of the values into the JS range array.
This statement only places a command into the command queue: it does NOT actually load the values. The next statement await context.sync() executes all the commands that are in the queue and actually gets the values.

Similarly the second Let statement identifies the range to be written to, and then the rngTo.Values=rngFrom.values statement queues a command to write the values back. The next statement await context.sync() executes the 2 commands that are in the queue.

So this is the basic JS API pattern using Typescript’s Async Await: it enables the code to be read and understood as though it is processing sequentially and synchronously just like  VBA.

The real value of this asynchronous queuing up of a batch of commands and controlling when to execute them comes when the code is executing online or accessing a server in the cloud.

So now lets look at the JS benchmark equivalent of the VBA code.

$('#run').click(function () {
invokeRun()
});
function invokeRun() {
return Excel.run(async function (context) {
// get number of cells array from worksheets
var actsheet=context.workbook.worksheets.getActiveWorksheet();
var cellsrange = actsheet.getRange("B19:B25").load("values");
// loop. on cells array
var j = 0;
var t0 = 0;
var t1 = 0;
var t2 = 0;
var range1;
var range2;
var range3;
var nCellsArr;

context.sync().then(function () {
nCellsArr = cellsrange.values;
// read and time requested number of cells
function task() {
t0 = performance.now();
range1 = context.workbook.worksheets.getItem("Sheet1").getRange("A1").getResizedRange(nCellsArr[j][0] - 1, 0).load("values");
return context.sync()
.then(function () {
t1 = performance.now();
context.application.suspendCalculationUntilNextSync();
// write back and time the cells just read
range2 = context.workbook.worksheets.getItem("Sheet1").getRange("D1").getResizedRange(nCellsArr[j][0] - 1, 0)
range2.values = range1.values;
return context.sync();
})
.then(function () {
t2 = performance.now();
// create array for timings
var millisecs = new Array(1);
// store timings in millisecs array
millisecs[0] = new Array();
millisecs[0][0] = (t1 - t0);
millisecs[0][1] = (t2 - t1);
context.application.suspendCalculationUntilNextSync();
// write timings back to sheet
range3 = context.workbook.worksheets.getItem("Sheet2").getRange("C19:D19").getOffsetRange(j, 0);
range3.values = millisecs;
return context.sync();
})
.then(function () {
if (j < 6) {
j++;
//console.log(j);
return task();
} else {
context.application.calculate("Full");
console.log("Success");
return context.sync();
}
});
}

return task();

});
});
}

This code is using the Javascript Promises syntax.

The main new things in this code are:

using performance.now() for timings

using suspendCalculationUntilNextSync() to switch off calculation until the next context.sync() has processed the queue of commands.

Benchmark Results

I ran both the VBA benchmark and the JS API benchmark once in Manual calculation mode and once in Automatic calculation mode.

Note: If you don’t use suspendCalculationUntilNextSync then in Automatic calculation mode the Javascript triggers a recalculation once for every 1500 or so cells it writes. This has a drastic slowing down effect!

Conclusions

As expected writing data back to Excel is considerably slower than reading data.

JavaScript read is between 20 and 200 times slower than VBA, and it looks like there is a much higher JS overhead per read than VBA.

JavaScript write is between 10 and 15 times slower than VBA, but the overhead looks much more comparable.