Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the sample code.
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.
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.
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.
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.
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:
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
End Sub
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
WkSheetCount = ThisWorkbook.Worksheets.Count
End Sub
Sub CountThroughWorksheets()
Dim WkSheetNum As Integer
Dim WkSheetCount As Integer
WkSheetCount = ThisWorkbook.Worksheets.Count
For WkSheetNum = 1 To WkSheetCount
Next WkSheetNum
End Sub
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
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
For WkSheetNum = 1 To WkSheetCount
Set ws = ThisWorkbook.Worksheets(WkSheetNum)
ws.ListObjects.Add _
SourceType:=xlSrcRange, _
Source:=ws.Range("A1").CurrentRegion
Next WkSheetNum
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
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
Each worksheet should have a formatted table.
Sub ClearEveryTable()
Dim WkSheetNum As Integer
Dim TableNum As Integer
Dim ws As Worksheet
Dim tbl As ListObject
End Sub
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
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
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
Although the formatting has been retained, the table has been converted into a normal range of cells.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.