Nick's step by step guide to learn VBA

Nick's picture

Learning VBA is all about building on a few fundamentals. Once you've started, it gets a lot easier. This VBA Training section will teach you those VBA fundamentals.

Hundreds of New VBA Training Videos added here

 

IMPORTANT: Worried about SECURITY? Open Excel in SAFE MODE, examine the VBA, and if you're happy, open Excel normally


Further reading:

AttachmentSize
LearnVBA_Lesson1.xls30 KB
LearnVBA_Lesson3.xls31.5 KB
record-macro-excel.xls30.5 KB
record-macro-VBA.xls28.5 KB
Nick's picture

5. VBA Lesson - What does my recorded Macro code actually mean ?


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.
record-macro-VBA

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:
record-macro-excel

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.
record-macro-VBA

Step3:
Stop the macro recording:
record-macro-excel

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

It will look something like this:
record-macro-VBA

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

Nick's picture

4. Learn VBA - Record a Macro


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:
record-macro-excel

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

Step3:
Stop the macro recording:
record-macro-excel

Step4:
Now press ALT and F11, double click on "module1" and look what the macro recorder has recorded.
It will look something like this:
record-macro-excel
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

Thank you

Thanks a lot, this has been really useful and easy to follow. Thank you.

Nick's picture

3. Learn VBA - SUBS and FUNCTIONS explained

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

when to use functions??

As in the above code there is no need of function. so can you please suggest when to go for function.

it makes the code cleaner,

it makes the code cleaner, more easily read, and easier to debug. Think of the phrase "spaghetti code"

Rather than suddenly having my programming running down some tangentially related function which is a nightmare to comprehend, I can have that happen in a later paragraph.

when you need something

when you need something returned

Nick's picture

2. Learn VBA - FOR Loops

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.
Data

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
Data

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

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.
Data

Download workbook to play with

?

Ok but could you please explain the code itself? Offset? Maxweight? I don't think anybody is going to use that exact example, we need to learn how to build it from scratch, thanks

Code

Offset:
Range("A1").offset(2,1) means B3.. go down 2 and across 1 from A1

MaxWeight - This is a variable that we use to store the maximum weight