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


AiyshaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.