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("Hello World", 1, Nothing) will print "Error 2015" in the Immediate window. That line should not 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 = "MidStr"
    
    If leftPos < 0 Then Err.Raise 5, methodName, "Invalid left position"
    If rightPos < 0 Then Err.Raise 5, methodName, "Invalid right position"
    
    If rightPos = 0 Then rightPos = Len(textin)
    If rightPos < 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.

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("Hello World", 1, Nothing) will print "Error 2015" 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 = "MidStr"
    
    If leftPos < 0 Then Err.Raise 5, methodName, "Invalid left position"
    If rightPos < 0 Then Err.Raise 5, methodName, "Invalid right position"
    
    If rightPos = 0 Then rightPos = Len(textin)
    If rightPos < 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.