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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

MODULES

LESSONS

Collections and Objects

For Next Loops

For Each Loops

Title

Head office

London

Manchester