Visual Basic

Calling on All Your Problem-Solving Skills

Constantly examine your approach to solving problems, and always encourage input and criticism from all quarters on the same. Think problems through. And always profile your code!

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