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 objects of the same type. For example, Excel has a collection of all open workbooks (the Workbooks collection), and each workbook has a collection of work-sheets (the Worksheets 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, and checks each cell. 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
The preceding code sample has a problem: It 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 ChangeSign2()
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
And here’s one more For Each-Next example. This procedure 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 repre-sents 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
To write a procedure like ChangeCharts, you need to know something about the object model for charts. You can get that information by recording a macro to find out which objects are involved and then checking the Help system for details.
Excel 2007 users are out of luck here: The macro recorder in Excel 2007 does not record all chart changes you make.
No comments:
Post a Comment