Programming Excel with VBA and .NET

Expose Objects

The objects you create within a workbook are usually private to that workbook. That means outside applications can’t see them or use them in their code. In some rare cases, you may want to expose a custom object so that other applications can use it. To do so:

1. Declare the object as Public.

2. Initialize the object. Usually you do that on start-up when the workbook loads.

3. Change the class’s Instancing property to 2 - Public not creatable.

To see how this works, select the Messages class in the Visual Basic Project window and change the Instancing property as shown in Figure 5-8.

Repeat that for the Message class, and then add the following code to the ThisWorkbook object:

    ' ThisWorkbook object.
    Public g_Messages As Messages

    Private Sub Workbook_Open( )
        ' Intialize the Messages collection.
        Set g_Messages = New Messages
        ' Create some messages
        Dim msg1 As New Message
        msg1.Title = "Msg1"
        msg1.Value = "From collection."
        msg1.icon = Information
        g_Messages.Add msg1
        Dim msg2 As New Message
        msg2.Title = "Msg2"
        msg2.Value = "From collection."
        msg2.icon = Warning
        g_Messages.Add msg2
    End Sub

Figure 5-8. The Instancing property exposes objects outside Excel

The preceding code creates two Message objects and adds them to the Messages collection. That collection is then exposed through the g_Messages collection.

Save and close the workbook, and then reopen it to run the Workbook_Open procedure. ...