VBA Express : Excel - Test procedure run time

Excel

Test procedure run time

Ease of Use

Intermediate

Version tested with

2000, 2003

Submitted by:

Ken Puls

Description:

This function returns the length of time it takes for your specified procedure to run (in seconds).

Discussion:

Sometimes, we want to test the amount of time it takes for a procedure to complete, possibly to evaluate the effect of minor changes, vs a completely different approach, or just for our own satisfaction. This function takes one argument (the procedure name), and returns the time in seconds it takes to run. It should be noted, however, that any type of message box or input box can significantly skew results since they wait for user input. Also, to get a true picture of time, you should run the function a few times and average the results.

Code:

instructions for use

Option Explicit Function TimeTester(sProcName As String) As Single 'Macro Purpose: To test the time a macro takes to run Dim TimeStart As Single, TimeEnd As Single TimeStart = Timer Application.Run (sProcName) TimeEnd = Timer TimeTester = TimeEnd - TimeStart End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Navigate to the correct module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

Create a new procedure that returns a "Single" using the following logic:
myTime=TimeTester("myProcedureName")
Run the procedure.
For more detailed information, review the sample in the attached file.

Sample File:

ProcedureTimer.zip 9.9KB

Approved by mdmackillop

This entry has been viewed 163 times.