title: Excel VBA vs Javascript Performance for Iterating Large Datasets
body: <p>I'm currently doing a project that involves filtering large datasets down to manageable numbers of datapoints that can be plotted on charts.</p>
  
  <p>I wrote the following code in Javascript that iterates through a range of data and picks out the first value at a given step-size, in this case start value = 0 and step size = 0.1. This works great and executes very quickly; I haven't quantified it, but definitely &lt;1 second for >10000 datapoints.</p>
  
  <pre><code>var data = [ ... ];
  
  var filteredData = [];
  var index = 0;
  var step = 0.1;
  
  for (var i=0; i &lt; data.length; i++) {
    if(data[i] &gt;= index) {
      filteredData.push(data[i]);
      index+=step;
    }
  }
  </code></pre>
  
  <p><a href="http://codepen.io/awenborn/pen/ZONRmv" rel="nofollow">Codepen of Javascript with Mini Sample Dataset</a></p>
  
  <p>However, all of our data is coming in as Excel Workbooks, so I rewrote the code as an Excel Macro using VBA as follows, outputting the datapoints to an adjacent column. To process the same number of datapoints takes A LONG time compared to the JS equivalent, something in the order of 20 seconds for 10000 datapoints.</p>
  
  <pre><code>Dim dataRange As Range
  Set dataRange = Range(Range("A8"), Range("A8").End(xlDown))
  Dim index As Double
  Dim stepsize As Double
  Dim outputRow As Integer
  index = 0
  step = 0.1
  outputRow = 8
  
  For Each cell In dataRange
      If cell.Value &gt;= index Then
          ActiveSheet.Cells(outputRow, 2).Value = cell.Value
          index = index + stepsize
          outputRow = outputRow + 1
      End If
  Next cell
  </code></pre>
  
  <p>Why is there such a huge disparity between the two methods? Is there anything glaringly inefficient about my VB code? I hope this question isn't too vague!</p>
  
  <p>Many thanks, Adam</p>