Microsoft Office/VBA

Variables

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

---

Conditional Clause

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.