VBA is causing Excel file to "Not Respond" - can the VBA be edited outside Excel?

[David Evans]

I saved some bad code and it's causing Excel to hang on opening a particualr file - I've tried shutting down the macros, holding Shift when I open the file etc ...

Anyone got any bright ideas on how I can get into the VBA editor for an Excel file without opening the file?

 

[Luke M]

If it's a 2007+ file:

Create a new file (I'll call it test). Write a simple macro to it like:

Sub Test()
MsgBox "Hi"
End Sub

Save file as an xlsm.

Make a backup copy of your buggy file (just to be safe).
Change the extension of Test.xlsm and BuggyFile.xlsm to zip, i.e.
Test.zip
BuggyFile.zip

Open both the zip files. Under xl folder, find the vbaProject.bin


Copy the bin file from BuggyFile.zip to Test.zip, over-writing the existing file. Close the Zip files. Rename Test.zip to Test.xlsm.
Open the Test file.
You should now have all the VB code available for edit. When you get the code correct/debugged, reverse the process.

 

[David Evans]

Thanks Luke - greatly appreciate your solution - will go and try it - have communicated via PP - have a beer on me ... Thanks!

 

[David Evans]

Nice one Luke - it worked ....
Thanks

 

[Luke M]

Good to hear, thanks for the follow-up.