Speed up VBA code

Why do macros crawl? VBA itself is almost never the reason. A slow macro is slow because it talks to the worksheet too often. Every time code reads or writes a cell, Excel may redraw the screen and recalculate the workbook. It may fire events too, and it does all that per cell. A loop that writes 10,000 cells one at a time can trigger tens of thousands of redraws and recalcs. The fix comes in two parts.

Part one is the speed trio. Wrap any bulk operation in it:

Application.ScreenUpdating = False      ' stop repainting until done
Application.Calculation = xlManual       ' stop recalcing on every write
Application.EnableEvents = False         ' stop firing Worksheet_Change etc.
'   ... the heavy work ...
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.EnableEvents = True

These three lines routinely cut a macro's run time by 5 to 20×. They cost you nothing except remembering to restore them in an error handler, because a crash with ScreenUpdating still off leaves the user staring at a frozen screen. ScreenUpdating is the big visual win. Calculation = xlManual is the big win on a formula-heavy model, as long as you set it back to automatic and force one Application.Calculate at the end. EnableEvents = False speeds things up as well, and it stops your writes from tripping the very Worksheet_Change handlers you wrote in VBA II.

Why are arrays faster than looping over cells?

Part two, arrays over cells, is the bigger prize. Even with the trio off, touching cells one at a time crosses the boundary between VBA and Excel's grid on every access, and that crossing is the expensive part. Professionals read the whole range into a Variant array in one hit, compute entirely in memory, and write it back in one assignment:

Dim data As Variant, i As Long
data = ws.Range("A2:A10001").Value        ' ONE read: 10,000 cells -> a 2-D array
For i = 1 To UBound(data, 1)
    data(i, 1) = data(i, 1) * 1.05        ' 5% uplift, in memory only
Next i
ws.Range("A2:A10001").Value = data        ' ONE write: array -> 10,000 cells

Now count the boundary crossings. This version touches the worksheet twice, once for the bulk read and once for the bulk write. The cell-by-cell loop touches it 20,000 times, since every cell gets a read and a write. On a 10,000-row column, that one change is the difference between "instant" and "watch the progress bar." Remember the array's shape from VBA II. Range.Value always yields a 1-based, two-dimensional array (data(row, col)), even for a single column, hence data(i, 1) and UBound(data, 1). Put a number on it. A base of $2,200 uplifted 5% becomes 2200 × 1.05 = $2,310.00, computed in the array and written back with the other 9,999 rows in one stroke.