Run Macro 100 times and store times on sheet

[jalrs]

Good afternoon guys,

I have this code that I managed to pull with the help of a old thread here and other website tips, and I want to save each msgbox time on cell A1:A100. How do I accomplish this?

This would help me discussing the results of my project, as I'd have a quite large sample of values.

Sub tempo()

Application.ScreenUpdating = False

Dim wb as workbook
Dim ws as worksheet
Dim i As Long
Dim StartTime As Double
Dim SecondsElapsed As Double

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Resultados")
StartTime = Timer
SecondElapsed = Round(Timer - StartTime, 2)

For i = 1 To 100

Call integrar
Call partilhar
Call regularizar

Next i

MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
 
Application.ScreenUpdating = True
 
End Sub

Any help is greatly appreciated.

Thanks!

 

[Anthony47]

I guess that now your messages always states that it tooks 0.00 seconds to complete the loop, because you calculate SecondElapsed BEFORE starting the loop; move the instruction SecondElapsed = Round(Timer - StartTime, 2) in between Next I and Msgbox for a real report

Going to your question, I didn't understand whether you would like to store the elapsed time for each of the 100 loops of your macro (so that you'll have the overall elapsed time with the msgbox and a detailed timing for each loop) or you would like to start several times your Sub tempo and store each overall elapsed time in a worksheet

 

[Jon Peltier]

Put the timer in the loop, and store the value while still in the loop.

Sub tempo()
  Application.ScreenUpdating = False

  Dim wb As Workbook
  Set wb = ThisWorkbook
  Dim ws As Worksheet
  Set ws = wb.Worksheets("Resultados")

  Dim i As Long
  For i = 1 To 100
    Dim StartTime As Double
    StartTime = Timer

    Call integrar
    Call partilhar
    Call regularizar

    Dim SecondsElapsed As Double
    SecondElapsed = Round(Timer - StartTime, 2)

    ws.Cells(i, 1).Value2 = SecondsElapsed
  Next i
 
  Application.ScreenUpdating = True
End Sub

 

[jalrs]

I guess that now your messages always states that it tooks 0.00 seconds to complete the loop, because you calculate SecondElapsed BEFORE starting the loop; move the instruction SecondElapsed = Round(Timer - StartTime, 2) in between Next I and Msgbox for a real report

Going to your question, I didn't understand whether you would like to store the elapsed time for each of the 100 loops of your macro (so that you'll have the overall elapsed time with the msgbox and a detailed timing for each loop) or you would like to start several times your Sub tempo and store each overall elapsed time in a worksheet

Hey Anthony,

You were absolutely correct regarding the secondelapsed instruction.
Regarding my question, the sub tempo answer, if possible.

Thanks for your time!

 

[jalrs]

Put the timer in the loop, and store the value while still in the loop.

Sub tempo()
  Application.ScreenUpdating = False

  Dim wb As Workbook
  Set wb = ThisWorkbook
  Dim ws As Worksheet
  Set ws = wb.Worksheets("Resultados")

  Dim i As Long
  For i = 1 To 100
    Dim StartTime As Double
    StartTime = Timer

    Call integrar
    Call partilhar
    Call regularizar

    Dim SecondsElapsed As Double
    SecondElapsed = Round(Timer - StartTime, 2)

    ws.Cells(i, 1).Value2 = SecondsElapsed
  Next i
 
  Application.ScreenUpdating = True
End Sub

Hey Jon,

Thanks for your answer.

I tried your method and I got returned 0s for all cycles.

Thanks.

 

[Anthony47]

You should use:

    SecondsElapsed = Round(Timer - StartTime, 2)

Do you see the difference?

 

[Jon Peltier]

Hey Jon,

Thanks for your answer.

I tried your method and I got returned 0s for all cycles.

Thanks.

I hadn't noticed the typo that Anthony pointed out. I have fixed it now in the code I posted earlier.

You actually declared SecondsElapsed, but then assigned the elapsed time to an undeclared variable named SecondElapsed.

This is why you MUST use Option Explicit. Open every code module and insert Option Explicit at the top. Then in the VB Editor, go to Tools > Options, and check Require Variable Declaration (and uncheck Auto Syntax Check), so Option Explicit will be inserted automatically at the top of every new code module.

 

[jalrs]

You should use:

    SecondsElapsed = Round(Timer - StartTime, 2)

Do you see the difference?

Hey Anthony,

Yes I see the difference. Second vs SecondS.

Thank you for pointing it out and for your time here Anthony.

I hadn't noticed the typo that Anthony pointed out. I have fixed it now in the code I posted earlier.

You actually declared SecondsElapsed, but then assigned the elapsed time to an undeclared variable named SecondElapsed.

This is why you MUST use Option Explicit. Open every code module and insert Option Explicit at the top. Then in the VB Editor, go to Tools > Options, and check Require Variable Declaration (and uncheck Auto Syntax Check), so Option Explicit will be inserted automatically at the top of every new code module.

Hey Jon,

I'm aware of Option Explicit, I don' know why I didn't activate it though. Would have helped for sure. Thanks for telling me how to automatically set it up. Just did it.

Thanks for the help and post marked as solution Jon!