title: Why does VBA 100,000 cell For Each loop take so long?
body: <p>Update: I know there are faster ways to achieve the outcome - my question is around the relative performance of my machine.</p>
  <p>I am following an Excel VBA tutorial in which the instructor runs a macro which creates a new worksheet and then 'For Each' loops through 100,000 cells (A1 to A100000) setting an arbitrary value in each cell (such as &quot;Hello&quot;). When she executes the macro, it takes &lt;5 seconds to complete.</p>
  <p>When I run the macro on my own computer, it takes forever (I haven't timed it as I usually force quit in frustration). Changing the code from 100,000 to 10,000 cells in the loop, the macro takes about three minutes.</p>
  <p>Is this because I am using an older computer (and processor) (Dell Optiplex 9020, 8GB RAM, 500GB HDD, Intel Core i5-4570 CPU @ ~3.2Ghz (4 processors)) or is there another possible reason?</p>
  <p>While the macro is running Task Manager shows total CPU and Memory utilization hovering around 50% and performance speed at ~3.4Ghz.</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Slow_code()
      Dim StartTime As Double
      Dim SecondsElapsed As Double
      Dim ShNew As Worksheet
      Dim cell As Range
      
      StartTime = Timer
        
      Application.StatusBar = &quot;Wait&quot;
      Set ShNew = Worksheets.Add
      For Each cell In ShNew.Range(&quot;A1:A10000&quot;)
          cell.Value = 10
      Next cell
      
      ShNew.Select
      Application.StatusBar = &quot;&quot;
          
      SecondsElapsed = Round(Timer - StartTime, 2)
      
      MsgBox &quot;This code ran successfully in &quot; &amp; SecondsElapsed &amp; &quot; seconds&quot;, vbInformation
      
  End Sub
  </code></pre>
  <hr />
  <p>Found the culprit: Deactivated an addin bundle (FastExcel v4) and the speed increased to a level comparable with the instructor and other users.</p>