Programming Excel with VBA and .NET

Show a Form

To test the form from the Visual Basic Editor:

1. Make sure the form or form code has focus and press F5 or click Run. Visual Basic switches to the Excel window and displays the form in Run mode.

2. Click the Close box on the form or click Reset in Visual Basic to return to Design mode.

To display the form from the Excel interface, you must create a procedure in a module that creates an instance of the form, then call the form’s Show method. Forms are a type of class, so they can’t just be run from the Macro dialog box. The following code shows a procedure that displays the Stock History dialog:

    ' StockHistoryModule
    Sub StockHistoryDialog(  )
        Dim f As New frmStockHistory
        f.Show False
    End Sub

Tip

Use the Show method to display a form in code. Use the Unload statement to close a form in code.

In the preceding code, I called Show with the Modal argument set to False. That shows the form nonmodally, which means you can still select cells and do tasks in Excel while the form is displayed. Modal forms block user access to Excel while they are displayed.

It is usually easier to program with modal forms , since the user can’t change the state of Excel while the form is running. However, modal forms are best suited for linear tasks. For nonlinear tasks, use nonmodal forms.

Once you’ve created a procedure in a module to show your form, you can display the form by assigning that macro to a menu item, toolbar button, or some other user action in Excel. See Chapter 19 for details on creating menus ...