Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Example Workbook

Counting Through a Collection

Using the Count Property

Referring to an Object

Using an Object Variable

Head office

London

Manchester