是否需要在 VBA 函数中将对象设置为空

Is there a need to set Objects to Nothing inside VBA Functions

提问by Ramon

I always read that it is recommended to set objects to nothing, once I am done with them. But I normally use them only in functions inside forms.

我总是读到，一旦我完成了它们，建议将对象设置为空。但我通常只在表单内的函数中使用它们。

Isn't the reference lost and memory released when the function scope is left, regardless of setting objects to Nothing?

是不是函数作用域离开时引用丢失，内存释放，不管对象设置为Nothing？

i.e. is it really necessary to do:

即真的有必要这样做：

Set db = Nothing
Set record_set = Nothing

回答by Tomalak

VB uses a so-called "reference counting" garbage collector.

VB 使用所谓的“引用计数”垃圾收集器。

Basically, the moment a variable goes out of scope, the reference counter on the referenced object is decremented. When you assign the object reference to another variable, the reference counter is incremented.

基本上，当一个变量超出范围时，被引用对象上的引用计数器就会递减。当您将对象引用分配给另一个变量时，引用计数器会增加。

When the counter reaches zero, the object is ready for garbage collection. The object resources will be released as soon as this happens. A function local variable will most likely reference an object whose reference count never goes higher than 1, so object resources will be released when the function ends.

当计数器达到零时，该对象已准备好进行垃圾回收。一旦发生这种情况，对象资源将被释放。函数局部变量很可能会引用引用计数永远不会高于 1 的对象，因此在函数结束时将释放对象资源。

Setting a variable to Nothingis the way to decrease the the reference counter explicitly.

将变量设置为Nothing显式减少引用计数器的方法。

For example, you read in a file, and set the file object variable to Nothingright after the ReadAll()call. The file handle will be released immediately, you can take your time process its contents.

例如，您读入一个文件，并Nothing在ReadAll()调用后将文件对象变量设置为right 。文件句柄将立即释放，您可以花时间处理其内容。

If you don't set to Nothing, the file handle might be open longer than absolutely necessary.

如果您没有设置为Nothing，则文件句柄的打开时间可能比绝对必要的要长。

If you are not in a "must unblock valuable resource" kind of situation, simply letting the variables go out of scope is okay.

如果您不是处于“必须解除对宝贵资源的阻塞”类型的情况，那么简单地让变量超出范围就可以了。

回答by BIBD

Garbage collection is rarely perfect. Even in .NET there are times where you are strongly encouraged to prompt the system to do garbage collection early.

垃圾收集很少是完美的。即使在 .NET 中，有时也强烈建议您提示系统尽早进行垃圾收集。

For this reason, I explicitly both closeand set to Nothingrecordsets when I'm done with them.

出于这个原因，当我完成它们时，我明确地关闭并设置为Nothing记录集。

回答by Shane Miskin

The very last line of the help topic for "Recordset.Close" in the Microsoft DAO help and the Access Developer Reference is this:

Microsoft DAO 帮助和 Access Developer Reference 中“ Recordset.Close”帮助主题的最后一行是这样的：

"An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing)."

“Close 方法的替代方法是将对象变量的值设置为 Nothing（Set dbsTemp = Nothing）。”

http://msdn.microsoft.com/en-us/library/bb243098.aspx

http://msdn.microsoft.com/en-us/library/bb243098.aspx

With that in mind, this articlefrom the Microsoft Knowledge Base entitled "How to prevent database bloat after you use Data Access Objects (DAO)", tells you that you should explicitly close if you don't want your databases to bloat. You'll notice that the article is a little vague about the details; the "Cause" section is unclear, almost to the point of being gibberish.

考虑到这一点，Microsoft 知识库中题为“使用数据访问对象 (DAO) 后如何防止数据库膨胀”的这篇文章告诉您，如果您不希望数据库膨胀，则应明确关闭。你会注意到这篇文章在细节上有点含糊；“原因”部分不清楚，几乎到了胡言乱语的地步。

http://support.microsoft.com/kb/289562

http://support.microsoft.com/kb/289562

SYMPTOMS: A Microsoft Access database has begun to bloat (or grow rapidly in size) after you implement Data Access Objects (DAO) to open a recordset.

CAUSE: If you do not release a recordset's memory each time that you loop through the recordset code, DAO may recompile, using more memory and increasing the size of the database.

MORE INFORMATION: When you create a Recordset (or a QueryDef) object in code, explicitly close the object when you are finished. Microsoft Access automatically closes Recordset and QueryDef objects under most circumstances. However, if you explicitly close the object in your code, you can avoid occasional instances when the object remains open.

症状：在您实现数据访问对象 (DAO) 以打开记录集后，Microsoft Access 数据库已开始膨胀（或大小迅速增长）。

原因：如果每次循环记录集代码时不释放记录集的内存，DAO 可能会重新编译，使用更多内存并增加数据库的大小。

更多信息：当您在代码中创建 Recordset（或 QueryDef）对象时，完成后显式关闭该对象。在大多数情况下，Microsoft Access 会自动关闭 Recordset 和 QueryDef 对象。但是，如果您在代码中显式关闭对象，则可以避免在对象保持打开状态时偶尔出现的情况。

Finally, let me add that I have been working with Access databases for 15 years, and I almost always let my locally declared recordset variables go out of scope without explicitly using the Close method. I have not done any testing on it, but it does not seem to matter.

最后，让我补充一点，我已经使用 Access 数据库工作了 15 年，而且我几乎总是让我本地声明的记录集变量超出范围而不显式使用 Close 方法。我还没有对其进行任何测试，但这似乎无关紧要。

回答by John Mo

References are supposed to be cleaned up when the variable goes out of scope. Presumably this has improved with later versions of the software, but it was at one time not reliable. I believe that it remains a good practice to explicitly set variables to "Nothing."

当变量超出范围时，应该清除引用。据推测，随着该软件的更高版本，这种情况有所改善，但它一度不可靠。我相信将变量显式设置为“Nothing”仍然是一个好习惯。

回答by david

When you are using ASP classic (server-side scripting), it is import to set all objects to nothing when you are through with them, because they do not go out of scope until the [virtual] server is shut down.

当您使用 ASP 经典（服务器端脚本）时，当您使用它们时，将所有对象设置为空是很重要的，因为它们在[虚拟]服务器关闭之前不会超出范围。

For this reason, all MS VB scripting examples always showed objects being closed and set to nothing. So that the script excerpts could be used in environments like ASP classic where the objects did not go out of scope.

出于这个原因，所有 MS VB 脚本示例总是显示对象被关闭并设置为空。这样脚本摘录就可以在 ASP 经典之类的环境中使用，其中对象不会超出范围。

There are, rarely, other situations where you wish to code long-running processes where the objects do not go out of scope, and you find yourself running out of physical memory if you do not explicitly release objects.

很少有其他情况，您希望对对象不超出范围的长时间运行的进程进行编码，并且如果您不显式释放对象，您会发现自己的物理内存不足。

If you find yourself coding ASP classic, or running processes in global scope for some other reason, then yes, you should explicitly release objects.

如果您发现自己在编写 ASP 经典代码，或者由于其他原因在全局范围内运行进程，那么是的，您应该明确释放对象。

回答by BobT

I usually always put this at the end of my procedures, or call a "CloseRecordSet" sub with it in if I'm using module level ones:

我通常总是把它放在我的程序的末尾，或者如果我使用模块级的，则调用一个“CloseRecordSet”子：

Private Sub Rawr()
On Error GoTo ErrorHandler

    'Procedural Code Here.

    ExitPoint:
        'Closes and Destroys RecordSet Objects.
        If Not Recset Is Nothing Then
            If Recset.State = 1 Then
                Recset.Close
                Conn.Close
            End If
            Set Recset = Nothing
            Set Conn = Nothing
        End If
        Exit Sub

    ErrorHandler:
        'Error Handling / Reporting Here.
        Resume ExitPoint
End Sub

That way however the procedure ends, (be it normally or due to an error) the objects are cleaned up and resources are free.

不管过程如何结束，（无论是正常情况还是由于错误），对象都会被清理并且资源是空闲的。

Doing it that way is quite safe in that it you can just slap it in and it will only do what is necessary in regards to closing, or destroying the recordset / connection object, incase it has already been closed (due to a runtime error or just closing it early as ya should, this just makes sure).

这样做是非常安全的，因为您可以将其插入，并且它只会执行关闭或销毁记录集/连接对象所需的操作，以防它已经关闭（由于运行时错误或只是尽可能早地关闭它，这只是确保）。

Its really not much hassle and its always best to clean up your objects when you're finished with them to free up resources immediately regardless of what happens in the program.

它真的没有太多麻烦，并且最好在完成对象后立即清理它们以立即释放资源，无论程序中发生什么。

回答by user2006617

Try this

尝试这个

If Not IsEmpty(vMyVariant) Then
    Erase vMyVariant
    vMyVariant = Empty
End If