Checking Items in a string

[harrisw]

Im trying to check for characters in a string but seem to be having problems

I have the following but VBA doesnt like it.

If Mid(Name, x, 1) not in("a","b") Then

Your help is appreciated

 

[Mile-O]

If Not [b]InStr([/b]1, Name, "a"[b])[/b] Or  Not [b]InStr([/b]1, Name, "b"[b])[/b] Then

 

[harrisw]

There will be loads of stuff to exclude is there a way I can use the

IN("a","b","x")

format for doing it.

 

[Mile-O]

What exactly are you trying to do? If you explain, there will probably be an easier way. Exclude alphabetical characters, for example?

 

[harrisw]

I just want to exclude characters from a string.

The string may change from job to job.

Thanks

 

[Mile-O]

If you collect all the characters into one string ( i.e. "ghkswoc@£%j" ) then you can pass it to this function below along with the text to check.

Public Function ContainsCharacters(strText As String, strChars As String) As Boolean
    
    On Error GoTo Err_ContainsCharacters
    
    Dim strCodes() As String
    Dim intCounter As Integer
    
    For intCounter = 1 To Len(strChars)
        ReDim Preserve strCodes(intCounter)
        strCodes(intCounter) = Mid(strChars, intCounter, 1)
    Next intCounter
    
    For intCounter = LBound(strCodes()) To UBound(strCodes())
        If InStr(1, strText, strCodes(intCounter)) Then
            ContainsCharacters = True
            Exit Function
        End If
    Next intCounter
    
Exit_ContainsCharacters:
    Exit Function
    
Err_ContainsCharacters:
    ContainsCharacters = False
    Resume Exit_ContainsCharacters

End Function

This bit of code will call it:

If ContainsCharacters(MyText, "ghkswoc@£%j") = True Then
    MsgBox "Has one of the specified characters.", vbExclamation
Else
    MsgBox "Contains none of the specified characters", vbInformation
End If