vb editor doesn't open

Question

I have one file where the Visual Basic editor won't open (I eventually get the "Not Responding" message.)  This is true whether I click Visual Basic on the Developer ribbon, click Macros and try to edit from the Developer ribbon, right-click the tab name or hit <Alt>F11.

All of the data on the worksheets remains intact.  Also, I know the code exists because the macros still run.  Excel told me it repaired the file, but that didn't solve the problem.

VB editor opens fine with all other files, and opens fine with restored (via Mozy) version of this file (which is of course earlier and thus doesn't have today's VBA code changes.)

This is the most bizarre form of file corruption I've ever heard of.  Is there anything I can do, short of going to yesterday's version and re-creating today's code?

Answer 1

Andy, I didn't realize that CodeCleaner could do it.  I hope it works for him and certainly sure he's glad you popped into the discussion!

Answer 2

You could try code cleaner.

http://www.appspro.com/Utilities/CodeCleaner.htm

This will, if it works on your file, export and import all code. Hopefully removing the corruption in the process.

Otherwise it's restore and retype.

Answer 3

Glad things got resolved and thanks for the feedback.  Big kudo to Andy Pope for thinking of CodeCleaner.

One thing this should point out to others is the value of a good file backup strategy at all times and version control during development changes.

It is pretty clear that you have one - otherwise you'd have been wailing about a lost file that you desperately need to recover rather than "just" a need to recover some code changes.

Answer 4

Thanks to you both for your prompt replies.  I downloaded Code Cleaner, but was not confident it would work because, since I'm using Excel 2007, I would have to run it from the VB Editor Tools menu - and it was the VB Editor that wouldn't open in the first place.

Nonetheless, I decided to give it a shot, but it became a moot point after somehow the problem fixed itself.  After a couple of false starts, what was clearly a recovered / restored version (loss of formatting etc.) opened.  This version did let me go to the VB editor, and my most recent code was there!  All I can imagine is that Excel was repairing the file all the time, and it just took longer than the hour or so I spent on this last night.  (Longer than it would have taken to retype the code, but it became the principle of the thing.)

Anyway, success.  Thanks for your input.

Answer 5

I really don't know what you could do that wouldn't take at least as much effort as going to yesterday's version and recreating your code changes.

If you can't get into the VB Editor in the file itself, or access the code in it, it's doubtful that any fancy efforts at writing code to use yet another Excel file with special code to access the VBA Project and export the code would be worthwhile.

A quick thing to perhaps try (maybe you already have) would be simply to open that file and then save it under a new name.  Since you say the code runs, we know it's there, and saving as a new file might just make it available for editing again.  But it's a long shot and I wouldn't hold my breath.