I'm having random macro corruptions where I can't open the workbook.

I would have to disable all macro without notifications then open the workbook and recompile the macros, save close. Then enabled macros and open the workbook, then the workbook opens fine. Its almost like refreshing the macros helps reopen the workbook. I've checked some of my modules and a few are over the 64kb size. I've read some articles pertaining to module size limits that causes corruption.

Anyone verify if this is also for excel 365 or any reasons for macros to get corrupted?

  • 4
    Write smaller modules? Read up on cohesion and principles like Single Responsibility - your code will be easier to maintain, and you won't be dealing with module size limits. – Mathieu Guindon Dec 7 '18 at 15:05
  • well, the point is, i'm just wondering if it could be a factor for my macros to be corrupting? Also excel randomly closes when copy, paste text into other sheets within the workbook causing the same macro corruption. – ThisGuyJustNeedsHelp Dec 7 '18 at 15:23
  • I've read some articles pertaining to module size limits that causes corruption - the internal storage format hasn't changed in 20 years, what makes you think O365 changes any of that? The limits aren't the problem here... – Mathieu Guindon Dec 7 '18 at 15:24
  • FWIW the 64KB limit isn't on the exported file size / text source code, it's on the compiled p-code in the internal storage. – Mathieu Guindon Dec 7 '18 at 15:36

The 64KB limit isn't on the exported file size, it's the maximum size of a compiled module.

If your module is less than 10K lines, if can be compiled.

A heavy but likely still healthy module would be 1K lines, tops - that seems to hover around 40KB when exported to a text file; 64KB doesn't strike me as completely indecent, although it is definitely above 1K lines of code, and thus could likely use some tweaks.

If your modules are named e.g. Module8 or Utilities, verify how cohesive their members are - is it all related to the same functionality? Or does it feel like random functions were dumped in there?

Look for repeated code, refactor it. Extract methods, parameterize them, watch your module melt away, while retaining all its functionality.

The internal storage mechanics for VBA code, haven't changed in 20 years - I don't see a reason for that to have changed recently, especially since VBA is pretty much frozen now, and changing anything in the storage mechanics would break millions of things everywhere.

But, it's not impossible that something in O365 was changed recently (are you using an Insider build?), and something broke and your workbook somehow got corrupted.... but if your module is slightly above 64KB of text source code, it's very unlikely to be related: the compiled code would be much smaller than that... assuming the project compiles.

  • All my modules when exported, is below 64KB and a few in the 100KB size. The workbook only randomly become corrupted. Sometimes copy paste from sheet to sheet within the workbook causes excel to crash. Then on reopening of the same workbook, the whole workbook is corrupted and I have to do the process stated in my post to recompile the macros for it to be able to reopen and everything works fine. – ThisGuyJustNeedsHelp Dec 7 '18 at 15:54
  • The file size is irrelevant, only number of lines matter (storage size isn't shown anywhere). – Mathieu Guindon Dec 7 '18 at 15:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.