Excel VBA Macros : Understanding Our First Macro
The Story So Far….
If you have been following these posts so far, you should have read
- Introduction
- Introduction 2 & Setting Up Excel For Macros
- Recording A Macro & Challenge Question
- Answer To Challenge Question
You should have a file saved from the earlier posts where you recorded a macro and ran it using keyboard shortcuts and by inserting a button to run the macro.
Open this file now and we will step through the code.
You are about to see a lot of code and read quite a bit. Take your time and take it step by step. Top up your coffee cup now and have fun!!
Note : Where explaining excerpts from the macro, I will type the VBA code in red font and bold below.
View The Code
Open your saved Excel file where you recorded the macro.
With Excel open, press Alt and F11 together to see the VBE (Visual Basic Editor). The code for our macro should appear in the main screen below, if not, open it by going to the Modules folder in the project window and opening “Module1”
Congratulations!!
You have recorded a macro and ran it different ways, then you looked at the code for that macro…. feeling like you could take on Bill Gates yet?
A Comment About Comments
When you look at the code in your VBE, if you see any font in green, these are just comments. They are ignored by the macro when running through the code, but when you are looking back on code you have written, or looking at somebody else’s code, these comments are really useful for giving an overview of what the code is doing or any other useful comments.
To make text a comment, just put a single apostrophe ( ‘ ) at the start of the line, when you move your cursor to another line you will see the font color changing where you placed the apostrophe.
Comments are good practice and will save you time later when you are scratching your head trying to understand what you were doing when you wrote the macro (trust me on this one!!!).
Understanding The Code
Now we are ready to break down the VBA code.
I will be ignoring comments from the recorded macro (green font).
Remember that when you recorded the macro, it recorded every keystroke or mouse click you did in Excel.
Your code may be slightly different to mine where you clicked in a different cell or something – don’t worry about these little differences.
When you write code you wouldn’t write these lines, but if you are recording a macro they shouldn’t cause any problems, so don’t sweat them at this stage.
The 16 Steps
When we recorded our macro, we had 16 steps to complete that we recorded.
- Change the header in column A to say “Item”
- Change the header in column B to say “Price”
- Change the header in column C to say “Quantity”
- Add in a fourth column with the header “Total”
- For each item, in column D, multiply the quantity by the price
- Change the number format of column B to give 2 decimal places and use commas for thousand separator
- Change the number format of column D to give 2 decimal places and use commas for thousand separator
- Make the headers in row 1 bold
- Center the headers in row 1
- Widen the columns
- Add a border around the table
- At the bottom of column D, add in a total of all the totals
- Make this total bold
- Put a border around this total
- Change the fill color for the total to yellow
- Add a border around the headers in row 1
As I have said already, when you record the macro you may have extra lines of code, this could be after putting a border around your table you might click on a cell before starting the next step, Excel still records these clicks but they can be left out. I won’t be concentrating on them as I go through the code sections below.
I will also be ignoring any comments in the code.
# – Creating The Macro
Sub myFirstMacro()
The first thing to do is create our macro, with Sub MacroName and two brackets ().
Later we can get into sub routines and functions and all that other stuff that you have been hiding from up to now. For now it is enough to know that the macro starts here and our macro name is “myFirstMacro” which is the name we gave it when we recorded it.
1 – Change the header in column A to say “Item”
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Item”
The first step we did was to select cell A1. This is the first line of the code above, we can select a range of cells, or our range can be one cell reference, as is the case here.
After we select A1 and make this our active cell, then we change the formula to “Item”. This isn’t strictly speaking a formula, but how Excel recorded the change in the text. You will see how the R1C1 works later, but again for now, just to get started you can take it that the second line of code here changes our active cell’s value to whatever we want.
2 Change the header in column B to say “Price”
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “Price”
Our second step was to change the header in cell B1 to “Price”.
You can see the code here is very similar to that in the previous step.
We select B1 and then change the active cell to say “Price”
3 – Change the header in column C to say “Quantity”
Range(“C1”).Select
ActiveCell.FormulaR1C1 = “Quantity”
Are you able to figure out this piece of code?
Hopefully you can!!
We select cell C1 and with our active cell, change the value to say “Quantity”.
4 – Add in a fourth column with the header “Total”
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “Total”
You are probably getting a bit bored of this code snippet by now! Don’t worry, this is the last header to change.
Here we select D1 and with our active cell, change the value to “Total”.
5 – For each item, in column D, multiply the quantity by the price
Range(“D2”).Select
ActiveCell.FormulaR1C1 = “=RC[-2]*RC[-1]”
The first line of our code here, you should already be familiar with, is to select cell D2.
The second line is slightly different to what you have seen already. instead of typing in a string of text, here we are going to make the active cell a formula.
After our first double apostrophe, as with all formulas we start with an equals sign.
The RC in our formula is a relative reference. The R stands for a number of Rows and C for a number of Columns in relation to our active cell.
In cell D2, we want to multiply the value in B2 by the value in C2. We do this using relative references.
From cell D2, cell B2 is two columns back/to the left. The relative column is therefore [-2] on the same row.
Likewise, cell C2 is one column back/to the left of D2 so the relative column reference is [-1] on the same row.
=RC[-2]*RC[-1] is the same as saying multiply the value 2 columns back (B2) by the value one column back (C2).
Range(“D2”).Select
Selection.AutoFill Destination:=Range(“D2:D11”)
This selects cell D2, which gives us our first total amount and autofills down the range from D2 to D11.
This is the same as copying the formula down, clicking on D2 and dragging down to D11, or autofilling from D2, depending on what method you would normally use to drag a formula down a column.
6 – Change the number format of column B to give 2 decimal places and use commas for thousand separator
Range(“B2:B11”).Select
Selection.Style = “Comma”
Before now, our ranges have been single cells, here our range is a …… a range of cells.
We are selecting from B2 down to B11.
In Excel, the format for 2 decimal places and thousand separators is called Comma – the numbers are displayed as 1,234,567.89.
In our code, we select from B2 to B11 and change the style of our selection to Comma.
7 – Change the number format of column D to give 2 decimal places and use commas for thousand separator
Range(“D2:D11”).Select
Selection.Style = “Comma”
In this case, we select the range D2 down to D11 and then change the style of our selection to Comma.
8 – Make the headers in row 1 bold
Rows(“1:1”).Select
Selection.Font.Bold = True
When we wanted to select one single cell, we could say Range(“A1”), or for several cells we would say Range(“A1:F10”).
For Rows, we enter more than one row, if we want to select only row 1, then we select from row 1 to row 1.
Don’t worry about knowing when to use one reference or more than one – when unsure, I usually just record a small macro to do what I want and then look at the code of that macro to figure out what I need to use.
Different parts of Excel have parameters that can be turned on or off.
With our selection, we are going to change the font, the bold parameter to be specific and we make it true.
Other changes that could be made include (but are not limited to)..
Selection.Font.Italic
Selection.Font.Underline
Selection.Font.Color
Selection.Font.TintAndShade
Selection.Font.Size
Selection.Font.Name
Selection.Font.Superscript
Parameters like Italic, Bold, Superscript, Subscript, etc are boolean – that means they are either true or false, on or off.
So to make selected font bold, we turn on bold with Selection.Font.Bold = True.
Other parameters have more options than on and off, like the size, color, name, etc.
You will come across these as you try out more macros in the future.
9 – Center the headers in row 1
Rows(“1:1”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Again, we select Row 1.
Then with our selection we change some of the parameters – here we set the horizontal alignment as centered.
The vertical alignment, wraptext, orientation, etc will be left as the defaults.
If you wanted to, you could change these parameters and run the macro to see what changes they make.
Note the With Statement
We can change multiple options of our selection using the With statement.
We don’t have to type something like:
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlBottom
Selection.WrapText = False
Selection.Orientation = 0
Selection.AddIndent = False
Selection.IndentLevel = 0
… and so on. When we start a With statement, everything applies to our selection up to the end of the With statement.
Be sure to close off your With statement with End With.
Indentation is also a very useful tip, after I start the With statement, everything is indented and it makes it more obvious where the statement begins and ends.
As you use more and more With, If and While statements, indentation will prove very useful and then further down the road when you have statements within statements within statements, you’ll wonder how you ever lived without indentation before!!
10 – Widen the columns
Cells.Select
Cells.EntireColumn.AutoFit
To highlight all cells in the sheet, we can use Cells.Select.
Then we change change the columns to AutoFit, this will widen columns where needed.
11 – Add a border around the table
Range(“A1:D11”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Panicking yet? This may seem like a lot of code, but break it down and it is actually surprisingly obvious.
There are 8 borders
- xlDiagonalDown
- xlDiagonalUp
- xlEdgeLeft
- xlEdgeRight
- xlEdgeTop
- xlEdgeBottom
- xlInsideVertical
- xlInsideHorizontal
Normally, when we draw a border we only think about the left, right, top and bottom edges. The other 4 are available though if needed.
We select our range A1 to D11 and with our selection we address the 8 borders.
Where there is no border, we set the border as xlNone.
This applies to xlDiagonalDown, xlDiagonalUp, xlInsideVertical and xlInsideHorizontal. We set no border using the format “Selection.Borders(xlDiagonalDown).LineStyle = xlNone” for each border.
For the remaining 4 borders we want a black border (ColorIndex=0), a thin line (Weight=xlThin) and a continuous line (LineStyle=xlContinuous).
For each border we use a With statement and set our parameters…..
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
12 – At the bottom of column D, add in a total of all the totals
Range(“D12”).Select
ActiveCell.FormulaR1C1 = “=SUM(R[-10]C:R[-1]C)”
The last row of our table is 11, so we will enter our formula in cell D12.
We select D12 and then change the active cell to a sum formula.
The sum will be of cells D2 to D11.
Relative to our active cell (D12) this is from 10 rows up, down to 1 row up on the same column.
This gives us =SUM(R[-10]C:R[-1]C)
13 – Make this total bold
Range(“D12”).Select
Selection.Font.Bold = True
We are going to set the Bold parameter of D12 to True, that is, we will make the font Bold.
14 – Put a border around this total
Range(“D12”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
As we have seen already, there are 8 types of borders. Here, for cell D12 we don’t need 4 of them.
The 4 that we do need will have a black, thin, continuous outside edge (left, right, top, bottom).
15 – Change the fill color for the total to yellow
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
We have already selected cell D12 from the prior step.
The background, or fill of a cell is the Interior.
There are several parameters that can be changed, the pattern and color are probably the most obvious.
We want a solid yellow background color. The pattern is set as solid and the color is set as 65535.
We don’t need to know all the possible shades of colors to know their numeric value. If I wanted to specify a color or shade, I would just record another small macro where i change the color to the color I need and then copy the numeric value.
16 – Add a border around the headers in row 1
Range(“A1:D1”).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
To set a border around our headers, we select our range from A1 to D1 and apply a thin, black, continuous border to the left, right, top and bottom edges.
# – End the Macro
End Sub
We started our macro with Sub myFirstMacro().
Every macro we start has to have an end, we end the macros with End Sub.
If you create a macro by keying in Sub MacroName() you will see that End Sub is automatically put in and you type your code between these two lines.
Summary
There was a lot of code on this post!!
You can’t be blamed for being confused at this point.
Take your time with it, compare the code to what you were doing to generate that code.
As you can also see, Excel recorded our cell references and used them in the code, for example, it put the total value in cell D12 and not at the bottom of the table which is how we might expect it to behave. If our table had 1,000 rows of data, the macro will still put a total of D2 to D11 in cell D12.
What next?
It is enough to understand the code you are seeing, you don’t have to memorize it, so don’t worry about that.
Personally, I use 3 ways to write code quickly
- When writing one big macro, I record a lot of small macros, check the code and copy the parts I need
- I use code snippets, for more technical or unusual pieces of code, I go back to projects where I previously did something similar and copy what I need. Over time I will start adding some of the code snippets I use most often to this site.
- Google – I can’t emphasize enough the power and usefulness of search engines. No matter what you are trying to figure out, there is a good chance that somebody else out there has already asked the same question.
Over the next few posts, I will rebuild this same macro, but this time improve it so that the macro will run as we expect it to regardless of how many rows or columns we have.
