Excel Has Stopped Working

[Neil-D]

I have an Excel 2007 macro that is really quite simple (but large) that looks through 14 open workbooks, takes some basic data from each and transfer the results to another workbook.
It has been working fine until last Monday.

Now, if I make any changes to the macro, even just adding a comment line, when I start the macro (by clicking a button in a worksheet with the macro assigned), Excel freezes and I get the message "Excel has stopped working".

I have tried so far -

• recompiling the vb - results in crash
• exporting the form and all modules to a new workbook - results in crash
• turning on error trapping, break on all errors - results in crash when run
• putting a break command on the first line of code "Application.ScreenUpdating = False" - results in crash when run
• going back to earlier versions and running them - they work fine but a lot of code has changed since
• creating new button in workbook and assigning macro to it - results in crash when run
• running macro straight form module - results in crash

I have no add-ins and there are no other macros in the workbook such as on event commands. I think there may have been a Windows update on my pc last Friday if that is relevant.

Looking at various searches and Microsoft Support and the common answer seems to be "this sometimes happens, live with it".
It is also suggested to clear the folder "C:\Windows\Temp" but unfortunately I do not have Admin rights. I have raised a ticket with IT but they seem reluctant to do anything (any mention of macros, sends them into the dark recesses of their server rooms, shaking their heads and tutting).

I work in a quiet office (and with the snow even quieter today) so will struggle to get someone else to test if it runs but I will try tonight on my home pc.

I need this macro for work (preferably today due to having to issue a report) so really could do with some sort of solution.

The only thing I can think of doing it to create a new workbook and copy/paste all code over. However, I have a complicated form I would rather not recreate it and the macro is spread over 30 modules so this is a major task.

Does anybody have any suggestions on what to do to get the macro working (and why preferably).

Thanks in advance if you have even read this far.

 

[mole999]

can you analyse the size of the macro in chunks, you can make them too big, and need to step out or make more steps

 

[Neil-D]

No, sorry I can't even start the macro.
It crashes as soon as the first sub is called by pushing the button.
If I put a break in the first line of code in the first module it crashes before it even gets to that break.
I have spread the macro over 30 modules as the number of lines was hitting the maximum per module.

 

[mole999]

does it work on another machine, has it been recompiled in a later version, are the references intact

 

[Neil-D]

It works fine on my home PC running MS Office 365 (i.e. later version).
I'm putting down to an issue on my work's MS Excel and raising a ticket to have it re-installed and see if that fixes it.
I can run the macro from my home PC for the moment.