## Calculating Time to Execute Code

You may wonder how to calculate elapsed time down to the thousandth of a second as shown in Figure 13.9.

This is the code used to generate the time results for the macros in this section:

Public Declare Function QueryPerformanceFrequency _ Lib "kernel32" (lpFrequency As Currency) As Long Public Declare Function QueryPerformanceCounter _

Lib "kernel32.dll" (lpPerformanceCount As Currency) As Long

Sub Test()

Dim Ar(1 To 20, 1 To 4) As Currency, WS As Worksheet

Dim n As Currency, str As Currency, fin As Currency

Dim y As Currency

Dim i As Long, j As Long

Application.ScreenUpdating = False For i = 1 To 4

Set WS = ThisWorkbook.Sheets.Add

QueryPerformanceFrequency y

QueryPerformanceCounter str

Select Case i

Case 1: Macro1

Case 2: Macro1_Version2

Case 3: Macro1_Version3

Case 4: Macro1_Version4

End Select

QueryPerformanceCounter fin Application.DisplayAlerts = False WS.Delete

Application.DisplayAlerts = True n = (fin - str)

Ar(j, i) = CCur(Format(n, "##########.############") / y) Next j Next i

.Value = Array("Macro1", "Macro1_Version2", "Macro1_Version3", "Macro1_Version4") .Font.Bold = True End With

With Range("A22").Resize(1, 4)

.FormulaR1C1 = "=AVERAGE(R2C:R21C)" .Offset(1).FormulaR1C1 = "=RANK(R22C,R22C1:R22C4,1)" .Resize(2).Font.Bold = True End With

Application.ScreenUpdating = True End Sub

0 0