这里有一个方法。

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim wsCount As Long
    
    Set wb = Workbooks.Add
    
    '~~> Put a breakpoint here and then close the workbook manually
    On Error Resume Next
    '~~> Check if there are worksheets
    wsCount = wb.Sheets.Count
    On Error GoTo 0
    
    If wsCount = 0 Then MsgBox "WB is Lost" Else MsgBox "WB is Not Lost"
End Sub

字符串
如果手动关闭其他工作簿会停止代码，则使用此方法。只需向工作簿添加一个Userform，以便您能够手动关闭其他工作簿。

*请注意 * 添加userform只是为了手动测试代码，在真实的场景中，不需要添加userform。

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim wsCount As Long
    
    Set wb = Workbooks.Add
    
    ThisWorkbook.Activate
    UserForm1.Show vbModeless
    
    '~~> Put a breakpoint here and then close the workbook manually
    On Error Resume Next
    '~~> Check if there are worksheets
    wsCount = wb.Sheets.Count
    On Error GoTo 0
    
    If wsCount = 0 Then MsgBox "WB is Lost" Else MsgBox "WB is Not Lost"
End Sub

型

检查工作簿是否超出范围（“意外”关闭）

编辑

• 您可以使用TypeName function来获取变量的各种“状态”：

Select Case TypeName(dWorkbook)
    Case "Nothing"
        MsgBox "The workbook is not set.", vbExclamation
    Case "Workbook"
        MsgBox "The workbook is set.", vbInformation
    Case "Object" ' or 'Case Else'
        MsgBox "The workbook lost scope.", vbCritical
End Select

字符串

• 下面应该是前一种的证明。

Sub SimpleTest()
    
    Dim wb As Workbook
        MsgBox "My type name is """ & TypeName(wb) & """!" _
            & vbLf & "I'm not yet a ""Workbook"".", vbExclamation
    
    Set wb = Workbooks.Add
        MsgBox "My type name is """ & TypeName(wb) & """." _
            & vbLf & "I am a ""Workbook"".", vbInformation
    
    wb.Close False
        MsgBox "My type name is """ & TypeName(wb) & """!" _
            & vbLf & "I'm not a ""Workbook"" anymore.", vbCritical

End Sub

型
也就是说，对于那些欣赏微妙差异的人来说：

• 工作簿已设置，但失去了作用域。如果工作簿在作用域中，即使变量为Nothing，意味着工作簿未设置，它也将返回False。

Function DidWorkbookGetOutOfScope(ByVal wb As Workbook) As Boolean
    If Not wb Is Nothing Then DidWorkbookGetOutOfScope = TypeName(wb) <> "Workbook"
End Function

型
或者，更实际一点：

• 工作簿已设置。它不是Nothing，并且没有超出作用域。

Function IsWorkbookInScope(ByVal wb As Workbook) As Boolean
    IsWorkbookInScope = TypeName(wb) = "Workbook"
End Function

型

初始发布

• 或者尝试以下解决方法。
  测试（用法）

Sub Test()
    
    Dim wb As Workbook: Set wb = Workbooks.Add
    
    wb.Close False
    
    If wb Is Nothing Then
        MsgBox "It's nothing.", vbExclamation
    Else
        If HasObjectName(wb) Then
            MsgBox "It's still open.", vbInformation
        Else
            MsgBox "It got closed!", vbCritical
        End If
    End If
    
End Sub

型

函数

Function HasObjectName(ByVal obj As Object) As Boolean
    On Error Resume Next
        HasObjectName = Len(obj.Name) > 0
    On Error GoTo 0
End Function

型

由于工作簿没有被你的代码关闭，我想变量没有被重置。你可以使用这样的check-Routine：

Public Function isWbValid(wb As Workbook) As Boolean
   On Error Resume Next
   Dim c As String
   c = wb.Windows(1).Caption
   isWbValid = Err = 0
   On Error GoTo 0
End Function

字符串
然后你可以像这样使用它：debug.print isWbValid(dWorkbook)