Scope

A VBProject is the Visual Basic project that includes all the code modules, Microsoft Excel objects, references, and forms contained within a given workbook. In Microsoft Excel 97, public constants, variables, and procedures live in Standard Modules. Constants, variables, and functions declared with the Public keyword in a Standard Module are available to all other code in all VBProjects, unless the module contains the Option Private Module statement, in which case they will only be available to the VBProject that contains that Standard Module.

There are certain differences in the way the Public keyword is used in Object Modules and Class Modules. Here are the key differences:

• Constants, DLL declarations, and fixed-length strings cannot be declared as Public in any Object or Class Module.
• Variables declared as Public within an Object or Class Module become new Properties of that object.
• Procedures (functions and subroutines) declared as Public within an Object or Class Module become new Methods of that object.

You can create custom properties for an Object or Class Module, using the Property Get procedure. The following example illustrates how this is done:

• Create a new workbook.
• Start the VBE and add one UserForm and one Standard Module.
• Add a ListBox and a CommandButton to the UserForm.
• Name the ListBox lstDemo.
• Name the CommandButton cmdOK and give it the caption OK.

The following procedures should be created in the Object Module for the UserForm:

Property Get ListChoice() As String
    ListChoice = lstDemo.Value
End Property

Private Sub cmdOK_Click()
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    lstDemo.AddItem "Item 1"
    lstDemo.AddItem "Item 2"
    lstDemo.AddItem "Item 3"
    lstDemo.AddItem "Item 4"
    lstDemo.ListIndex = 0
End Sub

In the cmdOK_Click event procedure above, the Me keyword is an implicitly declared object variable that refers to the object within which code is currently executing. The next procedure goes in the Standard Module.

Sub ShowForm()
    UserForm1.Show
    MsgBox "You chose " & UserForm1.ListChoice
    Unload UserForm1
End Sub

Adding the Property Get procedure to the UserForm Object Module actually creates a new property for that UserForm. In this example, the new ListChoice property exposes the choice made in the listbox.

When you run the ShowForm procedure, the UserForm is displayed. Select an item in the listbox and click the OK button. The ShowForm procedure then generates a message box that accesses the ListChoice property of the UserForm to show which item you chose from the list.

The Wizard demo provided later in this chapter (and on the CD) also contains an example of using the Property Get procedure with a UserForm.