title: How can I optimize a VBA module that requires many calls to a subroutine?
body: <p>I am writing code for an Excel workbook provided by a client.</p>
  <p>The purpose of this macro is to load the data for a customer into a sheet. When it does, the listener on one of the loaded cells is triggered, and it calls the appropriate subroutine associated with that customer's information.</p>
  <p>The code is operating exactly as expected and completes in a few seconds. However, the client wishes us to perform this task on roughly 15,000 customers in sequence.</p>
  <p>When we attempt to iterate the macro over roughly a hundred customers, the macro completes in a matter of a few minutes. However, when we increase the number of customers to around a thousand, the time to complete the run increases drastically.</p>
  <p>I've been trying for days to optimize the subroutine itself, but the slowdown only arises when we are calling the subroutine upwards of a thousand times.</p>
  <p>Why would repeated calls to the subroutine cause slowdown in the execution of the code, and is there a way that it can be mitigated?</p>
  <pre><code>Sub Looprun()
  
  Dim n As Long
  Dim lastRow As Long
  Dim SecondLastrow As Long
  Dim Start As Long
  Dim Finish As Long
  
  Application.Calculation = xlManual
  Application.ScreenUpdating = False
  
  'Set starting timestamp
  Range(&quot;RunStart&quot;).Value = Now()
  
  Start = Sheets(&quot;Reserve Summary&quot;).Range(&quot;L12&quot;).Value
  Finish = Sheets(&quot;Reserve Summary&quot;).Range(&quot;L13&quot;).Value
  
  lastRow = Cells(Sheets(&quot;Reserve Summary&quot;).Rows.Count, 2).End(xlUp).Row - 1
  
  For n = Start To Finish
      
      Sheets(&quot;Calculation&quot;).Cells(3, 3).Value = n
  ' Cell C3 has a listener attached to it; see below. The customer's other information is loaded in via formulas that are keyed to cell C3.
  
      Sheets(&quot;Reserve Summary&quot;).Cells(n + 2, 3).Value = Sheets(&quot;Calculation&quot;).Range(&quot;C4&quot;).Value
      
      Sheets(&quot;Reserve Summary&quot;).Cells(n + 2, 5).Value = Sheets(&quot;Calculation&quot;).Range(&quot;Q25&quot;).Value
      
      Sheets(&quot;Reserve Summary&quot;).Cells(n + 2, 7).Value = Sheets(&quot;Calculation&quot;).Range(&quot;Q22&quot;).Value
      
  Next
  
  Range(&quot;RunEnd&quot;).Value = Now()
  
  Application.Calculation = xlAutomatic
  Application.Calculate
  Application.ScreenUpdating = True
  
  End Sub
  
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim company As String
  
      If Target.Address = &quot;$C$3&quot; Then
      
          Worksheets(&quot;Calculation&quot;).Calculate
          
          company = Sheets(&quot;Calculation&quot;).Cells(3, 11).Value
          
          If LCase(company) = &quot;A&quot; Then
              Call SubroutineA
              Exit Sub
          End If
          
          If LCase(company) = &quot;B&quot; Then
              Call SubroutineB
              Exit Sub
          End If
          
          Call DefaultSubroutine
      
      End If
      
  End Sub
  </code></pre>