Looping through a Collection

VBA supports yet another type of looping — looping through each object in a collection. Recall that a collection consists of a number of the same type of object. For example, each workbook has a collection of worksheets (the Worksheets collection), and Excel has a collection of all open workbooks (the Workbooks collection).

When you need to loop through each object in a collection, use the For Each-Next structure. The syntax is

For Each element In collection [statements] [Exit For] [statements] Next [element]

The following example loops through each worksheet in the active workbook and deletes the first row of each worksheet:

Sub DeleteRow1()

Dim WkSht As Worksheet

For Each WkSht In ActiveWorkbook.Worksheets

WkSht.Rows(1).Delete Next WkSht End Sub

In this example, the variable WkSht is an object variable that represents each worksheet in the workbook. Nothing is special about the variable name WkSht — you can use any variable name that you like.

The example that follows loops through the cells in a range, checking each one. The code switches the sign of the values (negative values are made positive; positive values are made negative). It does this by multiplying each value times -1. Note that I used an If-Then construct, along with the VBA IsNumeric function, to ensure that the cell contains a numeric value:

Sub ChangeSign()

Dim Cell As Range For Each Cell In Range("A1:E50") If IsNumeric(Cell.Value) Then

Cell.Value = Cell.Value * -1 End If Next Cell End Sub

Here's another example that loops through each chart on Sheetl (that is, each member of the ChartObjects collection) and changes each chart to a line chart. In this example, Cht is a variable that represents each ChartObject. If Sheetl has no ChartObjects, nothing happens.



Dim Cht As ChartObject

For Each Cht In Sheets("Sheet1").ChartObjects

Cht.Chart.ChartType = xlLine

Next Cht



Was this article helpful?

0 0

Post a comment