Since upgrading our users from Excel 2007 to 32-bit Excel 2013, our company has faced a mountain of performance and stability issues.
自从我们的用户从Excel 2007升级到32位Excel 2013以来，我们公司面临着大量的性能和稳定性问题。
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.
32位版本的Excel 2013似乎使用了自己的内存管理器，从32位内存映射到Windows 7使用的64位内存。
The problem is, when this memory becomes too fragmented, Excel 2013 will completely crash.
问题是，当内存变得过于分散时，Excel 2013将完全崩溃。
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.
有时，仅仅使用一个小时，我们就看到Excel 2013试图搜索一个XX mb的内存块，如果它找不到这个大小的连续内存块，它就会崩溃。
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.
作为一家金融公司，我们在这些Excel文件中有很多遗留的VBA代码，迁移到64位的Excel 2013会带来更多的问题。
So does anyone have any idea how to fix the memory problems with Excel 2013..?
有人知道如何用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.
一些读者(可以理解)问我们为什么不安装64位版本的Excel 2013，尤其是在我们运行64位Windows 7的时候。
The reason, to quote Microsoft, is compatibility.
用微软的话来说，原因在于兼容性。
64-bit editions of Office 2013
64位版本的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."
我们向大多数用户推荐32位版本的Office，因为它更适合大多数其他应用程序，尤其是第三方插件。这就是为什么Office 2013的32位版本是默认安装的，即使在64位Windows操作系统上也是如此。
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.
我们的用户不想要铃铛、口哨或时髦的动画。他们想要的东西是稳定的，即使他们的Excel文件和VBA是10年前写的。其中一些代码甚至使用FORTRAN .dll计算引擎、DAO库等。
And from an IT support point of view, we just want a version of Excel which is still supported by Microsoft.
从IT支持的角度来看，我们只想要一个仍然由微软支持的Excel版本。
Which is why we're using 32-bit Excel 2013 for now.
这就是为什么我们现在使用32位的Excel 2013。
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 ?
问题是，这是一个严重的问题，32位的Excel 2013，大多数金融公司仍然大量供应遗留的Excel/Access应用…我们能做些什么使这个环境更稳定?
2 个解决方案
#1
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.
王我也面临这个问题在我的项目中,但当我与doevents托管代码关键字并把适当的内存管理代码的功能和程序,明确把doevents所以它的变量和后将减少90%的崩溃。
#2
0
"Change is inevitable, and those who adapt most quickly are the most likely to survive." -Dr. Larry Fleinhardt (Numb3rs)
“变化是不可避免的，那些适应得最快的人最有可能生存下来。”是。拉里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).
如果您认为最好的解决方案包括维护旧代码，那么请切换到Excel 64位。这两件事。它为你提供了一个更大的可寻址内存，用于解决内存问题。它还具有修改程序部分的附加价值，可以修复由于内存泄漏而导致的任何问题。还有其他的选项，如具有VBA集成的OpenOffice/LibreOffice(您的里数可能不同)。
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.
我建议学习一种编程语言Python，它有像熊猫这样的Excel模块。这是一个强大的解决方案，但是需要一些时间来使用，但是从长期来看，您可以从中获得更多的价值。
#1
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.
王我也面临这个问题在我的项目中,但当我与doevents托管代码关键字并把适当的内存管理代码的功能和程序,明确把doevents所以它的变量和后将减少90%的崩溃。
#2
0
"Change is inevitable, and those who adapt most quickly are the most likely to survive." -Dr. Larry Fleinhardt (Numb3rs)
“变化是不可避免的，那些适应得最快的人最有可能生存下来。”是。拉里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).
如果您认为最好的解决方案包括维护旧代码，那么请切换到Excel 64位。这两件事。它为你提供了一个更大的可寻址内存，用于解决内存问题。它还具有修改程序部分的附加价值，可以修复由于内存泄漏而导致的任何问题。还有其他的选项，如具有VBA集成的OpenOffice/LibreOffice(您的里数可能不同)。
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.
我建议学习一种编程语言Python，它有像熊猫这样的Excel模块。这是一个强大的解决方案，但是需要一些时间来使用，但是从长期来看，您可以从中获得更多的价值。