Microsoft KB Archive/120794

= Microsoft Knowledge Base =

XL5: Problems When Procedure Has Same Name as Module Sheet
Last reviewed: March 27, 1997

Article ID: Q120794

5.00 5.00c 7.00 | 5.00

WINDOWS        | MACINTOSH kbother kbcode

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS
In Microsoft Excel, you may encounter problems using Visual Basic, Application Edition, procedures and functions that have the same names as module sheets in the same workbook.

Procedure
When you call a procedure that has the same name as a module sheet in the workbook, you may receive the following error message:

Expected variable or procedure, not module

Auto_Open or Auto_Close Procedure
If you have an Auto_Open procedure and a module sheet is named Auto_Open in the same workbook, the Auto_Open procedure will not run when the workbook is opened. However, you will not receive an error message. The same behavior occurs when you are using a workbook that contains both an Auto_Close procedure and a Module sheet named Auto_Close; the Auto_Close macro will not run when the workbook is closed, and you will not receive an error message.

Function
When you call a function with the same name as a module sheet in the workbook, you may receive the following error message:

Type mismatch When you reference a function from a worksheet, and the function has the same name as a module sheet in the workbook, you may receive the following error message:

Expected Procedure, not Module

To call a procedure
To work around this problem when you call a procedure that has the same name as a module sheet in the workbook, do either of the following:

  Change the code for the procedure to: Sub Example1 Module1.Module1 End Sub

-or-  Rename module sheet or the procedure.

To use an Auto_Open or Auto_Close procedure
When an Auto_Open (or Auto_Close) procedure and a module sheet named Auto_Open (or Auto_Close) are in the same workbook, you must rename the module sheet.

To call a function
To work around this problem when you call a function that has the same name as a module sheet in the workbook, change the code for the procedure to:

Sub Example2 Dim x as integer x=Module1.Module1 Msgbox x  End Sub

-or- Rename the module sheet or the function.

To work around the problem when referencing a function from a worksheet if the function has the same name as a Module sheet in the workbook, do either of the following:

  Change the formula in cell A1 to: =Module1.Module1 -or-  Change the module sheet named "Module1" or the function named "Module1" to another name.

STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel versions 5.x and 7.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base when it becomes available.