Excell "upgrade" problems

Brass Contributor

SUDDENLY !  the 

Private Sub Workbook_Open()

routine falls over at several different places.

That routine I installed over 20 years ago.

 

But more infuriating is that "it" ignores F9 stops, AND ignore the instruction "Stop", so I cannot step through a routine.

 

All since yesterday's "upgrade" !

I have tried a re-install, without success.

 

OR - is it something I have done ?  Clicked on something by mistake ?

2 Replies

@RobinClay You are not alone. There's been a high number of complaints over the past few months from people describing a wide variety of strange and unexplainable glitches with their macro-enabled workbooks. Personally, I made the jump from Office 2010 to MS365 around 2 years ago. Since then, I've had several bugs show up from time to time ranging in severity from annoying inconveniences to catastrophic failure (corrupted files). With all of the advancements Microsoft has made regarding dynamic array formulas and other features, they've clearly dropped the ball when it comes to VBA. It's disappointing, to put it politely.

 

Just last week, I too had a workbook with some very basic code in the Workbook_Open event that was not firing when opening the file. In my case, no errors occurred... it just skipped over the code as if it wasn't even there. All I did to get it working again was to simply add and delete a line within the Workbook_Open sub routine (I just clicked into a blank line at the top of the sub and pressed Enter then Backspace), then recompiled the project (Debug > Compile VBAProject).

 

I've also experienced other situations where a basic sub routine that was working fine suddenly throws an application object error for no reason. Simply clicking into the problematic sub routine, pressing Enter and Backspace, then recompiling the code seems to fix it (in most cases).

 

With more severe problems, where a macro enabled workbook cannot be opened (ie: "Errors were detected when opening the file..."), or the VBA modules cannot be accessed, one solution that seems to work is to move the file into a folder that is not "trusted" and open the file without enabling macros. From there, open any module in the VB Editor and go to Debug > Compile VBAProject, then save, close and reopen the file. Re-enable macros and everything should work again.

 

Having said that, I've also read recently that the vast majority of these bugs are only a problem with the 64-bit version of Office. Some have claimed that uninstalling and reinstalling the 32-bit version has solved all of their problems, although I have not personally tried this yet to confirm it. If you decide to go this route, please share the results so that it may help others...

 

Best of luck! :)

Thank you for that long reply - bet your typing fingers were tired after !
I shall "experiment" along those line. Thank you !