Array problem: Key words-Variant Array, single-element, type mismatch error

[davidm]

The code below serves to highlight a problem I am confronted with in my
project.
Can someone explain why the code generates Type mismatch error when
there is only one element in the defined range? It works fine if column
A contains more than one populated cell.

Sub VariantArrayA()
Dim u
Dim v


Range("a1") = 100
num = Application.CountA(Range("a:a"))

'create 1st variant array
u = Range("a1:a" & num)

Range("a1:a" & num).Clear


Range("a1") = 500

'create 2nd variant array
v = Range("a1:a" & num)

For i = 1 To num
p = u(i, 1) - v(i, 1) 'Type mismatch error on this line
MsgBox p
Next

End Sub


For comparison, the modified version below generates no error.

Sub VariantArrayB()
Dim u
Dim v


Range("a1") = 100
Range("a2") = 200
num = Application.CountA(Range("a:a"))


'create 1st variant array
u = Range("a1:a" & num)

Range("a1:a" & num).Clear


Range("a1") = 500
Range("a2") = 1000

'create 2nd variant array
v = Range("a1:a" & num)

For i = 1 To num
p = u(i, 1) - v(i, 1)
MsgBox p 'code correctly returns p=-400; p=-800
Next

End Sub

 

[Rowan Drummond]

If column A only contains one populated cell then u and v will not be
arrays, they will be variables of the datatype Variant/Double.
Try:

Sub VariantArrayA()
Dim u
Dim v
Dim p
Dim num As Long
Dim i As Long

Range("a1") = 100
num = Application.CountA(Range("a:a"))

'create 1st variant array
u = Range("a1:a" & num)

Range("a1:a" & num).Clear


Range("a1") = 500

'create 2nd variant array
v = Range("a1:a" & num)

If num > 1 Then
For i = 1 To num
p = u(i, 1) - v(i, 1) 'Type mismatch error on this line
MsgBox p
Next
Else
p = u - v
MsgBox p
End If

End Sub


Hope this helps
Rowan

 

[davidm]

Thanks Rowan.

It is then implied that there is nothing like *a single-element Array
?

Davidm

 

[Rowan Drummond]

Hi David

You can certainly create a single-element array manually:

Sub test()
Dim myArr(0) As Variant
Dim i As Integer
myArr(0) = "TheValue"
For i = 0 To UBound(myArr)
MsgBox myArr(i)
Next i
End Sub

but if you pass a single value to a variant it will be stored in a
variable and not an array as you have discovered.

Regards
Rowan

 

[davidm]

Thanks again Rowan.

We learn something everyday, don't we? Following the revelation, using
conventional (as opposed to Variant) Array in my example removes any
danger as the following code illustrates:

Sub m()
Dim a()
Dim b()

Range("a1") = 100
'Range("a2") = 500 *'commented out*
num = Application.CountA([a:a])


For i = 1 To num
ReDim Preserve a(i)
a(i) = Cells(i, 1)
Next

Range("a1:a2").Clear
Range("a1") = 92
'Range("a2") = 800 *'commented out*

For i = 1 To num
ReDim Preserve b(i)
b(i) = Cells(i, 1)
Next

For i = 1 To num
MsgBox a(i) - b(i) 'NO ERROR returned
Next

End Sub


The mighty lesson I have learnt in all this is that uncritical use of
the Variant Array is fraught with danger *where the defined range is
unstable*.
Variant Array - a banana peel if ever there was one!


David.

 

[Rowan Drummond]

Hi David

I am glad I have helped.

As you have said you example below will not produce an error but it may
not be working exactly as you expect. If you do not have the statment
"Option Base 1" at the top of your module then the way you have coded
this means that the arrays a and b are actually 2 element arrays and you
are using the second element of these arrays. This is because by default
the lower bound element of an array is 0.

So using the default Option Base of 0 your code could be:

Sub m()
Dim a()
Dim b()

Range("a1") = 100
'Range("a2") = 500 *'commented out*
num = Application.CountA([a:a])


For i = 1 To num
ReDim Preserve a(i - 1)
a(i - 1) = Cells(i, 1)
Next

Range("a1:a2").Clear
Range("a1") = 92
'Range("a2") = 800 *'commented out*

For i = 1 To num
ReDim Preserve b(i - 1)
b(i - 1) = Cells(i, 1)
Next

For i = 1 To num
MsgBox a(i - 1) - b(i - 1) 'NO ERROR returned
Next

End Sub

I apologise if you knew all this already
Regards
Rowan

 

[davidm]

Hi Rowan,


You are dead right! I fell into the hole of unwittingly using th
default Optional Base 0. I have been guilty a few times of thi
careless lapse. As a matter of precaution, I often avoid thi
situation by declaring my one-dimensional arrays as
columnar * Dim xArray( n to 1)* rather than the normal Row Di
xArray(n).

Thanks Rowan.


davi