Programming Excel with VBA and .NET

Classes

Excel provides built-in classes for each workbook and sheet. You can add code directly to those classes to respond to events on those objects as described in Chapter 4. You can also create your own custom classes that you can use elsewhere in code.

Custom classes need to be instantiated as objects before they can be used. This allows you to create multiple instances of the code, each running at the same time and acting independently of one another.

To create a new class:

1. In Excel, open a workbook and choose Tools → Macro → Visual Basic Editor to start programming.

2. In the Visual Basic Editor, choose Insert → Class Module. Visual Basic adds a new class to the Project window and displays the new, empty class in an Edit window.

3. Select Name in the class’s Properties window and type String to rename the class.

4. Add the following code by typing in the class’s Edit window:

       ' Message class
       Public Value As String
       Public Title As String
   
       Public Sub Show( )
           MsgBox value, , title
       End Sub

You can’t run this class just by pressing F5; instead, you must first create an instance of the class from a module, then use the class in some way as shown here:

    ' TestMessage module
    Sub TestMessageClass( )
        Dim msg1 As New Message, msg2 As New Message
        msg1.Title = "Msg1 Object"
        msg1.Value = "This message brought to you by Msg1."
        msg2.Title = "Msg2 Object"
        msg2.Value = "This message brought to you by Msg2."
        msg1.Show
        msg2.Show
    End Sub

The preceding code creates two objects from the Message class, msg1 and msg2 ...