XL: Type Mismatch: Array or User Defined Type Expected

Last reviewed: February 3, 1998
Article ID: Q121114
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 97 for Windows
  • Microsoft Excel for the Macintosh, version 5.0
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for Windows NT, version 5.0

SUMMARY

In Microsoft Excel, when a Microsoft Visual Basic for Applications procedure passes the value of one array to another array, if the data types of the two arrays are not compatible, you may receive one of the following error messages:

   Type mismatch: Array or User Defined Type Expected.

   -or-

   Compile error:
   Type mismatch: array or user-defined type expected

These error messages are not clear. A more accurate error message is the following:

   Type mismatch: arrays not of compatible types

RESOLUTION

To make sure that the data types for both arrays are compatible, dimension both arrays with the same data type.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In Microsoft Excel, create a new workbook.

  2. From the Insert menu, choose Macro, and then choose Module.

    In Microsoft Excel 97, press ALT+F11 to activate the Visual Basic Editor. Then, click Module on the Insert menu.

    In Microsoft Excel 98 Macintosh Edition, press OPTION+F11 to start the Visual Basic Editor. Then, click Module on the Insert menu.

  3. In the module, type the following procedures:

          Sub First()
             Dim A(1 to 20) As Double
             Second A()
          End Sub
    
          Sub Second(B() As Single)
          End Sub
    
    

  4. From the Tools menu, choose Macro. Select the macro titled First, and choose Run.

    In Microsoft Excel 97, click Macros on the Tools menu. Click First, and then click Run.

    At this point you should receive one of the error messages shown above.

The correct syntax would be to have the data types of both arrays compatible. For example, type the following procedures on the same module sheet as the example above.

    Sub Correct()
       Dim A(1 to 20) As Double
       Example A()
    End Sub

    Sub Example(B() As Double)
    End Sub

Note that the data type of both arrays is double.


Additional query words: 5.00 5.00a 5.00c 7.00 97 XL97

Keywords : kbcode kbprg xlvbahowto PgmHowto PgmOthr
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.