Microsoft Knowledge Base

WINDOWS         | MACINTOSH

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

   Expected Procedure, not Module

WORKAROUNDS

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-