How to Import or Create a VBA Class Module

Overview

This guide explains how to import or create a VBA class module in Excel. It covers importing pre-written modules, creating new modules, and initializing them with practical examples.

Benefits of Using VBA Class Modules

 

  • Simplified and Reusable Code:
    Streamlines workflows by replacing repetitive VBA code with clean, reusable methods that are easy to implement.

  • Centralized Error Handling:
    Proactively manages potential issues in one place, ensuring more reliable and predictable execution of your code.

  • Scalable and Maintainable Solutions:
    Enables you to handle complex tasks by grouping related functionality into well-organized, reusable modules, making future updates easier and less time-consuming.

  • Enhanced IntelliSense Support:
    With class modules, IntelliSense automatically suggests methods, properties, and variables, reducing errors and improving development speed by showing available options as you type.

Considerations When Using VBA Class Modules

 

  • Beginner-Friendly OOP Required:
    Some familiarity with object-oriented programming (OOP) concepts is needed, but it’s relatively easy to pick up.

  • Grouping Functions for Reuse:
    Effective use requires planning how to organize related functions into logical groups, which may take practice.

 

Introduction to the VBA Development Environment

Before starting, here’s a quick overview of the VBA environment and how to set up a macro-enabled workbook:

  1. Understanding Workbook Types:
    a. XLSX (Excel Workbook): Does not support macros or VBA code.
    b. XLSM (Macro-Enabled Workbook): Required for running VBA macros, including class modules.

  2. How to Create an XLSM Workbook:
    a. Open Excel and create a new workbook (File > New) or open an existing workbook (File > Open).
    b. Go to File > Save As or Save a Copy (depending on your Excel version).
    c. Select Excel Macro-Enabled Workbook (*.xlsm) from the Save as type dropdown.
    d. Enter a name for your file and click Save.

3. Accessing the VBA Editor:

a. Press Alt + F11 in Excel to open the VBA Editor. This is where you’ll manage modules and write code.

4. Navigating the VBA Editor

The VBA Editor is where you’ll manage your class modules and write VBA code. Below are its key components and how to access them:

  1. Project Explorer:

    • Displays your workbook and all its associated modules, sheets, and forms.
    • How to Open: If it’s not visible, go to View > Project Explorer.

  2. Code Window:

    • The area where you write and view VBA code for selected modules.
    • How to Open: Double-click a module or object (e.g., Sheet1) in the Project Explorer.

  3. Menu and Toolbar:

    • Provides tools for inserting modules, running, debugging, and managing your code.

  4. Properties Window:

    • Displays and allows editing of the properties for the selected object (e.g., worksheet, module).
    • How to Open: If it’s not visible, go to View > Properties Window.

  5. Immediate Window:

    • Useful for testing code snippets, printing debug information, and interacting with your VBA project in real time (e.g., using Debug.Print).
    • How to Open: If it’s not visible, go to View > Immediate Window.

With these basics in mind, you’re ready to dive into the guide.

Overview of Steps

The guide below walks you through the following steps and assumes no prior knowledge of VBA macros:

  1. Opening the VBA Editor:
    Access the VBA environment where you can manage your modules.

  2. Importing a Pre-Written Class Module:
    Learn how to quickly import pre-written modules into your project.

  3. Or (an Alternative to Step 2), Creating a New Class Module (Copy and Paste Method):
    This method lets you manually create and customize class modules.

  4. Initializing the Class Module:
    Set up and start using the class module in your project.

Step 1: Open the VBA Editor

  1. Open Excel and press Alt + F11 to open the VBA Editor.
  2. Verify that the Project Explorer panel is visible.
    • If not: Go to View > Project Explorer to enable it.

Step 2: Import a Pre-Written Class Module

  1. In the VBA Editor, go to File > Import File....
  2. Browse to the .cls file you want to import and select it.
  3. Verify that the new class module appears under Class Modules in the Project Explorer.

 4. Browse to the .cls file, select it, and click Open to import the class module.

 5. Verify that the module is added under Class Modules in the Project Explorer.

Step 3: Step 2 Alternative - Create a New Class Module (Copy and Paste Method)

Note, this method is useful when you do not have a .cls file but you do have VBA code that you can copy and paste. 

 1. In the VBA Editor, navigate to the menu bar and select Insert > Class Module to create a new blank class module.

 2. Rename the new class module:

  • Right-click the module in the Project Explorer and select Properties.
  • In the Name field, enter the desired name.
  • Example: If you’re adding the AWE_Sheet class module, you will need to name it AWE_Sheet.

 3. Add the Code to the Class Module:

  • Open a text editor and copy your pre-written code (e.g., the code for AWE_Sheet).
  • Paste the copied code into the newly created class module in the VBA Editor.
  • This ensures the class module is fully functional and ready for use.

Step 4: Initialize the Newly Added Class Module

Now that you’ve successfully imported or created (via copy and paste) your class module, you’re ready to use it. From anywhere in your VBA code, you can instantiate the class module and leverage its public methods and properties to streamline and enhance your functionality.

Insert a Module:

  1. In the VBA Editor, navigate to the menu bar and select Insert > Module to create a new standard module.
  2. This step is optional if you’re adding code to an existing module.

Initialize the Class Module:

For example, if you’re implementing the AWE_Sheet class module, the following code demonstrates how to iterate through rows where the "Name" column equals "John" and retrieve associated values:

Dim mySheet As New AWE_Sheet
Dim iRow As Long

mySheet.Initialize "Worksheet Name"

For Each iRow In mySheet.LookupRowNbrs("Name", "John")
    Debug.Print mySheet.GetCellRng(iRow, "Name").Value
    Debug.Print mySheet.GetCellRng(iRow, "Age").Value
Next iRow