Visual Basic Concepts

Private Collection Example: The House of Sticks

This topic continues the code example begun in "Public Collection Example: The House of Straw." You may want to read that topic before beginning this one.

A somewhat more robust way to link Employee objects with the SmallBusiness object is to make the Collection object private. For this example, you'll reuse the form and most of the code from the "Public Collection" example.

The Employee class module is unchanged. The SmallBusiness class module, however, gets a complete facelift. Replace the declaration of the public Collection object with the following declaration, and add the Sub and Function procedures described in the following paragraphs.

Option Explicit
Private mcolEmployees As New Collection

As before, the code that adds an employee does most of the work. (You can take the block of code between the dotted lines out of the cmdEmployeeAdd_Click event procedure in the previous example.)

The important change is that the Add method of the Collection object can no longer be called from any module in your program, because mcolEmployees is Private. You can only add an Employee object using the EmployeeAdd method, which correctly initializes the new object:

' Method of the SmallBusiness class.
Public Function EmployeeAdd(ByVal Name As String, _
ByVal Salary As Double) As Employee
   ' - - - - - - - - - - - - - - - -
   Dim empNew As New Employee
   Static intEmpNum As Integer
   ' Using With makes your code faster and more
   ' concise (.ID vs. empNew.ID).
   With empNew
      ' Generate a unique ID for the new employee.
      intEmpNum = intEmpNum + 1
      .ID = "E" & Format$(intEmpNum, "00000")
      .Name = Name
      .Salary = Salary
      ' Add the Employee object reference to the
      ' collection, using the ID property as the key.
      ' - - - - - - - - - - - - - - - -
      mcolEmployees.Add empNew, .ID
   End With
   ' Return a reference to the new Employee.
   Set EmployeeAdd = empNew
End Function

The EmployeeAdd method returns a reference to the newly added Employee object. This is a good practice, because as soon as you create an object you will most likely want to do something with it.

The EmployeeCount, EmployeeDelete, and Employees methods delegate to the corresponding methods of the Collection object. Delegation means that the Collection object does all the work.

' Methods of the SmallBusiness class.
Public Function EmployeeCount() As Long
   EmployeeCount = mcolEmployees.Count
End Function

Public Sub EmployeeDelete(ByVal Index As Variant)
   mcolEmployees.Remove Index
End Sub

Public Function Employees(ByVal Index As Variant) _
As Employee
   Set Employees = mcolEmployees.Item(Index)
End Function

Note   You can add extra functionality to these methods. For example, you can raise your own errors if an index is invalid.

The last method is Trouble. This method attempts to add an uninitialized Employee object to the collection. Any guesses what will happen?

' Method of the SmallBusiness class.
Public Sub Trouble()
   Dim x As New Employee
   mcolEmployees.Add x
End Sub

Changes to the Form

You'll have to make a few changes to the form module. You can use the same module-level declarations used for the previous example, and the Click event for the Close button is the same, but the other event procedures have changed — the Add button code is much shorter, while the code for the Delete and List Employees buttons have changed in small but significant ways:

Private Sub cmdEmployeeAdd_Click()
   sbMain.EmployeeAdd txtName.Text, txtSalary.Text
   txtName.Text = ""
   txtSalary.Text = ""
   cmdListEmployees.Value = True
End Sub

Private Sub cmdEmployeeDelete_Click()
   ' Check to make sure there's an employee selected.
   If lstEmployees.ListIndex > -1 Then
      ' The first six characters are the ID.
      sbMain.EmployeeDelete Left(lstEmployees.Text, 6)
   End If
   cmdListEmployees.Value = True
End Sub

Private Sub cmdListEmployees_Click()
   Dim lngCt As Long
   lstEmployees.Clear
   For lngCt = 1 To sbMain.EmployeeCount
      With sbMain.Employees(lngCt)
         lstEmployees.AddItem .ID & ", " & .Name _
         & ", " & .Salary
      End With
   Next
End Sub

But what's all this extra code in cmdListEmployees_Click? Unfortunately, in pursuit of robustness you've given up the ability to use For Each ... Next to iterate through the items in the collection, because the Collection object is now declared Private. If you try to code the following, you'll just get an error:

' Won't work, because Employees isn't really a
' collection.
For Each emp In sbMain.Employees

Fortunately, the EmployeeCount method can be used to delimit the iteration range.

The Trouble button changes a little, too, but it's still, well, Trouble.

Private Sub cmdTrouble_Click()
   sbMain.Trouble
End Sub

Run the project and experiment with the Add, Delete, and Refresh List buttons. Everything works just like before.

When you click the Trouble button, once again no error is generated. However, if you now click the Refresh List button, you can see that the uninitialized Employee object has somehow been added to the collection.

How can this be? By making the Collection object private, you protect it from all the code in your program that's outside the SmallBusiness object, but not from the code inside. The SmallBusiness object may be large and complex, with a great deal of code in it. For example, it will very likely have methods like CustomerAdd, ProductAdd, and so on.

A coding error, or the creation of a duplicate of the EmployeeAdd method, can still result in erroneous data — even invalid objects — being inserted into the collection, because the private variable is visible throughout the class module.

For More Information   This example is continued in "Creating Your Own Collection Class: The House of Bricks."