Type Mismatch: Array or User Defined Type Expected

Article translations Article translations
Article ID: 121114 - View products that this article applies to.
This article was previously published under Q121114
Expand all | Collapse all

On This Page

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.
  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 preceding example.

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

    Sub Example(B() As Double)
    End Sub
				
NOTE: The data type of both arrays is double..

Properties

Article ID: 121114 - Last Review: October 11, 2006 - Revision: 1.5
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
Keywords: 
kbdtacode kbinfo kbprogramming KB121114
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com