In our last discussion about VBA Ranges, we learned how to get and set cells. In this post, I’d like to build on top of that knowledge by showing you how to loop through cells in VBA. There are a few ways to loop through cells with VBA, but in this post, we’ll focus on the For Each looping mechanism. We’ll cover other ways to loop through cells in future posts. Let’s get started.
For Each Syntax
The syntax of
For Each is the following:
For Each [object] In [collection] [statements] [Exit For] Next [object]
||This is an object that will contain a reference to the current item within the collection. This is what you’ll be working with within the
||This is an object of a
||This is another optional statement that, if executed, will exit the
For Each Basic Example
Here’s a simple
For Each example:
Option Explicit Public Sub UpdateTextUsingForEach() Dim cell As Excel.Range For Each cell In Selection cell.Value = cell.Value & " extra text" Next cell End Sub
In this example, we simply loop through all of the cells in the current Selection and add some extra text to it. Here is this code in action:
For Each Example Skipping some Cells
Let’s say that we wanted to skip certain cells as we loop through them. For example, if we want to only add some extra text to cells that are not blank, we need to add a condition to check for that and then skip that cell and move on to the next.
In other languages, we could use the
Continue keyword. Unfortunately, VBA
doesn’t have that option, but we can get around that with using a
Option Explicit Public Sub UpdateTextUsingForEach() Dim cell As Excel.Range For Each cell In Selection If (cell.Value = "") Then GoTo Continue End If cell.Value = cell.Value & " extra text" Continue: Next cell End Sub
Here, we check if the cell’s value is blank and if so, we go to the Continue
label, which is at the end of the
For Each loop.
Here it is in action:
Once the code reached a cell that was blank, you see the
GoTo Continue line
was executed and the rest of the
For Each loop was skipped.
For Each Example with Exiting the Loop
Finally, let’s say that if you find a blank cell, that you’d like to completely stop the loop. You may need this if the user were to select a bunch of extra cells that were blank. This could help save you execution time and speed up the code as well.
Here’s an example of what that code would look like:
Option Explicit Public Sub UpdateTextUsingForEach() Dim cell As Excel.Range For Each cell In Selection If (cell.Value = "") Then Exit For End If cell.Value = cell.Value & " extra text" Next cell End Sub
And here’s what that would look like in action:
Once we hit a cell that had blank text, the rest of the cells were skipped because we exited the loop.
By the way, don’t mind that weird extra text “d Sub” in the image. That’s just Excel VBA for Mac acting all wonky.
Order of Operation
One last thing I’d like to talk about is the order that the
For Each goes
through if you have cells selected that span multiple rows and columns. Going
back to our original code, what order do you think Excel would go in if we
A1:B3? Would it go through column
A first, then move on to
B? Or would it go through the cells row by row?
Let’s take a look:
As you can see, it goes row by row, starting at
A3, and finally
B3. This might not mean much to you now, but you never know
when you will need to process the cells in a different order. For example, what
if you wanted to start from the bottom and work your way up? For that, we’ll
need to use a different looping construct which we’ll talk about in another post.