Need code to run faster

[User Name Active]

I have a very long code that does exactly what I need it to do, except it takes 25 seconds to run. I have narrowed it down to the following two sections of code that are slowing it down. Can anyone help me on how to make these run faster?

First Section:

lastrow = Sheets("Crew Log").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Sheets("Crew Log").Range("L2:L" & lastrow)
        If rng <> "" Then
            Sheets("Storage").Range("A2:K2").Copy Sheets("Crew Log").Cells(rng.Row, 1)
            Sheets("Storage").Range("BM2:DQ2").Copy Sheets("Crew Log").Range("BM:DQ").Cells(rng.Row, 1)
         
        End If
    Next rng

Second Section:

Sheets("Summary").Unprotect "password"
 With Sheets("Summary")
    .Rows(42).Copy .Rows(43).Resize(Sheets("Restructure").Range("G1"))
End With
 Application.CutCopyMode = False

 

[frabulator]

Do you have:

Application.ScreenUpdating = Flase
'your code
Application.ScreenUpdating = True

... before and after your code that is executing? Doing so should speed up your code if you have not done so.

Another thing to keep in mind is that Dim'ing things inside of a loop will slow down your looping. It is always a good idea to Dim things outside of a loop.

 

[Dr. Demento]

The nicest set of optimizing code I've found is here: Guide to Improving VBA Performance. Faster Excel VBA - The Analyst Cave | Excel, VBA, programming and more (see OptimizeVBA sub)

 

[frabulator]

The nicest set of optimizing code I've found is here: Guide to Improving VBA Performance. Faster Excel VBA - The Analyst Cave | Excel, VBA, programming and more (see OptimizeVBA sub)

Thank you! I am keeping that link in my vault

 

[Rick Rothstein]

Another thing to keep in mind is that Dim'ing things inside of a loop will slow down your looping. It is always a good idea to Dim things outside of a loop.

I do not think that is true. I am pretty sure that Dim statements are read only one time, at the beginning of the compile process, in order to create the symbol tables (memory location assignments) for the variables... after that they would not be needed any more, so I do not believe any machine code is created for them afterwards.

 

[Rick Rothstein]

The nicest set of optimizing code I've found is here: Guide to Improving VBA Performance. Faster Excel VBA - The Analyst Cave | Excel, VBA, programming and more (see OptimizeVBA sub)

I think that link has its statement backwards for the section titled "Use With…End Clause"... it says "With..End With" is better in the description (which I believe it is), but then says the opposite in the example. I would comment that back to the website, but it requires a login that I am not wiling to commit to.

 

[Dr. Demento]

@Rick,

Copy. I'll pass it along.

 

[frabulator]

I do not think that is true. I am pretty sure that Dim statements are read only one time, at the beginning of the compile process, in order to create the symbol tables (memory location assignments) for the variables... after that they would not be needed any more, so I do not believe any machine code is created for them afterwards.

Please explain me this. I understand that I was wrong in my assumption that Dimming something inside a loop was slower, however in all my test it seems as though dimming something inside a loop is actually faster than dimming it outside of a loop. Why?

I am using the code below to test which is faster, dimming inside or outside a loop. The code is contained inside of a userform with 1 button and 3 lables (label2 [width of 200 with a boarder], label3 [same size and location as label2 except this label has a different color background - essentially i was making a progress bar], label4 [show time laps after completion]). In every test, regardless if I run 'dim inside' or 'dim outside' first the loop that has the Dim's inside always runs faster than the one that has the dims on the outside of the loop.

This is quite perplexing to me as I have always assumed that even though (like you said) they are only created once, the program still reads it and then moves on because it has already been done once before. Why then does it take less time to read something again as apposed to not reading it at all? Given, the difference is minuscule (one - two seconds at most), but still, it doesn't make any sense to me

Private Sub CommandButton1_Click()
Label4.Caption = ""
    looperOUT
    Me.Repaint
    looperIN
End Sub








Sub looperIN()
    
    Dim dt As Date
    dt = Now
    
    For i = 1 To 80000
        Dim pie As String
        Dim loopn As Long
        Dim size As Integer
        
        pie = "WITH DIMS IN"
        loopn = i
        size = Math.Round(i / 400, 0)
        
        Label3.Caption = pie
        Label3.Width = size
        Me.Repaint
        
        
    Next


    Dim dt2 As Date
    dt2 = Now
    
    Dim dtDuration As Long
    dtDuration = DateDiff("s", CDate(dt), CDate(dt2))
    Label4.Caption = Label4.Caption & "| Dims In: " & dtDuration
    
End Sub




Sub looperOUT()
    
    Dim dt As Date
    dt = Now
    Dim pie As String
    Dim loopn As Long
    Dim size As Integer
        
        
    For i = 1 To 80000
        pie = "WITH DIMS OUT"
        loopn = i
        size = Math.Round(i / 400, 0)
        
        Label3.Caption = pie
        Label3.Width = size
        Me.Repaint
        
    Next


    Dim dt2 As Date
    dt2 = Now
    
    Dim dtDuration As Long
    dtDuration = DateDiff("s", CDate(dt), CDate(dt2))
    Label4.Caption = Label4.Caption & " | Dims Out: " & dtDuration
    
End Sub

 

[frabulator]

Another interesting find.

While executing the above code on loops less than 100,000, Dimming inside the loop is faster. However when you exceed 100,000 loops dimming outside becomes faster.

at 160,000 loops dimming inside a loop is was 8 seconds slower than dimming outside a loop.

Something else that has me perplexed.

 

[Rick Rothstein]

Another interesting find.

While executing the above code on loops less than 100,000, Dimming inside the loop is faster. However when you exceed 100,000 loops dimming outside becomes faster.

at 160,000 loops dimming inside a loop is was 8 seconds slower than dimming outside a loop.

Something else that has me perplexed.

I am not sure what to say... given my understanding of the process (which comes from the compiled version of Visual Basic and which I thought would apply to all VB's), that makes no sense to me. Perhaps, being "semi-compiled" and needing to "stay alive" in some way so that interactive debugging can take place, Dim's are read in place as opposed to at compile time... I don't know, but it appears it is outside my understanding as it relates to VBA.