Kawser Ahmed
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.
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.
Similar Readings
- How to Create a UserForm: an Overview
- Use VBA Input Function in Excel (2 Examples)
- How to Use Excel Combo Box (A Complete Guideline)
- What are Excel Events and Their Types
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.
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.
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 🙂
I was not able to figure out how to get “CubeRoot” to run.