Excel VBA crashes when altering Dim statement

[Nightspore68]

This happens to me periodically, maybe twice a year: I realize I need my arrays to be bigger and when I alter the Dim statement, Excel crashes. At one point I was able to export the module, remove the module, and then import the module to fix the problem. But now I can't even remove the module without Excel crashing.

The funny thing is that I am able to add a comment line without trouble. But I can't mess with the declarations.

If history is any guide, it will work fine tomorrow when I get to work. Even though I've already tried a reboot today.

I'm using Office 2007 and Windows XP.

Wondering if anyone else has similar problems.

 

[Ragnar1211]

Sounds like you have live code running... do you have any event type code running at the same time?

Perhaps load excel in safe mode:

"C:\Program Files\Microsoft Office\Office\Excel.exe" /s

 

[Nightspore68]

No, I don't have any event type coding running.

When I start Excel up in Safemode, I don't know how to get to the visual basic editor. I assume it's not available in safemode. I can't make any changes to my VBA outside of the VBA editor, can I?

thanks,
Michael

 

[Ragnar1211]

Macro's still work in safe mode.

Alt+F11 will bring up the visual basic editor.

If all else fails there is also a settings file that can get corrupted from time to time in the user folders:

%appdata%\Microsoft\Excel\Excel14.xlb

Delete the xlb file, it will get recreated. I have seen this file grow and slow excel down quite a lot.

 

[jproffer]

%appdata%\Microsoft\Excel\Excel14.xlb

Delete the xlb file, it will get recreated. I have seen this file grow and slow excel down quite a lot.

Not trying to hijack, but does this only work in safe mode, or will it get re-created regardless? I assume it happens when you load excel next time...??

 

[Ragnar1211]

It will work the next time you open excel, either way.

 

[Nightspore68]

This morning I went into Excel (normal mode) and had the same problem - crash when updating the Dim statement. So I went into safemode and got the VBA editor up with Alt-F11. Thanks for the tip, Ragnar! However, my modules were not present, presumably because of safemode? So I couldn't get to the troublemaking script. I guess I could import them... I'll try that in a bit.
Yesterday I did use Windows search to try to find .XLB files, but it didn't find any, even looking in system files (but not hidden files). This morning I looked in c:\Documents and Settings\myname\Application Data\Microsoft\Excel, but there were no XLB files. I even unchecked "hide protected operating system files" but no XLBs appeared.
Weird, huh?
I have been having other troubles with Excel that are probably related. For instance, it no longer closes gracefully and I have to force it shut, not with task manager, but with a warning window that pops up after a minute or two. This is all the time. The good news is I'm due for a computer upgrade soon. Hopefully these problems will vanish on the new machine.
Thanks for your help . If you do think of anything else, please let me know and I'll give it a shot.
Michael

 

[Nightspore68]

Ooo, good news! In safe mode I was able to import my backup of the script in question AND alter the dim statement! I think I'm back in business!
Thank you

 

[jproffer]

It will work the next time you open excel, either way.

Good deal...I'd just hate to delete something that I need (in normal mode)...and be FUBARed afterwards

Thank you