_README.md

Excel VBA vs Javascript Performance for Iterating Large Datasets

Paired with https://stackoverflow.com/questions/39100690/excel-vba-vs-javascript-performance-for-iterating-large-datasets/59156561#59156561

In terms of raw compute time JS is actually significantly slower than VBA. However this largely depends on the algorithm you use.

If we are trying to build an array with 50000000, then it is significantly faster for VBA to do than JS. However if we are to produce an array of any number of items ( 1 to n ) VBA becomes incredibly slow.

The results on my computer are listed below.

VBA fixed size long array:      0.00933219999933 seconds
VBA fixed size variant array:   0.01075579999815 seconds
JS dynamic size variant array:  0.03299999999953 seconds
VBA Collection:                 0.10702589999709 seconds
VBA dynamic size long array:    0.60271329999886 seconds
VBA stdArray:                   7.95831580000231 seconds
VBA dynamic size variant array: 8.36757760000182 seconds

A note on terminology:

___ Size

Here the options for ___ are fixed, dynamic and hybrid. A dynamic size array will change it's length when items are added to it. A fixed size array will only define its size at the beginning and will never be redefined thereon. Hybrid sized arrays redefine their size in batches to get the best of both worlds. In general in VBA hybrid arrays are prefferable. A hybrid array requires seperate tracker variables to track length etc.

___ array

Here the options for ___ are data types. E.G. Variant, Long, Object, Double, ... Typically (as shown) variant arrays are significantly slower than long arrays.

DEV.bas

Option Private Module
  
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
Public iTimerAverage As Double, iTimerStart As Double

Function MicroTimer() As Double
  ' Returns seconds.
  Dim cyTicks1 As Currency
  Static cyFrequency As Currency
  MicroTimer = 0

  ' Get frequency.
  If cyFrequency = 0 Then Call getFrequency(cyFrequency)

  ' Get ticks.
  Call getTickCount(cyTicks1)

  ' Seconds
  If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function

Public Sub Timer_Start()
  iTimerStart = MicroTimer()
End Sub
Public Sub Timer_Stop()
  Call Timer_Refresh
  Debug.Print "Average time: " & iTimerAverage & " seconds"
  iTimerAverage = 0
End Sub
Public Sub Timer_Refresh()
  Dim x As Double
  x = MicroTimer()
  If iTimerAverage = 0 Then
    iTimerAverage = (x - iTimerStart)
  Else
    Dim tmp As Double: tmp = (x - iTimerStart)
    iTimerAverage = (iTimerAverage + tmp) / 2
  End If
  Call Timer_Start
End Sub
Public Sub Timer_Reset()
  iTimerAverage = 0
End Sub

Test.js

function testMain(){
  cons max = 50000000
  var arr=[]
  x=performance.now()
  for(var i=0;i<max;i++) arr.push(i)
  console.log((performance.now()-x)/1000)
}

VBAArrayTests.bas

Option Private Module

Const max As Long = 500000

Sub VBA_fixed_size_long_array()
    Dim i As Long, arr(1 To max) As Long
    DEV.Timer_Start
    For i = 1 To max
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub

Sub VBA_fixed_size_variant_array()
    Dim i As Long
    Dim arr(1 To max) As Variant
    
    DEV.Timer_Start
    For i = 1 To max
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub

Sub VBA_Collection()
    Dim i As Long, arr As Collection
    Set arr = New Collection
    
    DEV.Timer_Start
    For i = 1 To max
         Call arr.Add(i)
    Next
    DEV.Timer_Stop
End Sub

Sub VBA_dynamic_size_long_array()
    Dim i As Long, arr() As Long
    ReDim arr(1 To 1) As Long
    
    DEV.Timer_Start
    For i = 1 To max
         ReDim Preserve arr(1 To UBound(arr) + 1) As Long
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub

Sub VBA_stdArray()
    'Requires https://github.com/sancarn/VBA-STD-Library
    Dim i As Long, arr As stdArray
    Set arr = stdArray.Create()
    
    DEV.Timer_Start
    For i = 1 To max
         Call arr.push(i)
    Next
    DEV.Timer_Stop
End Sub

Sub VBA_dynamic_size_variant_array()
    Dim i As Long, arr() As Variant
    ReDim arr(1 To 1) As Variant
    
    DEV.Timer_Start
    For i = 1 To max
         ReDim Preserve arr(1 To UBound(arr) + 1) As Variant
         arr(i) = i
    Next
    DEV.Timer_Stop
End Sub