“The battlefield is a scene of constant chaos. The winner will be the one who controls that chaos” – Napoleon Bonaparte

 
 
Ever feel like all the VBA examples are way simpler than the code you face in real life?

A post with simple code is good for explaining a topic clearly. It can make things much easier to understand.

But the real world is more complex. The code you meet can often look like it was fed through the Enigma machine first.

In this post, I’m going to do something different. I’m going to take code from a real world application and

• explain it to you in simple English
• convert it to code a professional would write
• show you an alternative way of writing the same code

 
 
If you understand the ideas presented here it will dramatically improve you skills when you deal with real world VBA code.

 
 

Quick Notes

1. The underscore character “_” is used to break up long lines of code in VBA. The code behaves exactly the same as if it was on one line. It’s just easier to read.
2. To view the Immediate Window select View->Immediate Window from the menu or press Ctrl + G.
3. If you are new to VBA you may want to get familiar with VBA Cells and Ranges first.
4. For more info on the Loops used here, go to For Loops and For Each Loops.

 
 

The Code

Imagine you have been given the following piece of code to change

Sub Update()

Dim collect As Long, inptr As Long
collect = 3
inptr = 11

Dim x As Long, j As Long
For x = 1 To 4
    For j = 1 To 5
      Cells(x + collect, 4 + j).Value = _
        Cells(x + collect, 4 + j - 1).Value + Cells(inptr, j).Value
    Next j
Next x

End Sub

 
 
The following image shows the starting data

 

If you are familiar with VBA you will have seen code like this a lot. If you are new to VBA then this code can seem daunting.

What do the numbers mean? What are the calculations for? What does it all mean?

These are some of the questions you may have.

I’m going to answer these questions. Then I’m going to break the code down in the simplest terms possible. I’m going to explain each part in plain English.

 
 

Breaking the Code into Simple Parts

The main line in our code is of course

Cells(x + collect, 4 + j).Value _
   = Cells(x + collect, 4 + j - 1).Value + Cells(inptr, j).Value

 
 
The code on the right of the equals is adding the value of two cells. The cell to the left of the equals receives this value.

This is what the code is doing in simple terms

Cell1 = Cell2 + Cell3

Let’s look at Cell1 first and see which cell or cells the code is referring to

The Left of the Equals

This left side of the  line uses the Cells property to place a value in a cell.

Cells(x + collect, 4 + j).Value =

 
 
The Cells property allows us to read from or write to a cell on a worksheet. Remember that Cells takes Row and Column as arguments. So for example

Cells(1,1) refers to the cell A1
Cells(5,2) refers to the cell B5
Cells(2,1) refers to the cell A2

This code writes a value to a cell. We can see the row and column of the cell is

Row: x + collect
Column: 4 + j

Cells(x + collect, 4 + j).Value =

 
 
We know that the value of collect is set to 3 at the start of the Sub

Dim collect As Long, inptr As Long
collect = 3

It never changes in the code. So we can update our calculation

Row:  3 + x
Column: 4 + j

The variables x and j are each used in with a for loop. So next we will look at how the x and j are used.

How the For Loop works

I am going to show you how the values change as the code runs through the For Loop. If you are not familiar with loops then you may want to read The Ultimate Guide to Loops in Excel VBA first.

If you would like to see a simple example of a loop in action then check out the section How a Loop Works.

Let’s look at the first For loop in the code

For x = 1 To 4

Next x

What a For loop does is to repeat a line of code a given number of times. In the above code it will repeat the lines between For and Next four times.  That is the values from 1 to 4.

The first time the loop runs x will have the value 1. The second time, x will be 2,  third time 3 and the fourth time 4. Let’s look at the code

    For x = 1 To 4
        Cells(x + collect, 4 + j).Value =
    Next x

As the code runs through the loop the values change as follows

x	collect	Row
1	3	4
2	3	5
3	3	6
4	3	7

Note: Row in the table is calculated as: x + collect

The original code has two loops so let’s look at the second For loop.

The Second For Loop

The reason we have two for loops is because we are dealing with rows and columns. The outer loop (for x) reads through rows and the inner loop(for j) reads through the columns.

    Dim x As Long, j As Long
    For x = 1 To 4
        For j = 1 To 5
            Cells(x + collect, 4 + j).Value =
        Next j
    Next x

If you are new to programming a loop within a loop may seem tricky. The following will show the values as the code runs

Running the Code

The For x loop runs  4 times. Each time it runs the For j loop runs 5 times.

The first time it runs the For x loop sets x to 1.
Then the For j loop sets j to 1.

When we reach the cells line for the first time the values are as follows:
Cells(x + collect, 4 + j) will be Cells(1 + 3, 4 + 1) which gives Cells(4, 5) which is cell E4.

When this line runs we reach the Next j line. This sends us to the start of the loop. Here, j is now set to 2
Cells(x + collect, 4 + j) will be Cells(1 + 3, 4 + 2) which gives Cells(4, 6) which is cell F4.

The Cells code will be repeated 20 times. The For loop x runs 4 times. Each time it runs the For j  loop runs 5 times.

A Quick Look at the Values

The table below shows the values for Cells, row and column for the first 8 times.

x	collect	x + collect	4	j	4 + j
1	3	4	4	1	5
1	3	4	4	2	6
1	3	4	4	3	7
1	3	4	4	4	8
2	3	5	4	1	5
2	3	5	4	2	6
2	3	5	4	3	7
2	3	5	4	4	8
3	...

The following are all 20 cells we place a value in

(4,5), (4,6), (4,7), (4,8), (4,9)
(5,5), (5,6), (5,7), (5,8), (5,9)
(6,5), (6,6), (6,7), (6,8), (6,9)
(7,5), (7,6), (7,7), (7,8), (7,9)

or using the column as a letter

E4, F4, G4, H4, I4
E5, F5, G5, H5, I5
E6, F6, G6, H6, I6
E7, F7, G7, H7, I7

When the code runs, all these cells are assigned a new value. This value is got from the code to the right of the equals. Let’s look at this next.

Finding the second cell

Let’s have a look at the code to the right of the equals. The result of this calculation will appear in the cells we have shown.

    Cells(x + collect, 4 + j - 1) + Cells(inptr, j)

You can see that the first part of this assignment

Cells(x + collect, 4 + j - 1)

is similar to the cell we write the value

Cells(x + collect, 4 + j) =

The difference is that it refers to the cell one column to the left.

So the code

Cells(x + collect, 4 + j).Value = Cells(x + collect, 4 + j - 1)

is saying

E4 = D4
F4 = E4
G4 = F4 and so on

Finding the third cell

The second part of the assignment is

Cells(inptr, j).Value

The variable inptr is set to 11 at the start of the Sub. It never changes. So this cell is always in row 11.

The column we use here takes the value j.

So when x = 1 and j=1 our code

Cells(x + collect, 4 + j) = _
     Cells(x + collect, 4 + j - 1) + Cells(inptr, j)

gives

Cells(4, 5) = Cells(4, 4) + Cells(11, 1)

which is doing:
E4 = D4 + A11

So
x=1 and j=1 gives E4 = D4 + A11
x=1 and j=2 gives F4 = E4 + B11
x=1 and j=3 gives G4 = F4 + C11
x=1 and j=4 gives H4 = G4 + D11
x=1 and j=5 gives I4 = H4 + E11

x=2 and j=1 gives E5 = D5 + A11
x=2 and j=2 gives F5 = E5 + B11
and so on.

The following screenshots shows the first two values being assigned

 

Oh No, My Brain is Bursting!

Well done on reaching this far.  It shows you are serious about learning VBA. Your brain may be hurting but don’t worry. The worst is over. The rest of this post is much simpler.

So sit back and relax as I rewrite the code and make it easier to read.

Giving the Code a Makeover

Let’s take the original code again. We’re going to make to change it piece by piece until it is a thing of beauty!

Sub Update()

Dim collect As Long, inptr As Long
collect = 3
inptr = 11

Dim x As Long, j As Long
For x = 1 To 4
    For j = 1 To 5
      Cells(x + collect, 4 + j).Value = _
        Cells(x + collect, 4 + j - 1).Value + Cells(inptr, j).Value
    Next j
Next x

End Sub

 

Step 1: Remove the Calcs

The first thing that springs to mind is this. Rather than adding a collect number of cells each time why  not start at the correct cell. So instead of

   For x = 1 To 4
        Cells(x + collect, 4 + j).Value =

to something like

   For x = collect To collect + 4
        Cells(x, 4 + j).Value =

Then we don’t need to calculate the row each time. x is already the correct row.

Step 2: Create Useful Variables

Let’s create some variables to define our range. We’ll give them names that mean something.

    Dim rowStart As Long, rowEnd As Long
    Dim colStart As Long, colEnd As Long
    colStart = 5
    colEnd = 9
    rowStart = 4
    rowEnd = 7

 

Step 3: Rewrite the For Loops

Now we have all the cells positions. We can create the For loops to read through these cells. No calculations needed to find the cells.

Note: It is standard to use i and j as variables in For loops.

    Dim i As Long, j As Long
    For i = rowStart To rowEnd
        For j = colStart To colEnd

        Next j
    Next i

 

Step 4: Rewrite the Cell Assignment Code

Let’s look at the main line next. It is now much simpler. See the original and new versions below.

    ' Original
    Cells(x + collect, 4 + j) = Cells(x + collect, 4 + j - 1)

    ' New version
    Cells(i, j) = Cells(i, j - 1)

So you can see the code is much more readable. We only have one calculation for a cell and that is subtracting 1 from j.

Step 5: Rewrite the Cell Values Code

The last part is a little bit tricky. We know the row with the values is always 11 so let’s rename the variable Inptr to rowValues.

    Dim rowValues As Long
    rowValues = 11

The value in A11 is added to E4, B11 is added to F4 and so on. So for the first value we add from column 1, the second value from column 2 etc.

Let’s create a counter for this column

    Dim colCnt As Long

We place this counter before the second loop. This means it starts at one each time the For j loop runs. We then a line to the loop that adds one to it each time so it moves on one column.

    ' Reset value column to 1
    colCnt = 1
    For j = colStart To colEnd
        Cells(i, j) = Cells(i, j - 1) + Cells(rowValues, colCnt).Value
        ' Move value column on 1
        colCnt = colCnt + 1
    Next j

 

Final Code

The final code including comments looks like this

Sub UpdateNew()

    ' Set the totals range
    Dim rowStart As Long, rowEnd As Long
    Dim colStart As Long, colEnd As Long
    rowStart = 4
    rowEnd = 7
    colStart = 5
    colEnd = 9

    ' Set the values row
    Dim rowValues As Long
    rowValues = 11

    Dim colCnt As Long

    Dim i As Long, j As Long
    ' Read through the rows
    For i = rowStart To rowEnd

        ' Reset value column to 1
        colCnt = 1
        ' Read through the columns for the current row
        For j = colStart To colEnd
            Cells(i, j) = Cells(i, j - 1) + Cells(rowValues, colCnt)
            ' Move value column on 1
            colCnt = colCnt + 1
        Next j

    Next i

End Sub

You can see this code is much more readable. The variable names give a clear indication of what the values are.

There are no complicated calculations. This makes code easier to read or change. It is also less likely to have errors.

For completeness I’m going to show another way you could perform the same task.

Version 2 – Use the For Each Loop

Let’s write a version of this using the For Each loop. The major difference this time,  is that we only need one loop.

    ' Range of totals
    Dim sRange As String: sRange = "E4:I7"

    ' Go through each cell in the range
    Dim rCell As Range
    For Each rCell In Range(sRange)

    Next rCell

Now we want to set the value of the cell to something.

        rCell =

The first part of the calculation is the cell to the left. We can use the Offset property of  range.

To get one cell to the left we use a minus value

        rCell = rCell.Offset(0, -1)

If the rCell here was E4 then rCell.Offset(0, -1) would be D4.

This final item we need is the value from the additions row. We add the offset for the column

    ' A count to the column on the values row.
    Dim colValuesOffset As Long: colValuesOffset = 4

Now we use the offset to get the value cell from the current one. The row is always 11 i.e. rowValues. The column is 4 less than the current cell column

    ' Read addition value
    addValue = Cells(rowValues, rCell.Column - colValuesOffset)

So the final code looks like this

Sub UpdateForEach()

    ' Range of totals
    Dim sRange As String: sRange = "E4:I7"

    ' Row that contains the addition values
    Dim rowValues As Long: rowValues = 11

    ' A count to the column on the values row.
    Dim colValuesOffset As Long: colValuesOffset = 4

    Dim rCell As Range, addValue As Long
    ' Go through each cell in the range

    For Each rCell In Range(sRange)

        ' Read addition value
        addValue = Cells(rowValues, rCell.Column - colValuesOffset)
        ' new cell value is cell to left plus add value
        rCell = rCell.Offset(0, -1) + addValue

    Next rCell

End Sub

You can see we have broken down the calculation into two lines. This makes the code easier to read and understand.

We also only have one For loop which means we have not complex calculations to figure out.

Conclusion

If you have read this far then congratulations! This was not a simple topic. If you can grasp the ideas then you are well on the way to becoming proficient at VBA.

So what we did here was as follows

1. We took a real word piece of code
2. Then we broke it down into simple parts
3. We looked at how the cells were calculated
4. We rewrote the code so it was much simpler to read
5. We wrote it a second way using a For Each loop which made the code much simpler

If you come face to face with complex code you can approach it like this. Break it down into simple parts. Try to understand what each part is doing.

What Next?

If you would like more information about the topics covered here you may want to check out Cells and Ranges, For Loops and For Each Loops.

You can get the complete list of all the posts here. I also have a free eBook(see below) which you will find useful if you are new to VBA

Get the Free eBook

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.