Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Annie Russell   on Feb 13 In MS Office Category.

  
Question Answered By: Beau Smith   on Feb 13

I think we have several things going on here (or not going on).

First, You may be confusing the VBA "Time" function with the
WorkSheetFunction Time()

In VBA, the time  function only returns the current system time.
(which you are using properly with: Stime = time)

But you cannot use: time(hour(stime),minute(stime),second(stime))
to convert the hour,minutes,seconds to a time format, because that is
the WorkSheetFunction version of Time.

Second, your Application.Wait function says to wait until 1:30 in the
morning... I'm not sure that's what you're wanting.

Try: Application.Wait (Now + TimeValue("0:01:30"))
This calculates a time 1 minute, 30 seconds from Now, and tells
the macro to "sleep" until that time is reached.

The function you probably need is called DateDiff.
It calculates the difference between two dates (including time) in
whatever units you're interested in.

Using this criteria, your macro becomes:

Dim Stime,Dtime
Private Sub Timecal()
Stime = Now
Application.Wait (Now + TimeValue("0:01:30"))
Dtime = DateDiff("s", Stime, Now)
MsgBox Dtime & " seconds"
End Sub

which will produce a Message Box that says: "90 seconds"

Now, another option would be to use the "Timer" function.
this function returns the number of seconds since midnight.
It isn't quite as useful if you're starting  a job at 11:55pm and
ending after midnight, but you probably work day shift anyway.

what I often use is:
----------------------------------------------------------
tstart = Timer
...
Lots of code
...
tstop = Timer
TElapsed = tstop - tstart
TMin = 0
TMin = TElapsed \ 60
TSec = TElapsed Mod 60
msg = ""
If (TMin > 0) Then msg = TMin & " mins "
msg = msg & TSec & " sec"
----------------------------------------------------------

Both of these worked for me.

Share: 

 

This Question has 9 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Time taken - Calculation Or get search suggestion and latest updates.


Tagged: