Wednesday, July 27, 2011

Random Bit of VBA - Better InStr() Function

No comments:
Put this together with the help of Clint. I don't know why, but I feel like posting it, so here it is.


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 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