Solved

Time count

Posted on 2003-10-23
5
185 Views
Last Modified: 2010-05-03
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
Comment
Question by:Aiysha
5 Comments
 
LVL 1

Expert Comment

by:calebS
Comment Utility
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
 
LVL 1

Expert Comment

by:calebS
Comment Utility
Run this:

Sub happy()
    MsgBox Timer
End Sub

to see what the timer displays.
0
 

Author Comment

by:Aiysha
Comment Utility
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
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 125 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now