View Full Version : Solved: An N-by-1 variant array throws runtime error when N gets down to 1
xltrader100
07-25-2008, 09:06 PM
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
07-26-2008, 01:34 AM
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
07-26-2008, 06:31 AM
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
07-26-2008, 10:38 AM
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!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.