VBA is Corrupting My File

[Maryz]

I’ve been working on an Excel file (many user forms and VBA code) for a couple of months. Tonight I made some adjustments to one of the user forms (added some combo boxes and text boxes and associated code). When I tested the form, my code stopped executing and I was told that it did not recognize one of my user forms. I’ve had this happen in the past (fairly rarely). When I add or delete objects on a user form, VBA acts like certain forms (and objects on them) do not exist when clearly they do. To get around this, I can usually re-open the file with macros disabled and save it again. For some reason, this loads the forms and now they are all recognized by the VBE?? The same thing just occurred, but when I try to reopen the file, I am told that I am in the file and that it is locked for editing. When I continue to open the file (as read-only), I get an Excel.exe application error “The instruction at “0x6501ce26” referenced memory at “0x00000063”. The memory could not be “read”. Click on OK to terminate the program.” This means that Excel always shuts down and I cannot gain access to the file or any VBA modules/forms. My computer has a 2000 operating system so I tried to open the file on a computer with Windows 98. On this computer, I can open the file as read-only for about 30 seconds. I then get a system error “&H8000FFFF(-2147418113)” and the following error message:

Excel

This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor.



EXCEL caused an invalid page fault in
module VBE6.DLL at 0187:650e6ada.
Registers:
EAX=00000047 CS=0187 EIP=650e6ada EFLGS=00010286
EBX=0000001a SS=018f ESP=0062ebc8 EBP=0062ebf8
ECX=80000004 DS=018f ESI=00000004 FS=50f7
EDX=00000004 ES=018f EDI=02617a38 GS=0000
Bytes at CS:EIP:
8b 70 1c eb 03 8b 70 40 c1 e9 02 81 e1 ff ff ff
Stack dump:
02617a38 00000004 650bbd68 02617a38 00000000 80000000 651b321d 02617a38 02617538 0000000d 0000001a 02617968 026174bc 651b31e5 02617538 0000000d

I’d like to know if I can export and sheets/modules/forms from this corrupt file? Does anyone know why this occurs occasionally and why I cannot access the file this time? Any suggestions are helpful. The Microsoft web site was COMPLETELY USELESS in trying to explain the error messages. I’d like to try to salvage some of my work (last backup copy was yesterday, so worst case scenario, I lose a days worth of work) and make sure this does not happen again. Any insight is appreciated. Thank you.

 

[Juan Pablo González]

Try to open the workbook in a Excel XP machine. Another thing that you may try (when you recover your work), instead of closing, opening without macros, saving, is just removing the form, and reimporting it.

 

[Maryz]

I was able to open the workbook while disabling macros in ’98 and copy the sheets I needed into a new workbook (thankfully, as there was a lot of boring monotonous work on those sheets that I did not want to redo). I was also able to copy the entire code from the user form into a word document before Excel and the VBE crashed so I should be able to rebuild the file this morning without too much pain. I tried to use the Excel workbook rebuilder from vbausers.com and it could not rebuild the book. Can anyone shed any light on why I sometimes experience a problem with the VBE not recognizing certain user forms or objects on them while I am editing? Any thoughts are appreciated.

 

[DauntingGecko]

Just figured out WHY this happens.

The object on one of your forms is corrupt (such as a text box).

To fix this you need to delete the troublesome object and create it again.

This fixed it for me.

 

[pfarmer]

Just figured out WHY this happens.

The object on one of your forms is corrupt (such as a text box).

To fix this you need to delete the troublesome object and create it again.

This fixed it for me.

I have had problems with a sheet bombing when a form was open and then saved.

When working with forms I now make sure to make several copies during development, basically anytime I add or change nearly anything.

In your case you found a problem with a checkbox, very similar to a problem I ran into last weekend with a set of labels. At home on my 2003 Excel it worked fine. At work on 2002 the labels were not functioning normally which caused parts of the sheet to quit (trapped by error traps).

Simply removing the labels and putting them back in worked fine and works fine on both machines. I am starting to think that there may be some differences between labels on 2002 and 2003 Excel versions. If so maybe the same follows with some of the other controls as well that causes some of these weird problems.

Perry

 

[AndrewMalko]

System Error in Excel VBA

When a userform in VBA won't close because of a system error, create a new userform of the same size, select all objects (textboxes, buttons, etc.) and copy and paste them into the new userform. Do the same for the userform code. Change any module references from the old userform to the new, and remove the corrupt userform from the project.

Seems faster than trying to trace the corrupt portion of the userform.

 

[pfarmer]

Re: System Error in Excel VBA

When a userform in VBA won't close because of a system error, create a new userform of the same size, select all objects (textboxes, buttons, etc.) and copy and paste them into the new userform. Do the same for the userform code. Change any module references from the old userform to the new, and remove the corrupt userform from the project.

Seems faster than trying to trace the corrupt portion of the userform.

Of course if you made backups you may just be able to move backwards one step which may fix the problem even faster.

In my case I don't think it really was corrupt, that is it worked fine on my 2003 Excel but not on the 2002 at work. Replaced the labels using the 2002 machine and the form functioned fine on both, which to me indicates possibly some difference between versions of the controls or Excel versions that somehow played into it.

Perry