Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Time count

Posted on 2003-10-23
5
189 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
ID: 9610970
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
ID: 9610974
Run this:

Sub happy()
    MsgBox Timer
End Sub

to see what the timer displays.
0
 

Author Comment

by:Aiysha
ID: 9611009
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
ID: 9612228
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
ID: 9616652
>>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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
change vba from autofit to 13.5 width? 4 29
VBA Shell can't Find Word document 11 111
Automatic Email Reminder 4 57
checkbox to hide entire section 10 41
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

839 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