Reduce excel xlsm file load time

[Dhamo]

Hi,


I have an XLSM file(6 user forms & 3 modules, file size: < 250 KB). Whenever I open it is taking 30 seconds to load than other excel files.


Is there any way to reduce the load time?


PS: When workbook open, I am calling 'Userfrm.show()' alone.

 

[NARAYANK991]

Hi Dhamo ,


Can you first disable the Workbook_Open macro , and see how much time it takes to open the file ? This will be the benchmark against which the other times will have to be evaluated , since the additional times can be attributed to the macros.


Next , before the user form is loaded , which macros are executed ? Can you enable / disable these one by one to decide which is the one which is causing the delay ?


Narayan

 

[Hui]

Two areas I'd check


1. If you have any connections, SQL etc links or formula links to other spreadsheets on a network drive, that is more likely the problem.


2. Do you have areas of Sumproduct or Volatile formulas filling large areas of tables

 

[vasim]

Just sharing my experience, I was dumb enough to format cell all the way till 1048576 rows (basically the whole column), making me excel slow

 

[Dhamo]

Narayan,


I did try removing 'Workbook_Open - code'. But still the file is taking same time to load.


Hui,

I have code for sql-db, but I am not calling the sql code when the user form is initialized. Though, when clicking on 'submit' button, the sql code will run.

 

[Dhamo]

Indian, You're right if we have 1048576 non blank rows, in that case the file size cannot be lesser than 250 KB. And whenever I close the file I am clearing all the values.

 

[NARAYANK991]

Hi Dhamo ,


If you find that the workbook , without executing any macro , is taking a long time to load , it means either like Indian has posted , your UsedRange is unnecessarily large , or there are very time-intensive calculations taking place when your file loads , or you may be loading unwanted add-ins.


To check the former , press CTRL END , and see where your cursor is placed ; check if really these many columns and rows are used. If not , you need to reset the UsedRange ; there are several ways of doing this.


Narayan

 

[Dhamo]

Narayan,


Normal excel file(xlsx)is not taking that much time, I have installed only one addin.


To be detail, My xlsm file has 5 blank sheets and 1 data sheet (used range: A1:F15). No formats, Also did hide.

 

[NARAYANK991]

Hi Dhamo ,


Can you upload your file ? Since it is only a matter of checking the loading ( opening ) time , you can protect your VBA code , and upload the workbook. Check what are the references included in your VBE ; just the following two should be sufficient for normal applications ; any others are dependent on whether your code needs them.


1. Visual Basic for Applications

2. Microsoft Excel Object Library


Narayan

 

[Dhamo]

Thanks Narayan.


I am not supposed to upload or send the file through my mail since I am in office. Otherwise I would have done this 3 hours before. Even the upload url will not open here.


I am not using any ref other than the two you mentioned above.

 

[NARAYANK991]

Hi Dhamo ,


In the absence of the file itself , only one more point occurs to me ; save the file as a .xlsb , and see whether it improves matters.


Narayan

 

[sgmpatnaik]

@Dhamo


Hi


please add the below code in the workbook

[pre]

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_Close()
Application.Calculation = xlCalculationAutomatic
End Sub

[/pre]

Hope it will solve some of the problem to load the file


Thanks


SP

 

[shrivallabha]

I tested small workbook_open procedure with and without userform.


1. If there is no code calling for userform then it gets loaded within 1 second. I am at home so the file was on local drive. Where is your file located? If it is in network folder then it will add to time. Hui has asked you this but you've not clarified the location of file.


2. After adding Userform.Show to workbook_open procedure it added 3 seconds to the loading time so there is some interference for sure. My userform contains one Textbox and a Label. What does the Userform's initialize procedure look like?