VBA Macro performance timers?

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...
4.40K views
0

G’Day,

Just wanting to ask you about measuring performances of VBA macro’s that is I could see how fast/slow it takes to run the entire length of time to execute whilst an addin such as Rubberduck is doing something else.

The reason I am asking is that I wanted to see if I could achieve the means to monitor performance differences of macros running the same time or not for running Rubberduck add-ins. I am attempting to evaluate how different version of Rubberduck 1.4.3 compares with v2.0.1a on my exisiting VBA code. I had made a issue request about it and Matt Mug has put his thoughts on it.

Kinda stumped about how to measure it in general, I had recalled you mentioned some articles about it.

Perhaps I am off my ball mark thinking a Powershell measure-object would do as I realised it only applies to excuting one VBA xlm file.

Any tips would be appreciated.

Peter.

0

Hi Peter,

interesting activity. What is you objective behind comparing RubberDuck performance? Is it because your macros are running slow?

Depending on how precise you want to be in terms of Performance Measures there are 3 approaches:

  1. Use the VBA Timer – precision 1/1’000 of a Second:
  2. Using the QueryPerformanceCounter kernel32 function – precision 1/1’000’000 of a second
    (function implemented within my VBA Time Saver Kit)
  3. Use WMI Performance metrics (various metrics like CPU Usage and RAM usage).
    I wrote a post on using WMI in VBA here. Or use the WMI Explorer from Microsoft.

My personal opinion is that in most cases the VBA Timer will do fine. Simply run your procedure X number of times in a loop i.e. the longer it runs the better the precision. WMI and the QueryPerformanceCounter is an overkill in most cases. You may just as well use the Windows Task Manager to check how much RAM does the RubberDuck AddIn use (vs Excel w/o RubberDuck).

commented on answer

Simply the best place to learn Excel VBA