How to Use VBA Modules in Excel (8 Simple Ways)

VBA is the most complex feature in Excel. If you have a background in object-oriented programming languages like C++, Java, or any other language, you can grasp the VBA idea easily. Before going into details, here is a concise summary of how VBA Modules actually work in Excel.


Download Practice Workbook


What Are VBA Modules in Excel?

We write VBA codes in VBA modules to perform our actions in Excel. We then execute this macro in various ways. VBA modules are stored in an Excel workbook. You can store any number of VBA modules in a workbook.


Types of VBA Modules

There are different types of VBA modules. They are:

  1. Standard Module: This type of module mostly holds the VBA codes. Customized codes are mainly written in this type of module. The standard modules are located in the module folder in the workbook.
  2. Sheet Module: This type of module holds code that is specially written for that specific sheet. Every worksheet in an Excel workbook has its own sheet module.
  3. Workbook Module: This module is named ThisWorkbook by default. Every Excel workbook has only one workbook module. This type of module holds codes that are for workbook events.
  4. UserForm Module: UserForm helps to create a customized dialog box. UserForm module holds code for that specific UserForm. An Excel workbook can contain multiple UserForm modules.
  5. 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 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 the object. Examples of some objects are whole Excel, a workbook, a worksheet, a cell range on a worksheet, a chart, and a rectangle shape.

Objects are arranged in a hierarchy. An object can contain one or more other objects. Excel itself is an object. 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, and so on.

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 as follows:

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 Chart object’s properties but maybe in 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 (.). Firstly, use the object, then we place any of the 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. Say, you set a variable Interest and you want to store the value of cell A1 in this variable. Assume cell A1 is in “Sheet1” of the “Expenses.xlsx” workbook. You can use this VBA statement to insert the A1’s value in the Interest variable: Interest = Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value

Objects have methods. Using a method you can perform some specific job with the object. 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 (.). At first, we use the object, then we place any of the methods that can be performed with that object. For example, to clear the contents of the range E11: F20, use the following VBA statement:
Worksheets(“Sheet1”).Range(“E11:F20”).ClearContents

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


8 Simple Ways to Use VBA Modules in Excel

Here, I have taken the following dataset. I will use this dataset to explain how VBA modules work in Excel. I will explain 8 different ways in which VBA modules work.

vba modules excel


1. Inserting VBA Modules in Excel

Here, I will explain how you can insert VBA modules in Excel. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

On the other hand, you can simply press ALT+F11 on your keyboard to open the Visual Basic editor. This is the keyboard shortcut for opening the Visual Basic editor.

Inserting VBA Modules in Excel

Now, the Visual Basic editor window will open.

Opening Visual Basic Window to Insert VBA Modules in Excel

  • Firstly, select the Insert tab.
  • Secondly, select Module.

Here, a module will open. You can write VBA code in this module.

  • After that, you can rename the module from Properties if you want. Here, I renamed mine as Sub_Procedure.

Renaming VBA Modules in Excel


2. Creating a Sub Procedure

A VBA module is made up of procedures. A procedure is a piece of computer code that performs some actions. I will show a simple example of a Sub procedure. Let’s see the steps.

Steps:

  • Firstly, write the following code in a module.
Sub Showing_Sum()
    Sum_of_numbers = 10 + 6
    MsgBox "Result of Sum is: " & Sum_of_numbers
End Sub

Creating a Sub Procedure in VBA Modules in Excel

Code Breakdown

  • Here, I created a Sub Procedure named Showing_Sum.
  • Next, I set Sum_of_numbers as 10+6. This will add 10 and 6 and assign the result as Sum_of_numbers.
  • After that, I used the MsgBox function to display Sum_of_numbers in a tiny dialog box.
  • Finally, I ended the Sub Procedure.

3. Running VBA Modules from Editor

In this part, I will show you how you can run VBA modules in Excel from the Visual Basic editor. Let’s see the steps.

Steps:

  • Firstly, click on the Run button.

Running VBA Modules from Editor in Excel

After that, a dialog box named Macros will appear.

  • Now, select the Macro Name you want to run. Here, I selected Showing_Sum.
  • Then, select Run.

Finally, your Macros will run and you will get the output.


4. Using Macros Command to Run VBA Modules

Here, I will show you how to use the Macros command to run VBA modules in Excel. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Macros.

Using Macros Command to Run VBA Modules in Excel

After that, a dialog box named Macro will appear.

  • Then, select the Macro Name you want to run. Here, I selected Showing_Sum.
  • Next, select Run.

Finally, the Sub Procedure you created will run and you will get your desired output.


5. Creating a Function in VBA Modules

A VBA module may also have Function procedures. A Function procedure internally performs calculations and returns a single value. A Function can be called from another VBA procedure. You can also use the Function directly in a worksheet formula. These types of functions are called User Defined Functions.

I have taken the following dataset to explain this example. Here, I will use VBA modules in Excel to create a function that will calculate the Area of the following Rectangles.

Creating a Function in VBA Modules in Excel

Let’s see the steps.

Steps:

  • Firstly, write the following code in a module.
Function Area_Rectangle(length, width)
    Area_Rectangle = length * width
End Function

Code Breakdown

  • Here, I created a Function named Area_Rectangle. In the brackets, I specified the arguments of the function as length and width.
  • Next, I set Area_Rectangle as length*width. Here, the length of the rectangle will be multiplied by the width, and assign the result as Area_Rectangle.
  • Finally, I ended the Function.
  • After that, save the code and go back to your worksheet.

Now, I will use the Function.

  • Firstly, select the cell where you want the Area of the rectangle. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=Area_Rectangle(C5,D5)

Here, in the Area_Rectangle function, I selected cell C5 as length and D5 as width. The formula will multiply the value in cell C5 by the value in cell D5 and return the Area of a rectangle as result.

  • Finally, press ENTER to get the result.

  • After that, drag the Fill Handle to copy the formula to the other cells.

Now, the formula is copied to the other cells and you can see that the function is working properly.


6. Removing VBA Modules in Excel

In this part, I will show you how you can remove VBA modules in Excel if you don’t need them anymore. Let’s see the steps.

Steps:

  • Firstly, Right-click on the module you want to remove. Here, I want to delete Module1.
  • Secondly, Remove Module1.

Removing VBA Modules in Excel

Here, a warning box will appear asking if you want to export the module before removing it.

  • Thirdly, select No as you don’t need the module anymore.

Finally, you will see the selected module is removed from your VBAProject.

There is another way of removing VBA Modules in Excel.

  • Firstly, select the module you want to remove. Here, I selected Module1.
  • Secondly, go to the File tab.
  • Thirdly, select Remove Module1.

Now, a warning box will appear asking if you want to export the module before removing it.

  • After that, select No as you don’t need the module anymore.

In the following picture, you can see that the selected module is removed from the VBAProject.


7. Exporting VBA Modules in Excel

Here, I will explain how you can export VBA modules in Excel and save it as a file. Let’s see the steps.

Steps:

  • Firstly, Right-click on the module you want to export. Here, I want to export Module1.
  • Secondly, Export File.

Exporting VBA Modules in Excel

Or, you can follow the following steps.

  • Firstly, select the module you want to export. Here, I selected Module1.
  • Secondly, go to the File tab.
  • Thirdly, select Export File.

After that, a dialog box named Export File will appear.

  • Now, select the folder where you want to save the file.
  • Then select Save.

Finally, your file will be exported to the selected folder.


8. Importing VBA Modules

In this part, I will explain how you can import VBA modules to your VBAProject. Let’s see the steps.

Steps:

  • Firstly, Right-click on the VBAProject.
  • Secondly, select Import File.

Importing VBA Modules in Excel

  • Thirdly, select the file you want to import. Here, I selected a module named Module1.
  • After that, select Open.

Finally, you will see that the selected file is imported to your VBAProject.


VBA Code Examples in Excel

In this section, I will show you an example of VBA code in Excel. For this example, I have taken the following dataset. This dataset contains the Name, Age, and Color Code columns. I will use a VBA code to assign Color Codes according to their age. The people whose ages are more than 18 will be assigned the Green color and the others will be assigned the Red color.

VBA Code Examples in Excel 

Let me show you how you can write the VBA code in Excel.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic. 

After that, the Visual Basic editor window will open.

  • Now, select the Insert tab.
  • Then, select Module.

Here, a module will be inserted. In that module, write 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

  • Here, I created a Sub Procedure named Assign_color.
  • Next, I declared a variable named cell_value as Integer and another variable named return_cell as String.
  • After that, I used a For Next Loop to go through the rows in columns.
  • Then, I set cell_value 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.
  • Next, I used an IF Statement to check if the cell_value is greater than 18. If it is greater than 18 then the return_cell will be assigned as vbGreen. Else the return_cell will be assigned as vbRed.
  • After that, I ended the IF Statement.
  • Finally, I ended the Sub Procedure.

Now, save the code and go back to your worksheet.

  • Firstly, go to the Developer tab.
  • Secondly, select Macros.

After that, a dialog box named Macro will appear.

  • Then, select the Macro Name you want to run. Here, I selected Assign_color.
  • Next, select Run.

Finally, you will see that your VBA code will run and you will get your desired output.


Things to Remember

  • Whenever working with Excel VBA, you must save the file as Excel Macro-Enabled Workbook. Otherwise, the macros won’t work.

Practice Section

Here, I have provided a practice sheet for you to practice how VBA modules work in Excel.

Practice Sheet for How VBA Modules Work in Excel


Conclusion

To conclude, I described how VBA modules work in Excel in a nutshell. For related articles, stay connected with ExcelDemy. And, if you have any questions, feel free to let me know in the comment section below.


This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.


Read More…

Tags:

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment

Leave a reply

ExcelDemy
Logo