Jun 18, 2015

Unlike the traditional For Next loops, the For Each loops do not iterate a ‘set’ number of times. Simply put, this looping structure allows you to iterate through a collection of objects without knowing or caring, or needing to specify, which item in the collection you are currently looking at.

In order to be able to use these loops, you do need to know a couple of things.

Firstly, you need to know what the collection is called and generally speaking, most collection objects have a plural in their name e.g. worksheets, workbooks. Some collections do not have a plural e.g. UsedRange, Selection, however, these are the rarity.

Secondly, and most importantly, you need to know what type of object the collection contains.

The basic syntax of the For Each Loop is as follows:

For Each In ‘Code goes here' Loop

Where item is a variable whose type is determined by what the collection contains. If you are unsure of what the collection contains, you can always use the Object Browser (F2) or Help (F1) to check this.

The variable needs to be of the correct type because as we iterate through the collection, it will act as a placeholder and be pointed to each actual object in that collection. If the type is not correct, you will receive a ‘Type Mismatch’ error when running through your loop.

Consider the following code:

Dim wksTemp as Worksheet For Each wksTemp in Worksheets MsgBox wksTemp.Name Loop

By declaring our variable (wksTemp) as being of type ‘Worksheet,’ we can then use it as a placeholder for each item in the collection. When we want to then call methods or set/get properties for that item in the collection, we use its name as our reference.

Sometimes, collections can contain a number of different subtypes of objects. An example of this would be the controls collection on a userform. It may contain TextBox, CommandButton, OptionButton objects etc.

If we then tried the following code:

Dim ctlTemp as Control For Each ctlTemp in frmStuff.Controls Caption = UCASE(ctlTemp.Caption) Loop

We would probably receive an error stating ‘Object does not support this property or method.’ This would be because our variable was declared as being of type ‘Control’ and we only have common properties available. The Caption property we referred to isn't available for all Control objects.

The following code wouldn’t work either:

Dim ctlTemp as Label For Each ctlTemp in frmStuff.Controls Caption = UCASE(ctlTemp.Caption) Loop

This is because the Controls collection contains more than just Label objects, and therefore we would get the type mismatch error we discussed earlier.

So, assuming the collection contains different subtypes of objects, how do we get our code to work successfully?

Let’s assume we have a single UserForm with 10 textboxes, 10 labels and 2 buttons. We want to check to see if each textbox has something in it and turn it red if it does not.

We know we would have problems with the following due to type mismatches:

Dim ctlTemp as MSForms.TextBox For Each ctlTemp in frmStuff.Controls If ctlTemp.Text = “” Then CtlTemp.BackColor = vbRed Loop

We also know that the following would fail due to the object not supporting the property or method:

Dim ctlTemp as Control For Each ctlTemp in frmStuff.Controls If ctlTemp.Text = “” Then CtlTemp.BackColor = vbRed Loop

So we can use the ‘typeof’ statement to check for compatibility before we attempt to use the properties or methods.

Dim ctlTemp as Control For Each ctlTemp in frmStuff.Controls If typeof ctlTemp is MSForms.TextBox Then If ctlTemp.Text = “” Then CtlTemp.BackColor = vbRed End If Loop

This will then only attempt to access the Text property if the specific control we are currently looking at is a TextBox object. If it is anything other, then we simply move on to the next control in the collection.

For Each loops can be supremely powerful, just remember that typeof is your friend and to always be aware of the kind of objects your collection contains!