Solved: An N-by-1 variant array throws runtime error when N gets down to 1

[xltrader100]

Is there something special or (not allowed?) about a 1x1 variant array? As I redim the "rows" dimension of my array downward from a large number, it breaks when N gets to 1.

option base 1
Sub junk()
Dim testRange1 As Range
Dim testRange2 As Range

Set testRange1 = Range("A1:A1")
Set testRange2 = Range("A1:A2")

ReDim vArray1(1, 1)
ReDim vArray2(2, 1)

vArray2 = testRange2 '<<< this works fine
Debug.Print "vArray2(1, 1) = "; vArray2(1, 1)

vArray1 = testRange1 '<< this gives runtime error 13, type mismatch
End Sub

Edited by Aussiebear: Using the VBA tags button makes any code supplied, so much easier to read.

[Bob Phillips]

Oddly, dimming the arrays first worked for me



Sub junk()
Dim testRange1 As Range
Dim testRange2 As Range
Dim vArray1 As Variant
Dim vArray2 As Variant

Set testRange1 = Range("A1:A1")
Set testRange2 = Range("A1:A2")

ReDim vArray1(1, 1)
ReDim vArray2(2, 1)

vArray2 = testRange2
Debug.Print "vArray2(1, 1) = "; vArray2(1, 1)

vArray1 = testRange1
End Sub


But when you load arrays from ranges, you do not need to size them, the load automatically sizes them correctly, reducing any potential error


Sub junk()
Dim testRange1 As Range
Dim testRange2 As Range
Dim vArray1 As Variant
Dim vArray2 As Variant

Set testRange1 = Range("A1:A1")
Set testRange2 = Range("A1:A2")

vArray2 = testRange2
Debug.Print "vArray2(1, 1) = "; vArray2(1, 1)

vArray1 = testRange1
End Sub

[mikerickson]

vArray1=Range("A1:A1").Value sets vArray1 to either a String, Double, Boolean, Error or Empty.

Sub junk()
Dim testRange1 As Range
Dim testRange2 As Range
Dim vArray1 As Variant
Dim vArray2 As Variant

Set testRange1 = Range("A1:A1")
Set testRange2 = Range("A1:A2")

ReDim vArray1(1, 1)
ReDim vArray2(2, 1)

vArray2 = testRange2
Debug.Print "vArray2(1, 1) = "; vArray2(1, 1)

vArray1 = testRange1
MsgBox TypeName(vArray2) & ":" & TypeName(vArray1)
End Sub
Returned Variant():String in my testing.


Sub junk()
Dim testRange1 As Range
Dim testRange2 As Range
Dim vArray1 As Variant
Dim vArray2 As Variant

Set testRange1 = Range("A1:A1")
Set testRange2 = Range("A1:A2")

ReDim vArray1(1, 1)
ReDim vArray2(2, 1)

vArray2 = testRange2
Debug.Print "vArray2(1, 1) = "; vArray2(1, 1)

If testRange1.Cells.Count = 1 Then
ReDim vArray1(1 To 1, 1 To 1)
vArray1(1, 1) = testRange1.Value
Else
vArray1 = testRange1.Value
End If
MsgBox TypeName(vArray2) & ":" & TypeName(vArray1)
End Sub
returns Variant():Variant()

[xltrader100]

Thanks folks, that totally solved the problem. That's the first time I've come up against a case where dimming a vArray before reDimming it actually made a difference. But that will be SOP for me from now on.

Also, I wasn't aware that vArrays could be autosized just by assigning a range to them. I'd been making a lot of extra work for myself (and opening up to hidden errors, like here) by trying to calc the size ahead of time.

What a great resource this group is!!