Variant Array with String Values - Type Mismatch

[jamiee]

Hi

I'm sure there's a simple answer, but for the moment I'm stuck and I
have a deadline to meet. I have declared a three dimensional array as
being of variant type and then assigned various values to the array.
Some of the values assigned are numerical, some are strings.

Part of my code compares the values in the array. When comparing
values that contain numerical values there is no problem, yet when
comparing values that are strings I get the type mismatch error.

If one value was numerical and the other was a string I could
understand why this is, however I have checked that the two values are
both strings.

Anyone know what I'm doing wrong?

 

[pikus]

How are you comparing them

 

[Tom Ogilvy]

if isnumeric(a) and isnumeric(b) then
if cdbl(a) = cdbl(b) then

else

end if
elseif vartype(a) = 8 and vartype(b) = 8 then
if a = b then

else

end if
else
' they don't match
end if

 

[jamiee]

Thanks for your help Tom, however the VarType value that is returned
is 8204. Which is the value for an array with variable variants. Could
it be anything to do with the fact that my array is a global array
declared at the start of my module and that various procedures use the
array?

Dim Resultblocks(1 To 12, 1 To 100, 1 To 2)

'Result blocks declared at the start of the module
'if last dimension of resultblock is 2, the value stored is numerical
'if last dimension of resultblock is 1, the value stored is a string


Sub Display_Schedule_Changes()
'variables declared

For t = 2 to end_of_list
If (Resultblocks(t - 1, j, 2) = Resultblocks(t, i, 2)) Then
If resultblocks(t-1,j,1) = resultblocks(t,i1) Then
'perform task
End If
Next t
end sub

 

[Tom Ogilvy]

Don't test the whole array - test the individual elements.

Sub Tester9()
Dim vArr(1 To 2, 1 To 2, 1 To 2) As Variant
vArr(1, 1, 1) = "this is text"
vArr(2, 2, 2) = 10
Debug.Print VarType(vArr(1, 1, 1))
Debug.Print VarType(vArr(2, 2, 2))
End Sub

produces
8
2

 

[jamiee]

Tom, thanks.

Your code does exactly as you stated, however it doesn't seem to apply
to my situation. When I check all of the values of
vartype(Resultblock(?,?,2)) - which contain the integers - the result
is 5 (e.g. double type which is acceptable). When I check all of the
values of vartype(Resultblock(?,?,1)) - which contain the strings - I
get 8204.

Perhaps it has something do with how the values enter the array? I use
another procedure to assign values from a spreadsheet into an array.

Resultblocks(ColumnSelected, i, 1) = myCell.MergeArea.Value
Resultblocks(ColumnSelected, i, 2) = myCell.MergeArea.Rows.count / 2

Thanks for your co-operation so far.

 

[Tom Ogilvy]

Resultblocks(ColumnSelected, i, 1) = myCell.MergeArea.Value

if the cells are merged, thus the reference it multicell, you are assigning
an array to your variable element. that is probably the reason for your
type mismatch. Comparing an array to a scalar will produce type mismatch.

try doing

Resultblocks(ColumnSelected, i, 1) = myCell.MergeArea(1,1).Value