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

 

[Tushar Mehta]

When XL/VBA recognize that there is just one element to be assigned to
the variant, it assigns that value to the variant. In those cases
where the range contains multiple entries, the software creates an
array and assigns it to the variant.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions