PRB: Password Prompt for VBA Project Appears After Excel Quits (280454)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2000
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
This article was previously published under Q280454

SYMPTOMS

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.

CAUSE

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

RESOLUTION

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.

MORE INFORMATION

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.
    2. From the Tools menu, click Macro and select Macros. Choose "MyMacro" in the list and click Run.
    3. Dismiss the userform that the macro displays.
    4. Quit Microsoft Excel.
    5. 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.
Modification Type:MajorLast Reviewed:12/15/2003
Keywords:kbprb KB280454
©2003 Microsoft Corporation. All rights reserved.