Ask ChatGPT, Claude or Copilot for a macro: nine times out of ten, it contains a loop that reads and writes the cells one by one. The code works. But on 50,000 rows, it takes 20 to 40 seconds 🫤🫣
But there are always tricks to optimize code, especially when you have to create loops. In this article, I will show you how to make the same program run 200 times faster 😮, by working in memory instead of on the worksheet. Let’s see the trick now.
1. Why the loop of the AI is slow
At each turn of the loop, VBA goes to the worksheet to read a cell, then goes back to write the result. This back and forth between VBA and Excel is the most expensive operation of a macro. With 50,000 rows, that’s 100,000 trips.
Dim i As Long
For i = 2 To 50001
Cells(i, 3).Value = Cells(i, 2).Value * 1.1
Next i2. The idea: an array in memory
You make one single trip: the whole range is copied at once into an array, in memory. You calculate in this array, which is almost instant. Then you make one single trip back to write everything in the sheet.
Dim data As Variant, i As Long
data = Range("B2:B50001").Value ' 1 single read
For i = 1 To UBound(data)
data(i, 1) = data(i, 1) * 1.1 ' calculation in memory
Next i
Range("C2:C50001").Value = data ' 1 single writeThere is still a loop. But it no longer touches the worksheet: everything happens in memory.
- An array read from a range always has two dimensions, rows and columns, even for a single column. That’s why we write data(i, 1).
- The first row of the array is 1, not 0.
- The destination range must have the same size as the array.
3. Measure it yourself
To compare the two versions, add Timer at the beginning and at the end of each macro:
Dim t As Double
t = Timer
' ... your code ...
Debug.Print "Time: " & Format(Timer - t, "0.00") & " s"
4. Tell it to the AI
We have seen in this article that your AI answer will be more optimized if you start your prompt with /vba. And we have seen other tricks too 😉
/vba
Read the range into an array, work in memory, and write the result back in one step.
Don't read or write cells inside a loop.What to remember
The AI gives you code that works, not necessarily code that is fast. A Cells(i, …) inside a loop is the first thing to look for in a macro written by an AI. And before running it, check the other traps in our article Does AI Always Give the Right Result in Excel?.