[RESOLVED] Excel hangs on workbook open

[VBAhack]

I have a relatively complicated Excel application with maybe 30 routines and 100's of lines of VBA code. During development over the past several months, I diligently saved different versions, and frequently save while making modifications. After my last modification (can't be 100% sure but I think I just renamed a routine), Excel now hangs when trying to open the workbook. If I disable macros I can open it, but the moment I enable macros, Excel hangs (can't even kill it via task manager End Task). CPU is at 25% so it doesn't appear to be an infinite loop. I can open other worbooks with macros without issue.

Any suggestion as to how I can go about diagnosing and fixing the issue? I can certainly resume from my last version and lose about 4 hours of work, but I'd like to see if I can figure out what is wrong. Whatever the issue is might also be in previous versions.

[westconn1]

what is in workbook open?

[VBAhack]

what is in workbook open?

Workbook_Open() saves Excel calculation mode and MoveAfterReturnDirection values to worksheet cells, changes the calculation and MoveAfterReturnDirection values, and sets several range and global variables.

I did some playing around and can get the condition to clear merely by opening the workbook with macros disabled and saving it under the same name. If I then enable macros and open the workbook, everything is fine. Don't know if it matters, but I noticed the size of the workbook reduces from 902KB to 805KB just by opening it with macros disabled and saving. The only real issue I found in the code is a non-existent routine being called in one of the routines (not associated with workbookopen), but I can't imagine this could ever trigger Excel hanging.

Any idea what could cause this? Scary stuff....

[TnTinMN]

...Any idea what could cause this? Scary stuff....

If you still have a copy of the bad workbook, try opening it and go to the VBA IDE. Open the Project Explorer window and look for duplicate Worksheet Names. Several years ago I had a project that would go crazy and the file size would start doubling. It would be storing phantom worksheet copies (same friendly name) that could only be found via the Project Explorer. I never tried your solution though and always ended up copying the good sheets to a new workbook.

[VBAhack]

Open the Project Explorer window and look for duplicate Worksheet Names.

I've saved a copy of the failing workbook for debug purposes. There are no duplicate worksheet names. It has been many months since I've added any worksheets to the workbook and/or changed any worksheet names. All I've done is make code changes, worksheet formula changes, and chart changes.

It is very troubling that a simple thing like changing the name of a routine can trigger such extreme behavior with no clue what the issue is and then completely recover by opening the workbook w/ disabled macros and saving the workbook again. I've used the workbook for the past day and a half w/o any issue at all. I hate just waving my arms and declaring it a mystery that will hopefully never happen again, but I don't know what to pursue.

[TnTinMN]

I know this answer is not what you want, but sometimes stuff just happens and the file gets corrupted.

Will it ever happen again? It is quite possible that it will and it is just as likely that you will never figure out the sequence of events that caused it to happen. It is also possible that file becomes corrupted again and your current remedy does not work.

I had an experience in college where I was able to identify a bizarre cause for a programming problem.

It was a Friday and I had finished my code for a database system and only need to make the final runs on it before needing to submit at the end of the following week, so I decided I would take the weekend off and generate my stuff on Monday. I came in Monday and guess what, my code refused to run and was throwing an invalid record error. Through shear luck I found if I added a line to seek the first record in the DB prior to actually running my main code it would work. I asked the operators (yes this was the days of mainframes) if they knew of any issues with the drive systems and got laughed at. A week later they told me that their boss wanted to talk to me. It turned out that a disk controller had failed and he wanted to know why I suspected that there was a hardware problem. I explained the scenario to him and was dismissed again as a crazy kid. I decided to go back to my program and remove the dummy code that I placed in it to make it work and it did just as it did before the issue arose. Would that same condition ever come up again, not very likely.

So sometimes, sh*xyz*t just happens, and you just have to accept that.

[VBAhack]

So sometimes, sh*xyz*t just happens, and you just have to accept that.

Yeah, you are right. Thanks for the thoughtful posts. I guess I should be happy that I was able to recover and not lose the 4+ hours of work. I have to keep reminding myself that the tools we use (and code we write) are not 100% bug free. On to the next challenge...

P.S. I used mainframes in college also, and experienced the transition from punched cards to dumb terminals. I was nervous for quite some time about not being able to hold my program under my arm in a cardboard box!