Types of VBA Macros in Excel (A Quick Guide)

Are you looking for a proper and complete guide about the types of VBA macro? Don’t worry, you have landed in the right place. In this article, I will discuss about types of VBA macros in Excel.

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

VBA Macros in Excel

VBA (Visual Basic for Applications) is the programming language for Microsoft Excel and other Microsoft Office programs. If you want to automate tasks in Excel, you can do it by writing macros with VBA. In case of getting repetitive solutions to formatting and correction problems, an effective way is VBA. Excel VBA macro uses the VBA and creates user-generated functions automating the whole process. A VBA macro is also known as a procedure.

To access the VBA window, press ALT+F11. This will take you to the VBA editor window from the Excel worksheet. Then, click Insert and select Module to open the module window. Here, you can assign your code.

Types of VBA Macros in Excel (A Quick Guide)

Before we dig into how to create macros, we need to understand the types of VBA macros. For this purpose, you need to understand a key distinction. 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 with proper illustrations. Let’s check them now!

Macro Type-1: VBA Sub Procedures

Sub-procedure can be treated as a new command like other commands in Excel. Any user of your workbook can use this command. Another Macro can also execute a 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 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.

A simple VBA procedure.

How to Write a Sub-Procedure

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 Sub-Procedure

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.

Executing a Sub-Procedure

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

• 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.

Read More: 25 VBA Macro Example for Enhanced Productivity

Macro Type-2: VBA Function

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.

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. The 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.

Executing VBA Function

In order to execute the above VBA function, follow the steps below.

• First of all, switch to the excel window by pressing ALT+Q.
• Now, select a cell and start typing the function name you have assigned in the code. Excel will suggest the name.

• Then, double-click on the function name and input the number you want to get the result (I have assigned 27).

• Hence, the cell will show the cubic root of the assigned number.

Download Practice Workbook

You can download the practice book from the link below.

Conclusion

In this article, I’ve tried to show two types of VBA macros in Excel. I hope you like the article. If you have any queries or suggestions helpful regarding the article, don’t forget to share them in the comment box below. You can visit our website also for more articles related to VBA. Keep in Touch!

Happy Excelling 🙂

Related Articles

• How to Save VBA Code in Excel
• Using Macro Recorder in Excel
• How to Record a Macro in Excel
• Excel Macro Shortcut Key
• How to Edit Macros in Excel
• How to Remove Macros from Excel
• How to Save Macros in Excel Permanently
• How to Use Excel VBA to Run Macro When Cell Value Changes
• Excel VBA to Pause and Resume Macro
• Excel Macro Enabled Workbook