Learn Excel VBA....

You can easily learn VBA by working your way through the variety of free lessons that we offer ...

If you think that creating macros is too complicated or beyond your capacity, don't worry, this course is intended for those of us who are just starting out in programming, so it starts from zero.

Just like our Excel course, these lessons have lots of examples, and we've simplified them as much as possible so that it's easy to learn VBA.

We recommend that you work your way through the lessons in the order in which they are provided, because at each level you will need the skills acquired in the previous ones.

VBA Course : Introduction

 

A new tab will be added :

If the version of Excel that you are using is lower than 2007, add the "Control Toolbox" and "Formulas" toolbars.

To work with VBA code, we'll need an editor, which is installed by default. You can open it by pressing the shortcut key combination "Alt F11" :

 

VBA Course : First Macro

The macro recorder makes it very easy to automate certain tasks.

To give just one example, we will automate the following actions :

• delete the contents of columns A and C
• move the contents of column B to column A
• move the contents of column D to column C

To do this, click on "Record Macro" and then "Ok", carry out the actions described above without interruption (because everything you do will be recorded) and then click on "Stop Recording".

For versions of Excel lower than 2007 : Tools > Macros > Record New Macro.

 

Excel has recorded your actions and translated them into VBA code.
To view your macro, open the editor (Alt F11) and click on "Module1" :
This code represents the recorded actions.
Let's take a moment to look at the code that Excel has generated :

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:A").Select
    Selection.ClearContents
    Columns("C:C").Select
    Selection.ClearContents
    Columns("B:B").Select
    Selection.Cut Destination:=Columns("A:A")
    Columns("D:D").Select
    Selection.Cut Destination:=Columns("C:C")
    Columns("C:C").Select
End Sub

Sub and End Sub mark the beginning and end of the macro, and "Macro1" is the name of this macro :

Sub Macro1()

End Sub

Let's edit the name of the macro to make it more descriptive, changing "Macro1" to "column_handling" (the name of the macro cannot contain any spaces) :

Sub column_handling()

The text in green (text preceeded by an apostrophe) is commentary, and will be ignored when the code is executed :

'
' Macro1 Macro
'

'


This kind of commentary can be very useful for finding things when there is a lot of code, or when you want to prevent the execution of certain lines of code without deleting them.

Sub column_handling()
'
'My first commentary !
'
    Columns("A:A").Select
    Selection.ClearContents
    Columns("C:C").Select
    Selection.ClearContents
    Columns("B:B").Select
    Selection.Cut Destination:=Columns("A:A")
    Columns("D:D").Select
    Selection.Cut Destination:=Columns("C:C")
    Columns("C:C").Select
End Sub

Now we want this macro to be executed at the click of a button.
Click on Insert > Button (Form controls) :

For versions of Excel lower than 2007 : "Button" from the "Formulas" toolbar.

Insert your button and then just select the macro that you created :


When you click on the button, your macro will be executed :

continues...