Option Explicit
Function SetToNothing(ParamArray vObjects() As Variant) As Boolean
Dim i As Long
On Error Resume Next
SetToNothing = True
For i = LBound(vObjects) To UBound(vObjects)
Set vObjects(i) = Nothing
If Err.Number <> 0 Then SetToNothing = False
Next i
End Function
Function IsNothing(vObject) As Boolean
On Error Resume Next
IsNothing = vObject Is Nothing
If Err.Number <> 0 Then IsNothing = True
End Function
Sub SetToNothingExample()
Dim IE As Object, RegEx As Object, oXMLHTTP As Object, vConn As Object, vRS As Object
Dim fso As Object, dict As Object
Dim XL As Object, WB As Object, WS As Object, RG As Object
Dim BeforeSetting As String, AfterSetting As String, AfterClearing As String
BeforeSetting = "Before setting variables" & vbCrLf & _
"IE: " & IsNothing(IE) & vbCrLf & _
"RegEx: " & IsNothing(RegEx) & vbCrLf & _
"oXMLHTTP: " & IsNothing(oXMLHTTP) & vbCrLf & _
"vConn: " & IsNothing(vConn) & vbCrLf & _
"vRS: " & IsNothing(vRS) & vbCrLf & _
"fso: " & IsNothing(fso) & vbCrLf & _
"dict: " & IsNothing(dict) & vbCrLf & _
"XL: " & IsNothing(XL) & vbCrLf & _
"WB: " & IsNothing(WB) & vbCrLf & _
"WS: " & IsNothing(WS) & vbCrLf & _
"RG: " & IsNothing(RG)
Set IE = CreateObject("internetexplorer.application")
Set RegEx = CreateObject("vbscript.regexp")
Set oXMLHTTP = CreateObject("microsoft.xmlhttp")
Set vConn = CreateObject("adodb.connection")
Set vRS = CreateObject("adodb.recordset")
Set fso = CreateObject("scripting.filesystemobject")
Set dict = CreateObject("scripting.dictionary")
Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Add(1)
Set WS = WB.Worksheets(1)
Set RG = WS.Range("A1")
WB.Close False
XL.Quit
IE.Quit
AfterSetting = "After setting variables" & vbCrLf & _
"IE: " & IsNothing(IE) & vbCrLf & _
"RegEx: " & IsNothing(RegEx) & vbCrLf & _
"oXMLHTTP: " & IsNothing(oXMLHTTP) & vbCrLf & _
"vConn: " & IsNothing(vConn) & vbCrLf & _
"vRS: " & IsNothing(vRS) & vbCrLf & _
"fso: " & IsNothing(fso) & vbCrLf & _
"dict: " & IsNothing(dict) & vbCrLf & _
"XL: " & IsNothing(XL) & vbCrLf & _
"WB: " & IsNothing(WB) & vbCrLf & _
"WS: " & IsNothing(WS) & vbCrLf & _
"RG: " & IsNothing(RG)
SetToNothing IE, RegEx, oXMLHTTP, vConn, vRS, fso, dict, XL, WB, WS, RG
AfterClearing = "After clearing variables" & vbCrLf & _
"IE: " & IsNothing(IE) & vbCrLf & _
"RegEx: " & IsNothing(RegEx) & vbCrLf & _
"oXMLHTTP: " & IsNothing(oXMLHTTP) & vbCrLf & _
"vConn: " & IsNothing(vConn) & vbCrLf & _
"vRS: " & IsNothing(vRS) & vbCrLf & _
"fso: " & IsNothing(fso) & vbCrLf & _
"dict: " & IsNothing(dict) & vbCrLf & _
"XL: " & IsNothing(XL) & vbCrLf & _
"WB: " & IsNothing(WB) & vbCrLf & _
"WS: " & IsNothing(WS) & vbCrLf & _
"RG: " & IsNothing(RG)
MsgBox BeforeSetting & vbCrLf & vbCrLf & AfterSetting & vbCrLf & vbCrLf & _
AfterClearing, Title:="Are these variables nothing?"
End Sub
|