How to Deal With Real World VBA Code
“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
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
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
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.
© Suprijono Suharjoto | Coming face to face with real world code can be daunting
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
xcollectRow
134
235
336
437
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.
xcollectx collect4j4 j
134415
134426
134437
134448
235415
235426
235437
235448
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
E4(Red)= D4(Blue) A11(Blue)
F4(Red)= E4(Blue) A11(Blue)
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
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 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.
The post How to Deal With Real World VBA Code appeared first on Excel Macro Mastery.
