Frank
Thank you for the in depth explanation of the workings of a macro.
I will use the info to speed up my macro's.
BlouBul
Just to prove a point, you might like to try these 2 macros on a BLANK spreadsheet.
With no other spreadsheet loaded into Excel. they are WRITELIST and WRITELIST2
The code in them is identical except that the second macro has code which disables both screen updating and spreadsheet recalculation during the macro run.
All each macro does is write 1000 lines of arithmetic to the first three columns of a spreadsheet. There is a test to see if the number in the first column is a multiple of 10, to decide what to do in column 3. What it does is not important, its nonsense just to give Excel some work to do. What is important is HOW LONG IT TAKES. Both macros have timers built in so you can compare.
Notice the difference between the one that recalculates every time it writes to the spreadsheet and the one that doesn't ?
Now try the same experiment with a large spreadsheet ALSO loaded into Excel, just sitting in the background.
When you run the first macro you might want to go and make a cup of coffee ...
When you run the second one, you wont have time to.
Try it and see..
Sub WRITELIST()
Dim i As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double
'Remember time when macro starts
StartTime = Timer
Columns("A:C").Select
Selection.ClearContents
Range("a1").Select
For i = 1 To 1000
ActiveCell.Offset(i, 0).Value = i
ActiveCell.Offset(i, 1).Formula = "=RC[-1]*R[-1]C[-1]"
If i Mod 10 = 0 Then
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-2]"
Else
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-1]"
End If
Next
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub
Sub WRITELIST2()
Dim i As Integer
Dim StartTime As Double
Dim SecondsElapsed As Double
'Remember time when macro starts
StartTime = Timer
Columns("A:C").Select
Selection.ClearContents
Range("a1").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To 1000
ActiveCell.Offset(i, 0).Value = i
ActiveCell.Offset(i, 1).Formula = "=RC[-1]*R[-1]C[-1]"
If i Mod 10 = 0 Then
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-2]"
Else
ActiveCell.Offset(i, 2).Formula = "=RC[-1]*R[-1]C[-1]"
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub