VBA - Analyse The Recorded Code

Hi guys, welcome back for another session on VBA. In our previous post, we recorded a macro and executed to see how it works. In this post, let’s see the code behind that macro and analyse what’s going on behind the curtains.

See the Code of MyFirstMacro & Analyse

Open your VBA learning workbook that we saved with MyFirstMacro. Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here).

In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window.

Double click Module1 and your code window will be opened on the right hand side. I pasted the code below (coloured part). Have a quick look and we will analyse it line by line below. Remember, we have recorded MyFirstMacro using absolute references.

Sub MyFirstMacro()
'
' MyFirstMacro Macro
' This is my first macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'    Range("A1:A10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    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
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Let’s start analysing this code. As we analyse, we will also be discussing the code structure we should follow  in general, so follow closely.

Sub MyFirstMacro()

Every macro code should start with a word Sub. This word refers to Subroutine. It tells Excel that a code is going to follow and Excel has to perform task given in that code.

Next to Sub is the name we gave to our macro i.e. MyFirstMacro. Followed by this, there are open and close parenthesis (we know as brackets but get used to the word parentheses). Remember excel built in functions where we give arguments in parentheses? Here also the same logic follows. If you need any arguments as inputs to your macro, they can be given in this parentheses (we will see more about it as we move deeper into coding). If no arguments are required, just give empty parentheses.

Sub should be always be closed with End Sub at the end of the code. After this line, VBA stops executing the code for this macro. Notice the very last line of the code above.

' (Apostrophe)

Apostrophe is used by VBA to indicate a comment or information that need not be executed as code. Whatever you enter right side of apostrophe will not be executed by VBA. This character is used extensively by developers to explain the code. In our code, VBA enters the description, short-cut keys next to apostrophe.

Though we gave a short-cut key, code will have it just as information and handling the key press is Excel user interface's job and not VBA's job.

Range("A1:A10").Select

Pretty straight forward, isn’t it? We are selecting Range A1 to A10 here. But look at the construction. Range is an object; it has one of the method Select. We are using a dot after Range to call Range object related method i.e. Select.

Range takes an argument as input that refers to an Excel cell range. You can supply a real range like we have in this example i.e. A1:A10 within quotes or you can give a named range etc. as an argument.

Tip: Press F1 in VBA help and type "Range Object" in search. Once you read about "Range". Once this is done, also read about "Range Object Members".

With Selection.Interior untill End With

We already know that we have to follow a particular hierarchy to work with lower level objects. For example, in cell A1, I want to apply bold, italic and underline to the font. I will have to write 3 different lines of code with full hierarchy. It looks like below.

Range(“A1”).Font.Bold = True
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Underline = True

We are only starting from Range object going 2 levels below. This is still very simple line. Think of drill-down from an application object level until some 8/9 levels down, each line will be lengthy and since VBA will have to interpret many number of drill-downs, it's performance would be slower in longer codes. So VBA has a workaround too. Above 3 lines can also be written like below.

With Range(“A1”).Font

   .Bold = True

   .Italic = true

   .Underline = true

End With

Here we are trying to combine like items under one qualifier With and once we are done using it closing it off by End With.

Selection is another object in VBA that represents the range of currently selected cells. Since while recording code we selected A1:A10, Selection indirectly represents Range(“A1:A10”). One of the methods of Selection object is Interior object. Interior is where the background colour is set. In this line of code, we are defining a With qualifier to work with interior of selected cells in next lines of code until End With code line.

Other lines below this Selection.Interior are essentially to set various fields required in Interior. What we are concerned is the line .Color = 255. This is where you are setting the colour required as a background of cell. Number 255 essentially used to represent red colour  You can read more about the numbers for all colours in VBA help.

With Selection.Borders(xlEdgeTop) till the end of the Code

All these lines are used to define various properties Excel requires to set border line. You can pretty much derive what each line is trying to set by reading the line. This code is in human readable form, right?

Having said that best way to learn VBA coding is to record a macro and analyse the code, there is a serious flaw with this method. At times, VBA tries to give larger code where there is no need for. This often makes code very lengthy and confusing.

For example, to apply colour to a cell, you need not select the cell. Remember our code line Range("A1:A10").Select? This line selects the range defined. But to work with a range in VBA, there is no need to select it. So instead of our original code;

Range("A1:A10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

We can also write

With Range(“A1:A10”).Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

Notice that I have merged first two lines of initial code in 1 line. This modified version works exactly same way but with fewer lines of code, there by improving quality of the code and speed of execution.

This is not the perfect coding either. You need not define values for some default properties unless we need to change them specifically. For example: to apply colour to a cell, we need not change its values for Pattern, TintAndShade etc as given in our original code. So our code can be further reduced like below.

With Range(“A1:A10”).Interior

        .Color = 255

End With

Needless to say, this snippet of code works exactly same as our lengthy version. So is this effective? No. When we are working with only one property as above, we need not use With qualifier. So final code will be;

Range(“A1:A10”).Interior.Color = 255

Our 8 line initial code is reduced to one simple and cute line. This line applies red background to cells A1:A10. On similar lines, let’s see how the lengthy 32 lines border related code can be condensed in 8 lines.

With Range("A1:A10")

        .Borders(xlEdgeLeft).LineStyle = xlContinuous

        .Borders(xlEdgeTop).LineStyle = xlContinuous

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

        .Borders(xlEdgeRight).LineStyle = xlContinuous

        .Borders(xlInsideVertical).LineStyle = xlContinuous

        .Borders(xlInsideHorizontal).LineStyle = xlContinuous

End With

Now look at the code again, line by line. We are writing code to apply border on left, right, top, bottom, vertical & horizontal of each cell, all in separate lines. With a bit of research in VBA help, we would find that all borders are referred in a collection named as Borders. Setting a value to this collection will be applied to individual items in the collection.

Also see both the codes to apply colour and borders. Both works with Range(“A1:A10”) so you can combine both of them with the With qualifier as below.

With Range("A1:A10")

        .Interior.Color = 255

        .Borders.LineStyle = xlContinuous

End With

Started with a 40+ lines of code given by VBA recorder, we arrived at 4 lines which works exactly the same way but with increased efficiency. Go ahead and replace MyFirstMacro code with above snippet between Sub and End Sub and see if this works exactly same as before. (Or create a new macro with this code, you can compare better this way)

But still, without knowing head and tail of VBA, recording and analysing is the best option you have to manoeuvre this mammoth. Whenever you have time, go through the Object Hierarchy to know the short-cuts and techniques in using objects effectively.

See the Code of MySecondMacro & Analyse

We have recorded this second macro using relative references. In your VBE code window, scroll down the code until you see a horizontal line across the code after End Sub line of MyFirstMacro code. This horizontal line represents starting of another macro. You will be able to see Sub MySecondMacro() immediately below this line.

Compare the code of MyFirstMacro and MySecondMacro. You will notice only one difference in the code (ofcourse, apart from difference in name, description and short-cut key) i.e. MySecondMacro contains ActiveCell.Range("A1:A10").Select instead of Range("A1:A10").Select in MyFirstMacro.

What it means is simple, instead of hard coding range A1 to A10, MySecondMacro tells Excel to consider current active cell as the base cell and from there select 10 cells below. That is what relative reference is, right?

I trust we are clear on how to analyse the code. Once you know what the recorded code means, it only takes changing it to suit your requirement. For example, in our code to apply background colour, change the number 255 to something you wish and see what happens. Whenever there is an error (if you enter a value outside allowed range), don’t forget to consult VBA Help to see why. It will be fun to see how the changed values effects the background colour of the cell. See it yourself!

We will have many opportunities to write codes to achieve different tasks in Excel. We will analyse tons of code during those times. Before diving head first into the serious coding, we will see some good practises you should follow while writing VBA code. Stay tuned for next post!!