Checking the length of a variant

[craigorama]

Hi everyone.
I'm trying to do something in vba that i know is easy in VB.NET.

I'd like to find out the length of a variant (array) in vba.
I'm loading a text file line by line into the array and then inserting it into a table.

I have something like this:

dim Line as Variant
Line = Split(loaderTXTStream.Readline, vbTab, , vbTextCompare)

then i'd like to write an if statement saying:

if Line.length >2 then
tmpRecSet.AddNew

tmpRecSet.Fields("BEAR_ID") = tmpLine(0)
tmpRecSet.Fields("UPLOAD_DATE") = tmpLine(1)
tmpRecSet.Fields("UPLOAD_TIME") = tmpLine(2)
tmpRecSet.Fields("TTF") = tmpLine(3)



but i don't know how to find the length. Is this just a VB.NET thing only?

thanks
craig

 

[craigorama]

after some more research i found out about the Len method but when i try that i only get a type mismatch error on this line:

length = Len(tmpLine)

?

thanks
craig

 

[cmmrfrds]

I don't think len is what you are looking for. Check how may occurances of the array are loaded and the data type of each occurance. For example if the occurance value is not a date then it will error when trying to update a date.

Example of building an array and looking at the values.

Dim varArr(15) As Variant, indx As Integer
varArr(0) = #1/1/2006#
varArr(1) = "abcd"
varArr(2) = 345
Debug.Print "upper bound = "; UBound(varArr)

For indx = 0 To UBound(varArr)
Debug.Print "arr type = "; VarType(varArr(indx))
Debug.Print " arr value = "; varArr(indx)
Next

Look up vbvartype to see what constant is assigned to each type. For example, an integer is a 2.

If VarType(tmpLine(0)) = 0 then
debug.print "no data in line"
End If

If VarType(tmpLine(1)) = 7 then
tmpRecSet.Fields("UPLOAD_DATE") = tmpLine(1)
else
debug.print "not a date"
End If

 

[TheAceMan1]

How are ya craigorama . . .

As you've shown it:

[blue]   If [purple][b][i]UBound([/i][/b][/purple]Line[purple][b][i])[/i][/b][/purple] >2 then[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[craigorama]

Thanks you two.

Yes, UBound was what i was looking for!!!

later