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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
This modification of your existing code will allow you to pick which macro to time.
Code:
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top