如何清除内存以防止“内存不足错误"在 excel vba 中? [英] How to clear memory to prevent "out of memory error" in excel vba?

问题描述

我正在大型电子表格上运行 VBA 代码.如何清除程序/调用之间的内存以防止发生内存不足"问题?

I am running VBA code on a large spreadsheet. How do I clear the memory between procedures/calls to prevent an "out of memory" issue occurring?

谢谢

推荐答案

我找到了解决方法.起初似乎它会占用更多时间，但实际上由于交换更少和可用内存更多，它实际上使一切工作更顺畅、更快.这不是一种科学方法，需要进行一些测试才能奏效.

I've found a workaround. At first it seemed it would take up more time, but it actually makes everything work smoother and faster due to less swapping and more memory available. This is not a scientific approach and it needs some testing before it works.

在代码中，让 Excel 不时保存工作簿.我不得不在一张有 360 000 行的纸上循环，结果卡住了.每 10 000 次之后，我就让代码保存工作簿，现在即使在 32 位 Excel 上也能正常工作.

In the code, make Excel save the workbook every now and then. I had to loop through a sheet with 360 000 lines and it choked badly. After every 10 000 I made the code save the workbook and now it works like a charm even on a 32-bit Excel.

如果您同时启动任务管理器，您会看到每次保存后内存利用率急剧下降.

If you start Task Manager at the same time you can see the memory utilization go down drastically after each save.

这篇关于如何清除内存以防止“内存不足错误"在 excel vba 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！