VBA: Check Workbook Object is not empty

Question

I have a workbook object previously created in code, all good.

However, for proper error handling, if the Workbook for whatever reasons gets closed, even though unlikely, I want to check this before further code execution.

The watch window shows the workbook after closure via direct entry (for simulation) with <no variables>:

However, nothing seems to work to check the workbook object is empty:

Debug.Print IsObject(dWorkbook) ' returns true > expected as obj as such still exists
Debug.Print IsNull(dWorkbook)   ' returns false
Debug.Print IsEmpty(dWorkbook)  ' returns false
Debug.Print TypeName(dWorkbook) ' returns object > expected

If dWorkbook Is Nothing Then    ' steps into Else
    Debug.Print "is Nothing"
Else
    Debug.Print "something"
End If

Thanks for a suggestion here to find a way to the check the workbook object has content.

Answer 1

Here is one way.

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

If closing the other workbook manually is stopping the code, then use this method. Just add one Userform to your workbook so that you will be able to close the other workbook manually.

Please note that adding the userform is only so that you can test the code manually. In a real scenario, you do not have to add a 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