Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how does Excel initialize itself (xl97/x.2000)

    I'm curious if anyone knows the inner guts of Excel as to what it does at initialization. This is before the auto_open or workbook_open event occurs. I'd like to know what takes so damn long. It's short if there's no VBA but once I start adding modules it gets longer and longer. This has nothing to do with the code in workbook_open since I'm talking about the stuff that occurs before this (which I have no control over). I guess since VBA isn't compiled into the workbook there is some set up stuff it does even before it gets executed.

    Same goes for clean up after workbook_close event, it takes a long time to close. I've done research on this stuff but have found up empty. No books or web sites that I've seen discuss it at all.

    Thxn, Deb <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Not an authoritative answer, but the more add-in's you have specified the longer it will take to start Excel. Also, certain add-in's take longer than others to load as Excel is starting. Try un-checking some of the add-in's that you don't use in everyday operations.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Not using any add-ins. This workbook just has a tons of formulas, 25 worksheets and some VBA (not much). I tend to think it's that I reached the internal garbage limit of Excel whereby after this point it needs to be rebuilt like the other replier said. I've done it several times before (not for this WB but others) and it's such a pain since I have named ranges which don't copy nicely to a new WB.

    Also, it takes so long to save that some people think it's locked up so end up rebooting their PC when in reality it's still writing to disk. I can't solve that problem for them.

    Thnx, Deb <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Couple of suggestions:

    1 - Maybe divide the 25 or so sheets into separate workbooks. A more efficient design might work better...and faster. I have a 2.5 meg workbook that takes around 10 sec on a 550 machine but 30 sec or so on a 333 machine.

    2 - If the file is large, depending on the speed of the machine, it might take a while to close.

    3 - If you set ScreenUpdating=false and (possibly, depending on what you are doing) calculation=xlmanual in your VBA code it might run faster. Don't forget to set them back to true after you have done whatever you intend to do.

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Unfortunately I can't divide up the workbook without a total rewrite (because the references and formulas would all need to change to refer to the other workbooks plus I'd have to distribute more than one workbook and that'd be a user nightmare).

    I always use screenupdate=false, but my question really has nothing to do with how I'm coding really, but rather it's about what Excel do BEFORE it even gets to the workbook_open event. My code hasn't even executed yet since workbook_open is the first chance I get to interact with Excel. WIth a brand new workbook, it's very quick, as time goes on and you add more sheets and code, it gets longer so there's obviously some initialization stuff that goes on. I wondering what this is so maybe I can minimize it.

    Just curious, I'll have to live with it.
    Thnx, Deb

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Well, I would guess that Excel first gathers up parts of your workbook should they be stored in various places on your hard drive. Then I would think that it reads ahead and starts loading all the things that your workbook needs to function properly and (quickly <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>), and loads everything into RAM.

    If this is the same workbook you refer to in your other post, 34 megs is quite hefty, and Access or another database program might be a more suitable vehicle for your requirements.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    Hey Guys, mind if I jump in with a question? What does setting screenupdate=false do and how do you do it?
    Thanks

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    You can set it yourself, it's not an Excel menu thing that I've ever found.

    You generally put it around code that would update the screen a lot like activate worksheets, writing lots of stuff to cells, etc. It greatly speeds things up and minimizes flicker (it doesn't completely go away though).

    application.screenupdating = false
    ' do cell updates, active sheets, etc.
    application.screenupdating = true

    Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how does Excel initialize itself (xl97/x.2000)

    There is one thing Xl does with VBA: it DOES get compiled. You can test this by creating a new workbook, enter some code and save it. Note the filesize. Now open it once more, run the code and save again, You'll see an increase in filesize.

    Example:
    - open new book
    - insert module with this code:
    Sub Test()
    MsgBox "Hi"
    End Sub

    Save. Filesize: 18944

    Now open, compile project and save again. Filesize: 24064.

    Furthermore, Once a file gets opened, XL starts calculating (I think) before any procedures run. Try setting calc to manual *before* opening the workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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