VBA For Next Loop runs slower after first time through

[bergie7isu]

This one is throwing me for a loop (apologies for the pun...please still help me!).

I'm confused about this one.

Here's what the macro does, high level.

• Opens a template workbook.
• Saves a copy of the template workbook as a temp file.
• Loops through a list of other "original" workbooks:
  • One at a time, it takes data from the original workbook and transfers it to the temp file.
  • Closes the original workbook and renames it.
  • Saves/Closes the newly populated temp file and renames it (using the name of the original).
  • Repeats until all the way through the list.
• Closes the template workbook.

To be clear, the macro works...does just what I want it to. However, the first time through the loop takes about 10 seconds. All subsequent loops take about 90 seconds.

Now, if I put a msgbox at the end of each loop, each loop only takes 10 seconds.

Hmm...

Any ideas?

A simplified version of my code is below.

Sub throwingmeforaloop()


'open the template file
Workbooks.Open "G:\" & templatefilename


'update authorized docs
For Each cell In ThisWorkbook.Sheets("Flash").Range("authorizeddocs")


    'start the timer
    StartTime = Timer
    
    'open the original team file
    teamfilename = cell.Value & ".xlsm"
    Workbooks.Open "G:\" & teamfilename
                
    'save a copy of the template file as a temporary team file
    tempfile = cell.Value & "temp.xlsm"
    Workbooks(templatefilename).SaveCopyAs ("G:\" & tempfile)
    Workbooks.Open "G:\" & tempfile
                
                
                
    'ALL SORTS OF DATA TRANSFER HAPPENING HERE
    'taking data from Workbooks(teamfilename) and transfering it to Workbooks(tempfile)
            
            
            
    'close the original team file
    Workbooks(teamfilename).Close (False)
    
    'rename and move the original team file
    Name "G:\" & teamfilename As "G:\" & cell.Value & "backup.xlsm"
                
    'save and close the tempfile
    Workbooks(tempfile).Close (True)
                
    'rename and move the new team file
    Name "G:\" & tempfile As "G:\" & teamfilename
                
    'stop the timer
    SecondsElapsed = Round(Timer - StartTime, 2)
                
Next cell
    
'close the template file
Workbooks(templatefilename).Close (False)
    
'tell the user that everything finished
ThisWorkbook.Activate
response = MsgBox("See Flash Status for results.", vbInformation, "Flash Complete!")


Workbooks(templatefilename).Close (False)


End Sub

 

[bergie7isu]

It was the pun, wasn't it? I apologize. If I could go back and change it, I would.

Still stumped by this one...

Anybody got an idea why a loop would run quickly the first time though, but then slow down? And then doesn't slow down at all if I put a msgbox between each loop?

 

[DanteAmor]

Add teh following lines to make it faster.

Sub throwingmeforaloop()
[COLOR=#ff0000]    Application.ScreenUpdating = False[/COLOR]
    'open the template file
    Workbooks.Open "G:\" & templatefilename
    'update authorized docs
    For Each cell In ThisWorkbook.Sheets("Flash").Range("authorizeddocs")
        'start the timer
        StartTime = Timer
        'open the original team file
        teamfilename = cell.Value & ".xlsm"
        Workbooks.Open "G:\" & teamfilename
        'save a copy of the template file as a temporary team file
        tempfile = cell.Value & "temp.xlsm"
        Workbooks(templatefilename).SaveCopyAs ("G:\" & tempfile)
        Workbooks.Open "G:\" & tempfile
        'ALL SORTS OF DATA TRANSFER HAPPENING HERE
        'taking data from Workbooks(teamfilename) and transfering it to Workbooks(tempfile)
        'close the original team file
        Workbooks(teamfilename).Close (False)
        'rename and move the original team file
        Name "G:\" & teamfilename As "G:\" & cell.Value & "backup.xlsm"
[COLOR=#ff0000]        DoEvents[/COLOR]
        'save and close the tempfile
        Workbooks(tempfile).Close (True)
        'rename and move the new team file
        Name "G:\" & tempfile As "G:\" & teamfilename
[COLOR=#ff0000]        DoEvents[/COLOR]
        'stop the timer
        SecondsElapsed = Round(Timer - StartTime, 2)
    Next cell
    'close the template file
    Workbooks(templatefilename).Close (False)
    'tell the user that everything finished
    ThisWorkbook.Activate
    response = MsgBox("See Flash Status for results.", vbInformation, "Flash Complete!")
    Workbooks(templatefilename).Close (False)
End Sub

 

[bergie7isu]

Yes! That did it. Thanks, Dante. I always thought DoEvents slowed codes down, but I suppose I've got a special case here.

For my own education, does displaying a msgbox act like DoEvents? Still not entirely clear on why putting a msgbox in my loop made it faster (before adding the DoEvents).

Thanks again!

 

[DanteAmor]

Yes! That did it. Thanks, Dante. I always thought DoEvents slowed codes down, but I suppose I've got a special case here.

For my own education, does displaying a msgbox act like DoEvents? Still not entirely clear on why putting a msgbox in my loop made it faster (before adding the DoEvents).

Thanks again!

in fact, doevents does slow down the process, because for a moment it passes the control to the operating system, so that it finishes executing the task.
Well, doevents and msgbox are not the same, but in this particular case, msgbox was somehow sending control to the operating system to open a new window, it was because it looked faster.
Doevents is also used in the cycles so that the control returns to the operating system and at some point you can interrupt the process.