When do I need to dispose objects in VBA

Question

While looking at this code (most of which has been removed for simplification of this question), I started to wonder if I need to dispose of the collection or class that I used.

Option Explicit    
Private terminals As Collection

Sub BuildTerminalSummary()
  Dim terminal As clsTerminal
  Call LoadTerminals
  For Each terminal in terminals
  ...Do work here
  Next terminal

  Set terminals = Nothing

End Sub

Private Sub LoadTerminals()
  Do
    Set terminal = New clsTerminal

    ...Do work here

    'Add terminal to terminals collection
    terminals.Add terminal, key

  Loop Until endCondition
End Sub

When dealing with VBA, when should I dispose of an object (if ever)?

Answer 1

Looks to me as though you've got the scope for the terminals collection in the wrong place. Have LoadTerminals return the create terminals collection:-

Option Explicit    

Sub BuildTerminalSummary()

  Dim terminals As Collection
  Dim terminal As clsTerminal

  Set terminals = LoadTerminals

  For Each terminal in terminals
  ...Do work here
  Next terminal

End Sub

Private Function LoadTerminals() As Collection
  Dim terminals As Collection : Set terminals = New Collection
  Do
    Set terminal = New clsTerminal

    '' # ...Do work here

    terminals.Add terminal, key

  Loop Until endCondition

  Set LoadTerminals = terminals

End Function

As long as you have variables there is no need to especially "dispose" of them (which I guess you mean assigning Nothing to the variable holding a reference to them.