Looping through a Collection

VBA supports yet another type of looping — looping through each object in a collection of objects. 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

Note that the code sample above changes any formulas in the range it loops through to values, zapping your formulas. That's probably not what you want. Here's another version of the Sub that skips formula cells. It checks whether the cell has a formula by accessing the HasFormula property:

Sub ChangeSign() Dim Cell As Range For Each Cell In Range("A1:E50") If Not Cell.HasFormula Then

If IsNumeric(Cell.Value) Then Cell.Value = Cell.Value * -1 End If End If Next Cell End Sub

Here's another example that loops through each chart on Sheet1 (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 Sheet1 has no ChartObjects, nothing happens.

Sub ChangeCharts()

Dim Cht As ChartObject

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

Cht.Chart.ChartType = xlLine Next Cht End Sub

The For Each-Next structure is usually much faster than looping by using the For-Next construction. So if you're in a hurry, use For Each-Next.

0 0

Responses

  • UFFO CLAYHANGER
    How to loop through collection vba?
    8 years ago

Post a comment