How much memory do I really need

Check out the Microsoft provided page that was put together specifically to try to help address (no pun intended) these seemingly memory related problems:

http://support.microsoft.com/kb/2779852/en-us

With your 8 GB RAM you should be well above any normal needs - the system I'm on has 12GB and I seldom see more than about 3.6-4.0 GB in use regardless of what's going on.  Excel and other Office apps will use up around 2 GB, the rest being used by other programs running and the OS itself.  That's where increasing above the 2 or 4 GB point helps: gives some room for other things to go on without beating the drive to death swapping needed processes in and out of memory.

Even if you piled on the entire possible 192GB or RAM in a 64-bit system, you can still run out of 'resources' - some portions of various applications (and Excel is one of them) have a pre-defined limit for use of some memory areas such as stack space.  Once you hit that limit, all the RAM in the world is of no use to you.

Now, I've never tested this theory but I often wonder in cases like this where a person is having to open multiple files to get at all of the data if linking to that data might not be a better option.  Let's say Book1 needs to reference a bunch of stuff in just one sheet of Book2, but it needs to be done using some function that requires Book2 to be open.  What if you added a sheet to Book1 and then simply 'directly' linked the cells on that sheet, 1-to-1 with the cells of the sheet in Book2.  In other words, in Book2's new sheet in cell A1 you'd put a formula like =[Book2]DataSheet!A1  and then just fill right and down as far as needed to pick up all data from Book2.  Then in Book1 you'd set things up to use that new sheet instead of the sheet in Book2.  Tradeoffs are that by not having to open Book2, you save some memory and resources at the expense of the behind the scenes activity keeping the links between the workbooks open.

Here are a few suggestions you might try:

[1]  Click on "File -> Options -> Advanced -> Display", then be sure it is unchecked for "Disable hardware graphics acceleration".

[2]  Be sure your Excel files are saved with the .xlsm extension, and not .xls.  This will reduce the file size.

[3]  Within the Windows settings, change from 32 bit color to 16 bit color. 

[4]  Open the Windows Task Manager, and watch the Performance of your PC (especially the Memory usage).  If your computer is getting up to 6 GB or more while you are doing your Excel macros, then more memory might help.  Also, your Windows Task Manager might help you to see how you might be able to re-do parts of your Excel process, to prevent the out of memory issue.

[5]  If your computer does not have a video card (graphics card), then, if possible, consider adding one to your computer (or get a different computer that has a video card).  Microsoft's website, when speaking about MS Office, says "Graphics hardware acceleration requires a DirectX 10 graphics card."

[6]  Microsoft's website, when speaking about MS Office, says that a requirement is "PC: 1 GB RAM (32-bit); 2 GB RAM (64-bit)."  Thus your 8 GB of ram is already well above the minimum requirement.  My 64-bit computer has only 2 GB of memory, and one processor, and no video card, and it runs office 2013 fine.  However, my second 64-bit computer has 12 GB of memory, and 8 processors, and a video card, and when it runs Office 2007, the performance is very fast, compared to my computer that has 2 GB of memory, which is slower at doing things in Microsoft Office.

[7]  Be sure that all your Excel files, which are having memory issues, reside on the c-drive, and not on a networked location.

[8]  If not done so already, you might try putting all of your Excel process into a single Excel file, instead of having several Excel files that are cross-linked.

[9]  You might consider re-doing your process, so that it runs in MS Access instead of Excel.

This is a dumb suggestion, but check your Windows information using the <WIN><PAUSE/BREAK> key to display System Information.  A machine with 8GB should have 64 bit Win. I completely expect that yours does.  Check the "System Type" to make sure it says "64-bit Operating System..."

I agree with JL, 8 GB RAM should be more than enough.  I have regularly had up to a dozen Word documents (some fairly large) open at the same time with only 3GB of RAM (but no complex macro processing).

Note: the "Max RAM" has been "artificially" limited by MS below the 192 GB that JL quoted. Here are the numbers for Win 8

<snip http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778%28v=vs.85%29.aspx >

Physical Memory Limits: Windows 8

The following table specifies the limits on physical memory for Windows 8.

</snip>

While you are running these sheets and macros, have the task manager window open and select the "Performance" tab. Watch the memory use. There is a summary box for Memory (RAM) use or you can right click on the graph, under "View" select "Memory". This switches the large graph to display RAM use over time.  You will see if it is maxing out.  On my machine it normally only shows 50% RAM use, even though I wish it would use more of my limited 3GB all the time <sigh>.

64 bit Office may be the answer to your problem. The simple test criteria are file size approaching 2GB. But there is a less commonly checked one of MEMORY (RAM) use approaching 2GB. Your complex interconnecting spreadsheets and macros may be approaching this limit.  

Also, memory use depends on what you are doing in the macros. As JL pointed out, there are hard coded limits within Windows and Office applications that could be causing your problem. Another question about a Word macro problem was resolved by 64 bit Word (with help from MS Support).  It turned out that the macro was making so many changes in the document that the "Undo Stack" was overflowing, causing problems.  (This was the first time I'd seen documentation of a NEED for 64 bit Word).

Additionally, 64bit Office is only usually required for very large workbooks, some 2gb

Hi

In case you run any 32 bits OS-Operaton System, the maximum that it coultd handle is 4 GBs

For 64 bits OS, the money is the limit (194 GBs of memory)

For your problem of instable software, I wwould search for another cause of trouble.

Regards

JY