如何在VBA中判断文件夹查找函数是否返回Nothing？

我编写了一段VBA代码，调用sf_HEM_find_folder函数查找名为“MyFolder”的文件夹，该函数未找到目标文件夹时会返回Nothing。我需要判断函数是否返回Nothing，但发现使用IsObject判断时，即便返回Nothing，IsObject仍返回True，尝试将变量声明为Object或Folder类型均无改善。

相关代码如下：

调用代码

Dim sf_folder As Object
Dim Err_text As String
 
Set sf_folder = sf_HEM_find_folder("MyFolder")
 
If Not IsObject(sf_folder) Then
  Err_text = "Folder " & sf_text & " not found"
  GoTo ErrorHandler
End If
 
VbMsgBoxResult = MsgBox "MyFolder found", vbOK

函数代码

Function sf_HEM_find_folder(ByVal sf_folder_name As String) As Object

'The calling code must ensure that the current folder is email
'returns nothing if folder not found

  Dim sf_folder As Object
  Dim sf_f0 As Folder

  Set sf_HEM_find_folder = Nothing
  
  Set sf_f0 = Application.ActiveExplorer.CurrentFolder

' Iterate through first group sub-folders of the inbox to find the one specified in sf_folder_name

  For Each sf_folder In sf_f0.Folders
' * Until the end of list
'   Find the folder by name

    If sf_folder.Name = sf_folder_name Then
      sf_HEM_find_folder = sf_folder
      Exit For
    End If
  Next sf_folder
  
End Function

问题原因及解决方法 #

1. IsObject的作用误区 #

IsObject函数的作用是判断变量的类型是否为对象类型，而非判断对象是否被实例化（是否为Nothing）。哪怕对象变量的值是Nothing，它的类型依然是Object，所以IsObject(sf_folder)始终会返回True，这是该函数的设计逻辑，并非错误。

2. 正确判断对象是否为Nothing的方式 #

判断对象变量是否为Nothing，应该使用Is Nothing语句，语法如下：

If sf_folder Is Nothing Then
    ' 对象未找到的处理逻辑
End If

3. 函数代码中的隐藏错误 #

原函数中找到目标文件夹时的赋值语句缺少Set关键字，对象类型的赋值必须使用Set，否则会触发运行时错误。修正后的赋值代码应为：

If sf_folder.Name = sf_folder_name Then
  Set sf_HEM_find_folder = sf_folder
  Exit For
End If

修正后的完整代码 #

调用代码

Dim sf_folder As Object
Dim Err_text As String
Dim sf_text As String ' 补充定义原代码中未声明的变量
sf_text = "MyFolder"
 
Set sf_folder = sf_HEM_find_folder(sf_text)
 
If sf_folder Is Nothing Then
  Err_text = "Folder " & sf_text & " not found"
  GoTo ErrorHandler
End If
 
MsgBox "MyFolder found", vbOK
Exit Sub ' 正常流程结束，避免进入错误处理

ErrorHandler:
MsgBox Err_text, vbExclamation

函数代码

Function sf_HEM_find_folder(ByVal sf_folder_name As String) As Object

'The calling code must ensure that the current folder is email
'returns nothing if folder not found

  Dim sf_folder As Object
  Dim sf_f0 As Folder

  Set sf_HEM_find_folder = Nothing
  
  Set sf_f0 = Application.ActiveExplorer.CurrentFolder

' Iterate through first group sub-folders of the inbox to find the one specified in sf_folder_name

  For Each sf_folder In sf_f0.Folders
    If sf_folder.Name = sf_folder_name Then
      Set sf_HEM_find_folder = sf_folder
      Exit For
    End If
  Next sf_folder
  
End Function

内容的提问来源于stack exchange，提问作者theHeatingEngineer