This post has been moved to a new location.
Private Sub test()' This is just an example sub to demonstrate how the new function is used.
Dim vResult As VariantDim 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 iLoopEnd IfvResult = AllInStr("33.333.3333.333.33", "33")If UBound(vResult) = -1 ThenMsgBox "Nothing found."ElseFor iLoop = LBound(Result) To UBound(Result)MsgBox Result(iLoop)Next iLoopEnd IfvResult = AllInStr("ABC", "123")If UBound(vResult) = -1 ThenMsgBox "Nothing found."ElseFor iLoop = LBound(Result) To UBound(Result)MsgBox Result(iLoop)Next iLoopEnd IfEnd 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 IfNext iPosition' If we found at least one result, then remove the trailing semicolon (;).
If Len(sResult) > 1 ThensResult = Left(sResult, Len(sResult) - 1)End If' Split the string into its individual parts and return the result.
AllInStr = Split(sResult, ";")End Function