Multiple Apps

Set multiple variables to Nothing

Ease of Use

Easy

Version tested with

2000 

Submitted by:

mvidas

Description:

Sets multiple object variables to Nothing, clearing the memory used for them. 

Discussion:

It is good coding practice to set all object variables to Nothing at the end of your code. With big projects, you can often take up many lines at the bottom of your subroutines and functions setting the variables to nothing. This function just lets you set them to Nothing using only one line for many variables, saving you room in each routine. This would be used more for aesthetics than anything else. 

Code:

instructions for use

			

Option Explicit 'Gets an array of variables, loops through them all, setting them to nothing. ' 'If something was passed to it that cannot be set to Nothing, the function ' continues, but sends a 'False' value back for the function. Most people would ' not need to check that, but written in for the rare cases where it would be useful. 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 'if not object/variant variable 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() 'Dimension commonly used object variables 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 'Dimension variables to store status Dim BeforeSetting As String, AfterSetting As String, AfterClearing As String 'Get 'Nothing' status before setting variables 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) 'Late bind these variables 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") 'close these applications, note how variable is not "Nothing" until setting to nothing WB.Close False XL.Quit IE.Quit 'Get 'Nothing' status after setting variables, but before clearing them 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) 'Clear multiple variables in one line SetToNothing IE, RegEx, oXMLHTTP, vConn, vRS, fso, dict, XL, WB, WS, RG 'Get 'Nothing' status after clearing variables 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 status of variables during each point MsgBox BeforeSetting & vbCrLf & vbCrLf & AfterSetting & vbCrLf & vbCrLf & _ AfterClearing, Title:="Are these variables nothing?" End Sub

How to use:

  1. Press Alt-F11 from Excel to open the VBA Editor
  2. Go to Insert, then Module
  3. Paste the "SetToNothing" function into your project
  4. Send your object variables to the SetToNothing function while coding your project
 

Test the code:

  1. Press Alt-F11 from Excel to open the VBA Editor
  2. Go to Insert, then Module
  3. Paste the full code above into the new standard code module
  4. Run the "SetToNothingExample" subroutine. It will show you the status of many different object variables at various points of the code in a message box at the end of the subroutine.
 

Sample File:

SetToNothingExampleBook.zip 8.61KB 

Approved by mdmackillop


This entry has been viewed 165 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express