Measure - danielep71/VBA-PERFORMANCE GitHub Wiki

[Home]] ](/danielep71/VBA-PERFORMANCE/wiki/[[Previous-|-Measure) | Next

When testing and tuning VBA code, it is useful to systematically review your routines to identify where time is actually being spent and to eliminate true bottlenecks. Performance issues are rarely distributed evenly across a project: a small number of routines—or even a few lines inside a loop—often dominate total runtime. A disciplined measurement approach prevents guesswork and ensures that optimization effort is applied where it will have a meaningful impact.

The first step in any performance improvement effort is to isolate the slow section of code. That requires two things:

  1. A reliable method for recording elapsed time, with sufficient precision for short-lived routines; and

  2. A repeatable measurement approach, so results are stable and comparable across different implementation strategies.

To support this, a lightweight “timing” class can be extremely effective. The class acts as a small profiling utility: it can timestamp the start and end of a procedure (or individual blocks within it), compute elapsed time, and optionally log results. This makes it possible to:

  • measure end-to-end runtime of a routine,

  • compare alternative implementations of the same logic,

  • time-specific sections to locate hotspots,

  • quantify the benefit of an optimization (rather than relying on intuition),

  • and avoid regressions by periodically re-checking known critical paths.

Why Now(), Date(), and Time() are not suitable for fine-grained timing

The built-in VBA functions Now() (and related Date() and Time()) are typically useful only for measuring operations that take multiple seconds. In most environments, their effective resolution is about one second, which is far too coarse for timing typical VBA routines—many of which complete in milliseconds or less. Unless you repeat a test many times and average the results, Now () cannot reliably distinguish between “fast” and “faster.”

Choosing the right clock: resolution and “tick” size

Fortunately, VBA can access several different timing sources (“clocks”), each with its own resolution—the smallest time interval it can measure. A clock’s resolution is often referred to informally as its “tick size.” The finer the resolution, the more useful the clock is for short routines and micro-benchmarks.

However, resolution is not the only consideration. Different clocks also vary in:

  • accuracy (how close the measurement is to the true elapsed time),

  • stability/monotonicity (whether time always moves forward, independent of system clock adjustments),

  • overhead (how expensive it is to read the timer),

  • and portability across Windows versions and 32/64-bit VBA.

A comparative approach: common timing options in VBA

Because VBA frequently allows multiple ways to achieve the same outcome—with very different performance characteristics—this class is designed to benchmark procedures consistently using several commonly available timing mechanisms. The intent is to compare results and understand the practical trade-offs of each option:

  • VBA Timer. Simple and built-in; provides sub-second timing but has limitations (e.g., wraps at midnight and its precision varies).

  • GetTickCount API. Returns elapsed milliseconds since system start (subject to wraparound after a long uptime). Good for coarse measurements, not ideal for micro-benchmarks.

  • timeGetTime API. Also millisecond-based, historically associated with multimedia timers. Similar use cases to GetTickCount, with its own platform considerations.

  • TimeGetSystemTime API. Provides access to system time with higher nominal resolution, but practical precision and behavior depend on Windows configuration and implementation details.

  • QueryPerformanceCounter (QPC) / High-Resolution Timer. Often the best choice for short measurements. QPC is designed for high-resolution, high-accuracy timing and is commonly referred to as a “micro-timer.” It is typically the preferred source when you need sub-millisecond timing and a monotonic clock.

  • VBA Now(). Included mainly as a reference point to demonstrate why it is insufficient for short durations.

Target precision: what “hundreds of nanoseconds” really means

Ideally, the class should support sub-microsecond measurement (on the order of hundreds of nanoseconds). In practice, it is important to distinguish between:

  • the resolution of the underlying clock (what the timer can represent), and

  • the effective precision of your measurement (what you can reliably observe given call overhead, OS scheduling, CPU frequency scaling, background activity, and VBA runtime overhead).

High-resolution timers such as QPC can represent extremely fine intervals, but in VBA, you will rarely achieve repeatable “nanosecond-accurate” measurements for arbitrary code blocks. What you can do—very effectively—is measure small code sections by running them repeatedly and using a high-resolution clock to obtain stable averages. That yields actionable, trustworthy comparisons between alternative implementations.

Summary

A dedicated timing class provides a structured, repeatable way to profile VBA procedures, identify bottlenecks, and evaluate optimization opportunities. By using multiple timing mechanisms—ranging from built-in clocks to high-resolution APIs—you can balance simplicity, portability, and measurement fidelity, and you can select the most appropriate timer for the runtime scale you are trying to analyze.

Home