|
When you use object-type variables, those variables can become
disconnected from the object they reference when that reference object is
destroyed. For example, in the following code, the variable WS becomes
disconnected from its target when the worksheet is deleted.
Dim WS As
Worksheet
Set WS = ActiveSheet
ActiveSheet.Delete
Debug.Print WS.Name
When you reference the WS
variable in the Debug.Print
statement, you'll receive an automation error because the
WS
variable no longer references a valid object. It is very important to
remember the WS
variable is not set to Nothing when its target is destroyed.
This is a common misconception. You can test it with the code
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
|
|