Password prompt for VBA project appears after Excel quits

After running a macro that passes a reference for a workbook containing a password-protected VBA project to an ActiveX dynamic-link library (DLL), you are prompted for the VBA project password when Excel quits.

This problem occurs if the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project.

To correct this problem, design classes in your ActiveX DLL so that workbook references are properly released before the classes terminate.

A common scenario where a class Terminate event does not fire is one where you have a circular reference between objects. A circular reference would be created if, for example, a parent object creates a child object and hands that child object a reference to the parent. If the child's reference to the parent is not released, the parent object will not terminate.

The steps in the following section demonstrate how a circular reference between objects might occur and how the password prompt will appear if the objects hold onto a reference for a protected workbook when Excel quits. The resolution, which is to call a method for one of the objects to break the circular references so that the objects can terminate, is also described below.

Steps to Reproduce Behavior

1. In Visual Basic, create a new ActiveX DLL project. Change the project name to ExcelTest.
2. Change the name of Class1 to ParentClass and then add the following code to ParentClass:

   Option Explicit
   
   Private oChild As ChildClass
   Private WorkbookRef As Object
   
   Private Sub Class_Initialize()
      Set oChild = New ChildClass
      Set oChild.Parent = Me
   End Sub
   
   Private Sub Class_Terminate()
      Set oChild.WorkbookRef = Nothing
      Set oChild.Parent = Nothing
      Set oChild = Nothing
      MsgBox "ParentClass Terminate Event"
   End Sub
   
   Public Sub Clear()
       Set oChild.Parent = Nothing
   End Sub
   
   Public Sub SetWorkbook(o As Object)
       Set WorkbookRef = o
       Set oChild.WorkbookRef = o
   End Sub
   					

3. Add another class module, name it ChildClass, and then add the following code to ChildClass:

   Public Parent As ParentClass
   Public WorkbookRef As Object
   					

4. Build "ExcelTest.dll".
5. Create a new workbook in Microsoft Excel. Press ALT+F11 to open the Visual Basic Editor.
6. From the Insert menu, click UserForm to add a new userform to the VBA project.
7. From the Insert menu, click Module to add a new module to the VBA project. Add the following code to the new module:

   Public o As Object
   Sub MyMacro()
       UserForm1.Show
       Set o = CreateObject("ExcelTest.ParentClass")
       o.SetWorkbook ThisWorkbook
       'o.Clear  '<=== Remove comment to demonstrate the workaround.
       Set o = Nothing
   End Sub
   					

8. From the Tools menu, select VBAProject Properties. On the dialog box that appears, click the Protection tab. Click to select Lock Project for Viewing. Supply a password and click OK.
9. Save the workbook and close it.
10. Now, to reproduce the problem with the VBA project password appearing when Excel quits:
    1. Open the workbook that you saved in step 9.
       
       In Microsoft Office Excel 2007, if you are prompted by a security warning that indicates that macros have been disabled, click Options. In the Security dialog box, click Enable this content, and then click OK.
    2. Perform one of the following actions:
       • In Excel 2007, click Macros in the Code group on the Developer tab. If the Developer tab does not a[[ear, click the Microsoft Office Button, click Excel Options, click Popular, click to select the Show Developer Tab in the Ribbon check box, and then click OK.
       • In Microsoft Office Excel 2003 or in an earlier version of Excel, click Macro on the Tools menu, and then click Macros.
    3. Select MyMacro in the list, and then click Run.
    4. Dismiss the userform that the macro displays.
    5. Quit Microsoft Excel.
    6. You are prompted for the VBA project password when Excel quits.

When you use the steps above to reproduce the problem, note that the Terminate event for the ParentClass class does not fire even after setting its object in the Excel macro to Nothing. (The absence of the MessageBox with the message "ParentClass Terminate Event" indicates that this event did not fire.)

To correct the problem so that the reference to the Excel workbook is properly released, remove the comment from the line designated in the Excel macro, save the macro, and then repeat the test. When the workbook reference is properly released, the object's Terminate event will fire and you will no longer be prompted for the VBA project password when Excel quits.