何时应终止 Excel VBA 变量或将其设置为 Nothing？

When should an Excel VBA variable be killed or set to Nothing?

提问by Instant Breakfast

I've been teaching myself Excel VBA over the last two years, and I have the idea that it is sometimes appropriate to dispose of variables at the end of a code segment. For example, I've seen it done in this bit adapted from Ron de Bruin's code for transferring Excel to HTML:

在过去的两年里，我一直在自学 Excel VBA，我认为有时在代码段的末尾处理变量是合适的。例如，我已经看到它在改编自Ron de Bruin 的用于将 Excel 转换为 HTML 的代码中完成的：

Function SaveContentToHTML (Rng as Range)

        Dim FileForHTMLStorage As Object
        Dim TextStreamOfHTML As Object
        Dim TemporaryFileLocation As String
        Dim TemporaryWorkbook As Workbook

...

        TemporaryWorkbook.Close savechanges:=False
        Kill TemporaryFileLocation
        Set TextStreamOfHTML = Nothing
        Set FileForHTMLStorage = Nothing
        Set TemporaryWorkbook = Nothing

End Function

I've done some searching on this and found very little beyond how to do it, and in one forum post a statement that no local variables need to be cleared, since they cease to exist at End Sub. I'm guessing, based on the code above, that may not be true at End Function, or in other circumstances I haven't encountered.

我已经对此进行了一些搜索，发现除了如何做之外几乎没有发现，并且在一个论坛中发布了一个声明，指出不需要清除局部变量，因为它们在End Sub. 我猜测，根据上面的代码，在End Function或其他我没有遇到过的情况下可能不是这样。

So my question boils down to this:

所以我的问题归结为：

• Is there somewhere on the web that explains the when and why for variable cleanup, and I just have not found it?

• 网上有什么地方可以解释变量清理的时间和原因，而我只是没有找到它？

And if not can someone here please explain...

如果没有，请在这里有人解释一下......

• When is variable cleanup for Excel VBA necessary and when it is not?
• And more specifically... Are there specific variable uses (public variables? Function-defined variables?) that remain loaded in memory for longer than subs do, and therefor could cause trouble if I don't clean up after myself?

• 什么时候需要对 Excel VBA 进行变量清理，什么时候不需要？
• 更具体地说......是否有特定的变量使用（公共变量？函数定义的变量？）在内存中的加载时间比 subs 更长，因此如果我不自己清理可能会导致麻烦？

回答by GSerg

VB6/VBA uses deterministic approach to destoying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.

VB6/VBA 使用确定性方法来销毁对象。每个对象存储对自身的引用数。当数量达到零时，对象被销毁。

Object variables are guaranteed to be cleaned (set to Nothing) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required.

对象变量在Nothing超出范围时保证被清除（设置为），这会减少它们各自对象中的引用计数器。无需手动操作。

There are only two cases when you want an explicit cleanup:

当您想要显式清理时，只有两种情况：

1. When you want an object to be destroyed before its variable goes out of scope (e.g., your procedure is going to take long time to execute, and the object holds a resource, so you want to destroy the object as soon as possible to release the resource).

2. When you have a circular reference between two or more objects.

   If objectAstores a references to objectB, and objectBstores a reference to objectA, the two objects will never get destroyed unless you brake the chain by explicitly setting objectA.ReferenceToB = Nothingor objectB.ReferenceToA = Nothing.

1. 当您希望在其变量超出范围之前销毁对象（例如，您的过程将需要很长时间才能执行，并且该对象持有资源，因此您希望尽快销毁该对象以释放资源）。

2. 当您在两个或多个对象之间有循环引用时。

   如果objectA存储对 的引用objectB并objectB存储对 的引用objectA，则除非您通过显式设置objectA.ReferenceToB = Nothingor来中断链，否则这两个对象将永远不会被销毁objectB.ReferenceToA = Nothing。

The code snippet you show is wrong. No manual cleanup is required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code.

您显示的代码片段是错误的。无需手动清理。进行手动清理甚至是有害的，因为它会让您误以为更正确的代码。

If you have a variable at a class level, it will be cleaned/destroyed when the class instance is destructed. You can destroy it earlier if you want (see item 1.).

如果您在类级别有一个变量，则当类实例被破坏时，它将被清除/销毁。如果需要，您可以提前销毁它（请参阅项目1.）。

If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item 1.).

如果你有一个模块级别的变量，它会在你的程序退出时被清除/销毁（或者，在 VBA 的情况下，当 VBA 项目被重置时）。如果需要，您可以提前销毁它（请参阅项目1.）。

Access level of a variable (public vs. private) does not affect its life time.

变量的访问级别（公共与私有）不影响其生命周期。

回答by SheetJS

VBA uses a garbage collector which is implemented by reference counting.

VBA 使用通过引用计数实现的垃圾收集器。

There can be multiple references to a given object (for example, Dim aw = ActiveWorkbookcreates a new reference to Active Workbook), so the garbage collector only cleans up an object when it is clear that there are no other references. Setting to Nothing is an explicit way of decrementing the reference count. The count is implicitly decremented when you exit scope.

对给定对象可以有多个引用（例如，Dim aw = ActiveWorkbook创建对 Active Workbook 的新引用），因此垃圾收集器仅在明确没有其他引用时才清理对象。设置为 Nothing 是减少引用计数的显式方法。当您退出作用域时，计数会隐式递减。

Strictly speaking, in modern Excel versions (2010+) setting to Nothing isn't necessary, but there were issues with older versions of Excel (for which the workaround was to explicitly set)

严格来说，在现代 Excel 版本 (2010+) 中不需要设置为 Nothing，但旧版本的 Excel 存在问题（解决方法是明确设置）

回答by Tomasz

I have at least one situation where the data is not automatically cleaned up, which would eventually lead to "Out of Memory" errors. In a UserForm I had:

我至少有一种数据没有自动清理的情况，这最终会导致“内存不足”错误。在用户表单中，我有：

Public mainPicture As StdPicture
...
mainPicture = LoadPicture(PAGE_FILE)

When UserForm was destroyed (after Unload Me) the memory allocated for the data loaded in the mainPicturewas not being de-allocated. I had to add an explicit

当 UserForm 被销毁时（之后Unload Me），为加载在 中的数据分配的内存mainPicture没有被取消分配。我必须添加一个明确的

mainPicture = Nothing

in the terminate event.

在终止事件中。