text box to show time taken (1 Viewer)

[CoffeeGuru]

Hi guys

I have some vba that ends with a text box being displayed that says "file created"

I would like to add to that text box how long it took.

My initial thoughts are to add to the start of the code a variable that stores the current time (stTime)

and at the line before the text box is shown get the time again (endTime)
and subtract one from the other to get the time taken in mins and sec

How would I achieve this

 

[namliam]

msgbox "file created in " & format(endtime - sttime, "HH:MM:SS")

 

[pr2-eugin]

You would exactly the same, as you just mentioned.

Public Sub yourSubName()
    Dim stTime As Date, endTime As Date
    Dim totSec As Long, totMin As Long
    Dim elapsedTime As String
    
    stTime = Now()
    '
    'All your code in between
    '
    endTime = Now()
    
    totSec = DateDiff("s", stTime, endTime)
    
    totMin = totSec \ 60
    
    elapsedTime = totMin & " Minutes and " & " Seconds."
    
    MsgBox elapsedTime, vbInformation
End Sub

 

[CoffeeGuru]

Thanks both of you, I just needed the extra missing steps

 

[Mile-O]

Okay, it's solved, but how about this as an alternative solution?

It uses a class module - so if you want to time multiple things throughout your database you need only code it once - and can return the elapsed time in hours, minutes, seconds, or milliseconds.

Basically, create a new Class Module and copy in this code (sorry, I do like lots of comments!)

'================================================================================
' CLASS StopWatch: Start
'================================================================================
' ABOUT:    A simple start and stop timer for accurately measuring how long
'           an activity takes
'================================================================================

'================================================================================
' DECLARATIONS: Start
'================================================================================
    Option Compare Database
    Option Explicit
    
    Private Declare Function GetTickCount Lib "kernel32" () As Long
'================================================================================
' DECLARATIONS: End
'================================================================================

'================================================================================
' ENUMERATIONS: Start
'================================================================================

    Public Enum vbDateInterval
        vbHours
        vbMinutes
        vbSeconds
        vbMilliseconds
    End Enum ' vbDateInterval

'================================================================================
' VARIABLES: Start
'================================================================================
    Private mlngStart As Long
'================================================================================
' VARIABLES: End
'================================================================================

'================================================================================
' SUBROUTINES: Start
'================================================================================
    Public Sub TimerStart()
        mlngStart = GetTickCount
    End Sub ' StartTimer
'================================================================================
' SUBROUTINES: End
'================================================================================
'================================================================================
' FUNCTIONS: Start
'================================================================================
    Public Function TimerStop() As Long
        ' calculates the actual tick count since the timer started
        TimerStop = (GetTickCount - mlngStart)
    End Function ' TimerStop
    
    Public Function ElapsedTime(ByRef TimerValue As Long, ByRef DateInterval As vbDateInterval) As Long
        
        ' quick function to calculate the elapsed time (based on tick count)
        
        Select Case DateInterval
                    
            Case vbHours:   ElapsedTime = Int(TimerValue / 1000 / 60 / 24)
            
            Case vbMinutes: ElapsedTime = Int(TimerValue / 1000 / 60)
            
            Case vbSeconds: ElapsedTime = Int(TimerValue / 1000)
            
            Case vbMilliseconds: ElapsedTime = TimerValue
            
            Case Else: ElapsedTime = 0
            
        End Select ' DateInterval
        
    End Function ' ElapsedTime
    
    Public Function HMS(ByRef TimerValue As Long, ByRef DateInterval As vbDateInterval)
        ' HMS = Hours.Minutes.Seconds
    
        ' quick function to calculate the elapsed time element (based on tick count)
    
        Select Case DateInterval
        
            Case vbHours:   HMS = Int(TimerValue / 1000 / 60 / 24)
            
            Case vbMinutes: HMS = Int(TimerValue / 1000 / 60) Mod 60
            
            Case vbSeconds: HMS = Int(TimerValue / 1000) Mod 60
            
            Case vbMilliseconds: HMS = TimerValue Mod 1000

            Case Else: HMS = 0
            
        End Select ' DateInterval
        
    End Function ' HMS
'================================================================================
' FUNCTIONS: End
'================================================================================
'================================================================================
' CLASS StopWatch: End
'================================================================================

Then, to show it works, pop the following code into a regular module and run it.

Public Sub TestProcedure()

    ' create an instance of the StopWatch class
    Dim Clock As New StopWatch
    
    ' variable for recording how long activity lasted
    Dim lngElapsed As Long
    
    ' start the timer
    Clock.TimerStart
    
    ' stick some code in here
    MsgBox "This is an example of a line of code. So we'll time how long you don't press Ok for.", vbInformation + vbOKOnly, "Wait!"
    
    ' record how many milliseconds the activity lasted
    lngElapsed = Clock.TimerStop
    
    ' example of output
    MsgBox "Hours: " & Clock.ElapsedTime(lngElapsed, vbHours) & vbCrLf & vbCrLf & _
           "Minutes: " & Clock.ElapsedTime(lngElapsed, vbMinutes) & vbCrLf & vbCrLf & _
           "Seconds: " & Clock.ElapsedTime(lngElapsed, vbSeconds) & vbCrLf & vbCrLf & _
           "Milliseconds: " & Clock.ElapsedTime(lngElapsed, vbMilliseconds)
           
    MsgBox "Hours: " & Clock.HMS(lngElapsed, vbHours) & vbCrLf & vbCrLf & _
           "Minutes: " & Clock.HMS(lngElapsed, vbMinutes) & vbCrLf & vbCrLf & _
           "Seconds: " & Clock.HMS(lngElapsed, vbSeconds) & vbCrLf & vbCrLf & _
           "Milliseconds: " & Clock.HMS(lngElapsed, vbMilliseconds)
           
End Sub ' TestProcedure

If you declare publically Clock As StopWatch, you can effectively use it anywhere/anytime in your database.

The other 'win' is that you can import it from database to database and will work.

 

[CoffeeGuru]

Okay, it's solved, but how about this as an alternative solution?

Whoa! That is an exemplary answer.
It goes way beyond what I was asking for but I very much appreciate your time and effort and will certainly look at this. I could then use it in all my reports (even if just for laughs)

Martin

 

[Mile-O]

I'm such a meta-data collector in my databases. I like to know what users are up to, how they use my database, etc.

An example of how I would use something like this is to record average time spent on a form. If the average time is unusually high, it may pose questions such as a) are people sitting inactive in the form; b) is it too complex? c) other, must investigate.