Microsoft Office/VBA/General

Set to Null

 * 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

Garbage Collection

 * 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.
 * Uses:
 * Set To Null

'@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

VBA - IsNullOrEmpty

 * 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
 * Note: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.