This is a follow on from our previous VBA tip on how to record macros.

You can record a macro and get it to work without having to understand the code at all, and that is very useful for automation of simple tasks. The code can get quite long though, and if it gets too big, it becomes unwieldy. At this point it is a good idea to clean it up.

The best way to do this is to understand a few basics, and to be able to condense many lines of code into a few.
Lets see this in action for a real life example:

We have a feed coming into our Excel, and it brings in the latest share price.

Suppose we want to take a snapshot of that each day at the end of the day and then use that to calculate the daily change.
Let’s record what we would do to save that share price daily.

Step1:
To record a macro, press the following in sequence:
ALT then t then m then r
That will bring up this screen:

Step2:
Press OK, and now start the actions you would do to save the price daily.
- Select B2, press CTRL and C (to copy)
- Select A2
- Press ALT then e then s then v then ENTER (to paste special values)

Now, the daily change will be reset to zero, and it will start calculating the next day's daily change when you get in tomorrow.

Step3:
Stop the macro recording:

Step4:
Now press ALT and F11, and look what the macro recorder has recorded.

It will look something like this:

Now you attach the code to a button using our first VBA lesson, and press it every day when you want to run it.

NOW... here’s the important part of the lesson.
The macro recorder has recorded the following:
Range("B2").Select
- This is the action of selecting B2

Selection.Copy
- This is the action of copying the selection

Range("A2").Select
- This is the action of selecting A2

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
- This is the action of pasting only the values (not the formatting or the formula) of the cell B2 onto A2

This entire code can be condensed into one line:
Range("A2").Value = Range("B2").Value

NOTE – there’s no need to select anything... we can just set the value of cell A2 to equal the value of B2 !

Download sheet to practise recording macros

Do you find yourself doing the same tasks every day ?
Do you want Excel to automate them ?
This tip is about how to record a macro.
Recording macros is the first step to automate tasks, and to learn VBA.
You can get a decent amount of automation done with this simple procedure.

Step1:
Press the following in sequence:
ALT then t then m then r
That will bring up this screen:

Step2:
Press OK, and now do some basic actions like inserting a column.

Step3:
Stop the macro recording:

Step4:
Now press ALT and F11, double click on "module1" and look what the macro recorder has recorded.
It will look something like this:

Examining the code, we can see that we selected column D, and inserted a column.

Now you attach the code to a button using our first VBA lesson.

Download sheet to practise recording macros, and press the button

Now we're going to learn what VBA SUBS and FUNCTIONS are.

A SUB is a piece of code that runs something
A FUNCTION is a piece of code that returns something

Now lets see it in action with a real life example:

Your wife asks you the time.
You look at your watch
You tell her the time.

What would this look like in code ?

- If we attach the Sub TellWifeTheTime to a button and then press it, what would happen ?
- the Sub TellWifeTheTime goes away and gets the time from the function WhatTimeIsIt, and then displays the time in a message box

Download practise sheet, and press the button

Loops are fundamental to programming, and in this tip, we'll look at FOR loops.

If you have never programmed before then this step by step guide will show you how to write your first program.

Q. What is a loop ?
A. I think this is best explained using an example. Suppose you're at a farm, and a pig has just given birth to 10 piglets. The farmer asks you which is the biggest piglet. In order to answer this question, you will look at how big each piglet is, and once you have looked at all the piglets, you'll make your decision. A loop is the programming equivalent of looking at each piglet.

Now lets use this example.

Step1:
When you have the data as shown in the picture, Press ALT + F11 to bring up the VBA editor.

Step2:
You'll now need to insert a module, so right click on the blue area shown in the screen shot, and go: insert=>module

Step3:
Now, in the white space provided, type the following code:

Step4:
Click anywhere within the code, and press F5
- F5 runs code.

You will now see a message box showing which piglet is the biggest.

Download workbook to play with