Thread: InStr function using an array

Use regular expressions

Function StringContains(ByVal LookAt As String, ByVal LookFor As String) As Boolean
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Pattern = "[" & LookFor & "]"
    RegEx.Global = True
    StringContains = RegEx.Test(LookAt)
    Set RegEx = Nothing
End Function

use like so

?StringContains("Bob12is4her23e", 1234)

The problem is probably that your function does not have access to the array variable that you've defined.

My lightly re-written version of your code seems to work fine...

Sub Test()

Dim asTestArray()

asTestArray = Array("(1)", "(2)", "(3)")

MsgBox InArray("(3)", asTestArray())


End Sub

Function InArray(ByVal sSearchVal As String, asSearchArray()) As Boolean

Dim lArrayLoop As Long
Dim bTempVal As Boolean

bTempVal = False

For lArrayLoop = LBound(asSearchArray) To UBound(asSearchArray)
  If asSearchArray(lArrayLoop) = sSearchVal Then
    bTempVal = True
  End If
Next lArrayLoop

InArray = bTempVal

End Function

hi, SOS, for the matter of learning:

Sub CeckArray()
Dim ArrayVals

ArrayVals = Array("1", "2", "3", "4")

' start loop here to check wksheet
If InArray(ArrayVals, "3") Then
  Debug.Print "Value is in Array"
End If

End Sub
Function InArray(x, strValue)
  Dim i
  For i = 0 To UBound(x)
    If x(i) = strValue Then
      InArray = True
      Exit Function
    End If
  Next
  InArray = False
End Function

Wow, what can I say? I knew this was the place to come for an answer. Such speedy (and correct) replies.

Many thanks

Seamus