Before we digging into 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 another 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.
How to write a Subprocedure
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.
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
- 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 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.
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
Happy Excelling 🙂