Slow Workbook_Open event

Question

I have a workbook that takes more than 6 seconds to open due to a number of macros that run within the workbook_open event.

I want to speed this up so I have used a timer to test different parts of the code at startup vs being run while the workbook is open. All of the parts take the same time to run in both situations, except this part:

Dim ATime As Double
Dim BTime As Double

ATime = timer

Dim b As Long
For b = 5 To 268
    If Sheets("Orders").Range("F" & b) = "Locked" Then
        Sheets("Orders").Range("C" & b).Locked = True
        Sheets("Orders").Range("D" & b).Locked = True
        Sheets("Orders").Range("E" & b).Locked = True
    End If
Next

BTime = timer
MsgBox "1. " & Format(BTime - ATime, "0.00 \s\ec")

When run at workbook_open: 2.78 seconds. When run manually within workbook: 0.01 seconds.

What is the problem here?

try using `With Sheets("Orders")` and change the `Range()` to `Cells()`... this will save up a lot of time... also changing locked by default? better do check like `If Not .Cells(b, 3).Locked Then .Cells(b, 3).Locked = True`... all of this may speed up the process itself... however, unfortunately i dont know why there is such a big gap in time :/ — Dirk Reichel, Nov 16 '15 at 04:11
Can you change the order that the macros run in? If you do, does this change the time that this macro takes to run? — , Nov 16 '15 at 04:14

score 0 · Answer 1 · answered Nov 16 '15 at 06:16

0

Try:

With Sheets("Orders")
    For b = 5 To 268
        .Range("C" & b).Resize(1, 3).Locked = (.Range("F" & b) = "Locked") 
    Next
End With

answered Nov 16 '15 at 06:16

Tim Williams

154,628
8
97
125

This isn't quite the same as the original - it could **unlock** some cells, which the original never would. I can't say if that's important though! – Rory Nov 16 '15 at 08:33
To be fair, that may well be an irrelevance! – Rory Nov 16 '15 at 15:53

Slow Workbook_Open event

1 Answers1