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

Before we digging in how to create macros, we need to understand the types of VBA macros.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

Before getting into the details of creating macros, you need to understand a key distinction. A VBA macro is also known as a procedure. Macro can be of two types: a Sub or a Function. We’re going to discuss the differences between VBA Sub procedures and VBA functions.

VBA Sub procedures

Subprocedure can be treated as a new command like other commands in Excel. Any user of your workbook can use this command. A Sub procedure can also be executed by other Macro. 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 insert the current date into the active cell, apply a number format like “mmmm d, yyyy”, make the cell bold, 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 write a Subprocedure

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

Subprocedure syntax system.

Subprocedures always start with the keyword “Sub”, the macro’s name, and then a pair of parentheses. Every macro must have a unique name. The parentheses are required. They’re empty if the procedure doesn’t use any arguments. The “End Sub” statement signals the end of the procedure. The lines in between comprise the procedure’s code.

Using comment in Subprocedure

The CurrentDate macro also has a comment. Comments are simply notes for users, and they’re ignored by VBA. A comment line begins with an apostrophe. You can also put a comment in the same line as a statement. In other words, when VBA encounters an apostrophe, it ignores the rest of the text in the line.

 Read More: How to create VBA Macros in Excel using Macro Recorder

Executing a Subprocedure

A VBA Sub procedure can be executed using any of the following ways:

    • Choose Developer ➪ Code ➪ Macros or press Alt+F8 to display the Macro dialog box. Select the procedure name from the list, and then click Run to execute the Subprocedure
Two types of VBA Macros: VBA Sub procedures & VBA functions

Macro dialog box. Select a Sub procedure from the list.

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

VBA functions

VBA function is the second type of VBA procedure. Like a worksheet function, A VBA function returns a single value. A VBA function can be executed by other VBA procedures or used in worksheet formulas like we use Excel’s built-in functions in a worksheet cell.

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

This VBA function returns the cube root of its argument.

The figure, besides, shows a custom worksheet function. This function is named CubeRoot, and it needs a single argument to perform its job. CubeRoot VBA function calculates the cube root of its argument passed to it and returns the result. A VBA function procedure looks much like a Sub procedure discussed above. Notice that function procedures start with the keyword Function and end with an End Function statement.

Download this file to practice yourself

vba-macro.xlsm

Happy Excelling 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply