What Is a Procedure (Sub) in VBA?

Reading time: 2 minutes

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.

VBA procedure MyFirstProgram in Module_Init and its result in the worksheet

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 Sub

You 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?

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

What Is a Procedure (Sub) in VBA?

Reading time: 2 minutes

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.

VBA procedure MyFirstProgram in Module_Init and its result in the worksheet

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 Sub

You 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?

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *