XL: Calculating Elapsed Time for a Visual Basic Procedure

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Visual Basic for Applications, you can time procedures, statements, and functions by entering the Timer function before and after the statements that you want to time.

MORE INFORMATION

The Visual Basic Timer function can be used to record the starting and ending times for a series of commands. The following example sets the variable StartTime to the current system time, runs the code to be timed, then sets the variable EndTime to the current system time. Finally, the elapsed time between StartTime and EndTime is printed to the debug window using the Debug.Print command.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Example of Visual Basic Code

Sub ElapsedTime()
   Dim StartTime As Double, EndTime As Double

   'Stores start time in variable "StartTime"
   StartTime = Timer

   'Place your code to be timed here

   'Stores end time in variable "EndTime"
   EndTime = Timer

   'Prints execution time in the debug window
   Debug.Print "Execution time in seconds: ", EndTime - StartTime
End Sub

The following Visual Basic macro creates a text file containing a list of error messages that can be generated in Visual Basic, Applications Edition. The amount of time it takes to accomplish this procedure will be displayed in a message box.

Example of Visual Basic Code

Sub ErrorCodes()
   Dim StartTime As Double, EndTime As Double, X
   'creates a file of error messages
   StartTime = Timer 'Stores start time in variable "StartTime"
   Open "vbaerror.txt" For Output As #1
   For X = 1 To 3300
      Print #1, X, Error$(X)
   Next X
   Close #1
   EndTime = Timer 'Stores end time in variable "endTime"
   'Shows Message Box with elapsed time
   MsgBox "Execution time in seconds: " + Format$(EndTime - StartTime)
End Sub

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 8, page 158