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!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

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).

6.1 - Collections and Objects
6.1.1 - Collections in Excel
6.1.2 - Object references by name
6.1.3 - Object references by number

6.2 - For Next Loops
6.2.1 - Basic For Next Loops
6.2.2 - Counting Through Collections
6.2.3 - Counting Through Cells

6.3 - For Each Loops
6.3.1 - Basic For Each Loops
6.3.2 - Looping Through a Range
6.3.3 - Nesting For Each Loops

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.03

There is currently no test for this module.

This page has 0 threads

Add a new post

MODULES

LESSONS

Collections and Objects

For Next Loops

For Each Loops

Title

Head office

London

Manchester