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

Hendrixx

New Member
Joined
Nov 14, 2017
Messages
11
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,101
Office Version
2019, 2016, 2013
Platform
Windows
open fresh, then in vba, compile it, check your references, any with an X. would be what i would start to check
 

Hendrixx

New Member
Joined
Nov 14, 2017
Messages
11
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
 

Hendrixx

New Member
Joined
Nov 14, 2017
Messages
11
Thanks mole999.

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

Unfortunately Offcat tool didn't find any issues.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,101
Office Version
2019, 2016, 2013
Platform
Windows
any vba that runs before save ?
 

Hendrixx

New Member
Joined
Nov 14, 2017
Messages
11
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

New Member
Joined
Nov 14, 2017
Messages
11
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.
 

Forum statistics

Threads
1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top