Out of Memory....caused by "Save"!?!?

[Hendrixx]

Hi all,

I have an unusual VBA Out of Memory Error.

I have a spreadsheet which I have restored from backup, which opens fine, behaves fine, and does all it should (it contains about 10 modules, none of which is bigger than 64kb). It contains a couple of very small forms. It also contains about 50 public constants, and a similar number of public enums.

If I close it (without saving) and subsequently re-open it, all is still well.

As soon as I save it (even without having made any changes - i.e., open it, save it, close it with no other action taken) I get a VBA "Out of Memory" pop-up on re-opening it......

....so it appears as if the act of saving it causes the "Out of Memory" error.

"Save As..." has no effect - same OoM error.

I have had a search around the interweb and can find nothing describing a similar error (although I have checked all the other causes of OoM errors (module size etc) that I can understand, but none of them make any difference). The spreadsheet itself is only about 2.8Mb. I have similar workbooks (different versions from the same "parent template") which have even more public constants and enums, with no issues.

Anyone seen anything like this before, or does anyone have any ideas as to where I might look for a fix please?

Thanks

Phil

 

[mole999]

open fresh, then in vba, compile it, check your references, any with an X. would be what i would start to check

 

[Hendrixx]

Thanks mole999,

I unchecked all the references that I could (I was going to add them back one by one to try to isolate any problem ones) but there were 3 I couldn't uncheck - Visual Basic for Applications, Microsoft Forms 2.0 Object Library, and Microsoft Excel 12.0 Object Library (I'm using Excel 2007) as they were in use, and it seems to be one of these (possibly) causing the issue.

Short of reinstalling Excel, is there any way to fix or reinstall these references? I have had a quick look online, but can see nada....but its possible I'm asking Google the wrong question....

I appreciate your help.

Phil

 

[mole999]

microsoft offcat tool > https://www.microsoft.com/en-gb/download/details.aspx?id=36852

then also at the top of each module try option explicit to check for undefined variables

 

[Hendrixx]

Thanks mole999.

All modules already had "option explicit" and compiled OK.

Unfortunately Offcat tool didn't find any issues.

 

[mole999]

any vba that runs before save ?

 

[Hendrixx]

Not that I am aware of - There are no Workbook_Save or Workbook_BeforeSave events. There is a Workbook_Open event - Main.Initialise - which calls various "Sheets".Initialise, which in turn sets various ranges.

I have just noticed something though....while the Out of Memory error happens on Workbook open, clicking "OK" (to dismiss it) and then clicking on each of the worksheets in turn, with VBA code open, gives me another pop-up - "Compile error: Out of Memory" and highlights the same line in each worksheet's code - "Private contractTable as Range". These ranges are not particularly large - the biggest is $A$6:$M$64.

 

[Hendrixx]

I am too late to edit the above, but the highlight falls on the same line in each sheet as I mentioned - the declaration "Private contractTable as Range" (each sheet has other range declarations as well, but this is the first in each, which is maybe why it is highlighted).

Although each declaration in each sheet is private, I went through and subtly changed the name of each of the contractTables (e.g., contractTableAA, contractTableBB) in each sheet to see if this made any difference.

It didn't.