Pausing VBA and re-running it causes faster execution

Question

We are using a macro for some analysis that that we want to be able to look at the process, and for a reason that is out of scope here, we are forced to use Activate and Select in the macro. Both my colleagues and I are aware of downsides of using such methods. Meanwhile, it has been tested that explicit coding and selecting and activating is not the main reason for this issue.

In one of the sub-modules, that I am posting the (pseudo-)code of it below, we basically get the data from a sheet and copying it over to another one.

Problem

The problem is that this process is really slow, but when I pause the macro(Esc), hit debugging, step through (F8) for one or two steps of for-loop and run again (F5) it runs much faster.

This does not happen around specific steps of my for loop or for a specific sheet so has nothing to do with my data and how it is structured.

Question: What are the possible reasons for this? Does pausing/step running cause something like memory to clear or any other possible scenario that makes this to run faster? And how I can fix this (Make it run as fast without the need to pause and so on.)?

Important Note

As stated above, using Select and Activate is not the main reason that for slowing down the process. I am sorry to say this again, but I know how to use explicit option, set ranges, set values instead of copying, etc. I have already changed my code to avoid selecting to see if that resolves the issue to no avail. It was still running slow until pausing, stepping through and running again. I would appreciate if you take a closer look at the problem and describe the reason behind the issue. Or at least, specifically let me know why this issue has something to do with Select/Activate.

This is part of a bigger main module that runs a program as a whole but this is the part that causes the slow down. I have used some optimizing techniques in the main module.

Sub Copy_ModelInputs(RootDir, FileName, TranID, ModOutDir, Angle, x, y, Method, TypeN)
'For each 150 storms, step through model event tabs and copy into runup tabs
FileName = RootDir & "NWM\" & FileName
FileName_output = ModOutDir & TranID & "_Outputs.xlsm"
Workbooks.Open (FileName)
FileName = ActiveWorkbook.Name
Workbooks.Open (FileName_output)
Filename2 = ActiveWorkbook.Name

'copy the angle into the doc sheet
Windows(FileName).Activate
Sheets("doc").Select
Range("c12").Select
ActiveCell.value = Angle

'File Transect ID
Range("c6").Select
ActiveCell.value = TranID
ActiveCell.Offset(1, 0).Select
ActiveCell.value = FileName_output
Range("I4").Select
ActiveCell.value = Now
Range("d8").Select
ActiveCell.value = x
ActiveCell.Offset(0, 2).Select
ActiveCell.value = y


'copy model output to input into excel spreadsheets

For i = 1 To 150
    'input SWELs
    Windows(Filename2).Activate
    Sheets("Event" & i).Select
    Range("B2:B300").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    

    Windows(FileName).Activate
    Sheets("Event" & i).Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                    
    'input H
    Windows(Filename2).Activate
    Range("C2:C300").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    
    'Open runup template spreadsheet, copy H0
    Windows(FileName).Activate
    Range("D7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

                
        'input T
        Windows(Filename2).Activate
        Range("D2:D300").Select
        'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    
    'Open template
    Windows(FileName).Activate
    Range("G7").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    If TypeN = 1 Or TypeN = 3 Then
    
        'input deep
        Windows(Filename2).Activate
        Range("E2:E300").Select
        'Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
                        
        'Open template
        Windows(FileName).Activate
        Range("H7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
    
    'input local
    Windows(Filename2).Activate
    'If Method = 2 Then
    If TypeN = 2 Then
        Range("G2:G300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("I7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input model
        Windows(Filename2).Activate
        Range("F2:F300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("H7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input length
        Windows(Filename2).Activate
        Range("J2:J300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("J7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input data
        Windows(Filename2).Activate
        Range("I2:I300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("K7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If

    
    'input sheet
    Windows(Filename2).Activate
    If TypeN = 3 Then
        Range("H2:H300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("S7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If

    Windows(Filename2).Activate

    Application.StatusBar = "Model Output copied Event " & i
Next i

ActiveWorkbook.Save
ActiveWindow.Close
ActiveWorkbook.Save
ActiveWindow.Close

Sheets("Summary").Select
End Sub

P.S. I wonder if Application.Cursor = xlWait would significantly improve performance in addition to other Application properties.

N.B. Please do not bring up Select, Activate and Copy Paste argument. It has been already covered numerous times in the lines above and comments :)

Answer 1

I had a similar issue with Microsoft 365 (64-bit Windows 10 Pro, i5 9th gen. processor, 16GB RAM. Excel file size 4MB, heavy use of VBA. 64-bit Excel).

My code is around 770 lines and it called several functions and branched to several subs.

The trouble being the software worked/works fine when using Office 2010 (32-bit Excel on the same machine - and for that matter, on my previous machine of considerably lower specs).

On the MS365 machine. At Runtime, code would stall for between 30 to 300 or more seconds at random places in the code but never if in debug mode.

I tried all the suggested tips like inserting DoEvents and adding Waits of one or two seconds at various locations in the code. Even uninstalled MS365 and reinstalled with 32-bit.

To cut a long story short, I broke the code into smaller parts and called them from within the original code block as in the sample below ...

Sub xyz()
.....
.....
Call SetDoCalcPages
Call SetDoCalcPages1(r, ACellAddr, errStr, NowStr, errAddr)
Call SetDoCalcPages2(temWatch, fYoung, temYoung, doneonce, fK4, ACellAddr, goQT)
.....
.....
End Sub

Around 70 lines of code were shifted to the three subs mentioned above.

I cannot say why, but this resolved the issue. At runtime, the computation is done at great speed.

One thing is sure. The code activates sheets and cells and sets the colour and fonts - all of which are frowned upon by suggestions on many forums. I can confirm that these do not have any bearing on the symptom - which was random long stalls in the execution of macro code at run time.

Answer 2

After spending fair amount of time on the problem I want to report back on the issue;

As @Slai suggested I tried to find the bottleneck of the code by printing time between each process. It turned out that there is a lag between each step of the for loop that then disappears after Debug/Continue.

Also Application properties are not changing before and after Debug/Continue.

What @YowE3K proposed about running the macro from the Immediate Window actually resolved the issue. Somehow, it seems activated VBE is the solution.

I also tried saving my main workbook as * .xlsb which resolves the issue. However, it causes slower loading of the file at the beginning but in total overhead time-cost is not substantial.

I know immediate window is different in scope. It assumes global (Public) scope if nothing is running. Otherwise, it will be in the Application scope. I would appreciate if someone can explain in detail that in what way activated VBE is different from running the macro from a command button.

For reference, I want to also include in the answer that not disabling Application.ScreenUpdating can affect time of execution significantly. FWIW, select, activate and similar practices should be avoided if possible (programming-wise, they are always avoidable).