CODEWITHSUNDEEP
performanceexcelvba
user1135218
user1135218

Reputation: 403

Excel very slow after second time being opened

I have an excel workbook with LOTS of macros.

If I have Excel App. FULLY closed, and I open it, it runs fine.

However if I close the workbook (without closing excel app.), and then I open it again, there is a huge delay when opening, and also while running all the different macros in it.

I have nothing running at the beginning or on the background. In fact, I have put a flag on the thisworkbook Workbook_Open routine.... Just to get to this point (workbook_open event) it takes over 20 seconds! And no macros have run yet !

So to summarize, if opened from scratch (excel app is NOT running), it takes 2-3 seconds for workbook to be opened and ready. Everything runs fast (macros, etc)...

Then I close the workbook... If opened again (while Excel already running) takes 15-50 seconds. Basically anything while working (macros running, etc) takes x 10 longer

If I close Excel app. totally and open it again, it runs nice again (the first time)

I am thinking maybe the workbook has something which affects the memory and the second time it is opened it is affecting it... But I have no idea, it is just a guess...

Has anyone experienced this ?

Upvotes: 3

Views: 2377

Answers (1)

user1135218
user1135218

Reputation: 403

Thanks Jook...you send me on the right track and I fixed the problem...

I did a Google search based on "memory leaks" as you mentioned and found some links which advises about the importance of set xxx=nothing

I didn't think it was much of a problem and never thought it could cause such delays (in fact I have lots of other macros/workbooks and never had this problem)

Incredibly that was it, a missing set xxxx=nothing

I went though all the code which run on workbook_open event. I made sure set xxx=nothing was set on all (I found two missing)

Although it didn't affect the program from running correctly (first time opened), when closing it and reopening it again, that's when it caused a problem ! Weird.

But it is now fixed thanks to you !

I hope this post it helps someone else in future

Upvotes: 3

Related Questions