Prevent VBA While loop from getting progressively slower

Question

The loop copies formatted sections of code and inserts them x times. Doing it less than 10 times is fast, but it quickly starts to slow down to the point where, at about 60 or so it takes about a second for each insert, and continues to slow down from there. What could be causing it to get progressively slower?

While x < num
    Debug.Print ("Running: " & x)
    With Sheets(inoc)
    .Activate
    .Rows("12:15").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    End With
    With Sheets(count)
    .Activate
    .Rows("17:28").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
    .Range("C29").Value = "='Inoculation and Incubation'!B17"
    End With
    x = x + 1
    Application.CutCopyMode = False
    
Wend

[Avoid using `.Activate` and `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Scott Craner, Jun 13 '23 at 16:02
Btw, `While`...`Wend` is generally considered to be deprecated. — BigBen, Jun 13 '23 at 16:03

Scott Craner · Accepted Answer · 2023-06-13T17:33:17.377

2

From the comments:

Avoid using .Activate and .Select.

While...Wend is generally considered to be deprecated.

Dim i As Long
For i = x to num
    Debug.Print "Running: " & i
    With Sheets(inoc).Rows("12:15")
        .Copy
        .Insert Shift:=xlDown
    End With

    With Sheets(count).Rows("17:28").
        .Copy
        .Insert Shift:=xlDown    
    End With

    Sheets(count).Range("C29").Value = "='Inoculation and Incubation'!B17"

    Application.CutCopyMode = False
    
Next i

As to why it is getting slower:

Anytime one pierces the wall between vba and the worksheets it will slow just a little. That slowness does start to compound inside a loop. With what you are trying to do(copy/insert) there is not much more we can do to make it quicker, or overcome that compounding slowness. It probably has to do with the fact that excel must redraw the xml each loop and there is more and more data to deal with in storing that information in memory then redrawing the xml.

edited Jun 13 '23 at 17:33

answered Jun 13 '23 at 16:15

Scott Craner

148,073
10
49
81

2

Using the obligatory settings (ScreenUpdate, EnableEvents, Calculation) could help also in this case. See for example https://stackoverflow.com/a/47092175/7599798 – FunThomas Jun 13 '23 at 16:49
1

Thank you, this certainly makes it run faster, however it does not solve the problem of progressive slowing over time. Each iteration takes longer than the last. – Connor Baush Jun 13 '23 at 17:01
@BigBen I already have screenupdate and events disabled earlier in the code. I tried manual calculation but that actually caused it to crash. – Connor Baush Jun 13 '23 at 17:15
2

@ConnorBaush Anytime one pierces the wall between vba and the worksheets it will slow just a little. That slowness does start to compound inside a loop. With what you are trying to do(copy/insert) there is not much more we can do to make it quicker, or overcome that compounding slowness. It probably has to do with the fact that excel must redraw the xml each loop and there is more and more data to deal with in storing that information in memory then redrawing the xml. – Scott Craner Jun 13 '23 at 17:20
3

@ScottCraner I think that just about covers it then. I would say this is important for a complete answer to my question if you want to add it to the actual answer – Connor Baush Jun 13 '23 at 17:31

Prevent VBA While loop from getting progressively slower

1 Answers1