In a previous post I compared several different ways of processing data with VBA. The fastest way was to get the data into a variant array and loop on it.

JavaScript has some powerful array methods so I wanted to see how they performed compared to VBA. I looked at four different ways of processing data using the JS API:

Get data into a JavaScript Array and loop

Get data into a JavaScript proxy Range object and loop

Get data into a JavaScript array and use the JS Reduce method

Call the worksheet function COUNTIFS from JavaScript

The Test Data

I am using the same test data as the previous post: 100000 rows with 2 columns.
Column A is randomly 50% populated with “X” and Column B is randomly 50% populated with “Y”.

The task is to count how many rows have an “X” in column A and a “Y” in column B in the same row.

Getting the data from Excel into a JavaScript array

This task is much slower than VBA: JavaScript takes around 290 milliseconds to read 100000 rows and 2 columns, but VBA takes only around 16 milliseconds.

Let’s ignore that and just focus on how efficient JavaScript is at processing arrays.

Benchmark Timings

If you ignore the time taken to get the data into the array then JavaScript and VBA are equally efficient – 19 milliseconds each.

I expected it to be faster to process the proxy range object directly rather than copying the data into an array and using that – but I was wrong. Looping directly on the proxy object is a lot slower than looping on an array derived from the proxy object.

Similarly I thought that the array Reduce method would be fast: but Reduce is slower than directly looping the array!

The overall winner in this case is COUNTIFS because the data never gets transferred to VBA or JavaScript so the extra 290 (JS) or 16 (VBA) milliseconds don’t get incurred. Calling a worksheet function from VBA also has a much smaller overhead than calling it from JS.

The JavaScript code

Here are the four different examples of the JavaScript code.

Looping the Array

The complete JavaScript code I am using for the looping the array method looks like this:

The invokeRun function loads the values from the range into a proxy range object using context.sync() , then creates a JavaScript array (actually an array of arrays) from the proxy object and loops down the array looking for an X and a Y in the same row.

@Yuriy – Both the JS & VBA code shown give 2 times: first for getting the data and second for doing all the finds – so the times are comparable. The benchmark timings table shows only the second of these times (we already know that reading data into JavaScript is extremely slow compared to VBA)