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

问题描述

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

谢谢

解决方案

帮助记忆被释放的最佳方式是消除大对象：

  Sub Whatever（）
 Dim someLargeObject as SomeObject 
 
'昂贵的计算
 
设置someLargeObject = Nothing 
 End Sub 
  

还要注意，全局变量保持从一个调用分配给另一个调用，因此如果不需要持久性，则不应使用全局变量或不要再需要它们就可以使它们无效。

但是，如果：

• 您需要该过程后的对象（显然）


• 您的对象不适合内存

另一个可能性是切换到64位版本的Excel，应该能够在崩溃之前使用更多的RAM（32位版本通常限制在1.3GB左右）。

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?

Thanks

解决方案

The best way to help memory to be freed is to nullify large objects:

Sub Whatever()
    Dim someLargeObject as SomeObject

    'expensive computation

    Set someLargeObject = Nothing
End Sub

Also note that global variables remain allocated from one call to another, so if you don't need persistence you should either not use global variables or nullify them when you don't need them any longer.

However this won't help if:

• you need the object after the procedure (obviously)
• your object does not fit in memory

Another possibility is to switch to a 64 bit version of Excel which should be able to use more RAM before crashing (32 bits versions are typically limited at around 1.3GB).

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