MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to calcualte elapsed time in excel

  Asked By: Vernon    Date: Jan 09    Category: MS Office    Views: 6072

I am downloading data from web in excel via webquery macro . In the
status bar I have used the code line Application.StatusBar = "Please
wait while updating..." I want to add the time also as the statement
remains fixed So i wnat to add elapsed time in the format 00:00:01
which will increment for every second ie the status bar should show:
Application.StatusBar = "Please wait while updating..." 00:00:01
How can I do so?



1 Answer Found

Answer #1    Answered By: Elliot Evans     Answered On: Jan 09

This can be done, and it's not difficult, but I wouldn't advise
refreshing it every second.
First: It's very unlikely that you are doing ANYTHING in a loop that
will cycle every second. so, the Application.Statusbar statement will
not execute every second.
Second: Screen refreshes are VERY time-expensive!
Let's say you're retrieving 1,000 records and it takes about 30-45 seconds
to process. If you're updating  every second, your cycle time  could
be 3-5 MINUTES!
I usually do something like this while testing, then change it to update
about every 100 or 1000 records (depending on the query)

If you still want to do it, what I would do is:
Sub StatTest()
Dim tStart, tStop
Dim tHrs, tMin, tSec, tElapsed
tStart = Timer
'tStart = 20000.2 '(for testing purposes only)
For I = 1 To 65
tStop = Timer
tElapsed = tStop - tStart
tHrs = Int(tElapsed / 3600)
tMin = Int((tElapsed - (tHrs * 3600)) / 60)
tSec = Int(tElapsed - (tHrs * 3600) - (tMin * 60))
Application.Wait (Now + TimeValue("0:00:01"))
Application.StatusBar = "Please wait  " & Format(tHrs & ":" & tMin &
":" & tSec, "hh:mm:ss")
Next I
Application.StatusBar = "Total Elapsed time: " & tStop - tStart
End Sub

Didn't find what you were looking for? Find more on How to calcualte elapsed time in excel Or get search suggestion and latest updates.