Excel、VBA：清除内存
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19513271/
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: Clear memory
提问by waterplea
I have a macro for Excel, nothing too fancy, no objects, just plain old cycles with formulas. Basically it's this:
我有一个 Excel 宏，没什么特别的，没有对象，只是带有公式的普通旧循环。基本上是这样的：
I cycle through rows in one column, assigning pretty big SUMPRODUCT functions to the cells without executing them. Once I cycled through the row I do Range.Value = Range.Value twice to execute the formulas and to save result as value. Then I go for the next column.
我循环遍历一列中的行，将相当大的 SUMPRODUCT 函数分配给单元格而不执行它们。一旦我在行中循环，我会执行 Range.Value = Range.Value 两次以执行公式并将结果保存为值。然后我去下一个专栏。
With each column memory used by Excel increases significantly and after the macro is done and I save the file - the memory is still used (if I can trust Task Manager). After I close and reopen the file - all memory is, of course, freed and I can continue to work. I've reached a point where I can't process all the columns I need on a 32 bit Excel due to memory limit.
随着 Excel 使用的每一列内存显着增加，在宏完成并保存文件后 - 内存仍在使用（如果我可以信任任务管理器）。在我关闭并重新打开文件后 - 当然，所有内存都被释放了，我可以继续工作。由于内存限制，我无法在 32 位 Excel 上处理所需的所有列。
I was wondering, maybe there's some trick I could implement between the columns to make Excel forget whatever it keeps remembering? I don't really care if it will hit performance (in case it has cached anything useful for further calculations), I just need to keep memory usage from growing too huge to process. I googled a bit but all the advises are related to setting big objects to nothing - I don't really set much of anything to anything, just cycle through, so that probably won't apply to my case.
我想知道，也许我可以在列之间实施一些技巧来让 Excel 忘记它一直记得的东西？我真的不在乎它是否会影响性能（以防它缓存了任何对进一步计算有用的东西），我只需要防止内存使用量变得太大而无法处理。我用谷歌搜索了一下，但所有的建议都与将大对象设置为空有关 - 我并没有真正将任何东西设置为任何东西，只是循环，所以这可能不适用于我的情况。
Code is like this (don't have it on me right now, but it's a general simplified version of what is in it):
代码是这样的（我现在没有，但它是其中内容的一般简化版本）：
for i = 1 to 12
ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Font.Color = vbWhite
for m = 1 to x
ThisWorkbook.ActiveWorksheet.Cells(m, i).Formula = "'=SUMPRODUCT(blah)"
next m
ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value
ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value
ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Font.Color = vbBlack
next i
Basically, I color them white so I don't see the messy function text, add function, execute it, save as values, color text black. Important addition: I use SUMPRODUCT to sum some cells from closed files.
基本上，我将它们涂成白色，这样我就看不到凌乱的函数文本，添加函数，执行它，另存为值，将文本着色为黑色。 重要补充：我使用 SUMPRODUCT 对关闭文件中的一些单元格求和。
回答by
This isn't really enough code to help you improve the efficiency. I can point out a few tips for you now though.
这些代码不足以帮助您提高效率。不过，我现在可以为您指出一些提示。
The first thing to remember is to switch application screen updating to false at the start of the macro.
首先要记住的是在宏开始时将应用程序屏幕更新切换为 false。
Application.ScreenUpdating = False
Application.ScreenUpdating = False
then switch it back just before the end of the macro code
然后在宏代码结束之前将其切换回来
Application.ScreenUpdating = True
Application.ScreenUpdating = True
Changing the font color is unnecessary now. Your screen will be lockedfor refreshing while the macro is being executed so you will not be seeing the changes until the macro has finished working.
现在不需要更改字体颜色。在执行宏时，您的屏幕将被锁定以进行刷新，因此在宏完成工作之前您将看不到更改。
Consider, disabling Events too.
考虑一下，禁用事件。
Application.EnableEvents = Falseand Application.EnableEvents = truesame idea as with the screen updating.
Application.EnableEvents = False和Application.EnableEvents = true屏幕更新相同的想法。
The second thing is to make use of Evaluate() function. Please google it and read about how efficient that function can be.
第二件事是利用 Evaluate() 函数。请谷歌它并阅读有关该功能的效率。
Instead of putting a function into cell and then doing .Value = .Valueyou can just Evaluate()an expression and return the result straight to the cell which is much faster!
而不是将一个函数放入单元格然后执行.Value = .Value您可以只使用Evaluate()一个表达式并将结果直接返回到单元格，这要快得多！
So you could do something like
所以你可以做类似的事情
for i = 1 to 12
ThisWorkbook.ActiveWorksheet.Range(Cells(1,i),Cells(x,i)).Value = Evaluate("=SUM(blah)")
next i
I hope you get the idea.
我希望你能明白。
Here are a few links with tips on how to speed up your macro:
以下是一些链接，其中包含有关如何加速宏的提示：
http://msdn.microsoft.com/en-us/library/office/ff193019.aspx
http://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42-roper.aspx
http://xlvba.fr.yuku.com/topic/278/The-Evaluate-Method#.UmZP6Pmkp8s
回答by James Stott
I know this is an old question but a way that seems to work is saving the Workbook. Run ThisWorkbook.Saveevery once in a while and if you monitor EXCEL in the Task Manager you will notice that it clears up a significant amount of memory.
我知道这是一个老问题，但似乎可行的一种方法是保存工作簿。ThisWorkbook.Save每隔一段时间运行一次，如果您在任务管理器中监视 EXCEL，您会注意到它清除了大量内存。
It seems as though Excel may be caching certain function calls or something and saving clears this cache.
似乎 Excel 可能正在缓存某些函数调用或其他内容，并且保存会清除此缓存。
