VBA question - searching for one of several characters in a string

[CosmoPants]

Afternoon,

Long time lurker, first time poster - I'm trying to do something that I'm sure ought to be quite simple, but I'm absolutely stuck.

I'm reasonably sure I'm being thick, actually. Apologies if this is a tediously stupid question:

I'm pretty used to writing Crystal Reports, but have recently started to write some VBA too. Essentially, I'm trying to get Excel to loop through a string and count the occurence of certain characters. There are several characters which, if encountered by the loop, should ping the counter. My code looks something like this:

Function PRESENT(range As String)
Application.Volatile

Dim rangelen As Long
rangelen = Len(range)
chars = Array("/", "\", "W")

Dim i As Long
Dim n As Long


For i = 1 To rangelen
If Mid(range, i, 1) = "/" Then
n = n + 1
End If
Next i

PRESENT = n
End Function


which just counts the character "/" of course - I want it to count the characters in the array "chars." I can't find a way of doing it - I want to do something like:

For i = 1 To rangelen
If Mid(range, i, 1) in chars Then
n = n + 1
End If
Next i

or

For i = 1 To rangelen
If Mid(range, i, 1) = {"/", "\", "W"} then
n = n + 1
End If
Next i

but I can't find any referece to the right syntax. My other thought was to have a second loop to change the character being searched for, but it seems like a faff.

Any thoughts? Thanks muchly!

 

[VoG]

Try

Function PRESENT(range As String)
Application.Volatile

Dim rangelen As Long
rangelen = Len(range)
chars = Array("/", "\", "W")

Dim i As Long
Dim n As Long
Dim j As Long

For i = 1 To rangelen
    For j = LBound(chars) To UBound(chars)
        If Mid(range, i, 1) = chars(j) Then
            n = n + 1
        End If
    Next j
Next i

PRESENT = n
End Function

 

[VoG]

Actually (more efficient)

Function PRESENT(range As String)
Application.Volatile

Dim rangelen As Long
rangelen = Len(range)
chars = Array("/", "\", "W")

Dim i As Long
Dim n As Long
Dim j As Long

For i = 1 To rangelen
    For j = LBound(chars) To UBound(chars)
        If Mid(range, i, 1) = chars(j) Then
            n = n + 1
            Exit For
        End If
    Next j
Next i

PRESENT = n
End Function

 

[Richard Schollar]

Here's an alternative:

Function Present(ByVal strVal As String, strMtch As String) As Long
    Dim lngLength As Long
    Dim i As Long
    
    
    
    lngLength = Len(strVal)
    
    For i = 1 To Len(strMtch)
        strVal = Replace(strVal, Mid(strMtch, i, 1), "")
    Next i
    
    Present = lngLength - Len(strVal)
    
    
End Function

Use like:

MsgBox Present(myString,"\/W")

 

[CosmoPants]

Very elegant! Thank you all!