+ Reply to Thread
Results 1 to 4 of 4

WorkBook very slow on open. (How does Excel work behind scenes?)

  1. #1
    Registered User
    Join Date
    11-16-2011
    Location
    Vermont
    MS-Off Ver
    Excel 365
    Posts
    57

    WorkBook very slow on open. (How does Excel work behind scenes?)

    I have a workbook I use for personal accounting with has a sheet for every month plus a couple of sheets that process and analyze data. In terms of complexity of formulas or the amount of data compiled, it's really a rather simple workbook. However it has a userform that is its primary interface, and which is rather complex with a lot of code behind it. All data entry is done there, and a lot of detail and ability to do a lot of things.

    I don't think there's any need to post the code, but I certainly will if needed. The workbook works smoothly and very quickly, but its start-up time is abysmally slow. It seems like every tweak I attempt to speed things up slows it down instead. On my system (which is admittedly old—twin AMD 1.9 GHz processors, 8GB of RAM) the start-up time at last count was about 12 seconds. Also the WB is set to open minimized, Userform only on the screen—but it remains on screen until almost at the end of the start-up time, at which point it minimizes and the Userform pops up, with a hanging hourglass for another second or two.

    If I comment out everything in the WorkbookOpen event, it does not change the start-up time, so I believe Excel must be "reading through" the code and "thinking about it" before it does anything at all. I know that's a silly and simplistic way to describe it, but I don't know any other way to explain the fact that altering WorkbookOpen doesn't change start-up time. Lately I've tried moving code that was stored in Macros directly to the form, or to buttons on the form, with the thought that maybe there was too much jumping from one module to another. As I mentioned, if anything this has seemed to make things worse.

    So my questions:
    1. Am I correct that Excel reads through the code before activating the workbook?
    2. If so, how much of the code? Userform only? Macros? Everything?
    3. Will putting as much code as possible into discrete Macros slow things down? Speed them up? Make no difference?

    Thanks for any advice/clarification you can offer.
    Nick

    (I'm only an egg)

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: WorkBook very slow on open. (How does Excel work behind scenes?)


    As Excel needs to upload / build everything to memory so the more items, the slower …

    Save the workbook as a new .xlsx workbook so all VBA codes will automaticly be removed,
    close this new workbook and see if it's still the same when you open it.

  3. #3
    Registered User
    Join Date
    11-16-2011
    Location
    Vermont
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: WorkBook very slow on open. (How does Excel work behind scenes?)

    Well, Thank you, Marc— it is surprisingly slow that way, as well. I guess this means there's some sort of curruption, and I'll need to start with a blank workbook and start importing parts one-by-one until I find where the slow-down starts.

    But if you will, go back with me to question #3: does it make any difference, efficiency-wise, if I have all my code directly behind the Userform and its buttons, or if I have much of it in macros that the form and buttons refer to? Will one way perform better than the other? I mean this question not in terms of this specific workbook, which clearly has an issue, but in terms of Excel in general.

    Thanks

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: WorkBook very slow on open. (How does Excel work behind scenes?)


    What really matters is how the code is written, how it handles objects, not really its location
    even if some location can make a code easier to create than a general module …

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-01-2018, 04:55 AM
  2. [SOLVED] Fast Excel open, Slow Workbook open
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-28-2016, 11:09 AM
  3. Replies: 2
    Last Post: 11-10-2015, 01:53 PM
  4. Macros run slow after loading another workbook, and fast if I re-open Excel?
    By Pedsdude in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2013, 10:17 AM
  5. excel 2010 very slow to open/save workbook
    By bebe123 in forum Excel General
    Replies: 10
    Last Post: 01-06-2011, 11:10 AM
  6. Excel Workbook slow to open
    By Sandy M in forum Excel General
    Replies: 2
    Last Post: 01-09-2006, 05:15 PM
  7. Replies: 9
    Last Post: 03-02-2005, 11:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1