Locate

The Locate function may be used to find the index of a field, subfield, or sub-subfield within a dynamic array.

Group: Dynamic Array Extensions


Syntax: vNbr = Locate(vVAL, vStringData, [vFLD], [vSUB], [vOption])

vNbr           (Variant) is an integer that indicates the location of VAL, or 0 (zero) if VAL is not located.

vVAL          (Variant) is the string value to be located

vStringData (Variant) is the dynamic array variable that will be searched

vFLD          (Variant) Optional – is the array field number to search

vSUB          (Variant) Optional – is the array subfield number to search

vOption      (Variant) Optional – the column within the row to search

Example:

The ‘&’ character is used like the Chr(1) delimiter to separate the different rows. The ‘#’ character is used like the Chr(2) delimiter to separate different values within a record, In this case, the name and the age and the language. The ‘@’ symbol is used like Chr(3) to delimit between a sub-field. In this case, the difference between 2 languages.

Dim Nbr As Integer

Dim sNames As String

sNames = "John#31#Canadian English@UnitedKingdom English&Mike"

Nbr = Locate("Mike", sNames)  ' Nbr = 2

Nbr = Locate("John", sNames)  ' Nbr = 0 since there is depth to record 1

Nbr = Locate("John", sNames, 1) ' Nbr = 1

Nbr = Locate("31", sNames, 1)   ' Nbr = 2 since 1st record was specified

Nbr = Locate("UnitedKingdom English", sNames, 1, 3)

Nbr = 2 since 1st record and 3rd subfield was specified

If you do not know the row number where your data is then you can specify 0 in place of the Field and Sub values to search the entire array.

Nbr = Locate("31", sNames, 0, 0, "V2")  ' Nbr = 1

Assuming you know the layout of the array (Name, Age, Language) then you can locate the “31” anywhere in the array and specifically look at column 2 for the match. This will return which row is the first with the data.

If your columns are Chr(3)-delimited and the rows are still Chr(1)-delimited then use an “S” to find the row:

sNames = "Names" & "John@Mike@Steve@Rob" & "Addresses"

Nbr = Locate("Steve", sNames, 0, 0, "S3")  ' Nbr = 2

You still need to know the format of the array so, in this case, Steve was found in the known third position of the unknown second row.