VBA snippet time your code

This is a useful little runner to compare two techniques to verify which is fastest.

 

Option Explicit

Sub test()
Dim tstart As Double, tend As Double
Dim tdiff As Double, i As Long

' Method 2
tstart = Timer
Range("B1").Select
For i = 1 To 20000
ActiveCell.Offset(i - 1, 0).Value = i ^ 2
Next i
tend = Timer
tdiff = tend - tstart
MsgBox "Method 2 takes " & tdiff & " seconds to complete"


' Method 1
Application.ScreenUpdating = False

tstart = Timer
' Obtain initial time since midnight
Sheets("Sheet1").Range("A1").Select
For i = 1 To 20000
ActiveCell.Value = i ^ 2
ActiveCell.Offset(1, 0).Select
Next i
tend = Timer
' Obtain final time since midnight
tdiff = tend - tstart
' Calculate the time elapsed to finish the loop
MsgBox "Method 1 takes " & tdiff & " seconds to complete"
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × 2 =