4 - Understanding your recorded macro

So, how was your experience with your first recorded macro. I hope you liked it.
If you are not so excited, wait for it. I promise you will love it.

In your last exercise, you automated coloring of a cell and used that to create a chessboard.
You can do a lot of such stuff or combinations of them to save your time.
1. When I started I automated adding new row in between
2. Formatting cells
3. and that's it. I did not find much use of record until I learned how to modify codes.

Lets have a look at the code.
You will have to open the Visual editor.
- Click Visual basic editor under Developer

 or
- ALT + F11

You will get visual editor

Expand the module and double click on the module1

The Module1 in the red box is called procedure module. This module has the algorithms for business logic. 

The text in black box is the code that is recorded by Excel for you

1. Sub Macro1()

'

' Macro1 Macro

'

' Keyboard Shortcut: Ctrl+q

'

2.    Application.CutCopyMode = False

3.    With Selection.Interior

4.        .Pattern = xlSolid

5.        .PatternColorIndex = xlAutomatic

6.        .ThemeColor = xlThemeColorLight1

7.        .TintAndShade = 0

8.        .PatternTintAndShade = 0

9.    End With

10. End Sub

The text in Blue are VBA reserved keyword. In future when you write a code and the reserved keywords are not turning blue then something is wrong. 

Lets go line by line

Line 1 : The Sub means a subroutine. The subroutine is a sequence of program instructions that perform a specific task, packaged as a unit. That means if you have a set of task that you want to automate in single macro. You will have multiple set of program to complete those individual task. But you will have a single subroutine from where you will call those program. Usually I name the sub as Main(). 

the text in green are the comments. the apostrophe at the beginning of  the sentence make the entire sentence as a comment which is not read by the VBA macro.

Line 2: Application.CutCopyMode = False. You can ignore this for now as this has nothing to do with the our objective. I will come up to this later in the learning.

Line 3. With Selection.Interior. This is important on multiple concepts. If you recall, before choosing to record the macro, I asked you to select a cell. Here, the 'Selection' means whatever is selected by the user. (note you can make a selection from VBA too and I will explain that soon enough). 

Here Selection is actually an object. When I say object I mean the concept of object from Object Oriented Programming. 

There are lot of concepts of Object Oriented programming but I do not want you to go to that level of depth now. Though I want you to understand what an object is and how to access the method of object. 

Here Selection is an the cell selected as Cell is an  object. It not only have address, it has various other attributes such as color, size, height, width, format, border. At the same time it has some methods of its own as as Add(comments or hyperlink) or Address(). 

Since cell is an object and we selected the cell before recording macro, the selection here is an object. This selection(cell) has property called Interior and this Interior has its own set of sub properties. 

Now, we need to change the color to black. When you do that, Excel adjusts a lot of other properties other than just color and in order to change the all properties of the same cells, Excel uses With clause and say that anything following this line would be with respect to the 'Selection.Interior'

Line 4 to 8 these line are to color the selection (i.e, cell). Notice that each line here start with '.' . This is because we already said in Line 3 that next statements would with respects to 'Selection.Interior'. and we use '.' to show that this line is in continuation with the 'Selection.Interior'

Line 9: End With mean that the with respect to started above would not be considered any more. 

Note: If you have a 'With' statement , you would need an 'End With' in the program.

Also, If you want to access something between the With .. End With that is not with respect to the object in With, you can still have it. However, you will have to use the full path instead of starting with '.'

for example

With Selection.Interior

        .Pattern = xlSolid

        msgbox "Hello Sweety"

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .PatternTintAndShade = 0

End With

Line 10. End Sub. This is the way to scope the sub routine. 

Few additional things,

You see Application.CutCopyMode = False in Line 2. Here Application is an object referring to Excel itself and CutCopyMode is one of the setting of Excel. 

Now read it like, the Cut and Copy mode of Excel is set to False. That means, the code is telling Excel to not copy anything.

Hope you had fun with the code.