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


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=483026

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

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=48302

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.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=483026

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.