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

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

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 wrote:
> 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
>
>

Thanks Rowan.

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

Davidm.

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 wrote:
> Thanks Rowan.
>
> It is then implied that there is nothing like *a single-element Array*
> ?
>
> 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.

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 wrote:
> 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.
>
>

Hi Rowan,


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

Thanks Rowan.


david