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.

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.

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.