Module 6 - Collections and Loops

In this module you'll learn how to do the same thing to multiple objects (such as looping over the cells in a range, or doing the same thing to each worksheet in a workbook).

Choose what you want to learn from the list of lessons above.

This page provides a brief summary of what you've learned in this module.  You can click here to download the example code shown below.

Collections and Objects

You can refer to a collection by writing its name.

Workbooks

 

Worksheets

 

Charts

 

Sheets

You can precede a reference to a collection with the name of the object to which the collection belongs.

Application.Workbooks

 

ThisWorkbook.Worksheets

 

ActiveWorkbook.Charts

 

ThisWorkbook.Sheets

You must refer to the container object before referencing some collections.

ActiveSheet.Shapes

ActiveSheet.ChartObjects

 

Chart2.SeriesCollection

 

Sheet1.ListObjects

You can refer to an object in a collection using the name of the object.

Workbooks("Book1")

 

Worksheets("Sheet1")

 

Charts("Chart1")

 

Worksheets("Sheet1").ListObjects("Table1")

You can refer to an object in a collection using the index number of the object.

Workbooks(1)

 

Worksheets(1)

 

Charts(1)

 

Worksheets(1).ListObjects(1)

For Next Loops

You can loop over a set of instructions based on a counter using a For Next loop.

Dim i As Integer

 

For i = 1 To 3

 

'instructions go here

 

Next i

You can jump out of a For Next loop using the Exit For statement.

Dim i As Integer

 

For i = 1 To 3

 

If Condition = True Then

Exit For

End If

 

Next i

You can use a For Next loop to count through every item in a collection.

Dim i As Integer

 

For i = 1 To Worksheets.Count

 

Worksheets(i).Select

 

Next i

You can use nested For Next loops to count through the rows and columns of a block of cells.

Dim r As Long

Dim c As Long

 

For r = 1 To 100

 

For c = 1 To 10

 

Cells(r, c).Select

 

Next c

 

Next r

For Each Loops

You can process every item in a collection using a For Each loop.

Dim ws As Worksheet

 

For Each ws In Worksheets

 

ws.Select

 

Next ws

You can use a For Each loop to process the cells in a range.

Dim r As Range

 

For Each r In Range("A1:D10")

 

r.Select

 

Next r

You can nest For Each loops to process collections within other collections.

Dim wb As Workbook

Dim ws As Worksheet

Dim co As ChartObject

 

For Each wb In Workbooks

For Each ws In wb.Worksheets

For Each co In ws.ChartObjects

co.Chart.ChartType = xl3DColumn

Next co

Next ws

Next wb

This page contains reference material for the code used in this module.

Title

Feature to describe:

Heading 1 Heading 2
Text 1 Text 2

 


 

There is currently no test for this module.

This page has 0 threads Add post