Why does VBA 100,000 cell For Each loop take so long?

Question

Update: I know there are faster ways to achieve the outcome - my question is around the relative performance of my machine.

I am following an Excel VBA tutorial in which the instructor runs a macro which creates a new worksheet and then 'For Each' loops through 100,000 cells (A1 to A100000) setting an arbitrary value in each cell (such as "Hello"). When she executes the macro, it takes <5 seconds to complete.

When I run the macro on my own computer, it takes forever (I haven't timed it as I usually force quit in frustration). Changing the code from 100,000 to 10,000 cells in the loop, the macro takes about three minutes.

Is this because I am using an older computer (and processor) (Dell Optiplex 9020, 8GB RAM, 500GB HDD, Intel Core i5-4570 CPU @ ~3.2Ghz (4 processors)) or is there another possible reason?

While the macro is running Task Manager shows total CPU and Memory utilization hovering around 50% and performance speed at ~3.4Ghz.

Sub Slow_code()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim ShNew As Worksheet
    Dim cell As Range
    
    StartTime = Timer
      
    Application.StatusBar = "Wait"
    Set ShNew = Worksheets.Add
    For Each cell In ShNew.Range("A1:A10000")
        cell.Value = 10
    Next cell
    
    ShNew.Select
    Application.StatusBar = ""
        
    SecondsElapsed = Round(Timer - StartTime, 2)
    
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    
End Sub

---

Found the culprit: Deactivated an addin bundle (FastExcel v4) and the speed increased to a level comparable with the instructor and other users.

Answer 1

For once every time your set a single value, it recalculates the sheet.

You can however set all the values with one command, resulting in only one re-calc in the end.

Sub Fast_code()
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim ShNew As Worksheet
Dim cell As Range

    StartTime = Timer
  
    Application.StatusBar = "Wait"
    Set ShNew = Worksheets.Add
    ShNew.Range("A1").Resize(10000,1).Value2 = 10

    ShNew.Select
    Application.StatusBar = ""
    
    SecondsElapsed = Round(Timer - StartTime, 2)

  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub 

interestingly enough, the following replacement was about 3× faster

    ShNew.Range("A1").Resize(10000, 1).Value2 = Array(10)

I think this is because .Value and .Value2 expect an array when called with multiple cells in the range. So fewer conversions must happen internally.

Answer 2

Working with arrays in VBA to write data is so much faster than adding it one by one in a for loop.

Have a loot at the below example

Sub ArrayFillRange()

'turning off screen updating makes calculations happen faster
    Application.ScreenUpdating = False

' Create Sheet    
    Set ShNew = Worksheets.Add

'   Fill a range by transferring an array
    Dim CellsDown As Long
    Dim i As Long
    Dim TempArray() As Double
    Dim TheRange As Range
 
'   Change these values
    CellsDown = 100000

'   Redimension temporary array
    ReDim TempArray(1 To CellsDown)
 
'   Set worksheet range
    Set TheRange = Range(Cells(1, 1), Cells(CellsDown, 1))
 
'   Fill the temporary array 
    For i = 1 To CellsDown
        TempArray(i) = 10
    Next i
 
'   Transfer temporary array to worksheet
    TheRange.Value = TempArray

'   Turning screen updating back on
    Application.ScreenUpdating = True

End Sub

On a side note, i'm not sure why your code takes so long on your computer as it ran in .34 seconds on mine.

If you are interested in the speed difference look below, I've averaged the run time of both options (array and for loop) over 5 runs for a increasing number of rows.

You can clearly see that arrays outperform the for loop.