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
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.
Excel has recorded your manipulations and translated them into VBA code.
To see your macro, open the editor (Alt + F11) and click on Module1:
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.
Draw your button and then simply select your macro:
When you click on the button, the macro will be executed: