VBA Macros

What Is VBA Macro In Excel?

VBA or Visual Basic Application is a programming language used in the Microsoft applications like MS Excel, MS Word, MS PowerPoint, etc. Macro – Macro is a piece of code written in Excel to perform tasks frequently. VBA Macro in Excel automates repetitive and complex tasks to save time and manual work. We can record the VBA Macro in Excel and run it as per our requirement.

What Is VBA Macro In Excel?

VBA Macro is used to automate tasks that are time-consuming and repetitive.
We can use VBA Macros in 2 ways,
- Method 1: Select the “Developer” tab > go to the “Code” group > click on the “Record Macro” option.
- Method 2: Write a Macro code in the Macro window.
We can make the Macro interactive by inserting a Text Box or a Combo Box and link it to a Macro file. This feature helps us to easily run the Macro with just a click of a button.

Enable Developer Tab In Excel

Whenever we open an Excel worksheet, we will see the “Developer” tab on the Excel ribbon, as shown below.

However, if we are using the “Developer” tab for the first time, then the tab may not be available. Therefore, we must first enable the “Developer” tab as follows:

1: Go to the “File” tab.

2: Click on the “More…” option from the list > select the “Options”.

3: Once, the “Excel Options” window pops up, then click on the “Customize Ribbon” on the left side of the “Excel Options” window.

4: Check the “Developer” tab checkbox under the “Main Tabs” on the right side of the “Excel Options” window.

5: Click on “OK”. The “Developer” tab is enabled and appears on the ribbon, as shown below.

How To Record Macros In Excel VBA?

We can record Macros with the help of the “Developer” tab. We have a “Record Macro” option in the “Code” group of the “Developer” tab, as shown below.

At this point, we will enter the text as “VBA Macros” in the cell A1 and apply formatting to the text.

1: Select the “Developer” tab > go to the “Code” group > click on the “Record Macro” option.

2: The “Record Macro” window pops up, as shown below.

3: Type “Record Macro” as the name in the “Macro Name” field of the “Record Macro” window.

4: Click on “OK”. The recording starts immediately. Straightaway, Excel will continually capture all the activities and store them in the VBA code until we stop recording.

The first activity is to select the cell A1.

5: Enter the text “VBA Macros” in the selected cell.

6: Further, press the “Enter” key to move to the below cell i.e., the cell A2.

7: Once again, select cell A1 and apply the following formatting, Font > “Times New Roman”, Font Size > 12, Font Color > “Green” and Font Style > Bold.

8: Finally, select the “Developer” tab > go to the “Code” group > click on the “Stop Recording” option.

[Note: The “Record Macro” option changes to the “Stop Recording” when the recording starts, it will again change back to the “Record Macro” when we press the “Stop Recording”]

The recording stops, and the Macro is saved automatically for future use.

We can test the VBA Macro recording as follows:

1: Delete the cell A1 value and select any other value.

2: Select the “Developer” tab > go to the “Code” group > click on the “Macros” option.

3: The “Macro” window pops up. Now, choose the Macro we just recorded i.e., “Record_Macro”, and then click on the “RUN” button.

When we click “RUN”, it will perform all the activities [from 4 to 7] that are recorded in Macro.

VBA Macros Examples

We will learn the functionality of VBA Macros by writing Macro Codes.

Example #1 – Insert Serial Numbers

We will insert the serial numbers from 1 to 2 as follows,

Step 1: Select the “Developer” tab > go to the “Code” group > click on the “Visual Basic” option. [Alternatively, press the shortcut keys “ALT + F11” to open the “Visual Basic editor” window].

Now, the “Microsoft Visual Basic for Application”, also known as the “Visual Basic editor”, window pops up, as shown below.

Step 2: Select the “Insert” tab > click on the “Module” option > a “Module(Code)” window pops up, as shown below.

Step 3: We will write the code. First, type “Sub” [denotes the sub-procedure] and then name the Macro as “Serial_Numbers”, with “( )”, so the first line of the MAcro Code is Sub Serial_Numbers() as shown below.

Step 4: Press the “Enter” key, and the “End Sub” appears, as shown below.

Here, Macro Head >“Sub” and the Macro name.
Macro Tail >“End Sub”.
In between the Macro Head and the Macro Tail, we will write the Macro codes to execute tasks.

Step 5: First, insert the serial number 1 in cell A1. To access cell A1, use the “Range” function.

As we can see, Excel shows the IntelliSense list.

Step 6: Now, open the bracket and then give the cell address A1 in double-quotes i.e., “A1”.

Step 7: The cell A1 is referenced. To enter a value into this cell, choose the VALUE property by entering a dot.

Step 8: Enter the equals sign[=] and give the Value as 1.

To execute the code, press the F5 key or click the “Run” button, as shown below.

The output is shown below, in cell A1.

Step 9: Similarly, to insert the value 2 in cell A2, enter the code, “Range(“A2”).Value = 2”

Step 10: Accordingly, repeat the same procedure until cell A10, as shown below, and press the “Run” button.

The output is shown below. The Macro inserted the serial numbers from 1 to 10 in the cells A1 to A10.

Example #2 – Write Macros to Format Selected Cells

We will write a VBA Macro to format the selected cells.

The “Microsoft Visual Basic for Application” window pops up, as shown below.

Step 2: Select the “Insert” tab > click on the “Module” option > a “Module(Code)” window pops up, as shown below,

Step 3: Additionally, enter the following code inside the module.

Selection.Font.Color = vbBlue

Selection.Font.Name = “Verdana”

Selection.Font.Size = 12

Selection.Interior.Color = vbGreen

Step 4: Select the cell range from A1 to A10 and press the “Run” button.

The output is shown below. The cell range A1 to A10 is vbGreen, and the font color is vbBlue.

Additionally, to make the Macro interactive, we can also add a button as follows:

In the Excel worksheet, select the “Insert” tab > go to the “Text” group drop-down > click on the “Text Box” option, as shown below.

Now, click on any cell on the worksheet, and a “Text Box” appears, as shown below.

Right-click on the “Text Box” and click on the “Edit Text” option.

Type the text as, “Click here to format”.

Right-click on the “Text Box” and click on the “Assign Macro” option.

A window with a list of available Macros in the workbook pops up. At this point, select the appropriate Macro, i.e., “Formatting”.

Click on the “OK” button, and the selected Macro is assigned to the “Text Box”.

Select any cell range, E4 to G7 and click on the “Click here to Format” “Text Box”.

The output is shown above. The “Text Box” acts like a button, makes the worksheet interactive, and executes the Macro by coloring the cell range with the color vbGreen.

How To Save Macro Workbook?

We must save the Excel workbook in the Macro-Enabled Workbook file extension.

Once we create the VBA Macro code, we will get the following prompt when we click the “Save” button.

This is because the earlier saved file version was .xlsx. Since we have created the Macro in this file, we get the error, “The following features cannot be saved in Macro-enabled workbooks”.

Hence, we need to save the file with the “Excel Macro-Enabled Workbook” file type.

Finally, when we change the file to the “Excel Macro-Enabled Workbook” file type, we will see the difference in the Icons between the normal file extension and the Macro file extension, as shown below. .XLSM is the Macro file, and .XLSX is the non-Macro file.

Important Things To Note

Firstly, the Developer tab in Excel will be disabled by default, so we need to enable it manually.
VBA stands for Visual Basic for Applications. VBA is the programming language for Microsoft Office Applications.
ALT + F11 is the shortcut key to open the Visual Basic editor
F5 is the shortcut key to run a Macro.
VBA Macro file should be saved as the type “Excel Macro-Enabled Workbook”.

Frequently Asked Questions

How to run VBA Macros in Excel?

There are multiple ways to run the VBA Macros, namely:

1. We can press the Function key F5.
2. We can assign a Macro to a button or a Text Box to run it.
3. In the Visual Basic editor window, press the “Run” button as shown in the following image.

Example 1.7

How to view VBA Macros in Excel?

We can view the saved VBA Macros in Excel as follows:

1. Select the “Developer” tab > go to the “Code” group > click on the “Visual Basic” option.
2. The Visual Basic editor window opens.
3. Towards our left, we will see the Modules or the names of the saved Macros. Double-click on the file you want to see.

Where are VBA Macros in Excel?

VBA Macros are available under the “Developer” tab in Excel, as shown in the below image. Therefore, if we cannot see the “Developer” tab, we must first enable it.

VBA Macros FAQ

Download Template

This article must help understand VBA Macros with its formulas and examples. You can download the template here to use it instantly.

VBA Macros Download