In VBA, everything starts with a procedure. When you record a macro, Excel writes a procedure. When you run a macro with a button, you run a procedure. Here is what it is, how to write one, and how to make several procedures work together.
A procedure does something
A procedure is a block of code that performs actions. For example, a procedure can:
- sort a list;
- copy the content of a sheet to another sheet;
- create a new workbook and export data into it;
- send a message to the user;
- look for inconsistent values in a column;
- format a report, add the borders and the colors;
- delete the empty rows of a table;
- save a copy of the workbook in a folder.
⚠️ A procedure works on your data, but it doesn’t return any result. This is the difference with a VBA function, which calculates a value and sends it back.
In VBA, a procedure is called a Sub. This word comes from subroutine, the old name used in programming for what we now call a procedure.
A procedure starts with Sub, followed by its name, and ends with End Sub. Between the two, you write all your instructions, as many as you need:
Sub MyFirstProgram()
Dim MyNumber As Long
For MyNumber = 1 To 10
Cells(MyNumber, 1) = MyNumber
Cells(MyNumber, 2) = MyNumber + MyNumber
Cells(MyNumber, 3) = MyNumber * MyNumber
Next MyNumber
End Sub- The name can’t contain spaces or start with a number. Use a name that says what the procedure does, like FormatReport or CopyToArchive.
- The parentheses after the name are mandatory, even when they are empty.
- Everything between Sub and End Sub runs from top to bottom.
Where to write a procedure
💡 New to the VBA editor? Start with First Steps with VBA: Discover the VBA Editor.
Open the VBA editor with Alt + F11 (if you don’t see the Developer tab, see Enable the Developer Tab in Excel). Then, in the menu, choose Insert › Module, and type your procedure in the white window.
How to run a procedure
- In the VBA editor: click inside the procedure and press F5.
- In Excel: press Alt + F8, select the macro and click Run.
- With a button: in the Developer tab, insert a button and assign the procedure to it.
Call a procedure from another one
A long macro is easier to read when you split it into steps. A main procedure then calls the others, in order:
Sub MonthlyReport()
ImportData
FormatReport
PrintReport
End SubYou can also write Call ImportData: the result is the same, the word Call just makes the call more visible.
When you need a function instead
Imagine you want the total of a column with a 20% tax. A procedure can calculate it and write it in a cell, but you can’t type it in a cell like a formula. For that, you need a function: a block of code that calculates a value and gives it back to you, exactly like SUM or VLOOKUP. We explain it in What Is a Function in VBA?