title: Pointers needed for speeding up nested loop macro in VBA
body: <p>I need help speeding up my straightforward, 8-variable, nested loop macro.  Each loop still takes ~1 second and there are a few hundred thousand loops to completion so it takes 3 to 4 days to complete!  I’ve really done as much streamlining as I can from my reading here and experimenting but have now hit a wall.</p>
  
  <p>There are essentially 3 segments to my macro.  1) Assigning values to the variables on each loop, 2) plugging those values into my excel model to perform the calculations and 3) pasting the results from each iteration onto a new row each time.  I’ve timed each segment and the time required for each segment is (approximately) 0.4s, 0.4s and 0.2s, respectively.</p>
  
  <p>I'm mostly confused with segment 1) as it takes 0.4s just to assign values to the variables on each loop.  I’m accepting that segment 2)’s slowness is the result of my excel spreadsheet being ~5,000 lines in size (and probably my bad excel programming) and 3) has already been restructured (thanks to you guys on this forum!) and is MUCH faster than before.</p>
  
  <p>Here’s the code:</p>
  
  <pre><code>Sub VariableIteration2()
  Dim a As Double
  Dim b As Double
  Dim c As Double
  Dim d As Double
  Dim w As Double
  Dim x As Double
  Dim y As Double
  Dim z As Double
  Dim Row As Long
  Dim Results As Variant
  Dim T1S As Double
  Dim T1E As Double
  Dim T2S As Double
  Dim T2E As Double
  Dim T3S As Double
  Dim T3E As Double
  Dim T4S As Double
  Dim T4E As Double
  Dim V1S As Double
  Dim V1E As Double
  Dim V2S As Double
  Dim V2E As Double
  Dim V3S As Double
  Dim V3E As Double
  Dim V4S As Double
  Dim V4E As Double
  Dim ST1 As Double
  Dim ST2 As Double
  Dim ST3 As Double
  Dim ST4 As Double
  Dim SV1 As Double
  Dim SV2 As Double
  Dim SV3 As Double
  Dim SV4 As Double
  Dim Startime As Single
  Row = 18
  T1S = Range("S3").Value
  T1E = Range("S4").Value
  T2S = Range("t3").Value
  T2E = Range("t4").Value
  T3S = Range("u3").Value
  T3E = Range("u4").Value
  T4S = Range("v3").Value
  T4E = Range("v4").Value
  V1S = Range("s6").Value
  V1E = Range("s7").Value
  V2S = Range("t6").Value
  V2E = Range("t7").Value
  V3S = Range("u6").Value
  V3E = Range("u7").Value
  V4S = Range("v6").Value
  V4E = Range("v7").Value
  ST1 = Range("s5").Value
  ST2 = Range("t5").Value
  ST3 = Range("u5").Value
  ST4 = Range("v5").Value
  SV1 = Range("s8").Value
  SV2 = Range("t8").Value
  SV3 = Range("u8").Value
  SV4 = Range("v8").Value
  
  ‘SEGMENT_1_ASSIGNING_VALUES   
  Startime = Timer
  For a = V1S To V1E Step SV1
      For w = T1S To T1E Step ST1
          For b = V2S To V2E Step SV2
              For x = T2S To T2E Step ST2
                  For c = V3S To V3E Step SV3
                      For y = T3S To T3E Step ST3
                          For d = V4S To V4E Step SV4
                              For z = T4S To T4E Step ST4
                              Range("dy20") = Timer - Startime
  ‘SEGMENT_2_PASTE_VARIABLES_INTO_CALCULATIONS
                               Startime = Timer
                                  Range("s19").Value = w
                                  Range("s20").Value = a
                                  Range("t19").Value = x
                                  Range("t20").Value = b
                                  Range("u19").Value = y
                                  Range("u20").Value = c
                                  Range("v19").Value = z
                                  Range("v20").Value = d
                                  Range("s23:v24").Value = Range("s19:v20").Value
                               Range("dy21") = Timer - Startime
  ‘SEGMENT_3_RECORD_RESULTS
                               Startime = Timer
                                      Row = Row + 1
                                      Results = Range("g15:ax15")
                                      Range(Cells(Row, 131), Cells(Row, 131 + UBound(Results, 2) - 1)) = Results
                               Range("dy22") = Timer - Startime
                              Next z
                          Next d
                      Next y
                  Next c
              Next x
          Next b
      Next w
  Next a
  
  End Sub
  </code></pre>
  
  <p>Thanks so much for helping.</p>