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

Time count

I want to keep a time count for my entire process. Its a macro that takes data from excel and goes to  two different applications and then returns to excel.. so this is how I plan to do

note the initial time in a global variable1
note the final time in a global vaiable2

and at the end of the code print out a message with the difference in the time of variable2 and variable1

can someone please help me with this. This is what I have so far..And it doesn't work.

Dim InitialTime As Integer
Dim FinalTime As Integer
Dim TimeDifference As Integer

Sub Macro1()
IntialTime = Now()
FinalTime = Now()
TimeDifference = FinalTime - InitialTime
End Sub

Thanx for all the help


0
Aiysha
Asked:
Aiysha
1 Solution
 
calebSCommented:
You could use the Timer function

Dim InitialTime As Integer
Dim FinalTime As Integer
Dim TimeDifference As Integer

Sub Macro1()
IntialTime = Timer
FinalTime = Timer
TimeDifference = FinalTime - InitialTime
End Sub
0
 
calebSCommented:
Run this:

Sub happy()
    MsgBox Timer
End Sub

to see what the timer displays.
0
 
AiyshaAuthor Commented:
Thanx a lot, I had already written the code before you posted your response.. This is what I have and it is very similar to your code.. The timer displays the time in seconds and I wanted it in minutes thats why I divided it by 60..

Dim InitialTime As Date
Dim FinalTime As Date
Dim TimeDifference As Date
Dim formatTime As Double

Sub Macro1()
InitialTime = Timer
FinalTime = Timer
formatTime = FinalTime - InitialTime
formatTime = formatTime / 60
MsgBox (formatTime)

End Sub
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Paste this code into an Excel Module and Run it.  On my system it took 1 min 52 secs to complete.   You may have to change the value in the for loop to simulate a better timeframe on your system depending on your processor speed.  The code demonstrates how to determine time difference between two dates in seconds and also how to break those seconds out into minutes and hours.

Enjoy!

' ---------------------------------------------  Begin Code
Dim InitialTime As Date
Dim FinalTime As Date
Dim TimeDifferenceInSeconds As Long

Private Sub Macro1()
    Dim TotalSeconds As Long
    Dim TotalHours As Long
    Dim TotalMinutes As Long
   
    Dim outputString As String
   
    Dim a As Long, b As Date
       
    '-------------------------------
    InitialTime = Now()
   
    ' Simulate some long task...
    For a = 1 To 3000000
        b = Now()
    Next a
       
    FinalTime = Now()
    '-------------------------------
   
    TimeDifferenceInSeconds = DateDiff("s", InitialTime, FinalTime)
   
    TotalSeconds = TimeDifferenceInSeconds
    TotalHours = TotalSeconds \ 3600
    TotalSeconds = TotalSeconds - (TotalHours * 3600)
    TotalMinutes = TotalSeconds \ 60
    TotalSeconds = TotalSeconds - (TotalMinutes * 60)
           
    outputString = "InitialTime = " & InitialTime & vbCrLf
    outputString = outputString & "FinalTime = " & FinalTime & vbCrLf
    outputString = outputString & "Total Seconds = " & TimeDifferenceInSeconds & vbCrLf
    outputString = outputString & "Total Duration = " & Format(TotalHours, "00") & ":" _
        & Format(TotalMinutes, "00") & ":" & Format(TotalSeconds, "00")
    MsgBox outputString
End Sub
0
 
Anthony PerkinsCommented:
>>Thanx a lot, I had already written the code before you posted your response.. This is what I have and it is very similar to your code.. The timer displays the time in seconds and I wanted it in minutes thats why I divided it by 60..

Dim InitialTime As Date
Dim FinalTime As Date
Dim TimeDifference As Date
Dim formatTime As Double

Sub Macro1()
InitialTime = Timer
FinalTime = Timer<<

Except that the Timer does not return a value of type Date but rather a single (number of seconds since midnight) You can use an Integer like calebS suggests, except that sometime just after 9:00AM you will get an overflow.  You can of course use a Long.

Anthony
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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