- Very useful for when you have a "ton" of object variables and want to one line a "Set ... = Nothing" 
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
- Since VBA never did have an automatic garbage disposal system for object, at least a clean way of doing so, i create this for situations where i had a high usage of objects in a very small amount of coding "space". Instead of doing LBAL of SetNothing assignments, i developed a sub that i can pass an "infinite" number of object variables to and have it do the SetNothing.
'@vObjects - Variant object array 'Note: ' - This should be use for when you want close, if possible, and then set the object to ' Nothing. Public Sub GC(ParamArray vObjects() As Variant) 'RecordsetClose vObjects SetToNothing vObjects End Sub
- For those of us that have since gotten used to the .Net framework and are forced to regress back down to VBA, this is a nice little snippet that is useful in every situation. 
'Returns a Boolean value of whether the '@val - String value, Text to verify 'Notes: ' - This method is very useful in If Statements where you want to ' verify that a String value is not Empty or Nothing. Function IsNullOrEmpty(val As Variant) As Boolean 'First conditional validates for Nothing 'Second condition validates for an Empty String situation "" or " " Dim ret As Boolean: ret = False If IsMissing(val) Then ret = True ElseIf (val Is Nothing) Then ret = True ElseIf (val & vbNullString = vbNullString) Then ret = True ElseIf (Len(Trim(val)) <= 0) Then ret = True End If IsNullOrEmpty = ret End Function
- Since @val is being passed a Variant, may have to check if @val is an Object or Value variable. If it is an Object, will need to remove the ElseIf segment "(val Is Nothing)". If you do not, then it will fail on validation at that point. Next version will include a validation for this.
- ↑ mvidas (2000). "SetToNothing". VBA Express. http://www.vbaexpress.com/kb/getarticle.php?kb_id=793.
- ↑ User:Goldbishop