Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: AUTO_OPEN (Stupid question)

  1. #1
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey,

    I've just been sent a macro diseased workbook from a vendor. It was originally created in Excel 5.0/95 and uses the "AUTO_OPEN" doo hicky.

    My question is this. Obviously AUTO_OPEN is supported in XL2000 (which is what I've got) in some fashion for backward compatibility.

    As an example, say I didn't want to put any code in the worksheets or workbook objects. If I were to use AUTO_OPEN instead of the Workbook Open event, would I have to save the workbook in Excel 95 format. (it appears that AUTO_OPEN can be in any module and will still work. I'm also too busy to mess around with it just now)

    Additional question, does anyone use this sort of thing to keep their workbook free of code?

    PS. Yes, for a substantial discount on their equipment, I have offered the vendor my expertise to modernise their workbook.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark,

    The Auto_Open will work whether the workbook is saved as version 5.0 or 2000. There are a couple arguments for switching over to Workbook events rather than the Auto_Open:

    1. who knows how much longer Auto_Open will continue to be supported since it is an archaic feature. Microsoft is encouraging developers to switch from Auto_Open to the Open event;

    2. someone may try to add an Open event macro to the workbook without being aware of the Auto_Open, and the result could be code that conflicts;

    3. obviously, using Auto_Open does not keep the workbook free of code, since the standard code module containing the Auto_Open is stored with the workbook as well.

    All that said, I should also mention that there are some bugs still remaining in the worksheet/workbook events. If you encounter one of these it can sometimes be eliminated by reverting to the old Excel 5 Auto_Open type event routines. Usually these bugs are fairly obvious so you would know if you had one.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I have heard of only one advantage to using the Auto_Open rather than the Workbook_Open event. Naturally it came out of *yet another* small spat between Harlan Grove and an Excel MVP. This time it is Rob Bovey.

    The following is the original thread:

    http://groups.google.com/groups?hl=e...rldnet.att.net

    Here, when there is interaction between Excel and another program/application (a PERL controller in this case), occasionally Excel needs to "tidy up" a bit before opening fully and occasionally the open event happens before that clean up.

    Now, I may be misinterpreting what Rob is saying, but the post was from 2002, so Rob definitely is aware of the workbook_open event, and I believe it is a clear decision to go to the auto_open here.

    That said, Damon makes excellent points and the benefits seem to outweigh this one drawback by a considerable margin.

    Bye,
    Jay

    EDIT: On second thought, I think that a worksheet_open event will work, with Rob's suggestion to wait a bit before calling a routine, rather than just calling the routine directly.

    [ This Message was edited by: Jay Petrulis on 2002-05-06 11:00 ]

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys.

    Yeah, I was actually going to ask if XP still supported this feature. Usually I wouldn't expect too much backward compatibility after 3 versions.

    Obviously I wouldn't want to use this unless it's absolutely necessary and would only use it in versions that knew that it worked in. (and the question was more for future reference, should I ever need to use it)

    I was thinking that it's easier to strip/export standard modules from workbooks than from the worksheet and workbook objects. This would be one advantage I could see.

    Anyway, thanks again.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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