# 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 < 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
• It is interesting that midStr("Hello World", 2, 4) returns ell. I would have thought that it would return 2 characters. Not character 2, 3 and 4. Mar 30, 2021 at 23:44

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