[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Time count

Posted on 2003-10-23
5
Medium Priority
?
193 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
[X]
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
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 86

Accepted Solution

by:
Mike Tomlinson earned 500 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

656 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