9

Since upgrading our users from Excel 2007 to 32-bit Excel 2013, our company has faced a mountain of performance and stability issues.

We've had to disable hardware acceleration for some users, change settings in Control Panel\Ease of access center, and even upgrade Intel Graphics drivers to improve stability, all with varying levels of success.

But one issue we can't get around is this:

The 32-bit version of Excel 2013 appears to use its own memory manager to map from 32-bit memory to the 64-bit memory used by Windows 7.

The problem is, when this memory becomes too fragmented, Excel 2013 will completely crash.

Sometimes, after just an hour of useage, we have seen Excel 2013 attempt to search for a block of XX'Mb of memory, and if it can't find a contiguous block of memory of this size, it'll crash.

As a financial company, we have a lot of legacy VBA code in these Excel files, and moving to 64-bit Excel 2013 would introduce even more problems.

So does anyone have any idea how to fix the memory problems with Excel 2013..?

Update

Some readers have (understandably) asked why we haven't just installed the 64-bit version of Excel 2013, particularly as we're running 64-bit Windows 7.

The reason, to quote Microsoft, is compatibility.

64-bit editions of Office 2013

"We recommend the 32-bit version of Office for most users, because it's more compatible with most other applications, especially third-party add-ins. This is why the 32-bit version of Office 2013 is installed by default, even on 64-bit Windows operating systems."

Our users don't want bells, whistles or funky animations. They want something which is stable, even with their Excel files and VBA which was written 10 years ago. Some of this code even uses FORTRAN .dll calculation engines, DAO libraries, etc.

And from an IT support point of view, we just want a version of Excel which is still supported by Microsoft.

Which is why we're using 32-bit Excel 2013 for now.

The question is, this is a nasty issue with 32-bit Excel 2013, most financial companies still heavily supply on legacy Excel/Access apps... what can we do to get this environment more stable ?

Mike Gledhill
  • 23,658
  • 6
  • 133
  • 143
  • 1
    memory fragmentation is not an issue... the R in RAM stands for random. – Philipp Sander Nov 26 '15 at 13:19
  • This doesn't make a lot of sense. Are your users using 64-bit Windows 7? If so, what possible reason could you have for *not* upgrading to 64-bit Excel? – nagyben Nov 26 '15 at 13:21
  • @PhilippSander Memory fragmentation is certainly an issue. If you must have a continuous block of memory (which is a strict requirement in many situations) and your virtual address space is filled with tiny allocated blocks, then even if you have enough free bytes in your address space, you will not be able to allocate any useful memory. – GSerg Nov 26 '15 at 13:24
  • @exantas One reason would be using 32-bit COM components from VBA (such as ADO). This reason alone is often enough to not even consider 64-bit Office regardless of Windows bitness. – GSerg Nov 26 '15 at 13:26
  • @GSerg this doens't make any sense to me. this has nothing to do with fragmentation. – Philipp Sander Nov 26 '15 at 13:28
  • Then what is [memory fragmentation](http://blogs.msdn.com/b/oldnewthing/archive/2010/04/29/10004218.aspx) in your opinion @PhilippSander? – GSerg Nov 26 '15 at 13:30
  • 1
    @PhilippSander http://stackoverflow.com/questions/3770457/what-is-memory-fragmentation – nagyben Nov 26 '15 at 13:31
  • I know what it is. but why should it be a problem here? it occured after they upgraded excel – Philipp Sander Nov 26 '15 at 13:37
  • @PhilippSander Yes, that is the OP is saying. They upgraded Excel and the new version has its own memory manager. They have debugged it to see it crashes when unable to allocate more memory. – GSerg Nov 26 '15 at 13:41
  • Apparently, previous versions of Excel *did* also do this 32-bit to 64-bit memory management, but it has changed in Excel 2013 and, what we've found, is that with 2013, it's much less stable. Our actuaries usually have several (large) Excel files open at once, and since upgrading to Excel 2013, crashing has become a frustrating way of life for them. – Mike Gledhill Nov 26 '15 at 13:42
  • 1
    since you can read german this link might be helpful http://www.xlam.ch/xlimits/speicher.htm – Philipp Sander Nov 26 '15 at 13:42
  • out of curiosity (hardware issues are outside of my understanding) how much RAM do your users typically have? – Cor_Blimey Nov 26 '15 at 13:49
  • Good question. Typically, they have either 8Gb or 16Gb of memory. And we all had new HP laptops last year. But having extra memory doesn't seem to help. They can have *plenty* of free memory, but when it becomes too fragmented, thats when issues start... – Mike Gledhill Nov 26 '15 at 13:58
  • 1
    @MikeGledhill yes, but the probability of it being too fragmented I would in ignorance presume be lower with more memory. But 8GB should be more than enough. I wish my office would give us such swanky laptops. We have to strain away on Office 2010 running on punchcards! – Cor_Blimey Nov 26 '15 at 14:14
  • 3
    Don't be too jealous. We still do nightly backups onto ticker tape. – Mike Gledhill Nov 26 '15 at 14:23
  • Are you sure it just isn't running out of addressable memory? Have you monitored how much memory Excel is using prior to the crash, and is it close to 2GB? [This article](https://support.microsoft.com/en-us/kb/3066990) suggests 2013 uses more memory than previous versions which may result in issues like this. Unfortunately their workaround essentially amounts to opening each workbook in it's own instance of Excel (so each has 2GB available), or to change your workbooks to reduce their memory requirements... – andshrew Nov 26 '15 at 15:17
  • How do you know that is the problem? How do you know that Excel is looking for a specific size of memory - does it come back with a specific Exception? – PaulG Nov 27 '15 at 10:39
  • Disclaimer: I cannot explain why this works, but I have tried this and it works! So take it for what it is worth! We are in a similar situation (financial firm with tons of Excel VBA 2007 reports). Here is a list of items we did - 1. Uncheck 'Show the Start Screen when this application starts' in File - Options - General. 2. Uncheck 'Provide feedback with animation' under File - Options - Advanced - General 3. Check 'Diable hardware graphics acceleration' under File - Options - Advanced - Display. Again, like I said, I am unable to explain how doing this makes things work, but it seems to! – chaltahai Mar 17 '16 at 18:44

2 Answers2

1

I have also faced this king of issue on my project, but as I have managed the code with doevents keyword and put proper memory managed code like at the end of function and procedures , clear the variables and after that put doevents so it will be 90 % less crash.

Divyesh M.
  • 56
  • 5
0

"Change is inevitable, and those who adapt most quickly are the most likely to survive." -Dr. Larry Fleinhardt (Numb3rs)

Once you run out of addressable memory you are done, whether its due to memory leaks or usage limitations. If you believe there is a problem like a memory leak you will need to take it up with Microsoft, an expensive support ticket is in your future. They can fix it but you are just going the expensive route to maintain a system that is breaking and/or not meeting your needs.

If you believe your best solution involves maintaining old code then switch to Excel 64-bit. This does 2 things. It gives you a larger addressable memory to work with which kicks the can down the road for memory problems. It also has the added value of changing parts of the program which might fix any issues that you have due to memory leaks. Other options exist such as OpenOffice/LibreOffice having VBA integration (your mileage may vary).

I suggest learning a programming language, Python, that has Excel modules like Pandas. It's a powerful solution but it takes some time to use but you get more value out of it in the long term.

Back2Basics
  • 6,301
  • 1
  • 30
  • 39
  • 3
    Genuinely, one of the oddest replies I've ever read to a legitimate issue reported on here. Btw, this memory issue *is* fixed in Excel 2016 32-bit. The problem just goes away. Perhaps the quote should be "Bugs in Excel 2013 are inevitable, blah blah blah.." !! – Mike Gledhill Dec 28 '15 at 14:05