How to Use VBA Modules in Excel - 8 Simple Methods

Types of VBA Modules

There are different types of VBA modules:

Standard Module: This type of module holds VBA codes or Customized codes. Standard modules are located in the module folder in the workbook.
Sheet Module: This type of module holds a code that is specially written for that specific sheet. Every worksheet in an Excel workbook has its own sheet module.
Workbook Module: This module is named ThisWorkbook by default. Every Excel workbook has only one workbook module. This type of module holds codes for workbook events.
UserForm Module: UserForm helps to create a customized dialog box. UserForm module holds a code for the specific UserForm. An Excel workbook can contain multiple UserForm modules.
Class Module: This type of module helps to write codes that create objects, properties, and methods. You can create an object that does not exist in the Object Library using the class module.

VBA Objects

VBA works with Objects. Excel provides more than 100 built-in objects that you can work with. Almost everything you create is an object. Examples of some objects are the whole Excel, a workbook, a worksheet, a cell range on a worksheet, a chart or a rectangle shape.

Objects are arranged in a hierarchy. An object can contain one or more other objects. Excel’s object name is Application. Excel holds other objects such as Workbook objects. The Workbook object holds objects like Worksheet objects and Chart objects. A Worksheet object can hold Range objects, PivotTable objects, etc.

The same type of object forms objects collections. For example, all worksheets in a workbook make the Worksheets collection. In the same way, all the charts on a worksheet make ChartObjects collection. Collections are also objects.

You can refer to an object in your VBA code specifying its position in the object hierarchy. You can separate objects with a period (.) separator.
For example, you can refer to a workbook named Expenses.xlsx as
Application.Workbooks(“Expenses.xlsx”)
This expression expresses that the “Expenses.xlsx” workbook is one of the workbooks in the Workbooks collection. The Workbooks collection is in the Application object (it is, Excel). You can refer to Sheet1 in the “Expenses.xlsx” workbook as follows:
Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”)
You can also refer to a specific cell (“A1”) of “Sheet1” as follows:
Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”)

If you omit specific objects from the expression, Excel uses the active objects. If “Expenses.xlsx” is the active workbook, our preceding expression (Application. Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”)) can be simplified as follows:

Worksheets(“Sheet1”).Range(“A1”)

When “Sheet1” is the active sheet, it is possible to simplify the expression:

Range(“A1”)

Objects have properties. A property is basically a setting for an object. For example, a Range object has many properties: Value and Address are two of them. A Chart may have a title, type, and many other settings. These settings are the Chart object’s properties but may have a different name: HasTitle and Type. You can use VBA to set object properties. VBA can also be used to change the values of properties.

You can combine objects with properties separating them by a period (.).Use the object and indicate properties of that object. For example, you can express the value of cell A1 on Sheet1 as follows: Worksheets(“Sheet1”).Range(“A1”).Value

You can assign values to variables. A variable is a VBA element that stores a value or text. You set the variable Interest and want to store the value of A1 in this variable. Assume A1 is in “Sheet1” of the “Expenses.xlsx” workbook. You can use this VBA statement to insert the value in A1 in the Interest variable: Interest = Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value

Objects have methods. Using a method you can perform specific tasks. Methods are of both types: built-in and customized. For example, the Range object has a built-in method: ClearContents. This method clears the contents of the range.

You can combine objects with methods separating them by a period (.). Use the object and then the methods that can be performed with that object. For example, to clear the contents in E11: F20, use the following VBA statement:
Worksheets(“Sheet1”).Range(“E11:F20”).ClearContents

VBA also includes modern programming languages, including typed variables, arrays, looping, debugging aids, and so on.

This is the sample dataset.

Method 1 – Inserting VBA Modules in Excel

Steps:

Go to the Developer tab.
Select Visual Basic.

You can also press ALT+F11 to open the Visual Basic editor.

The Visual Basic editor window will open.

Select Insert.
Choose Module.

Enter the VBA code in the module.

Rename the module in Properties. Here, Sub_Procedure.

Read More: What You Can Do with VBA

Method 2 – Creating a Sub Procedure

Steps:

Enter the following code in the module.

Sub Showing_Sum()
    Sum_of_numbers = 10 + 6
    MsgBox "Result of Sum is: " & Sum_of_numbers
End Sub

Code Breakdown

A Sub Procedure: Showing_Sum is declared.
The Sum_of_numbers is set as 10+6. This will add 10 and 6 and assign the result as Sum_of_numbers.
the MsgBox function is used to display the Sum_of_numbers in a dialog box.
The Sub Procedure is ended.

Method 3 – Running the VBA Code

Steps:

Click Run.

In the Macros dialog box:

Select the Macro Name you want to run. Here, Showing_Sum.
Click Run.

You Macro will run and you will see the output.

Method 4 – Using the Macros Command to Run VBA Modules

Steps:

Go to the Developer tab.
Select Macros.

In the Macro dialog box:

Select the Macro Name. Here, Showing_Sum.
Click Run.

The Sub Procedure will run and you will get the output.

Method 5 – Creating a Function in VBA Modules

The following is the sample dataset. A function will be created to calculate the Area of the Rectangles.

Steps:

Enter the following code in the module.

Function Area_Rectangle(length, width)
    Area_Rectangle = length * width
End Function

Code Breakdown

A Function:Area_Rectangle is created. The arguments of the function – length and width, are specified in brackets.
Area_Rectangle is set as length*width. Here, the length of the rectangle will be multiplied by the width, the result is assigned as Area_Rectangle.
The Function is ended.

Save the code and go back to your worksheet.
Select a cell to display the Area of the rectangle. Here, E5.
In E5 enter the following formula.

=Area_Rectangle(C5,D5)

In the Area_Rectangle function, I selected C5 as length and D5 as width. The formula will multiply the value in C5 by the value in D5 and return the Area.

Press ENTER to get see the result.

Drag the Fill Handle to copy the formula to the other cells.

This is the output.

Method 6 – Removing VBA Modules in Excel

Steps:

Right-click the module you want to remove. Here, Module1.
Click Remove Module1.

In the warning box:

Select No.

The module is removed from your VBAProject.

You can also remove VBA Modules by:

Selecting the module: Module1.
Going to the File tab.
Selecting Remove Module1.

In the warning box:

Click No.

The module is removed from the VBAProject.

Method 7 – Exporting VBA Modules in Excel

Steps:

Right-click the module you want to export. Here, Module1.
Click Export File.

Or:

Select Module1.
Go to the File tab.
Select Export File.

In the Export File dialog box:

Select a folder to save the file.
Click Save.

Your file will be exported to the selected folder.

Method 8 – Importing VBA Modules

Steps:

Right-click the VBAProject.
Select Import File.

Select the file you want to import. Here, Module1.
Click Open.

The selected file is imported to your VBAProject.

VBA Code Examples in Excel

The following dataset contains Name, Age, and Color Code columns. Use a VBA code to assign Color Codes based on Age. People older than 18 will be assigned the Green color and the others the Red color.

How to create the VBA code:

Steps:

Go to the Developer tab.
Select Visual Basic.

In the Visual Basic editor window:

Select Insert.
Choose Module.

In the module, enter the following code.

Sub Assign_color()
Dim cell_value As Integer
    Dim return_cell As String
    For row_no = 5 To 11
    cell_value = Worksheets("VBA Example").Cells(row_no, 3).Value
        If cell_value > 18 Then
            return_cell = vbGreen
            Else: return_cell = vbRed
        End If
    Worksheets("VBA Example").Cells(row_no, 4).Interior.Color = return_cell
    Next
End Sub

Code Breakdown

A Sub Procedure: Assign_color is created.
A variable: cell_value as Integer and another variable: return_cell as String are declared.
A For Next Loop goes through the rows in columns.
cell_value is set as Worksheets(“VBA Example”).Cells(row_no, 3).Value. This will assign the value in that particular cell of that particular worksheet as cell_value.
An IF Statement is used to check if the cell_value is greater than 18. If it is greater than 18, the return_cell is assigned vbGreen. Otherwise, the return_cell is assigned as vbRed.
The IF Statement is ended.
The Sub Procedure is ended.