[RESOLVED] Lower code does not necessarily mean greater speed

[prote01]

They help me understand why, here are some examples

        Dim sString  As String: Dim lTemp As Long: Dim iCont As Integer: Dim sText As String
        sString = Replace("First,Second,Third,Fourth,Fifth,Sixth,Seventh", ",", vbTab)
        For lTemp = 0 To 9999999
         'sText = GetIt(sString, iCont)  '20.5715377233699 miliseconds
         'sText = GetIt1(sString, iCont) '39.5765626636905 miliseconds
         'sText = GetIt2(sString, iCont) '23.1855129632397 miliseconds
         If iCont = 9 Then iCont = 0 Else iCont = iCont + 1
        Next lTemp

20 lines

Public Function GetIt(ByVal vSource As String, vIndex As Integer, Optional vSep As String = vbTab) As String
 Dim oTexto As String
 Dim oTempo As Integer '2025-12-11
 If vSource = "" Then
  oTexto = ""
 Else
  If InStr(vSource, vSep) > 0 Then oTexto = vSource + vSep Else Exit Function
  Do While InStr(oTexto, vSep)
   If oTempo = vIndex Then
    oTexto = Mid(oTexto, 1, InStr(oTexto, vSep) - 1)
    Exit Do
   ElseIf oTempo > vIndex Then
    oTexto = ""
    Exit Do
   End If
   oTexto = Mid(oTexto, InStr(oTexto, vSep) + Len(vSep))
   oTempo = oTempo + 1
  Loop
  If Len(oTexto) > 200 Then oTexto = Trim(oTexto)
 End If
 GetIt = oTexto
End Function

8 lines

Public Function GetIt1(vSource As String, vIndex As Integer, Optional vSep As String = vbTab) As String
 Dim vStrings() As String '2026-07-03
 If vSource <> "" Then
  If InStr(vSource, vSep) > 0 Then
   vStrings = Split(vSource, vSep)
   If vIndex <= UBound(vStrings) Then GetIt1 = vStrings(vIndex) Else Exit Function
   If Len(GetIt1) > 200 Then GetIt1 = Trim(GetIt1)
  End If
 End If
End Function

17 lines

Public Function GetIt2(ByVal vSource As String, vIndex As Integer, Optional vSep As String = vbTab) As String
 Dim oTempo As Integer '2026-07-06
 If vSource <> "" Then
  vSource = IIf(InStr(vSource, vSep) = 0, "", vSource + vSep)
  Do While InStr(vSource, vSep)
   If oTempo = vIndex Then
    vSource = Mid(vSource, 1, InStr(vSource, vSep) - 1)
    Exit Do
   ElseIf oTempo > vIndex Then
    vSource = ""
    Exit Do
   End If
   oTempo = oTempo + 1
   vSource = Mid(vSource, InStr(vSource, vSep) + Len(vSep))
  Loop
  If Len(vSource) > 200 Then vSource = Trim(vSource)
 End If
 GetIt2 = vSource
End Function

[baka]

Split is slow.
do u compile it before the test or u run it in IDE?

[Zvoni]

I'm trying to figure out, what he's trying to achieve, though i think i can see it:
He want's to get the nth substring between 2 separators, when n=vIndex

not really tested

Private Function getIt(ByVal sSource As String, ByVal vIndex As Long, Optional ByVal Sep As String = vbTab) As String
Dim i As Long
Dim r As Long
Const tempSep As String = "|"
    getIt = ""
    sSource = Trim$(sSource)
    If sSource = "" Then Exit Function
    sSource = Replace(sSource, Sep, tempSep, , vIndex)
    r = InStrRev(sSource, tempSep)
    i = InStr(1, sSource, Sep)
    If i = 0 Then i = Len(sSource) + 1
    getIt = Mid$(sSource, r + 1, i - r - 1)
End Function

Sub main()
Dim i As Long
    s = "First,Second,Third,Fourth,Fifth,Sixth,Seventh"
    For i = 0 To 6
        Debug.Print getIt(s, i, ",")
    Next
End Sub

Returns

First
Second
Third
Fourth
Fifth
Sixth
Seventh

If index exceeds available substrings, it still returns the last one
e.g.
Debug.Print getIt(s, 9, ",") still returns "seventh"

[Zvoni]

v2

Private Function TrimSep(ByVal sSource As String, ByVal sSep As String) As String
Dim i As Long
    sSource = Trim$(sSource) 'Remove whitespace
    i = 1
    Do While i <= Len(sSource)
        If Mid$(sSource, i, 1) <> sSep Then Exit Do
        i = i + 1
    Loop
    sSource = Mid$(sSource, i)
    i = Len(sSource)
    Do While i > 0
        If Mid$(sSource, i, 1) <> sSep Then Exit Do
        i = i - 1
    Loop
    TrimSep = Left$(sSource, i)
End Function
Private Function CountChars(ByVal sSource As String, ByVal sChars As String) As Long
Dim i As Long
    CountChars = 0
    i = InStr(1, sSource, sChars)
    Do While i > 0
        CountChars = CountChars + 1
        i = InStr(i + 1, sSource, sChars)
    Loop
End Function
Private Function getIt(ByVal sSource As String, ByVal vIndex As Long, Optional ByVal Sep As String = vbTab) As String
Dim i As Long
Dim r As Long
Dim c As Long
Const tempSep As String = "|"
    getIt = ""
    sSource = Trim$(sSource)
    If sSource = "" Then Exit Function
    sSource = TrimSep(sSource, Sep)
    c = CountChars(sSource, Sep)
    If c < vIndex Then Exit Function
    sSource = Replace(sSource, Sep, tempSep, , vIndex)
    r = InStrRev(sSource, tempSep)
    i = InStr(1, sSource, Sep)
    If i = 0 Then i = Len(sSource) + 1
    getIt = Mid$(sSource, r + 1, i - r - 1)
End Function

Sub main()
Dim i As Long
    s = ",,First,Second,Third,Fourth,Fifth,Sixth,Seventh,,,"
    For i = 0 To 9
        Debug.Print getIt(s, i, ",")
    Next
End Sub

Returns the same as above, with following difference:
if Index exceeds available substrings, empty string is returned

I'm also stripping off any leading/trailing Separators

NotaBene: I'm aware that i#m Trimming sSource twice.
I've left it that way, since the TrimSep-Function can be called outside of the GetIt-Function
No Sanity-Check in CountChars it you are looking for a single character

[wqweto]

IMO the idea should be to convince OP that "lower code = greater speed", not that hundreds of lines are always fastest :-))

Edit: Here is a cached version of GetIt1

Public Function GetIt1Cached(vSource As String, vIndex As Integer, Optional vSep As String = vbTab) As String
    Static vStrings() As String '2026-07-03
    Static PrevSource As String
    
    If InStr(vSource, vSep) > 0 Then
        If vSource <> PrevSource Then
            PrevSource = vSource
            vStrings = Split(vSource, vSep)
        End If
        If vIndex > UBound(vStrings) Then
            Exit Function
        End If
        GetIt1Cached = vStrings(vIndex)
        If Len(GetIt1Cached) > 200 Then
            GetIt1Cached = Trim(GetIt1Cached)
        End If
    End If
End Function

This is 10x faster than original GetIt1 and works extremely well for CSV files as it splits each row once and then just returns "columns" as requested.

cheers,
</wqw>

[Eduardo-]

Ok, but for clarity for the OP: the point is right: LOC and speed are unrelated.

[PlausiblyDamp]

Ok, but for clarity for the OP: the point is right: LOC and speed are unrelated.

In fact there are times when a compiler might effectively rewrite your code to be larger e.g. loop unrolling... to get better performance.

[prote01]

Split is slow.
do u compile it before the test or u run it in IDE?

I tested it in IDE

[prote01]

I'm trying to figure out, what he's trying to achieve, though i think i can see it:
He want's to get the nth substring between 2 separators, when n=vIndex

not really tested

Private Function getIt(ByVal sSource As String, ByVal vIndex As Long, Optional ByVal Sep As String = vbTab) As String
Dim i As Long
Dim r As Long
Const tempSep As String = "|"
    getIt = ""
    sSource = Trim$(sSource)
    If sSource = "" Then Exit Function
    sSource = Replace(sSource, Sep, tempSep, , vIndex)
    r = InStrRev(sSource, tempSep)
    i = InStr(1, sSource, Sep)
    If i = 0 Then i = Len(sSource) + 1
    getIt = Mid$(sSource, r + 1, i - r - 1)
End Function

Sub main()
Dim i As Long
    s = "First,Second,Third,Fourth,Fifth,Sixth,Seventh"
    For i = 0 To 6
        Debug.Print getIt(s, i, ",")
    Next
End Sub

Returns


If index exceeds available substrings, it still returns the last one
e.g.
Debug.Print getIt(s, 9, ",") still returns "seventh"

Thanks for your response. The idea is to get text from a specific position defined by a separator (sometimes it can be a tab, a semicolon, a comma, or even text), but it's always important to return the actual value. In this case, position 9 should return empty ("").

The TRIM function only applies when the extracted string contains 200 spaces.

Because I need to hide the index within the combo box/listbox, for example:

Combo1.AddItem "James" + Space(200) + vbtab + "0"
Combo1.AddItem "Jane" + Space(200) + vbtab + "1"`

However, if I work with CSV files, cleaning the 200 spaces is not required.

[prote01]

v2

Private Function TrimSep(ByVal sSource As String, ByVal sSep As String) As String
Dim i As Long
    sSource = Trim$(sSource) 'Remove whitespace
    i = 1
    Do While i <= Len(sSource)
        If Mid$(sSource, i, 1) <> sSep Then Exit Do
        i = i + 1
    Loop
    sSource = Mid$(sSource, i)
    i = Len(sSource)
    Do While i > 0
        If Mid$(sSource, i, 1) <> sSep Then Exit Do
        i = i - 1
    Loop
    TrimSep = Left$(sSource, i)
End Function
Private Function CountChars(ByVal sSource As String, ByVal sChars As String) As Long
Dim i As Long
    CountChars = 0
    i = InStr(1, sSource, sChars)
    Do While i > 0
        CountChars = CountChars + 1
        i = InStr(i + 1, sSource, sChars)
    Loop
End Function
Private Function getIt(ByVal sSource As String, ByVal vIndex As Long, Optional ByVal Sep As String = vbTab) As String
Dim i As Long
Dim r As Long
Dim c As Long
Const tempSep As String = "|"
    getIt = ""
    sSource = Trim$(sSource)
    If sSource = "" Then Exit Function
    sSource = TrimSep(sSource, Sep)
    c = CountChars(sSource, Sep)
    If c < vIndex Then Exit Function
    sSource = Replace(sSource, Sep, tempSep, , vIndex)
    r = InStrRev(sSource, tempSep)
    i = InStr(1, sSource, Sep)
    If i = 0 Then i = Len(sSource) + 1
    getIt = Mid$(sSource, r + 1, i - r - 1)
End Function

Sub main()
Dim i As Long
    s = ",,First,Second,Third,Fourth,Fifth,Sixth,Seventh,,,"
    For i = 0 To 9
        Debug.Print getIt(s, i, ",")
    Next
End Sub

Returns the same as above, with following difference:
if Index exceeds available substrings, empty string is returned

I'm also stripping off any leading/trailing Separators

NotaBene: I'm aware that i#m Trimming sSource twice.
I've left it that way, since the TrimSep-Function can be called outside of the GetIt-Function
No Sanity-Check in CountChars it you are looking for a single character

Thanks for your reply, I appreciate the features, I'll try it and comment later.

A few minutes later, thank you very much, I made some small modifications and tested it.

Public Function GetIt(ByVal vSource As String, vIndex As Integer, Optional ByVal vSep As String = vbTab) As String
     Dim vTempo As Integer '2026-07-07
     If vSource = "" Then Exit Function
     If InStr(vSource, vSep) > 0 Then vSource = vSource + vSep Else Exit Function
     If vSep <> vbTab Then vSource = Replace(vSource, vSep, vbTab) 'Thanks Znovi
     If vIndex >= (Len(vSource) - Len(Replace(vSource, vbTab, ""))) Then Exit Function 'Thanks Znovi

     'For vTempo = 1 To vIndex
     '     vSource = Mid(vSource, InStr(vSource, vbTab) + 1)
     'Next vTempo
     'vSource = Mid(vSource, 1, InStr(vSource, vbTab) - 1) '39.7216017678224

     Do While InStr(vSource, vbTab)
          If vTempo = vIndex Then
               vSource = Mid(vSource, 1, InStr(vSource, vbTab) - 1)
               Exit Do
          End If
          vTempo = vTempo + 1
          vSource = Mid(vSource, InStr(vSource, vbTab) + 1)
     Loop '41.9700562501659

     If Len(vSource) > 200 Then vSource = Trim(vSource)
     GetIt = vSource
End Function

[prote01]

IMO the idea should be to convince OP that "lower code = greater speed", not that hundreds of lines are always fastest :-))

Edit: Here is a cached version of GetIt1

Public Function GetIt1Cached(vSource As String, vIndex As Integer, Optional vSep As String = vbTab) As String
    Static vStrings() As String '2026-07-03
    Static PrevSource As String
    
    If InStr(vSource, vSep) > 0 Then
        If vSource <> PrevSource Then
            PrevSource = vSource
            vStrings = Split(vSource, vSep)
        End If
        If vIndex > UBound(vStrings) Then
            Exit Function
        End If
        GetIt1Cached = vStrings(vIndex)
        If Len(GetIt1Cached) > 200 Then
            GetIt1Cached = Trim(GetIt1Cached)
        End If
    End If
End Function

This is 10x faster than original GetIt1 and works extremely well for CSV files as it splits each row once and then just returns "columns" as requested.

cheers,
</wqw>

Thanks for your reply, I appreciate the features, I'll try it and comment later.

A few minutes later, it's simply amazing, thank you so much. I modified it a little.

Public Function GetIt(ByVal vSource As String, vIndex As Integer, Optional ByVal vSep As String = vbTab) As String
 Static vStrings() As String
 Static PrevSource As String
 If InStr(vSource, vSep) > 0 Then
  If vSource <> PrevSource Then
   PrevSource = vSource
   vStrings = Split(vSource, vSep)
  End If
  If vIndex <= UBound(vStrings) Then GetIt = vStrings(vIndex) Else Exit Function
  If Len(GetIt) > 200 Then GetIt = Trim(GetIt)
 End If
End Function '5.9048239371205

[prote01]

Ok, but for clarity for the OP: the point is right: LOC and speed are unrelated.

Understood, thanks for the clarification.

[prote01]

In fact there are times when a compiler might effectively rewrite your code to be larger e.g. loop unrolling... to get better performance.

Interesting, I didn't know that.

[Eduardo-]

I tested it in IDE

Testing in the IDE can be useful as a clue, but the actual important performance test is compiled. In IDE and compiled the speed is usually very different.
Also, faster in IDE not always means faster when compiled.

[prote01]

Testing in the IDE can be useful as a clue, but the actual important performance test is compiled. In IDE and compiled the speed is usually very different.
Also, faster in IDE not always means faster when compiled.

The test performed with this code significantly improved the times, thanks to wqweto.