1 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 :