MODULES
LESSONS
TOPICS
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:
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:
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:
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:
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:
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:
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 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:
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:
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 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:
Add two lines of code within the For Next loop as shown here.
Run the subroutine and check the results in Excel:
Check that each chart has had the same changes applied to it.
To practise counting through a collection:
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- Run the subroutine and check the results in Excel:
Each worksheet should have a formatted table.
- 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
- 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
- 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
- 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
- Run the subroutine and check the results in Excel:
Although the formatting has been retained, the table has been converted into a normal range of cells.
- Save and close the workbook.