Excel VBA Type Mismatch Error passing range to array

arrays excel vba type-mismatch
MikeG picture MikeG · Aug 11, 2014 · Viewed 16.3k times · Source

I'm trying to retrieve the values of an array of cells in a worksheet (stored as an array rather than simple cells), but for some reason keep getting a Run-Time Error 13 Type Mismatch. I've read posts about similar issues, but many of those seem to have to do with either the array being of the wrong type (ie NOT Variant type), or being of static size.

Here is the relevant line where the error occurs in debugging:

Dim SizeSelection() As Variant
SizeSelection = Workbooks("Wheels.xlsx").Worksheets("Test").Range("B1:W1")

I've also tried using

Dim SizeSelection() As Variant
SizeSelection = Array(Workbooks("Wheels.xlsx").Worksheets("Test").Range("B1:W1"))

but still get the same error. Other than the 2 common mistakes mentioned above, does anyone have any idea why i would be getting a type mismatch? I've tried removing the array and storing the values in regular cells, but that made no difference.

Oh, I should mention that the data I'm trying to retrieve is in another workbook (though that should be clear from the code above) and that workbook is already open.

Thanks! Mike

Answer

stobin picture stobin · Aug 11, 2014

Change Dim SizeSelection() As Variant to Dim SizeSelection As Variant and you should be ok.

Related questions

How to clear the entire array?

I have an array like this: Dim aFirstArray() As Variant How do I clear the entire array? What about a collection?

Read More
Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More