Nick's step by step guide to learn VBA
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
Further reading:
Attachment | Size |
---|---|
LearnVBA_Lesson1.xls | 30 KB |
LearnVBA_Lesson3.xls | 31.5 KB |
record-macro-excel.xls | 30.5 KB |
record-macro-VBA.xls | 28.5 KB |
»
- Nick's blog
- Login or register to post comments
- 366763 reads
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.
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
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:
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
Thank you
Thanks a lot, this has been really useful and easy to follow. Thank you.
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
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.
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
?
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