README.md

Using Collections

So you have heard of Collections and may have even used them a few times. An unassuming word…collections. It doesn’t inspire much excitement in most circles, yet there are very few single words that represent such a powerful element of programming as they do. This article will outline some of the general and specific uses of collections in Visual Basic and Access. After reading it, you will hopefully have a higher respect for this often overlooked aspect of VB.

Just what are collections anyway? Well, they are just what their name implies. They are a logical grouping of objects in Visual Basic. The Visual Basic object model consists of objects and collections of objects. For example, you have a "Forms" collection which contains all of the forms in the application. Each form also has an Objects collection which contains all of the objects that are contained in the form. On the Access side, there is a TableDefs collection which contains all of the tables in your database, and each of these TableDefs contains a Fields collection. As you may have guessed, the Fields collections contains all of the fields that exists in each table.

What does this mean to the average coder? Where is the payoff for all of this organization? You are about to find out. Using the Forms example above, consider this problem:

For some strange reason, your client wants you to create a function that will show all of the forms in the entire application at once. You could so something like this:

Function ShowForms

End Function

This can be tedious if the application has 35 or so forms. And to make matters worse, they keep adding and removing forms, so you have to keep coming back and changing this function to keep from causing a compile error "Object required" every time one changes. What a pain. You could solve this entire problem by either finding a new job, talking some sense into your client (like THAT would work!) or by using this code:

Function ShowForms

End Function

Now the client can add, remove, and change the name of as many forms as he likes without effecting the operation of the application. By looping through (or "iterating") the collection, you have made you code immune to the whims of your client. Lets look at how this works by examining each statement.

Dim frmForm as Form

This statement creates an object variable that will hold each form object as we iterate through the Forms collection. It is basically a temporary storage space for a form object.

For each frmForm in Forms

If you haven’t yet started using the For Each … Next statement, you need to get with the program. It works just like the old Basic/VB For…Next, but it does it with objects instead of variables. This is the heart of working with collections.

FrmForm.Show

This magic little statement takes the place of all of those other .show statements in the prior example. With each pass through the For Each…Next loop, the object variable frmForm is reassigned to contain the current form object. So when you say "frmForm.Show", VB interprets it as frmSplash.Show, frmMainMenu.Show, or whatever form is currently being proccessed.

Next frmForm

Wraps up the loop. This will return execution back to the For Each… statement above it. Code execution will pass through this loop once for each form in the Forms collection.

Now that you understand the basic logic of iterating through collections, you can see how this could be put to practical use. To cascade all open forms on the screen, you could modify the code to this:

Function CascadeForms

Dim intTop As Integer

Dim intLeft As Integer

Dim frmForm as Form

For each frmForm in Forms

If frmForm.Visible = True Then

Next frmForm

End Function

This code will place forms over each other in cascade style, starting at coordinates 100,100 and moving down and to the right in increments of 100. It took almost as many letters to explain it as it does to write it!

The thing to note in this example is that ALL of the forms’ properties and functions are available as you loop though the collection. For example, you could have changed the caption of each one or the border style of only certain ones.

OK…enough about forms. Where else can these really cool collections be used? How about within a form? This code will print a list of all objects on a form to the debug window:

Function ShowObjects

End Function

This will work whether you have one or 1000 objects on a form…although I wouldn’t recommend putting that many controls on a single form…but hey, it would work with it!

The thing to note in the above code (besides the obvious compactness of it) is the use of the Me keyword. This is important. Me translates in VB to "Whichever form this code is running in". It is used to reference the Objects collection for the current form. This means that you could copy this code from one form and paste it directly into another and it would work with NO code changes. Here is a more practical example of the objects collection:

You have a form with 25 text boxes on it and you want to automatically center them when the user resizes the form. You could write some pretty painful code to do this, or you could do this:

Private Sub Form_Resize()

End sub

Have Fun!

M@