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:
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.