Sunday, 17 May 2015

How To Calculate the Time of Your Macro Takes To Run

VBA Code to Calculate How Much Time Your Macro Takes To Run
Method-I : To Calculate Macro Time in Seconds using Timer () Method
Sub Macro_Time_Seconds()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Timer

For x = 1 To 100000
Cells(x, 1) = "Hi"
Next x

EndTime = Timer

TimeTaken = Round(EndTime - StartTime, 2)

MsgBox "Time To Run the Macro: " & TimeTaken & " Seconds"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Seconds"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub




------------------------------------------------------------------------------------------------------------------------
Method-II : To Calculate Macro Time in Seconds using Now() Method
Sub Macro_Time_from_Now()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Now

For x = 1 To 100000
Cells(x, 1) = "Hi"
Next x

EndTime = Now

TimeTaken = Format((EndTime - StartTime) * 24 * 60 * 60, "#,##0")

MsgBox "Time To Run the Macro: " & TimeTaken & " Seconds"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Seconds"

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub



------------------------------------------------------------------------------------------------------------------------
Method-III : To Calculate Macro Time in Minutes using Timer() Method
Sub Macro_Time_Minutes()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

StartTime = Timer

For x = 1 To 1000000
Cells(x, 1) = "Hi"
Next x

EndTime = Timer

TimeTaken = Format((EndTime - StartTime) / 86400, "hh:mm:ss")

MsgBox "Time To Run the Macro: " & TimeTaken & " Minutes"
Debug.Print "Time To Run the Macro: " & TimeTaken & " Minutes"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thanks ,
TAMATAM



1 comment:

  1. Hi My Dear Users ,
    In the above macro , if you replace :
    For x = 1 To 1000000
    Cells(x, 1) = "Hi"
    Next x

    with :
    Activesheet.[A1:A1000000]="Hi"

    Then the macro will run only in 00:00:02 Seconds.

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts