Excel file taking a long time to open

[radubalmus]

Hello

I have an excel file in witch i use o lot of macros and since a while it takes about 30 sec to open(on slower pc takes even longer or if i put it on the network).

any ideas why??

how can i make it open faster?

Thanks

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Chance1234]

How long is a piece of string ?

a good place you might want to start is with the following articles

http://www.ozgrid.com/VBA/calc-stop.htm

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Chance,

F, G + Its official, its even on a organisation chart et all

 

[radubalmus]

Thanks for the sites

i am not using any macros when the file is opening.i use the macros after the i open de file.





There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Chance1234]

? can you explain

Chance,

F, G + Its official, its even on a organisation chart et all

 

[radubalmus]

i have an excel file witch i use as an interface to connect with a sql server. within the excel file i write and read in/from the sql server. so i have a lot of macros in the file.

but for some time now the excel takes long time to open, and i don't have any macros on workbook_open event.

Shouldn't the excel file open normaly??
and i mean by that not to wait a lot of seconds for the file to open(takes about 30 sec from the time i double click the shortcut till i get the Enable Macros Pop Up)

Thanks

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Chance1234]

from the sql server. so i have a lot of macros in the file.

Two questions

1. Is there any data in the actual spreadsheet before opening it ? if so is it formated ? if it is formated are unused cells being formated unecesarily ? are there a lot of formulas on teh sheet ?

2. You have macros in your spreadsheet, how big is your excel file in terms of size ? even though you dont have code in teh workbook open, this code has to be loaded on start.


As a starting point

Export all your modules as text files
create a new excel work book
Import all your modules

Copy over only what you need from your original spreadsheets,

check formating and make sure you aer only formating the ranges in use

Next start going through your macros, and reducing the code down



Chance,

F, G + Its official, its even on a organisation chart et all

 

[Spooku777]

One thing to check is the UsedRange

Even blank cells in your UsedRange can take up storage/memory. Sometimes if you've delete/cleared a swag of cells or rows then the used range can still be large and artificially inflate the size of your file.

Perform a Ctl+Shift+End to see where the Lastcell is. That'll select the "UsedRange" on a given sheet.


Try this in your VBA code to reset/clear the used range so that the blank cells are no longer part of the used range.

Sub SetUsedRange()
    Dim x As Variant
    x = ActiveSheet.UsedRange
End Sub

Note: Just assigning the UsedRange value clears it - you don't have to do anything with it.