Excel 2010 vba project being corrupted

[scottiex]

I have an interesting problem where certain .xlsm files keep getting corrupted (not all the time, often a user can go a long time without anything happening). the next time anyone tries to open it they will give the Microsoft excel is not working sort of popup and then we will need to kill excel. This will of course occur every time anyone tries to open the file from then onward.

I know the issue is related to the VBA project because these files are generally created from templates so I can open them in Zip and copy an old ones vba project into the current file (tip for anyone else who ever has this issue!) and even if it wasn't we have a good backup system so fixing it is trivial from my perspective - however it is annoying to users. I wonder if anyone knows the root cause for these files being corrupted - is it maybe a bad update to excel at some point in time and I need to make sure we have a certain service pack ? Or does anyone have any other ideas?

FWIW we run excel 2010 SP2 on windows server 2012 R2 V6.3 if that is relevant.

And it occurs on a variety of files - most have quite a bit of VBA code in them but it isn't a specific file.

[Winon]

Hello scottiex,

Is it possible that you may have a lot of Active X Controls, like Buttons, etc. in your Code.? I have found that it could cause such a problem.

Regards.

[scottiex]

All of the ones that have had a problem have buttons (actually shapes with macros assigned, which is my habit). So there is a decent chance that contributes to it.

However it'd be hard (not impossible, but still...) to redesign everything to not have buttons or controls.

I have also seen a slightly different problems with shapes (and images in excel) before which caused a situation where you cant save the file. To resolve this I have to delete and rebuild the shapes / controls (which I did via code).

[nigelog]

When I first installed Office 2016 I had this a lot - the resolve I found was to start a new workbook and import all the forms and modules and code and save that as macro enabled. Seems to have settled the issue as haven't seen for a while now

[scottiex]

Thanks Nigelog that sounds like it is worth a try (pretty straightforward to apply to my templates).

[LJMetzger]

Hi scottiex,

In addition to the 'Active X' problems mentioned above, there are a couple of subtle string features that may cause corrupt workbooks:
a. Creating Data Validation using a string:

Please Login or Register  to view this content.

Workaround is to put the Data Validation items in a column, and reference the range.


b. Accessing a string beyond it's boundaries:

Please Login or Register  to view this content.

I hope this helps.

Lewis