***NEW CUSTOMER OFFER***
Get 15 minutes of free Live Help!

How to time VBA procedures and create a log of elapsed time

2 comments

This set of functions will let you track how long it took for one or more pieces of VBA code to run.  The logic includes three sections:  name and initialize the , end the , and write the results to a log file.

Here is an example of timing part of the CalcHoursOrMinutes() procedure:

StartTimer "CalcHoursOrMinutes"
    ' Get the total minutes.
    lngTotalMinutes = (Nz(Hours, 0) * 60) + Nz(Minutes, 0)

    ' Get the hours.
    lngHours = Int(lngTotalMinutes / 60)

    ' Get the minutes.
    lngMinutes = lngTotalMinutes - (lngHours * 60)

    If CalcType = "Hours" Then
        CalcHoursOrMinutes = lngHours
    Else
        CalcHoursOrMinutes = lngMinutes
    End If
EndTimer

The section of code to be timed starts with a call to StartTimer() and ends with a call to EndTimer(). The EndTimer() procedure writes the timer results to a log file, which gets saved in the same folder as the project. For the example above, the tab-delimited results would look like this:

Timestamp	TimerName	ElapsedTime
10/30/2013 6:28:35 AM	CalcHoursOrMinutes	0.046875

When the text file is imported into Microsoft Excel or Microsoft Access, the file will create three , using the first row as the header.

The timer log procedures can be called multiple times in a work process, but they cannot be nested.

Code:

Option Compare Text
Option Explicit

' basTimerLog() Version 1.0.0
' Copyright © 2013 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected]
' Use (at your own risk) and modify freely as long as proper credit is given.

' This code module provides some procedures that can be used to start
' and end a timer, and then place the timer results in a tab-delimited
' text file.  Example:
' StartTimer "Loop through recordset"
'     Code here...
' EndTimer

' Determines whether or not we are using timers.
Public Const mblncTimer As Boolean = True

Public mvarTimerName
Public mvarTimerStart

Public Function StartTimer(TimerName)
' This procedure initializes the timer.
On Error GoTo Err_Handler

    ' If we are using timers, set the timer name and start time variable
    ' values.
    If mblncTimer Then
        mvarTimerName = TimerName
        mvarTimerStart = Timer
    End If

Exit_Proc:
    On Error Resume Next
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "StartTimer()"
    Resume Exit_Proc

End Function

Public Function EndTimer()
' This procedure stops the timer, collects information about the results,
' and then writes it to a file.
On Error GoTo Err_Handler

    Dim strFile As String
    Dim strContent As String

    If mblncTimer Then
        ' Build the log file name - a text file in the same folder as
        ' this project, like \project folder\project name TimerLog.txt.
        strFile = CurrentProject.Path & "\" _
        & Left(CurrentProject.Name, InStr(1, CurrentProject.Name, ".") - 1) _
        & "TimerLog.txt"

        ' If the timer log does not exist yet, start the file with column
        ' headers.
        If Len(Dir(strFile)) > 0 Then
            ' File is there already.  Do nothing.
        Else
            ' Build the header.
            strContent = _
            "Timestamp" & vbTab & _
            "TimerName" & vbTab & _
            "ElapsedTime"

            ' Write to the log file.
            WriteTextFile strFile, strContent
        End If

        ' Build the tab-delimited content and write it to the file.
        strContent = Now() & vbTab & mvarTimerName & vbTab _
            & (Timer - mvarTimerStart, "0.000000")
        WriteTextFile strFile, strContent
    End If

Exit_Proc:
    On Error Resume Next
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "EndTimer()"
    Resume Exit_Proc

End Function

Private Function WriteTextFile(FileName As String, Content As String) _
    As Boolean
' This function writes/appends the Content to the FileName file.
' Example:
' WriteTextFile "C:\Program Files\InvoicingDB\InvoicingDB TimerLog.txt", _
    Now() & vbTab & mvarTimerName & vbTab _
    & (Timer - mvarTimerStart, "0.000000")
On Error GoTo Err_Handler

    Dim intFileNum As Integer

    ' Get a free file.
    intFileNum = FreeFile
    ' Open the file for appending.
    Open FileName For Append As #intFileNum
    ' Append the content.
    Print #intFileNum, Content

    WriteTextFile = True

Exit_Proc:
    On Error Resume Next
    Close #intFileNum
    Exit Function

Err_Handler:
    MsgBox Err.Number & " " & Err.Description, vbCritical, _
        "WriteTextFile()"
    WriteTextFile = False
    Resume Exit_Proc
End Function

Download:

basTimerLog.zip

  1. Access Guru says:

    there is another way to get the elapsed time. To get the time period we have to write two function open date time and close date time on Form open and close event. These function return the time period.For create log file time period is main thing.

    For step by Step procedure :
    here is link:
    http://www.accessguru.net/Articles_MSAccess/0047-Create%20a%20log%20file%20in%20MS%20Access%20using%20VBA.php

    • Carl Connett says:

      Access Guru, thank you for the input. I typically use the functions provided in the post above to time specific parts of a work process so I can track down places in the logic that need improved efficiency. But StartTimer() and EndTimer() may certainly be called on a form’s Open and Close events if you would like to track how long a form was open.

Leave a Reply

Your email address will not be published. Required fields are marked *

Live Chat Software
%d bloggers like this: