Programming Excel with VBA and .NET : Knowing the Basics - Classes and Modules

Excel stores recorded code in modules and stores code associated with workbooks and worksheets in classes . Here's why:

Modules are static

That is, they exist without having to be created in memory at runtime. That means the code in modules is always available to Excel; however, it limits what type of code they can contain. Specifically, modules can't contain event procedures.

Classes are dynamic

They must be instantiated at runtime (that means an instance of an object must be created from the class at runtime). Classes can contain event procedures because of this relationship with an object.

Workbook, chart, and worksheet classes are automatically instantiated by Excel because those classes are associated with visible Excel objects: the current workbook and each of the sheets it contains. Because of that relationship, Visual Basic shows those classes as Microsoft Excel Objects in the Project window (Figure 1).

Figure 1. Excel Visual Basic projects store code in three different folders

Because Excel instantiates classes automatically, how you create objects from classes is mostly hidden and therefore often not completely understood. To see how creating an object from a class works, create a new workbook in Excel, start the Visual Basic Editor, and follow these steps:

1. Choose Insert → Class Module. Visual Basic creates a new class and adds it to the Class Modules folder of the Project window.

2. Select the Properties window and rename the class PublicClass and set the Instancing property to 2 - PublicNotCreatable, as shown in Figure 2.

Figure 2. Create a user class

Click on the class's Code window and type the following code:

    ' From PublicClass class
    Dim m_name As String

    Public Sub SetName(name As String)
        m_name = name
    End Sub

    Public Sub ShowName( )
        Debug.Print m_name
    End Sub

Now move the cursor to the ShowName procedure and press F5. Excel doesn't run the procedure; instead, it displays the Macros dialog, and it's empty! (See Figure 3.)

Figure 3. Procedures in user classes don't show up here because they don't have an instance

In order to run the ShowName procedure, you need to choose Insert → Module and add the following code:

    ' From Tests module.
    Sub TestUserClass( )
        Dim obj As New PublicClass
        obj.SetName ("New object")
        obj.ShowName
    End Sub

TestUserClass creates an instance of PublicClass as a new object named obj, then calls the SetName and ShowName methods of that object. In short, you can't do anything with a class until you create an object from it. Why is that useful? Because each object has its own storage. For example, you can create three different objects from the same class if you like:

    ' From Tests module.
    Sub MultipleObjects( )
        Dim obj1 As New PublicClass, obj2 As New PublicClass, obj3 As New PublicClass
        obj1.SetName ("First object")
        obj2.SetName ("Second object")
        obj3.SetName ("Third object")
        obj1.ShowName
        obj2.ShowName
        obj3.ShowName
    End Sub


				  

The preceding code displays each object's name in the Immediate window:

    First object
    Second object
    Third object

You can't do that with code stored in a module because the m_name variable changes each time you call SetName. With modules, you have only one instance, and you can't create that instance:

    ' From Tests module.
    Sub MultipleModules( )
        ' Code in modules is static, there's no such thing as:
        'Dim mod As New PublicProcedures
        PublicProcedures.SetName ("First object")
        PublicProcedures.SetName ("Second object")
        PublicProcedures.SetName ("Third object")
        ' All display "Third object"
        PublicProcedures.ShowName
        PublicProcedures.ShowName
        PublicProcedures.ShowName
    End Sub

Most programmers omit the module name when calling procedures from a module, but you can include it if you like, as shown by this example. The module name is required only if there is a procedure with the same name in two or more modules.

The classes that Excel provides for sheets and workbooks are single-instance classes . That means they follow some special rules that are different from user classes. You can create multiple variables that refer to a single-instance class, but all those variables refer to the same object. For example, the following code creates object variables that refer to the same worksheet:

    ' From Tests module
    Sub TestSheetClass( )
        Dim obj1 As New Sheet1, obj2 As New Sheet1
        obj1.Name = "New name"
        Debug.Print obj2.Name
    End Sub

When you run the preceding code, Debug.Print displays New Name in the Immediate window! This limitation comes from Excel—you can't have two sheets with the same name in a single Excel workbook, and you can't have two workbooks with the same name open at the same time in Excel.

Since Excel handles the creation of workbook and sheet classes, the New keyword in the preceding code is misleading: you can't really create new instances of those classes. However, you can create new instances of generic versions of those objects using Excel's Add method:

    ' Creates a new, blank worksheet
    Sub NewWorksheet( )
        Dim ws As Worksheet
        Set ws = Worksheets.Add
        ws.Name = "New sheet"
    End Sub

One other quirk of workbook and sheet classes is that you can run their procedures from the Code window by pressing F5. You don't need to first create an instance of those classes—Excel's already done it.