On Error Resume Next
    Dim WS As Worksheet
    Set WS = ActiveSheet
    ActiveSheet.Delete
    Err.Clear
    Debug.Print "WS Is Nothing: " & CStr(WS Is Nothing)
    Debug.Print WS.Name
    Debug.Print "Err: " & CStr(Err.Number), Err.Description

 

The code below will return TRUE if the Obj variable is connected to its target, or False if it is disconnected. It works by attempting to retrieve the Name property of the Obj variable. The Name property was chosen because nearly every object has a Name property.

If the result of getting the Name property is either C_ERR_NO_ERROR or C_ERR_DOES_NOT_SUPPORT_PROPERTY , we know that Obj is still connected to its referenced object. If we receive a C_ERR_OBJECT_REQUIRED error, we know the object has been disconnected from its target. We will receive a C_ERR_OBJECT_VARIABLE_NOT_SET value if the object reference is Nothing. In this case, we treat it as if it were disconnected. We may receive the generic C_ERR_APPLICATION_OR_OBJECT_ERROR. In this case, we do not know whether the object is connected or disconnected. Therefore, we do further testing by attempting to access the Parent property and examine the error code. We use the same tests here as we did for the Name property earlier in the procedure.

The complete VBA code is shown below. It will work in any version of Office, 97 or later. It will also work in Visual Basic 6. The code may be used in any application that supports VBA. There is nothing specific to Excel in the code.

Public Const C_ERR_NO_ERROR = 0&
Public Const C_ERR_OBJECT_VARIABLE_NOT_SET = 91&
Public Const C_ERR_OBJECT_REQUIRED = 424&
Public Const C_ERR_DOES_NOT_SUPPORT_PROPERTY = 438&
Public Const C_ERR_APPLICATION_OR_OBJECT_ERROR = 1004&

Public Function IsObjectConnected(Obj As Object) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsObjectConnected
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' http://www.cpearson.com/excel/ConnectedObject.htm
'
' This procedure determines whether an object type variable is still connected
' to its target. An object variable can become disconnected from its target
' when the target object is destroyed. For example, the following code will
' raise an automation error because the target of the variable WS had been
' destoryed.
'
' Dim WS As Worksheet
' Set WS = ActiveSheet
' ActiveSheet.Delete
' Debug.Print WS.Name
'
' This code will fail on the "Debug.Print WS.Name" because the worksheet to
' which WS referenced was destoryed. It is important to note that WS will NOT
' be set to Nothing when the worksheet is deleted.
'
' This procedure attempts to call the Name method of the Obj variable and
' then tests the result of Err.Number. We'll get the following error
' numbers:
' C_ERR_NO_ERROR
' No error occurred. We successfully retrieved the Name
' property. This indicates Obj is still connected to its
' target. Return TRUE.
'
' C_ERR_OBJECT_VARIABLE_NOT_SET
' We'll get this error if the Obj variable has been
' disconnected from its target. Return FALSE.
'
' C_ERR_DOES_NOT_SUPPORT_PROPERTY
' We'll get this error if the Obj variable does not have
' a name property. In this case, the Obj variable is still
' connected to its target. Return True.
'
' C_ERR_APPLICATION_OR_OBJECT_ERROR
' This is a generic error message. If we get this error, we need to
' do further testing to get the connected state.
'
' These are the only values that Err.Number should return. If we receive
' another error, err on the side of caution and return False.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim NameProp As String
Dim ParentObj As Object

On Error Resume Next
Err.Clear
NameProp = Obj.Name



Select Case Err.Number
    Case C_ERR_NO_ERROR
        ' We'll get this result if we retrieve the Name property of Obj.
        ' Obj is connected.
        IsObjectConnected = True

    Case C_ERR_DOES_NOT_SUPPORT_PROPERTY
        ' We'll get this result if Obj does not have a name property. This
        ' still indicates that Obj is connected.
        IsObjectConnected = True

    Case C_ERR_OBJECT_VARIABLE_NOT_SET
        ' This indicates that Obj was Nothing, which we will treat
        ' as disconnected. If you want Nothing to indicate connected,
        ' test the variable Is Nothing before calling this procedure.
        IsObjectConnected = False

    Case C_ERR_OBJECT_REQUIRED
        ' This indicates the object is disconnected. Return False
         IsObjectConnected = False

    Case C_ERR_APPLICATION_OR_OBJECT_ERROR
        ' This error may occur when the object is either connected or disconnected.
        ' In this case, attempt to get the Parent property of the object.
        Err.Clear
        Set ParentObj = Obj.Parent
        Select Case Err.Number
            Case C_ERR_NO_ERROR
                ' we succuesfully got the parent object. Obj is connected.
                IsObjectConnected = True
            Case C_ERR_DOES_NOT_SUPPORT_PROPERTY
                ' we'll get this error if Obj does not have a Parent property. This
                ' still indicates that Obj is connected.
                IsObjectConnected = True
            Case C_ERR_OBJECT_VARIABLE_NOT_SET
                ' we'll get this error if Obj is disconnected
                IsObjectConnected = False
            Case Else
                IsObjectConnected = False
        End Select

    Case Else
        ' we should never get here, but return False if we do
        IsObjectConnected = False
End Select

End Function