Module 6 - Collections and Loops
Lesson 6.2 - For Next Loops
Topic 6.2.2 - Counting Through Collections

In an earlier lesson we looked at how to reference items in a collection using the index number of each object.  A For Next loop provides a handy way to reference each item in collection, one-by-one, by counting through the index numbers.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the sample code.

The Example Workbook

Extract and open the workbook linked to in the Files Needed section above.  You'll find a workbook containing eight worksheets and eight charts:

Workbook

Each worksheet contains a table of results from the NFL 2017 season.

Each worksheet has a corresponding chart sheet which displays the result in a simple column chart:

Chart

Each chart resembles the one shown here.

Counting Through a Collection

We'd like to make changes to each of the eight charts in the workbook.  Rather than writing the same code eight times, we'll count through the Charts collection using a For Next loop.  Open the VBE and insert a new module.  Create a subroutine called CountThroughCharts and declare a variable which we'll use as a loop counter:

Start sub

Begin the subroutine as shown here.

 

We know that there are eight charts in the workbook, so we could write the For Next loop like so:

For Next

We can make the For Next loop count from 1 to 8 as shown here.

 

Using the Count Property

If you're not sure how many items are in the collection, or the number of items is likely to change, you can use the Count property of the collection as shown below:

Count charts

This code counts the number of objects in the Charts collection to work out how many times to loop.

 

You may prefer to use a variable to hold the number of objects in the collection, as shown below:

Variable

Declare a second variable to hold the number of objects in the collection and assign the Count of the Charts collection to it. You can then refer to this variable in the For Next loop.

 

Referring to an Object

You can refer to an object in the collection that you are counting through using the loop counter variable.  In our example, the loop counter variable is called ChartNum:

Refer to object

Refer to the Charts collection followed by the name of the ChartNum variable in a set of parentheses.

 

Once you've referenced an object, you can use any of its methods and properties.  For our example, we'll modify the ChartType property:

Chart type

Change the ChartType property as shown here. Feel free to use a different type of chart if you prefer.

Using an Object Variable

You may find it easier to use a variable to refer to the object that you want to manipulate.  Declare a variable which can hold a reference to a Chart object:

Chart variable

Declare a variable to hold a reference to a Chart object.

 

You can set a reference to a chart inside the For Next loop:

Set reference

Set a reference to the chart whose index corresponds to the value of the ChartNum variable. You can then use the chart variable to apply methods and properties to the object.

 

This technique is helpful when you want to apply multiple methods or properties to the object.  Add some code to change the ChartStyle and BarShape properties of the chart:

Chage other properties

Add two lines of code within the For Next loop as shown here.

 

Run the subroutine and check the results in Excel:

Results

Check that each chart has had the same changes applied to it.

To practise counting through a collection:

  1. Create a new subroutine called CountThroughWorksheets and declare a variable which will be used to refer to the index number of each object in the Worksheets collection:

Sub CountThroughWorksheets()

 

Dim WkSheetNum As Integer

 

End Sub

  1. Declare a second variable to hold the total number of worksheets in the workbook and assign the Count of the Worksheets collection to it:

Sub CountThroughWorksheets()

 

Dim WkSheetNum As Integer

Dim WkSheetCount As Integer

 

WkSheetCount = ThisWorkbook.Worksheets.Count

 

End Sub

  1. Create a For Next loop which counts from 1 to the count of worksheets:

Sub CountThroughWorksheets()

 

Dim WkSheetNum As Integer

Dim WkSheetCount As Integer

 

WkSheetCount = ThisWorkbook.Worksheets.Count

 

For WkSheetNum = 1 To WkSheetCount

 

Next WkSheetNum

 

End Sub

  1. Declare a variable to hold a reference to a Worksheet object:

Sub CountThroughWorksheets()

 

Dim WkSheetNum As Integer

Dim WkSheetCount As Integer

Dim ws As Worksheet

 

WkSheetCount = ThisWorkbook.Worksheets.Count

 

For WkSheetNum = 1 To WkSheetCount

 

Next WkSheetNum

 

End Sub

  1. Inside the loop, set the worksheet variable to reference an object in the Worksheets collection using the WkSheetNum variable:

Sub CountThroughWorksheets()

 

Dim WkSheetNum As Integer

Dim WkSheetCount As Integer

Dim ws As Worksheet

 

WkSheetCount = ThisWorkbook.Worksheets.Count

 

For WkSheetNum = 1 To WkSheetCount

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

Next WkSheetNum

 

End Sub

  1. Add code inside the loop which converts the range of cells containing data into a table:

For WkSheetNum = 1 To WkSheetCount

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

ws.ListObjects.Add _

SourceType:=xlSrcRange, _

Source:=ws.Range("A1").CurrentRegion

 

Next WkSheetNum

  1. Add an If statement to check if the first three characters of the worksheet name equals AFC:

For WkSheetNum = 1 To WkSheetCount

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

ws.ListObjects.Add _

SourceType:=xlSrcRange, _

Source:=ws.Range("A1").CurrentRegion

 

If Left(ws.Name, 3) = "AFC" Then

 

End If

 

Next WkSheetNum

  1. If the condition is met, apply a table style to the table that you have created.  Apply a different style to the non-AFC worksheets:

For WkSheetNum = 1 To WkSheetCount

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

ws.ListObjects.Add _

SourceType:=xlSrcRange, _

Source:=ws.Range("A1").CurrentRegion

 

If Left(ws.Name, 3) = "AFC" Then

 

ws.ListObjects(1).TableStyle = "TableStyleDark2"

 

Else

 

ws.ListObjects(1).TableStyle = "TableStyleDark3"

 

End If

 

Next WkSheetNum

  1. Run the subroutine and check the results in Excel:
Result

Each worksheet should have a formatted table.

 
  1. You can't run this subroutine again as you can't create a new table where one already exists.  Begin a new subroutine called ClearEveryTable and declare four variables as shown below:

Sub ClearEveryTable()

 

Dim WkSheetNum As Integer

Dim TableNum As Integer

Dim ws As Worksheet

Dim tbl As ListObject

 

End Sub

  1. Add a loop to count through the Worksheets collection and set a reference to a worksheet object each time:

Sub ClearEveryTable()

 

Dim WkSheetNum As Integer

Dim TableNum As Integer

Dim ws As Worksheet

Dim tbl As ListObject

 

For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

Next WkSheetNum

 

End Sub

  1. Add a second loop within the first to count through the collection of ListObjects on the worksheet and set a reference to a ListObject item each time:

Sub ClearEveryTable()

 

Dim WkSheetNum As Integer

Dim TableNum As Integer

Dim ws As Worksheet

Dim tbl As ListObject

 

For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

For TableNum = 1 To ws.ListObjects.Count

 

Set tbl = ws.ListObjects(TableNum)

 

Next TableNum

 

Next WkSheetNum

 

End Sub

  1. Apply the Unlist method to each item in the ListObjects collection:

Sub ClearEveryTable()

 

Dim WkSheetNum As Integer

Dim TableNum As Integer

Dim ws As Worksheet

Dim tbl As ListObject

 

For WkSheetNum = 1 To ThisWorkbook.Worksheets.Count

 

Set ws = ThisWorkbook.Worksheets(WkSheetNum)

 

For TableNum = 1 To ws.ListObjects.Count

 

Set tbl = ws.ListObjects(TableNum)

 

tbl.Unlist

 

Next TableNum

 

Next WkSheetNum

 

End Sub

  1. Run the subroutine and check the results in Excel:
Unlisted

Although the formatting has been retained, the table has been converted into a normal range of cells.

 
  1. Save and close the workbook.
This page has 0 threads Add post