len of item in an array

[JT]

Is it possible to check the length of each item in a specific field in an
array? Is so, how?

I am building an array but I want to check the length of a specific field
for each item in the array. For example, each "record" in my array has 4
fields. I want to check the length of the first field in each "record". If
it is greater than 0, I will do one action and if it is zero I will do
another action.

I'm using this in an edit to be verify the user has not selected a record
where the first field is blank.

Thanks for the help.

 

[Bernie Deitrick]

JT,

Sub JTArrayTest()
Dim i As Integer
Dim j As Integer
Dim myArr(1 To 2, 1 To 4) As String
Dim NotEmpty As String

For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
myArr(i, j) = "String " & i & " " & j
Next j
Next i

myArr(1, 1) = ""

For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
If Len(myArr(i, j)) = 0 Then
MsgBox "array element " & i & " " & j & " is length 0"
Else
NotEmpty = NotEmpty & vbLf & i & " " & j
End If
Next j
Next i

MsgBox "Not Empty were:" & vbLf & NotEmpty

End Sub

HTH,
Bernie
MS Excel MVP

 

[Joel]

See VBA help for UBound

UBound Function


Returns a Long containing the largest available subscript for the indicated
dimension of an array.

Syntax

UBound(arrayname[, dimension])

The UBound function syntax has these parts:

Part Description
arrayname Required. Name of the array variable; follows standard variable
naming conventions.
dimension Optional; Variant (Long). Whole number indicating which
dimension's upper bound is returned. Use 1 for the first dimension, 2 for the
second, and so on. If dimension is omitted, 1 is assumed.



Remarks

The UBound function is used with the LBound function to determine the size
of an array. Use the LBound function to find the lower limit of an array
dimension.

UBound returns the following values for an array with these dimensions:

Dim A(1 To 100, 0 To 3, -3 To 4)

Statement Return Value
UBound(A, 1) 100
UBound(A, 2) 3
UBound(A, 3) 4

 

[Rick Rothstein \(MVP - VB\)]

Assuming your field delimiter is a comma and that your array is named Record

LenFirstField = Instr(Record(X), ",") - 1

where X is the loop counter (record number). If you need this to be more
general, here is how to

LenOfField_F = Len(Split(Record(X), ",")(F - 1))

where F is 1 for the first field, 2 for the second, etc. and X is as defined
above.

Rick