• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Excel VBA

Is there a way to build a timer into the code to let you know how long the program ran?
2 Solutions

pls refer to http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code

or the simple

Dim start_time, end_time
start_time = Now()
' Do your stuff here '
end_time = Now()
MsgBox(DateDiff("s", start_time, end_time))

Open in new window

Martin LissOlder than dirtCommented:
If you want to find out how long the workbook was open then in the VBE (click Alt+F11 to get there) and if you don't already have a module go to Insert|Module and add one. At the top of the module put

Public gStartTime As Date

Open in new window

Then add this code in 'ThisWorkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intMin As Integer
Dim intSec As Integer
intSec = DateDiff("s", gStartTime, Now())
intMin = intSec \ 60
MsgBox "The program ran for " & DateDiff("m", gStartTime, Now()) & " minutes and " & intSec & " seconds"
End Sub

Private Sub Workbook_Open()
gStartTime = Now()
End Sub

Open in new window

Here's a workbook that does it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now