Excel VBA code midStr() function using left and right position instead of length used with mid() In the early days of Basic (1970's when i started coding) the midStr() function used left and right positions directly rather than left and length we now use with mid(). I always found it more efficient in my mind to code using the old midStr() method, so I finally just created some code to do it. It's certainly not as efficient as mid(), and if I am dealing with lots of data I'd use mid() directly instead, but for simplicity, I do like the old way better, it's more direct to simply code rightPos rather then rightpos - leftPos + 1, and to test for null.
I also made the rightPos optional, and when not used, the length of the textin is used instead, so it will simply return the remainder of the text starting at the leftPos.
Any kind critique is welcome, thank you.
Function midStr(ByVal textin As String, _
                ByVal leftPos As Long, _
       Optional ByVal rightPos As Variant) As Variant
   'midStr returns textin string using leftPos and rightPos (rather than num_chars).
   'rightPos is optional, len(textin) is used for the rightPos when it's not otherwise defined.
  
   On Error GoTo midStrErr
   If IsMissing(rightPos) Then rightPos = Len(textin)
   If rightPos &lt; leftPos Then
      midStr = vbNullString
   Else
      midStr = Mid(textin, leftPos, rightPos - leftPos + 1)
   End If
   Exit Function
 
midStrErr:
   On Error Resume Next
   midStr = CVErr(xlErrValue) '#VALUE!
End Function
While the Optional ByVal rightPos As Variant is a cool idea because you can use the IsMissing function, it introduces unnecessary errors. For example, Debug.Print midStr(&quot;Hello World&quot;, 1, Nothing) will print &quot;Error 2015&quot; in the Immediate window. That line should not be allowed to compile.
I would declare the rightPos parameter as Long. The compiler will take care of some of the issues - for example you won't be able to pass an Object. The Long will default to 0 so you can replace If IsMissing(rightPos) Then rightPos = Len(textin) with If rightPos = 0 Then rightPos = Len(textin).
The generally accepted convention in VBA is that method names are written with PascalCase and variable names are written with camelCase. Moreover, an Excel User Defined Function (UDF) is usually written with UPPERCASE.
This function seems to be designed as an Excel UDF. What if you need to use it in VBA? The Excel errors mean nothing to another VBA method. I would write this as a separate VBA only function that will work in any VBA-capable application host (no need to return a Variant):
Public Function MidStr(ByVal textin As String, _
                       ByVal leftPos As Long, _
              Optional ByVal rightPos As Long _
) As String
    Const methodName As String = &quot;MidStr&quot;
    
    If leftPos &lt; 0 Then Err.Raise 5, methodName, &quot;Invalid left position&quot;
    If rightPos &lt; 0 Then Err.Raise 5, methodName, &quot;Invalid right position&quot;
    
    If rightPos = 0 Then rightPos = Len(textin)
    If rightPos &lt; leftPos Then
        MidStr = vbNullString
    Else
        MidStr = VBA.Mid$(textin, leftPos, rightPos - leftPos + 1)
    End If
End Function

I would then have an Excel UDF:
Public Function MID_STR(ByVal textin As String, _
                        ByVal leftPos As Long, _
               Optional ByVal rightPos As Long _
) As Variant
    On Error GoTo ErrorHandler
    MID_STR = MidStr(textin, leftPos, rightPos)
Exit Function
ErrorHandler:
    MID_STR = CVErr(xlErrValue)
End Function

This allows the use of MidStr function in other VBA methods and the MID_STR is only going to be called via the Excel interface.