Integrating Excel and Access

Loops

There are several types of loops that you can use in VBA. Here are three that I use most often.

For...Next Loop

The For...Next loop is used to go through a set of numbers and execute a block of code through each iteration. By default, VBA increments by 1 each time it comes to a Next statement. However, you can use Step to change the increment. The following example goes from 0 to 10 in increments of 2 and writes the value of the number to the Immediate Window:

Dim x As Integer
For x = 0 To 10 Step 2
  Debug.Print x
Next x

For Each...Next Loop

This loop cycles through an array or collection. To use this loop, the variable that you use either has to be a type Variant, Object, or a specific type of object (such as Excel.Worksheet). Following is an example of cycling through each item in a collection using a Variant:

Dim xColl As Collection
Dim xItm As Variant
 
Set xColl = New Collection
 
xColl.Add 2
xColl.Add 10
xColl.Add 15
 
For Each xItm In xColl
  Debug.Print xItm
Next
 
Set xColl = Nothing

There were several examples in the book of cycling through a collection of objects. Here is an example that you can use in Excel that puts the name of each worksheet and chart sheet in the active workbook into the Immediate Window.

Dim xlWs As Excel.Worksheet Dim xlCs As Excel.Chart   For Each xlWs In ActiveWorkbook.Worksheets Debug.Print xlWs.Name & " - Worksheet" Next xlWs For Each xlCs In ActiveWorkbook.Charts Debug.Print xlCs.Name & " - Chart Sheet" Next xlCs   Set xlWs = Nothing Set xlCs = ...