Excel VBA“内存不足”调试技巧
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10327457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA "Out of Memory" debugging techniques
提问by Trindaz
I was debugging a problem mentioned in a few other* questions on SO and noticed a strange behavior during the debugging process.
我正在调试关于 SO 的其他几个*问题中提到的一个问题，并在调试过程中注意到了一个奇怪的行为。
The behavior: Experienced 'out of memory' error while pasting complex formulas. Only about half of the 20,000 rows I'm iterating get formulas pasted before the error. Commented out virtually all code, error goes away. Uncomment code incrementally in the hopes of discovering the specific section of code that's causing it. End up uncommenting all code and stop experiencing the bug!
行为：粘贴复杂公式时遇到“内存不足”错误。我正在迭代的 20,000 行中只有大约一半在错误之前粘贴了公式。注释掉几乎所有代码，错误消失。逐步取消注释代码，以期发现导致它的特定代码部分。最终取消所有代码的注释并停止遇到错误！
This means the exact same code worked fine in the same Excel instance, and fixing it only required running various lighter versions of the code before going back to the original version. What could possibly cause this?
这意味着完全相同的代码在同一个 Excel 实例中运行良好，修复它只需要在返回原始版本之前运行代码的各种较轻的版本。什么可能导致这种情况？
回答by Ben Strombeck
Assuming the data you were running on was exactly identical every time, it sounds more like your problem was with the environment - the problem might be that the operating system ran out of memory. In Excel 2007, usable memory for formulas and pivot caches was increased to 2 gigabytes (GB), so that is probably not the issue. However it is of course also limited by how much memory your operating system had available at that time.
假设您每次运行的数据都完全相同，这听起来更像是您的问题与环境有关 - 问题可能是操作系统内存不足。在 Excel 2007 中，公式和数据透视缓存的可用内存增加到 2 GB，因此这可能不是问题。但是，它当然也受到当时操作系统可用内存量的限制。
The problem may have occurred because when you first tested it, your available operating system memory was lower (from other processes running... could even have been pushed over the limit by background programs such as Antivirus software running a scan) than when you ran the full macro later. I would try running your macro with the Task Manager open to see if you are getting anywhere close to low on physical memory. Also, (assuming you are on Excel 2007 or later) look at how much memory Excel is using and see if you are getting anywhere close to the 2GB limit. I doubt that this would be the issue, but it's at least worth double checking. Also, like Zairja said, make sure you're setting the calculation to manual at the beginning.
出现此问题的原因可能是，当您第一次测试它时，您的可用操作系统内存低于运行时的可用操作系统内存（来自其他正在运行的进程......甚至可能被运行扫描的防病毒软件等后台程序推到了限制）完整的宏稍后。我会尝试在打开任务管理器的情况下运行您的宏，以查看您是否接近物理内存不足。此外，（假设您使用的是 Excel 2007 或更高版本）查看 Excel 使用了多少内存，看看您是否接近 2GB 限制。我怀疑这会是问题所在，但至少值得仔细检查。此外，正如 Zairja 所说，确保您在开始时将计算设置为手动。
You said that you were using complex formulas... check out this article on Improving Performance in Excel
你说你使用了复杂的公式......查看这篇关于提高 Excel 性能的文章
There is a lot of useful information in the article that will probably help you streamline your macro.
文章中有很多有用的信息可能会帮助您简化宏。
Is this helpful to you?
这对你有帮助吗？
