Trying to optimize macro speed - ideas for a macro timer that I can easily toggle on/off for troubleshooting purposes?

[d0rian]

I found code that will time how long it takes a macro takes to run (and generate a pop-up box with the time it took.) But that solution (pasted below), would -- if I'm understanding correctly -- require that I insert those lines of code around every macro that I want to time (and I've got like 50+), and then I suppose remove or comment it out if i no longer want the timer to run. That's clunkier than I want.

The holy grail for me would be some sort of checkbox that would let me toggle the timer on/off...so basically I want to tell Excel "When this box is checked, I want you to time any macro I run (and generate the popup letting me know the speed), but when it's unchecked, I don't want my macros timed." That would essentially let me turn it on when I wanted to work in 'troubleshooting mode', and then with a single click turn it off when I'm in work mode. But my current solution relies on lines of code added / deleted to the VBA whenever i want to turn the timer on/off, which isn't practical. Any ideas?

Sub CalculateRunTime_Seconds()'PURPOSE: Determine how many seconds it took for code to completely run

Dim StartTime As Double
Dim SecondsElapsed As Double


'Remember time when macro starts
StartTime = Timer


'*****************************
'Insert Your Code Here...
'*****************************


'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)


'Notify user in seconds
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


End Sub

 

[mikerickson]

This modification of your existing code will allow you to pick which macro to time.

Sub TimeAMacro()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim MacroName As String
    
    MacroName = Application.InputBox("What Macro Do You Want Timed", Type:=2)
    If MacroName = "False" Then Exit Sub: Rem cancel pressed
    
    StartTime = Timer
    Application.Run MacroName
    SecondsElapsed = Round(Timer - StartTime, 2)

    MsgBox MacroName & " ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

And a userform could be created that would substitute for the above InputBox and allow the user to choose from a list.
But I don't think that a "time everything" on/off macro is possible.

 

[MARK858]

Personally if doing this often then I would purchase FastExcel (even if just the SpeedTools Calc part).
Unfortunately there aren't any special offers I am aware of at present.

http://www.decisionmodels.com/FastExcelV3_Buy.htm

Please note that there is often a banner for this product at the top of most pages on MrExcel.