none
Monitor Code speed RRS feed

  • Question

  • Hi

    I have just taken over a complex and very slow set of excel VBA modules.

    is there a way I can use a 'monitoring tool' to time how long it takes to get through the code - perhaps taking 'time snap shots' as it reaches certain parts of the code?

    any ideas/help would be much appreciated - as this is quite a beast and extremely slow !

    many thanks

    Peter

    Thursday, September 22, 2016 3:00 PM

Answers

  • The Timer function returns the number of seconds since midnight, with a precision of 1/64 or 1/256 of a second.

    You could use code like this:

    Dim t0 As Single
    Dim t As Single
    t0 = Timer
    t = t0
    ...
    ...
    t = Timer - t
    Debug.Print "Part 1 took " & Format(t, "0.00") & " seconds"
    t = Timer
    ...
    ...
    t = Timer - t
    t0 = Timer - t0
    Debug.Print "Part 2 took " & Format(t, "0.00") & " seconds"
    Debug.Print "Total time " & Format(t0, "0.00") & " seconds"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, September 23, 2016 8:33 AM
    Thursday, September 22, 2016 3:39 PM
  • Hi py1,

    According to your description, you could follow Hans's suggestion building a Stopwatch type class around the built-in VBA Timer function, or make a super accurate Stopwatch class using the Windows API functions QueryPerformanceFrequency and QueryPerformanceCounter. 

    For more information, please refer to Accurate Performance Timers in VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by py1 Friday, September 23, 2016 8:34 AM
    Friday, September 23, 2016 2:35 AM

All replies

  • The Timer function returns the number of seconds since midnight, with a precision of 1/64 or 1/256 of a second.

    You could use code like this:

    Dim t0 As Single
    Dim t As Single
    t0 = Timer
    t = t0
    ...
    ...
    t = Timer - t
    Debug.Print "Part 1 took " & Format(t, "0.00") & " seconds"
    t = Timer
    ...
    ...
    t = Timer - t
    t0 = Timer - t0
    Debug.Print "Part 2 took " & Format(t, "0.00") & " seconds"
    Debug.Print "Total time " & Format(t0, "0.00") & " seconds"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, September 23, 2016 8:33 AM
    Thursday, September 22, 2016 3:39 PM
  • Hi py1,

    According to your description, you could follow Hans's suggestion building a Stopwatch type class around the built-in VBA Timer function, or make a super accurate Stopwatch class using the Windows API functions QueryPerformanceFrequency and QueryPerformanceCounter. 

    For more information, please refer to Accurate Performance Timers in VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Marked as answer by py1 Friday, September 23, 2016 8:34 AM
    Friday, September 23, 2016 2:35 AM
  • Hans

    thanks - this looks useful, I will try this.

    many thanks,

    Peter

    Friday, September 23, 2016 8:33 AM
  • David

    Thanks for this - I will try Hans's solution first - but thanks for the link as I am sure it will help going forward.

    Peter

    Friday, September 23, 2016 8:34 AM