“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.
Contents
Quick Notes
- 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.
- To view the Immediate Window select View->Immediate Window from the menu or press Ctrl + G.
- If you are new to VBA you may want to get familiar with VBA Cells and Ranges first.
- 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
' https://excelmacromastery.com/ 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 about the For Loop 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 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!
' https://excelmacromastery.com/ 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 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
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
- We took a real word piece of code
- Then we broke it down into simple parts
- We looked at how the cells were calculated
- We rewrote the code so it was much simpler to read
- 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’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
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.
John:
What about if using the Name Manager, you give a name to the first cell of row 11, say “first_cell” , and then
row_Values = Range(“first_cell”).row
Then, values at row 11 could be in any row of your sheet and you avoid using constant values (11) in your code.
Please correct me if I am wrong.
Sincerely yours,
K-Li-Ch
Hello, Thank you So Much your the great jobs. On MSDN They say about cells properties, that WORK WELL on loops, my question for you is : work “well” OR work “only”, and why you use in this line for example Cells(x + collect, 4 + j – 1).Value . . So what other mysteries exist inside the parenthesis after CELLS (??????); I tried out of any complicated loop or control structure, you are the best post I ever see on internet. wow
this is my try:Sub proof()
Dim x As Integer
Dim j As Integer
Dim collect As Integer
collect = 6
Cells(x + collect, 4 + j – 1).Value = “return”
End Sub
Try to teach beginners using const instead of dim for non changing Variables
IMHO this:
Dim sRange As String: sRange = “E4:I7”
should be
Const sRange as string = “E4:I7”
and so on …
Nice work otherwise, keep goin
dj