[RESOLVED] Any way to make this faster?

vb Code:

Public Function GetBetween(ByVal sSearch As String, ByVal sStart As String, ByVal sStop As String, _
                          Optional ByVal lSearch As Long = 1, Optional ByVal bCaseSensitive As Boolean = True) As String
Dim lStart As Long, lStop As Long
Dim lenStart As Long
    lenStart = Len(sStart)
    If bCaseSensitive = False Then
        lStart = InStr(lSearch, sSearch, sStart, vbTextCompare) + lenStart
        lStop = InStr(lStart, sSearch, sStop, vbTextCompare)
    Else
        lStart = InStr(lSearch, sSearch, sStart) + lenStart
        lStop = InStr(lStart, sSearch, sStop)
    End If
        If (lSearch + lenStart) <= lStart Then
            If lStart < lStop Then
                GetBetween = Mid$(sSearch, lStart, (lStop - lenStart - 1))
                Exit Function
            End If
        End If
    GetBetween = vbNullString
End Function

I am looking to make this as efficient as possible. =]

Nothing radical but this is a little shorter and may be a little quicker;

Code:

---

Public Function GetBetween2(ByVal sSearch As String, ByVal sStart As String, ByVal sStop As String, Optional ByVal lSearch As Long = 1, Optional ByVal Compare As VbCompareMethod = vbTextCompare) As String
   
    Dim lStart As Long, lStop As Long
    Dim lenStart As Long
   
    lenStart = Len(sStart)
    lStart = InStr(lSearch, sSearch, sStart, Compare) + lenStart
   
    If (lSearch + lenStart) <= lStart Then
        lStop = InStr(lStart, sSearch, sStop, Compare)
        If lStart < lStop Then
            GetBetween2 = Mid$(sSearch, lStart, (lStop - lenStart - 1))
        End If
    End If
   
End Function

---

Are you using this function in a loop? Depending on size of data and how many times you are calling it, you may want to re-work your code and possibly use byte arrays instead...

Otherwise, this may be a lot more code, but who cares? Some things that will slow yours down in a loop are...

1.) Passing string arguments ByVal. This creates a copy of the string each time the function is called. You should use ByRef (which is default)

2.) vbTextCompare is slow. It's actually better to convert the strings to lower case and perform a case-sensitive search. Try this...if it is not fast enough, then maybe a byte array solution will be better...

Code:

---

Option Explicit

Private Sub Form_Load()
    'Not case-sensitive
    MsgBox GetBetween("This is a test", "this", "test", , False) 'is a
   
    'Case-sensitive
    MsgBox GetBetween("This is a test", "This", "test") 'is a
End Sub

Private Function GetBetween(ByRef sSearch As String, _
    ByRef sStart As String, _
    ByRef sStop As String, _
    Optional ByVal lSearch As Long = 1, _
    Optional ByVal bCaseSensitive As Boolean = True) As String
   
    Dim lonS As Long, lonE As Long
    Dim strLCSearch As String
    Dim strLCStart As String, strLCStop As String
   
    'Case-sensitive...
    If bCaseSensitive Then
        lonS = InStr(lSearch, sSearch, sStart)
        If lonS Then
            lonS = lonS + Len(sStart)
            lonE = InStr(lonS, sSearch, sStop)
            If lonE Then
                GetBetween = Mid$(sSearch, lonS, lonE - lonS)
            End If
        End If
   
    'Not case-sensitive
    'Faster to convert string to lowercase rather than vbTextCompare
    Else
        strLCSearch = LCase$(sSearch)
        strLCStart = LCase$(sStart)
        strLCStop = LCase$(sStop)
        lonS = InStr(lSearch, strLCSearch, strLCStart)
        If lonS Then
            lonS = lonS + Len(strLCStart)
            lonE = InStr(lonS, strLCSearch, strLCStop)
            If lonE > 0 Then
                GetBetween = Mid$(sSearch, lonS, lonE - lonS)
            End If
        End If
    End If
End Function

---

Quote:

---

Originally Posted by guitar

It's actually better to convert the strings to lower case and perform a case-sensitive search.

---

Agreed. But, upper case could be faster due to smaller values :p

With the experience I have, this should be faster than the InStr versions here:

Code:

---

Public Function InStrBetween(ByRef Search As String, ByRef StringBegin As String, ByRef StringEnd As String, Optional ByVal Start As Long = 1, Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As String
    Dim lngLenSea As Long, lngLenBeg As Long, lngLenEnd As Long
    Dim lngBegin As Long, lngEnd As Long
    Dim strSearch As String, strBegin As String, strEnd As String
    ' get string lengths
    lngLenSea = LenB(Search)
    lngLenBeg = LenB(StringBegin)
    lngLenEnd = LenB(StringEnd)
    ' make sure we have lengths and a valid starting position
    If (lngLenSea <> 0) And (lngLenBeg <> 0) And (lngLenEnd <> 0) And (Start > 0) Then
        ' make start a byte position
        Start = ((Start - 1) * 2) + 1
        ' case sensitive?
        If Compare = vbBinaryCompare Then
            ' find the starting position
            lngBegin = InStrB(Start, Search, StringBegin, vbBinaryCompare)
            ' because InStrB finds byte positions, we have to ensure we do not get "mid character" positions
            Do While ((lngBegin And 1) = 0) And (lngBegin > 0)
                lngBegin = InStrB(lngBegin + lngLenBeg, Search, StringBegin, vbBinaryCompare)
            Loop
            If lngBegin Then
                lngBegin = lngBegin + lngLenBeg
                ' find the ending position
                lngEnd = InStrB(lngBegin, Search, StringEnd, vbBinaryCompare)
                ' because InStrB finds byte positions, we have to ensure we do not get "mid character" positions
                Do While ((lngEnd And 1) = 0) And (lngEnd >= lngBegin)
                    lngEnd = InStrB(lngEnd + lngLenEnd, Search, StringEnd, vbBinaryCompare)
                Loop
                ' make sure we have something
                If lngEnd > lngBegin Then
                    ' return the result
                    InStrBetween = MidB$(Search, lngBegin, lngEnd - lngBegin)
                End If
            End If
        Else
            ' make upper case copies
            strSearch = UCase$(Search)
            strBegin = UCase$(StringBegin)
            ' find the starting position
            lngBegin = InStrB(Start, strSearch, strBegin, vbBinaryCompare)
            ' because InStrB finds byte positions, we have to ensure we do not get "mid character" positions
            Do While ((lngBegin And 1) = 0) And (lngBegin > 0)
                lngBegin = InStrB(lngBegin + lngLenBeg, strSearch, strBegin, vbBinaryCompare)
            Loop
            If lngBegin Then
                lngBegin = lngBegin + lngLenBeg
                ' make upper case copy
                strEnd = UCase$(StringEnd)
                ' find the ending position
                lngEnd = InStrB(lngBegin, strSearch, strEnd, vbBinaryCompare)
                ' because InStrB finds byte positions, we have to ensure we do not get "mid character" positions
                Do While ((lngEnd And 1) = 0) And (lngEnd >= lngBegin)
                    lngEnd = InStrB(lngEnd + lngLenEnd, strSearch, strEnd, vbBinaryCompare)
                Loop
                ' make sure we have something
                If lngEnd > lngBegin Then
                    ' return the result
                    InStrBetween = MidB$(Search, lngBegin, lngEnd - lngBegin)
                End If
            End If
        End If
    End If
End Function

---

As you can see, it needs some additional checks because InStrB finds byte positions, not character positions, so strings containing Unicode characters or null characters could give incorrect results. Thus there is some extra looping with InStrB to make sure positions are odd results, not even results. Didn't benchmark.

VBspeed site has a faster InStr for TextCompare. There are also Boyer-Moore based InStr versions around, they have some extra slowdown as string to be found must be indexed, but strings longer than five characters should be mostly faster to find with Boyer-Moore than native InStr.

This is my short and simple version. I found that :
* with vbBinaryCompare : this function is faster than Merri's function
* with vbTextCompare : this function is slower than Merri's function

Code:

---

Public Function GetBetween(sSearch As String, sLeft As String, sRight As String, _
                          Optional lStart As Long = 1, _
                          Optional Compare As VbCompareMethod = vbBinaryCompare) As String
  Dim i As Long, j As Long
 
  i = InStr(lStart, sSearch, sLeft, Compare)
  If i Then
      i = i + Len(sLeft)
      j = InStr(i, sSearch, sRight, Compare)
      If j Then GetBetween = Mid$(sSearch, i, j - i)
  End If
End Function

---

ahn's is the same one I've always used in the past but I thought maybe mine would be faster for case-insensitivity...

But of course, Merri comes out with the faster one... :(

anhn: I guess you only tested under IDE, because compiled my code is much faster :)

I put a benchmark in to the VBspeed project, I'll update the project if people are interested enough.