A truly useful code profiler would include some way to time Visual Basic's routines. For example, how fast is Val when compared with its near functional equivalent CInt? You can do some of this profiling using the subclassing technique discussed in Chapter 1 (replacing some VBA routine with one of your own-see Tip 11), but here's a small example anyway:
Declarations Section
Option Explicit
Declare Function WinQueryPerformanceCounter Lib "kernel32" _
Alias "QueryPerformanceCounter" (lpPerformanceCount As LARGE_INTEGER) _
As Long
Declare Function WinQueryPerformanceFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (lpFrequency As LARGE_INTEGER) _
As Long
Type LARGE_INTEGER
LowPart As Long
HighPart As Long
End Type
In a Module
Function TimeGetTime() As Single
Static Frequency As Long
Dim CurrentTime As LARGE_INTEGER
If 0 = Frequency Then
Call WinQueryPerformanceFrequency(CurrentTime)
Frequency = CurrentTime.LowPart / 1000
TimeGetTime = 0
Else
Call WinQueryPerformanceCounter(CurrentTime)
TimeGetTime = CurrentTime.LowPart / Frequency
End If
End Function
Replacement for Val
Public Function Val(ByVal exp As Variant) As Long
Dim l1 As Single, l2 As Single
l1 = TimeGetTime()
Val = VBA.Conversion.Val(exp)
l2 = TimeGetTime()
Debug.Print "Val - " & l2 - l1
End Function
The TimeGetTime routine uses the high-resolution timer in the operating system to determine how many ticks it (the operating system's precision timing mechanism) is capable of per second (WinQueryPerformanceFrequency). TimeGetTime then divides this figure by 1000 to determine the number of ticks per millisecond. It stores this value in a static variable so that the value is calculated only once.
On subsequent calls, the routine simply returns a number of milliseconds; that is, it queries the system time, converts that to milliseconds, and returns this value. For the calling program to determine a quantity of time passing, it must call the routine twice and compare the results of two calls. Subtract the result of the second call from the first, and you'll get the number of milliseconds that have elapsed between the calls. This process is shown in the "Replacement for Val" code.
With this example, one can imagine being able to profile the whole of VBA. Unfortunately, that isn't possible. If you attempt to replace certain routines, you'll find that you can't. For example, the CInt routine cannot be replaced using this technique. (Your replacement CInt is reported as being an illegal name.) According to Microsoft, for speed, some routines were not implemented externally in the VBA ActiveX server but were kept internal-CInt is one of those routines.