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 |
Try doing one or more of the following exercises for this module:
Exercise 6.01 Exercise 6.02 Exercise 6.03There is currently no test for this module.