This article was previously published under Q121114
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel, when a Microsoft Visual Basic for Applicationsprocedure passes the value of one array to another array, if the data typesof the two arrays are not compatible, you may receive one of the followingerror messages:
Type mismatch: Array or User Defined Type Expected.
Compile error: Type mismatch: array or user-defined type expected
These error messages are not clear. A more accurate error message is thefollowing:
Type mismatch: arrays not of compatible types
To make sure that the data types for both arrays are compatible, dimensionboth arrays with the same data type.
Steps to Reproduce Behavior
In Microsoft Excel, create a new workbook.
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 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
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 arrayscompatible. For example, type the following procedures on the same modulesheet as the preceding example.
Sub Correct() Dim A(1 to 20) As Double Example A() End Sub Sub Example(B() As Double) End Sub