Types of VBA Macros in Excel (A Quick Guide)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.


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.

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

Executing Functions in VBA Macros in Excel


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo