VBA Course: First Macro

It is possible to automate certain tasks quite simply thanks to the macro recorder.

For a simple example, we will automate the following operations:

  • Clear the contents of columns A and C
  • Move the content of column B to column A
  • Move the content of column D to column C

macro1 first macro

To do this, click on Record Macro then on Ok, perform the above operations without interruption (as all manipulations are recorded) and to finish click on Stop Recording.

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

macro2 first macro

Excel has recorded your manipulations and translated them into VBA code.

To see your macro, open the editor (Alt + F11) and click on Module1:

macro3 first macro

This code corresponds to the recorded manipulations.

We will pause for a moment on the generated code:

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 delimit the beginning and the end of the macro, Macro1 corresponds to the name of this macro:

Sub Macro1()

End Sub

We will now change the name of this macro and give it a name that is a bit more meaningful. To do this, simply replace Macro1 with columnManipulations (the name must not contain spaces):

Sub columnManipulations()

The text in green (text preceded by an apostrophe) is a comment, it is not taken into account when executing the code:

' 
' Macro1 Macro 
' 

' 

Comments are very useful for finding your way around when you have a lot of code or for not executing certain lines of code without deleting them.

Sub columnManipulations()

    'My first comment

    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

We now want this macro to execute when clicking on a button.

Insert a button by clicking on Insert Button (Form controls):

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

macro4 first macro

Draw your button and then simply select your macro:

macro5 first macro

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

macro6 first macro