title: Optimizing performance of double loop in VBA Excel
body: <p>I'm interested in knowing the fastest way to execute a set of instructions in double loop to loop through a two-dimensional range of cells. My code will be like this:</p>
  
  <pre><code>Sub Test()
  
  For i = 1 To 1000000
      For j = 1 To 10   'It can be more than 10
          'I put a set of instructions here
      Next j
  Next i
  
  End Sub
  </code></pre>
  
  <p>For example, suppose I write a simple code to implement such a following task:</p>
  
  <pre><code>Sub Test1()
  T0 = Timer
  For i = 1 To 1000000
      For j = 1 To 10
          Cells(i, j) = j + Rnd()
      Next j
  Next i
  InputBox "The runtime of this program is ", "Runtime", Timer - T0
  End Sub
  </code></pre>
  
  <p>I ran the procedure <strong>Test1</strong> on my machine, it took 179.6406 seconds to complete. Since I don't declare the variables (i and j), the <strong>Test1</strong> is running with those variables defaulting to the Variant type. I then add one line to the <strong>Test1</strong> to declare the variables as Longs, since VBA is optimized for Longs. The new procedure, <strong>Test2</strong>, brought the running time on my machine down to 168.7539 seconds (almost 11 seconds faster). </p>
  
  <p>To improve performance of <strong>Test2</strong>, I turned off Excel functionality that isn't needed while <strong>Test2</strong> code runs.</p>
  
  <pre><code>Sub Test3()
  Dim i As Long, j As Long
  T0 = Timer
  
  ScreenUpdateState = Application.ScreenUpdating
  StatusBarState = Application.DisplayStatusBar
  CalcState = Application.Calculation
  EventsState = Application.EnableEvents
  DisplayPageBreakState = ActiveSheet.DisplayPageBreaks
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ActiveSheet.DisplayPageBreaks = False
  
  For i = 1 To 1000000
      For j = 1 To 10
          Cells(i, j) = j + Rnd()
      Next j
  Next i
  
  Application.ScreenUpdating = ScreenUpdateState
  Application.DisplayStatusBar = StatusBarState
  Application.Calculation = CalcState
  Application.EnableEvents = EventsState
  ActiveSheet.DisplayPageBreaks = DisplayPageBreaksState
  
  InputBox "The runtime of this program is ", "Runtime", Timer - T0
  End Sub
  </code></pre>
  
  <p>The above method helps improve the performance of <strong>Test2</strong> and <strong>Test3</strong> completes in 96.13672 seconds on my machine. So I'm wondering if there is a more effective way for doing this. Can anyone come up with a quicker version? If possible, even avoiding the double loop procedure.</p>