Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA damage in Excel (Excel 2003/XP)

    Hi everybody:

    I recently had a user experience an Excel crash because he had AutoSave turned on and it tried to save his file at the exact moment that he triggered some VBA code. The file was recoverable, but now one of the VBA routines keeps getting called over and over after the calling code in the Sheet_Recalculate event exits. I know how to recover damaged VBA projects in Access by opening the file decompiled and then compacting it, but does anyone know of a way to recover damaged Excel VBA sheet modules? (Deleting and recompiling didn't help.)

    Thank you, in advance, for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    Do you mean that you have deleted the worksheet code and this didn't help?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    Try running Code Cleaner on it - you can get it here: http://www.appspro.com/Utilities/CodeCleaner.htm
    If that doesn't work, you may need to copy the data and code modules to a new workbook.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    Thanks, but there is very little code to be cleaned (three functions!). It appears that the VBA project itself is damaged.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    Hi Hans:

    My first line of defense was to delete the VBA code, save the workbook, open and repair it, add the VBA code back in and compile. This did not solve the problem. So, I created a new workbook and copied and pasted everything into it, added the VBA code, and it is fine now. Apparently, the worksheet was damaged by the Autosave process crashing my VBA.

    I was just wondering if there is a way to repair Excel VBA projects as one can with Access.

    Thanks for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    The problem with worksheet modules (and with ThisWorkbook) is that you cannot completely delete them - you can only delete their contents. This leaves behind the invisible corruption. There is no /decompile option for Excel.
    The way you solved it is the best one.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA damage in Excel (Excel 2003/XP)

    I figured as much.

    Thanks!

Posting Permissions

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