Excel VB Code Execution Speed. Puzzled!!!!

[MadForIt]

Hi.
Can anyone please explain to me why the code in Test1 takes 3 times longer to execute than Test2? Unless i am missing something here it appears that it is better to select a sheet other than the one that you want to work with!

Cheers.

Sub Test1()
St = Now()
Sheet2.Select
For i = 1 To 10000
Sheet2.Cells(1, 1) = 1
Sheet2.Cells(1, 2) = 2
Sheet2.Cells(1, 3) = 3
Sheet2.Cells(1, 4) = 4
Sheet2.Cells(1, 5) = 5
Sheet2.Cells(1, 6) = 6
Next i
En = Now()
MsgBox (En - St) * 1440 * 60
End Sub

Sub Test2()
St = Now()
Sheet1.Select
For i = 1 To 10000
Sheet2.Cells(1, 1) = 1
Sheet2.Cells(1, 2) = 2
Sheet2.Cells(1, 3) = 3
Sheet2.Cells(1, 4) = 4
Sheet2.Cells(1, 5) = 5
Sheet2.Cells(1, 6) = 6
Next i
En = Now()
MsgBox (En - St) * 1440 * 60
End Sub

 

[MadForIt]

Hi.
Have answered my own question. It must be something to do with screen updating. Writing to the sheet that is selected constantly updates the screen and therefore slows down code execution. Changing Test1 to read as follows makes the code run at the same speed as Test2 does.

Sub Test1()
Application.ScreenUpdating = False
St = Now()
Sheet2.Select
For i = 1 To 10000
Sheet2.Cells(1, 1) = 1
Sheet2.Cells(1, 2) = 2
Sheet2.Cells(1, 3) = 3
Sheet2.Cells(1, 4) = 4
Sheet2.Cells(1, 5) = 5
Sheet2.Cells(1, 6) = 6
Next i
En = Now()
MsgBox (En - St) * 1440 * 60
End Sub