title: Excel vba: program performance is slow, out of memory error when dealing with large dataset in collection
body: <p>So I have a really huge excel spreadsheet with about 100 thousands rows. Each row represents a client. Each column stores that client's name, debt, probability of default and so on. There are about 12 columns.</p>
  
  <p>In my code, I loop through the rows and store the data for each client as a class object, then add that object to a collection:</p>
  
  <pre><code>Function getClients(dataWorkbook As Workbook)
      Dim resultColl As Collection
      Set resultColl = New Collection
  
      ...
       With dataWorkbook.Worksheets(globals("DATA_SHEET"))
          For i = firstRow To lastRow
              Set clientCopy = New Client
              clientCopy.setClientName = .Cells(i, column_names).value
              clientCopy.setContractNumber = .Cells(i, column_contract_numbers).value
              ...
  
  resultColl.Add clientCopy
          Next
      End With
  
      Set getClients = resultColl
  End Function
  </code></pre>
  
  <p>Then for each client in the collection I calculate the random numbers and store them in a collection for that client (the collection of size N, depending the number a user wants, usually 1000 - 5000).</p>
  
  <p>Then I go through those random numbers of each client and store an outcome in an outcomes collection, depending on the random number's value (if the number is greater than the client's number P, then I store 0, else 1).</p>
  
  <p>Then I calculate a financial result for each client, meaning, I check for each outcome in the outcomes collection and store appropriate values in losses and profits collections, depending if the outcome is 1 or 0.</p>
  
  <p>These methods inside the <code>Client</code> class:</p>
  
  <pre><code>Public Sub generateRandoms()
      Set randomNumbers = New Collection
  
      Dim i As Long
      For i = 1 To simulationCount
          randomNumbers.Add Rnd()
          'Debug.Print "random: " &amp; randomNumbers(i)
      Next
  End Sub
  
  Public Sub calculateOutcomes()
      Set outcomes = New Collection
  
      Dim i As Long
      If totalPd &lt;&gt; -1 Then
          For i = 1 To simulationCount
              If randomNumbers(i) &lt; totalPd Then
                  outcomes.Add 1
              Else
                  outcomes.Add 0
              End If
          Next
      Else
          For i = 1 To simulationCount
              outcomes.Add Null
          Next
      End If
  End Sub
  
  Public Sub calculateFinancialResult()
      Set losses = New Collection
      Set profits = New Collection
  
      Dim i As Long
      For i = 1 To outcomes.Count
          If outcomes(i) = 1 Then
              losses.Add totalLoss
              profits.Add 0
          ElseIf outcomes(i) = 0 Then
              losses.Add 0
              profits.Add totalProfit
          Else
              losses.Add Null
              profits.Add Null
          End If
      Next
  End Sub
  </code></pre>
  
  <p>The code in the main module goes like this:</p>
  
  <pre><code>Dim clientsColl As Collection
  Set clientsColl = getClients(dataWorkbook)
  
  Dim clientCopy As Client
  For Each clientCopy In clientsColl
      clientCopy.setSimulationCount = globals("SIMULATION_COUNT")
      clientCopy.generateRandoms 'up to this line it's fast
      'clientCopy.calculateOutcomes 'this line is slow, so i have to shut excel down
      'clientCopy.calculateFinancialResult
  
      clientCopy.clearSums
  Next
  
  MsgBox ("Done for")
  </code></pre>
  
  <p>While the data collection from the data workbook is rather slow, it takes a reasonable enough amount of time. At the same time, the calculations itself are extraordinary slow. I tried to figure out which function or sub takes the most time. So far it looks like <code>calculateOutcomes</code> is pretty slow, while <code>generateRandoms</code> is quite fast and takes less than a minute. </p>
  
  <p>I don't understand why is that so - both of them loop through each client and store data in a collection N times. At the same time the first is fast, while the second is slow. If I don't terminate the excel eventually I encounter an <code>Out of memory</code> error. Why?</p>
  
  <p>Is there anything I can do about this code's performance? Any suggestions are appreciated. What are the general practices in situations like this?</p>