Search in array

[Gerrit Kiers]

How would I check if a certain string exists in an array of strings.
I do not have to know where, only if it is there? Is there something
smarter method that for..next?

(For use in Excel 2000, but i think that should not matter...)

Thasnks in advance Gerrit

 

[Malcolm Smith]

Gerrit

You could use the Join() function to put the array of strings into one
larger string and then do an Instr() on that.

- Malc
www.dragondrop.com


Beware that I am just typing this in and so it hasn't been tested, but you
could investigate something like the following though the following isn't
of production strength as the comma may be in your search string...


Function IsStringInArray(sSearchString As String, asStrings() As String)
As Boolean

IsStringInArray = (InStr(1, sSearchString, Join(asStrings(), "|"),
vbTextCompare) > 0)

End Function

 

[Tushar Mehta]

Some XL functions, including MATCH, are available within VBA through
Application.WorksheetFunction. Just keep in mind that you might
encounter (un)documented length/size restrictions. The following
works, though I wouldn't be surprised if it has some limit on the array
size.

Option Explicit

Sub testIt()
Dim Arr(1 To 3), rslt As Long
Arr(1) = "A": Arr(2) = "B": Arr(3) = "C"
On Error Resume Next

rslt = LBound(Arr) - 1
rslt = Application.WorksheetFunction.Match("b", Arr, 0)
MsgBox IIf(rslt < LBound(Arr), "Not found", rslt)

rslt = LBound(Arr) - 1
rslt = Application.WorksheetFunction.Match("d", Arr, 0)
MsgBox IIf(rslt < LBound(Arr), "Not found", rslt)

On Error GoTo 0
End Sub


--
Regards,

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