Improving Workbook Macro "On Open" Speed

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
229
Office Version
  1. 365
Platform
  1. Windows
So as I have been working on an excel "database" that's used to log and keep track of customer complaints after I had added a sheet to it that has 440 formulas I noticed the spreadsheet takes about 30seconds to a minute to open.

I feel like the formulas are what's bogging it down because as the macro is running and adding new data from a separate master file its recalculating

Does Application.Calculation := xlmanual apply to the WHOLE workbook or only to active sheets? I experimented with adding this into my macro that I have set up for when workbook is opened but I saw no discernable effect.

I activated the COMs Addin Inquire to run workbook analysis but it wasn't what I was expecting it to be so I am not sure how to decipher the information it has

I already have
Application.Screenupdating=false
Application.Calculation:=xlmanual

Are there any other suggestions or things I should look for that I should review to try and improve efficiency of this opening macro?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Application.Calculation := xlmanual applies to the whole application, not just specific workbooks or sheets. Show your code, as that may give a clue as to what is causing the slowness.
 
Upvote 0
VBA Code:
Private Sub Workbook_Open()
  Dim srcSH As Worksheet, desSH As Worksheet
  Dim i As Long, j As Long, nRow As Long, n As Long
  Dim rng As Range, col As Range, c As Range, f As Range
 
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Sheets("Complaints").Unprotect Password:="Secret"
  Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")
  Set srcSH = Workbooks("ToyotaData.xlsx").Sheets("Data")
  Set desSH = Workbooks("Customer Complaint Tracker.xlsm").Sheets("Complaints")
  Set rng = srcSH.Range("A:B,D:E,H:K,O:O,Q:AB,AH:AH")
 
  For Each c In srcSH.Range("A2", srcSH.Range("A" & Rows.Count).End(3))
    Set f = desSH.Range("A:A").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then nRow = f.Row Else nRow = desSH.Range("A" & Rows.Count).End(3).Row + 1
    j = 0
    For Each col In rng.Columns
      n = col.Column
      j = j + 1
      desSH.Cells(nRow, j).Value = srcSH.Cells(c.Row, n).Value
    Next
  Next
  srcSH.Parent.Close False
  Sheets("Complaints").Protect Password:="Secret"
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

THis is the code i have set up for opening. its designed to look at a seperate workbook that we download weekly to update new information into a worksheet. this sheet doesnt grow that much in aweek
 
Upvote 0
OK so just one extra bit of information i ran that section of the code by itself and it took the same amount of time.
 
Upvote 0
You include a line that opens another workbook:

VBA Code:
Workbooks.Open ("O:\1_All Customers\Current Complaints\ToyotaData.xlsx")

... so, what if that is what is taking the time?
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top