2 Types of VBA Macros in Excel

Macro Type 1 – VBA Sub Procedures

A Sub-procedure acts as a new command. Different users can use this command and other Macros can execute the Sub procedure. You can have any number of Sub procedures in an Excel workbook.

The following figure shows a simple VBA Sub procedure. When this code is executed, VBA will display the current date in the active cell, apply the number format “mmmm d, yyyy”, bols the cell, set the text color to white, set the background color to black, and adjust the column width.

Two types of VBA Macros: VBA Sub procedures & VBA functions

A simple VBA procedure.

How to Create a Sub-Procedure

Subprocedure syntax system.

Subprocedures always start with “Sub”, the macro name (a unique name for every macro) and parentheses (they are empty if there are no arguments). The “End Sub” statement signals the end of the procedure. The lines in between comprise the code.

Using Comments in the Sub-Procedure

The CurrentDate macro has a comment. Comments are notes for users that are ignored by VBA. A comment line begins with an apostrophe.

Executing a Sub-Procedure

Select Developer ➪ Code ➪ Macros or press Alt+F8 to display the Macro dialog box.
Select the procedure name from the list and click Run.

Press the procedure’s shortcut key combination (if there is one).
Click a button or any other shape that has a macro assigned to it.
If the VB Editor is active, move the cursor within the code and press F5.
Execute the procedure by calling it from another VBA procedure.

Macro Type 2 – VBA Function

The VBA function is like a worksheet function. It returns a single value and can be executed by other VBA procedures or used in worksheet formulas.

This VBA function returns the cube root of its argument.

The image above shows a custom worksheet function (CubeRoot). It has a single argument and calculates the cube root of its argument. The function starts with a Function and ends with an End Function statement.