Excel VBA Type Mismatch Error passing range to array

Question

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

Solution

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

Comments

[stobin]

I should also admit that I don't actually know why using the parenthesis will cause an error in this situation, especially since Dim Arr() As Variant: Arr = Range("A1:C5") is acceptable but Dim Arr() As Variant: Arr = ThisWorkbook.Sheets(1).Range("A1:C5") isn't. I only know from encountering it myself.

[L42]

Hi, you might be interested in the link I posted in the OP's question. That in fact answers his and your question.

[Admin]

Thanks for the quick responses! For some reason declaring the variable without parentheses didn't work, but declaring a range variable and setting SizeSelection equal to that did the trick. @L42 thanks for the link to the article, I hadn't come across that one in my searching. To be honest I don't fully understand the reasoning behind it, but at least now I know the workaround if I encounter this again! Thanks so much for saving my sanity!

[L42]

@MikeG It's pretty simple. You just need to explicitly instruct excel what you're passing to an array of variant variable especially if what you're passing could be one of two things.