Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a relatively small (~100K) Excel 2010 spreadsheet that (via VBA) when opened, closes all worksheets except the start page, checks the user name and cross checks it with a SQL table, bringing back the user's record to check their permissions.

A few cells are updated rangeUserName, rangeCurrentUser, rangeSystemAccess etc...

If I open this workbook when there are other large spreadsheets (that also have vba), it takes about 30 to 60 seconds to open, when if no other workbooks are open, it takes under 5 seconds.

Why is this?

share|improve this question

migrated from stackoverflow.com Aug 22 '12 at 13:01

This question came from our site for professional and enthusiast programmers.

    
Does this happen again if you (re-)save the file after you opened it ? I had a similar issue once and got it solved by reading: excel2007-slow-open-file.blogspot.nl –  HerbalMart Aug 22 '12 at 13:07
    
Yes it does happen if I resave it after I open it and I don't think it relates to that. –  aSystemOverload Aug 22 '12 at 18:59
    
Do you know where in the VBA it stalls? –  CharlieRB Aug 22 '12 at 19:41
2  
This should not have been migrated from stack overflow given it is VBA related –  brettdj Aug 23 '12 at 2:17
    
Check the on open macro to see what it is doing, it seems this is the problem? –  enderland Aug 24 '12 at 11:18

1 Answer 1

By default Excel opens each file within a single EXCEL.EXE process - you can watch this in the Processes tab of the Windows Task Manager.

So continuing to open files can cause contention within that process - eventually it will run out of memory and slow down or crash.

To avoid this, you can start subsequent Excel windows as a separate EXCEL.EXE process. You do this by holding down the Shift key while you choose Excel from the Start menu or click it in your Taskbar. Once they launched as separate processes, Windows seems to manage the resources better - I believe it swaps inactive processes out to Virtual Memory.

For Excel 2013 they have made that game a bit harder - you have to hold down Shift + Alt until a confirmation window appears.

I tend to open every Excel window this way, especially if working with large files, VBA code, Pivot Tables, External Data or Add-Ins. As well as avoiding resource issues in the first place, it means that you only lose your work in one window if there is a crash or freeze, rather than losing all of them at once.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.