Disconnecting a Variable from an Object

When you use the CreateObject or GetObject function or the New keyword to assign a variable to an object, the object resides in your computer system's memory. Depending on your program's complexity, releasing certain objects from memory may speed up other parts of your program. To disconnect an object variable from an object in memory that you no longer need, set the object variable to the keyword Nothing. Once an object variable is set to Nothing, it no longer references an actual object in memory. If no other object variable refers to that object, it's removed from your system's memory.

NOTE
If the object variable is declared as a procedure-level variable, Visual Basic will automatically set it to Nothing once Visual Basic finishes executing the procedure containing the procedure-level variable. Explicitly setting object variables to Nothing is common for public or module-level object variables.

All Office applications provide, as a member of the respective Application object, a Quit method that closes the application whether it's visible or not. Running the Quit method in Visual Basic is the same as clicking Exit on the File menu in an Office application. In some cases, running the Quit method doesn't release the reference to the actual object until you explicitly set the object variable to Nothing or until the object variable goes out of scope.

Quit Excel Using Visual Basic Code

  1. Exit any running versions of Excel without saving changes.

  2. In the UsingGetObject procedure you just created in Word's Visual Basic Editor, add the following message box statement and With…End block after the last line of the procedure and just before the End Sub statement:

    3. MsgBox "Excel will now be closed."
With m_wbBook1.Application
    .DisplayAlerts = False
    .Quit
End With

    The DisplayAlerts property of the Excel Application object indicates whether Excel displays a dialog box asking if you want to save changes (or any other alert dialog box Excel normally displays). If DisplayAlerts is set to False, no dialog boxes are displayed. Thus, when Visual Basic executes the Quit method of the Excel Application object, Excel won't display a dialog box asking whether you want to save changes to the workbook.

  3. Place the cursor in the procedure and press F5 to run it. When you run the UsingGetObject procedure, Excel starts and the file, Book1.xls, is activated.

  4. Switch to Word, but do nothing to Excel. Once Book1.xls is displayed, Word displays a message box indicating that Excel will now be closed. When you click OK in the Word message box, Word closes Excel, along with the Book1 workbook.