Simple Loop in VBA

Before we run we must crawl.  Before we loop through 5000 simulations with many moving parts we need to be able to do a simple loop.

Here’s today’s Pro Tip:  Name important cells (I think you’ll understand what determines important in a few moments) so you can use them in your VBA macros!  Below you’ll see in yellow that I’ve actually renamed what was previously known as cell A2 as “Start”.

Today we want to write a macro to produce the following:

We’ll first open up the Macros button:

And type in the name of the loop we’re going to make, “LoopHorizontal”.  Hit Create!

The code above will produce our desired output but let’s walk through it:

You’ll always start a Sub, or subroutine by typing “Sub” and then the macro name.  The macro will end with “End Sub”.  These components are normally automatically created for you when you hit the Create button from the prior step.

Sub LoopHorizontal()

End Sub

Let’s now look at the meat of the macro.  The “Dim” part is what is used when you are dimensionalizing a variable.  Basically we’re defining the variable before we use it.  In our example we are making variable j an integer.  Often times when doing loops you’ll need to get counter variables either utilizing integers or doubles to do the counting.  Don’t allow a string to do an integer’s job!

Here we are defining the variable j:

Dim j As Integer

And the loop is below:

For j = 1 To 10
Range(“Start”).Offset(0, j) = j
Next j

This is the standard format for a For Loop.  We’re specifying what variable to use, in this case j, and what the values should be for it, in this case 1 through 10.  Think of the commands in the loop as follows:

1. Range(“Start”) tells Excel to look at the cell that we named “Start”
2. The offset function is straight money (it’s good) for producing looped output.  It’s in the format .Offset( row offset, column offset).  In our example we want to do everything in the same row so we have 0 in the row offset and the column offset is changing by our variable j, which is changing during each iteration.
3. “Next j” at the end increases our variable j by 1 each time it’s encountered.  When j is 10 and it encounters “Next j” j becomes 11.  11 is outside the range we specified in the first line of the loop so the loop is ended.

And Voilla!  You have your first loop.  Just hit F5 while your cursor is in the code to execute the code.

Challenge tasks/questions:

1. How would you change the code to print the numbers in a vertical line?
2. How would you change the code to print the numbers on five rows, each row consisting of two numbers?
3. Why is it helpful that we named cell A2 “Start”?  Hint:  Think if we wanted to quickly move the loop to start at R23.

 

“Who you are is up to you, don’t leave it up to them.”  -NF-