All About VBA in Excel (Part 2)

The next article by Mytour will provide you with all the information about VBA in Excel. Additionally, readers can refer to some other articles on Mytour to learn more about using Autofilter in VBA.

1. Adding Code in VB Editor

There are 2 places to add VBA code in Excel:

- The code window of an object. These objects can be workbooks, worksheets, user forms, etc.

- The code window of a module.

1.1 Module Window with Object Code Window

First, Mytour will introduce the differences between adding code in the module window and adding code in the code window of an object.

When adding code to any object, it depends on certain actions of the object to trigger that code. For example, if you want to unhide all worksheets in a workbook immediately upon opening the workbook, that code will access the ThisWorkbook object (representing the workbook).

Similarly, if you want to protect one worksheet after another worksheet is activated, that code will appear in the code window of the worksheet.

This activation method is called events, and we can associate a code to execute when an event occurs.

Conversely, code within modules must be manually executed or can be called from other subprograms.

When creating and recording a macro, Excel automatically generates a module and inserts the macro code into it. To run this code, we will have to manually execute the macro.

1.2 Adding VBA Code within a Module

While creating a macro, it automatically generates a module and inserts the code into it. However, when using the macro recorder, there are still some limitations, such as not being able to use loops or If Then Else conditions.

In such cases, the best approach is to manually copy and paste code or to write code by yourself.

A module can be used to store various types of VBA code:

1. Declaration: We can declare variables within a module. Variable declaration allows us to specify the data type that the variable can hold. We can declare a variable for a subroutine or for all subroutines within the module (or for all modules).

2. Subroutine: This is code containing steps that we want VBA to execute.

3. Function Procedures: These are codes that return a single value and we can use them to create custom functions (also called user-defined functions or UDFs in VBA).

By default, a module is not part of a worksheet, we need to insert a module before using it.

1.3 Adding a Module in VB Editor

Follow the steps below to add a module:

Step 1: Right-click on any object within the Workbook (where we want to add the module).

Step 2: Hover over the Insert option.

Step 3: Click on Module.

The above steps will immediately create a folder named Module and insert an object named Module1. If a module is already inserted, these steps will insert another module.

Once the module is inserted, we can double-click on the module object in the Project Explorer, and a code window will appear on the screen, where we can copy and paste code or write code ourselves.

1.4 Deleting a module

Follow the steps below to delete a module in VBA:

Step 1: Right-click on the module you want to delete.

Step 2: Choose the Remove Module option.

Step 3: A dialog box will appear on the screen, click No.

Note: We can export a module before deleting it. The exported file is saved as a .bas file, and we can import this file into other projects. To export a module, right-click on the module and select Export file.

1.5 Adding code to the object code window

To open the code window of an object, simply double-click on that object.

In the code window of the object, we can input code manually or copy and paste code from other modules or from the Internet.

Note: Some objects allow us to select events for the code we want to write.

For example, if we want to write code for some change occurring when a selection changes in a worksheet, first we need to select that worksheet from the menu at the top left corner of the code window, then select the change event from the menu at the top right corner.

Note: These are events specific to objects. When opening the code window for a workbook, we will see events related to the workbook. When opening the code window for a worksheet, we will see events related to the worksheet.

In the article 'All About VBA in Excel (Part 2)' above, Mytour has just introduced you to how to add code in a module and in the code window of an object. To understand more, you can go back to our previous article 'All About VBA in Excel Part 1'. Additionally, if there are any questions or inquiries, readers can leave their comments below the article.