Password prompt for VBA project appears after Excel quits

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.

      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.
プロパティ

文書番号:280454 - 最終更新日: 2009/03/23 - リビジョン: 1

フィードバック