Releasing reference to the Application object before exiting
 

The Workbook of the Add-In contains the following code:


Private WithEvents MyExcelApp As Excel.Application

Private Sub Workbook_Open()

If MyExcelApp Is Nothing Then

Set MyExcelApp = ThisWorkbook.Application

End If

End Sub


Before exiting Excel, which event in the Add-In's Workbook will allow me to
release the reference (MyExcelApp) to the application?

Releasing reference to the Application object before exiting
 

Before exiting Excel, which event in the Add-In's Workbook will allow me
to
release the reference (MyExcelApp) to the application?

The Workbook_BeforeClose event would be the place to do this. Note that
if you do this in an unsaved normal workbook rather than an add-in the user
can still stop your workbook from closing if Excel is not prevented from
displaying the save prompt, which fires after the Workbook_BeforeClose
event, potentially leaving the application in a bad state. This situation
shouldn't arise with an add-in, so you'll be OK using it there.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"cgr" wrote in message
...
The Workbook of the Add-In contains the following code:


Private WithEvents MyExcelApp As Excel.Application

Private Sub Workbook_Open()

If MyExcelApp Is Nothing Then

Set MyExcelApp = ThisWorkbook.Application

End If

End Sub


Before exiting Excel, which event in the Add-In's Workbook will allow me
to
release the reference (MyExcelApp) to the application?

Releasing reference to the Application object before exiting
 

Rob,

That's what I thought, but for some reason exiting Excel would not raise the
Workbook_BeforeClose event.

Repairing my Office installation seems to have fixed the problem.


Clarence

"Rob Bovey" wrote in message
...
Before exiting Excel, which event in the Add-In's Workbook will allow me
to
release the reference (MyExcelApp) to the application?

The Workbook_BeforeClose event would be the place to do this. Note
that
if you do this in an unsaved normal workbook rather than an add-in the
user
can still stop your workbook from closing if Excel is not prevented from
displaying the save prompt, which fires after the Workbook_BeforeClose
event, potentially leaving the application in a bad state. This situation
shouldn't arise with an add-in, so you'll be OK using it there.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"cgr" wrote in message
...
The Workbook of the Add-In contains the following code:


Private WithEvents MyExcelApp As Excel.Application

Private Sub Workbook_Open()

If MyExcelApp Is Nothing Then

Set MyExcelApp = ThisWorkbook.Application

End If

End Sub


Before exiting Excel, which event in the Add-In's Workbook will allow me
to
release the reference (MyExcelApp) to the application?