Sometimes you want to find out how many times some substring appears in a string (well, maybe you don't), but the standard
InStr() just doesn't cut it by itself. The code below allows you to actually grab each instance of that substring in an array you can work with.
Private Sub test()
' This is just an example sub to demonstrate how the new function is used.
Dim vResult As Variant
Dim iLoop As Integer
' VResult is a Variant treated as an array.
vResult = AllInStr("123456789TESTING123456789", "78")
If UBound(vResult) = -1 Then
' The string was not found. Same as classic InStr() returning 0.
MsgBox "Nothing found."
Else
' Because we have multiple values returned, loop through them.
For iLoop = LBound(Result) To UBound(Result)
' You don't have to MsgBox this. Again, this is just an example.
MsgBox Result(iLoop)
Next iLoop
End If
vResult = AllInStr("33.333.3333.333.33", "33")
If UBound(vResult) = -1 Then
MsgBox "Nothing found."
Else
For iLoop = LBound(Result) To UBound(Result)
MsgBox Result(iLoop)
Next iLoop
End If
vResult = AllInStr("ABC", "123")
If UBound(vResult) = -1 Then
MsgBox "Nothing found."
Else
For iLoop = LBound(Result) To UBound(Result)
MsgBox Result(iLoop)
Next iLoop
End If
End Sub
Function AllInStr(SourceString As String, SearchString As String) As Variant
' This is the actual function, returning an array-like Variant.
' SourceString is the string to search through.
' SearchString is the string being sought.
Dim iPosition As Integer ' Search position within the source string.
Dim iSourceLength As Integer ' The length of the source string.
Dim iSearchLength As Integer ' The length of the string being sought.
Dim sResult As String ' A string representation of the results, to be split before being passed back.
iSourceLength = Len(SourceString)
iSearchLength = Len(SearchString)
' Simple loop through the length of SearchString to read each iterative substring within.
For iPosition = 0 To iSourceLength - iSearchLength
' If we found the search in our substring...
If Left$(Right$(SourceString, Len(SourceString) - iPosition), iSearchLength) = SearchString Then
' ... save the position in the string.
' Each item separated by a semicolon (;)
sResult = sResult & iPosition & ";"
End If
Next iPosition
' If we found at least one result, then remove the trailing semicolon (;).
If Len(sResult) > 1 Then
sResult = Left(sResult, Len(sResult) - 1)
End If
' Split the string into its individual parts and return the result.
AllInStr = Split(sResult, ";")
End Function
