VBA Blogs: Getting Loopy Each Time

Loops are a powerful tool in programming to repeat a sequence of instructions. For Loops were covered here and they are really good for performing actions a fixed number of times.

But what if you want to perform an action to every object in a set?

For Each…Next loops are a great way to cycle through sets – like an array or a range. Sometimes we don’t know the size of the set, like in a table. Using ListObject.ListRows.Count is a way to find the number of rows in order to use the simple For loop but using For Each…Next more clearly illustrates that the instructions are happening to every row.

The example table today shows sales data, in a table called Tbl_Sales:

Let’s calculate each sales rep's cumulative sales as time goes by – as in, calculate the total sales they each made to the date of that sale i.e. Jill made $213 + $840 + $955 = $2,008 cumulative sales on the 4^th of July (admittedly this can be achieved quite easily with a SUMIF formula with a moving range).

Here’s the code:

Notice that when using Option Explicit to force declaration of variables, the child of the parent object must also be declared – in this example for a table, using ListRows.

Tada! This is a pretty basic example of how each row can be examined in a table, but For Each…Next can also be used for arrays and cell ranges.

Circle back to the VBA blog next Friday Until you can see what we can Do.