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.


Download Practice Workbook

You can download the practice book from the link below.


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.


2 Types of VBA Macros in Excel

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.

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

A simple VBA procedure.


How to Write a Sub-Procedure

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.


Similar Readings


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.

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.

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.

Read More: How to Use Excel VBA User Defined Function in Formula


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.

Executing Functions in VBA Macros in Excel

Read More: How to Use Excel VBA User Defined Function in Formula


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

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment
  1. I was not able to figure out how to get “CubeRoot” to run.

Leave a reply

ExcelDemy
Logo