Pausing VBA and re-running it causes faster execution

Question

We have a macro for some analysis that for the reason that we want to be able to look at the process, for a reason that will be 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 method. 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 so slow but when I pause the macro(Esc), hit debugging, step through (F8) 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 slowing down the process. I am sorry to post this but need to let you know that I know how to use explicit option, set ranges, set values instead of copying, etc. I have already changed my code to avoid selecting and see if that resolve the issue but was not the case. 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 let me know why this, by this I mean this specific issue that will be resolved after pausing and running again, is happening and why it 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 issue. 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 also wonder if Application.Cursor = xlWait would significantly benefit me in addition to other Application properties.

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

Have you tried turning off screen updating, using cursor = xlWait, etc? — dwirony, Jun 07 '17 at 21:51
@dwirony Thanks for the comment. `ScreenUpdating` and `Calculation` are set to false and manual. `Cursor` no, but that would improve the run time significantly? Apart from that, what is the reason that causing this, which would be resolved by those suggestion? — M--, Jun 07 '17 at 22:10
@dwirony Basically I tried [typical optimization techniques](https://stackoverflow.com/documentation/excel-vba/9798/excel-vba-optimization/30179/disabling-worksheet-updating#t=201706072212105588749). — M--, Jun 07 '17 at 22:12
Your code would be significantly improved though the use of a few workbook/sheet variables and completely avoiding select/activate unless necessary (and it typically isn't necessary) — Tim Williams, Jun 07 '17 at 22:23
@masoud it's not always the case but sometimes the cursor flickering can slow down processes significantly (especially if you're using select a lot). Add Application.Cursor = xlWait to the top and switch it back to xlDefault at the end. Can't hurt — dwirony, Jun 07 '17 at 22:39
@TimWilliams Thank you Tim. I knew I would get this advice so I included in my question that I know it's not typically necessary to use select/activate but I need to have that and that's not an option. Not offended tho. thanks for taking a look. — M--, Jun 07 '17 at 23:05
Do you set any Application properties like `Application.ScreenUpdating` before calling the Sub? What usually slows down Excel VBA code are the many calls between VBA and Excel. — Slai, Jun 07 '17 at 23:36
@Slai Thanks for the comment. This is part of bigger module and yes I set them to false. — M--, Jun 07 '17 at 23:38
@Slai Look at the latest update and the link included to documentation to know what Application properties I set. — M--, Jun 07 '17 at 23:41
I would recommend profiling the code to find what parts are slowest and how their time changes after pausing in the code execution https://stackoverflow.com/documentation/excel-vba/861/debugging-and-troubleshooting/6187/use-timer-to-find-bottlenecks-in-performance. You can also try checking the application properties that are set after stopping in the debugger, because I think the pause might be changing some of them. — Slai, Jun 07 '17 at 23:48
@Masoud - Really, avoiding select, paste etc will yield faster results, I don't see how there could be any arguement against that. In any event I'd suggest putting Debug.Print Now() in various places throughout the code so you can see which sections are taking up the time — Absinthe, Jun 08 '17 at 00:03
@Absinthe Thanks for bringing up the argument once again. I know that, I described in the **Important Note** that it would not resolve the issue and Slai already suggested the second point. thanks tho. — M--, Jun 08 '17 at 00:06
Have you tried **starting** the macro from the immediate window? (I am just wondering whether the worksheet / workbook Select and Activate will be being processed differently when the VBE is active, e.g. when you press F5 or run it from the Immediate Window, than when Excel itself is active.) — YowE3K, Jun 08 '17 at 03:12
@YowE3K thanks for your suggestion. It helped me a lot. If you can explain about differences between running from sheet and vb itself I'd appreciate. — M--, Jun 09 '17 at 13:51
@Slai Thanks for the suggestion. It helped to dig into the problem. — M--, Jun 09 '17 at 13:51
can you share what the problem is? I am a bit curious, and might be something helpful to know. — Slai, Jun 09 '17 at 15:52
By chance, is any of your code in a userform? I had a similar issue in MS Word and found that calling standard module code from a userform was very slow. See here for details: https://stackoverflow.com/questions/45326012/why-does-vba-code-run-faster-when-called-from-a-standard-module-instead-of-a-us — SlowLearner, Jul 26 '17 at 12:02
@SlowLearner I have buttons (form control) that may cause the same behavior. — M--, Jul 26 '17 at 13:18
It would be interesting to know if your issue can also be solved the same way as mine... was your original code in a userform / classModule or just a standard module? — SlowLearner, Jul 26 '17 at 13:22
@SlowLearner All of them are in the standard module. It may jump from one workbook to another one, but still in the modules. — M--, Jul 26 '17 at 13:24

M-- · Accepted Answer · 2019-11-12T18:27:31.070

1

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.

What I know about immediate window is its difference 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 add to this and explain in detail that in what way activated VBE is different from running the macro from a command button.

For a 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).

edited Nov 12 '19 at 18:27

answered Jun 09 '17 at 13:49

M--

20,766
7
52
87

1

Interesting. What would make more sense to me is if the difference doesn't depend on the VBA Editor, but on if the Excel Application/Workbook/Sheet is activated or visible. For example `Application.WindowState = xlMinimized` or the more risky `Application.Visible = False`. xlsb usually makes load time a bit faster, so that seems a bit puzzling too. – Slai Jun 09 '17 at 16:18
@Slai Indeed it is bizarre and is only happening on my machine (xlsb). I am also getting `Can't find project or library` once in a while when opening my workbook. About the `WindowState` option I think you're right. I will try that one too to see if that's the case but worth telling that I can see the workbook on the second screen so maybe `Visible` property would be a better shot. I need to keep digging into this. Thanks. – M-- Jun 09 '17 at 17:15
2

My **guess** is that, when running in the VBE environment, all your `Activate` and `Select` statements are not doing what they would do in an Excel environment - i.e. all they do is change the pointers to which sheet/cell is active, but they don't hand control of responding to any user-initiated events over to that sheet/cell. So I suspect the short answer is (and you aren't going to like it) ... avoid the use of `Select` and `Activate`. – YowE3K Jun 09 '17 at 19:52
@YowE3K haha. I know I should not. I wish I had any control over it. But when your senior engineer insists on keeping the macro as is cause it's been working for a while then you have to find a way around. Thanks for your help and comments. – M-- Jun 09 '17 at 20:00

score 1 · Answer 2 · edited Apr 07 '21 at 12:01

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).

Code Sub length - 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.

Pausing VBA and re-running it causes faster execution

Problem

Important Note

2 Answers2

Linked