title: Slow VBA macro writing in cells
body: <p>I have a VBA macro, that writes in data into a cleared out worksheet, but it's really slow!</p>
  
  <p>I'm instantiating Excel from a Project Professional.</p>
  
  <pre><code>Set xlApp = New Excel.Application
  xlApp.ScreenUpdating = False
  Dim NewBook As Excel.WorkBook
  Dim ws As Excel.Worksheet
  Set NewBook = xlApp.Workbooks.Add()
  With NewBook
       .Title = "SomeData"
       Set ws = NewBook.Worksheets.Add()
       ws.Name = "SomeData"
  End With
  
  xlApp.Calculation = xlCalculationManual 'I am setting this to manual here
  
  RowNumber=2
  Some random foreach cycle
      ws.Cells(RowNumber, 1).Value = some value
      ws.Cells(RowNumber, 2).Value = some value
      ws.Cells(RowNumber, 3).Value = some value
               ...............
      ws.Cells(RowNumber, 12).Value = some value
      RowNumber=RowNumber+1
  Next
  </code></pre>
  
  <p>My problem is, that the foreach cycle is kinda big. At the end, I'll get around 29000 rows. It takes more than 25 minutes to do this on a pretty OK computer.</p>
  
  <p>Are there any tricks to speed up the writing to the cells?
  I've done the following:</p>
  
  <pre><code>xlApp.ScreenUpdating = False
  xlApp.Calculation = xlCalculationManual
  </code></pre>
  
  <p>Am I referencing the cells in a wrong way? Would it be possible, to write in a whole row, instead of the single cells? </p>
  
  <p>Would that be faster?</p>
  
  <p>I've tested my code, the foreach cycle goes through pretty quicky (i wrote the values into some random variables), so I know, that writing into the cells is what takes up all this time.</p>
  
  <p>If you need further information, code snipplets please let me know.</p>
  
  <p>Thank you for your time.</p>