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”.

NameCell

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

1.10Horizontal

We’ll first open up the Macros button:MacrosButton

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

HorizontalLoop

HorizontalLoopCode

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-

 

 

 

 

 

 

 

 

 

Published by byoscope

My Name is Jack Allweil. I grew up in a small town in Michigan. I absolutely loved playing soccer growing up and while I don't play as much these days I still love watching my beloved London based club, Arsenal. After studying mathematics and economics at the University of Michigan I worked as an actuary for various insurers. After getting fired from one job in Charlotte, NC my head went spinning looking for new ideas. I started reading... A lot. Next thing I know Dr. Stefan Szymanski gave me many of his sources for his fantastically written books, Soccernomics and Money and Soccer. My journey to bet on the 2018 Russia World Cup began.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: