Excel VBA code midStr() function using left and right position instead of length used with mid()

Question

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

Answer 1

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.

Answer 2

Pascal Case

When I first started code, Hungarian Notations was a widely accepted standard for VBA. Over the years, Camel Case took over. In my opinion, it is time for Pascal Case to become the standard.

This has been my rant for some time. I welcome a debate from all user from noob to MVP. The following snippet exemplifies my argument:

Dim value As Range
value = Range("A1").value

Dim text As String
text = Range("A1").text

Using Camel Casing to name a variable changes the casing for all properties and methods that share the name for the whole project. I've answer over 2000 question in forum over the years. I can't tell you how many times the improper casing threw me off. It is ridiculous that Microsoft never fixed it.

Function Signature

Function midStr(ByVal textin As String, _
                ByVal leftPos As Long, _
       Optional ByVal rightPos As Variant) As Variant

As Cristian Buse mentioned rightPos should be typed Long and the function itself should return a string. I would add that rightPos is rather confusing.

*used left and right positions directly rather than left and length

When I first read this, I thought: "midStr returns a string using the Left() and Right() functions. How peculiar. I wonder how this is easier than mid" WRONG!! As it turns out it it use the starting and ending position to return a substring. That's great, I love it!

I would recommend calling the function SubString but it's named after a vintage VB function. Fo me, it would be better if it returned 1 less character like most similar substring functions.

leftPos and rightPos on the other hand they have got to go. It's too confusing, especially because the function already behaves a little different than expect by returning the extra character. I would use start and end if they were not reserved words. StartIndex and EndIndex or pStart and pEnd are much more intuitive to me.

Function MidStr(ByVal Text As String, ByVal StartIndex As Long, Optional ByVal EndIndex As Variant) As String 'midStr returns text string using startIndex and endIndex (rather than num_chars). 'endIndex is optional, len(text) is used for the endIndex when it's not otherwise defined.

   On Error GoTo midStrErr
   If EndIndex = 0 Then EndIndex = Len(Text)
   If EndIndex < StartIndex Then
      MidStr = vbNullString
   Else
      MidStr = Mid(Text, StartIndex, EndIndex - StartIndex + 1)
   End If
   Exit Function
 
midStrErr:
   On Error Resume Next
   MidStr = CVErr(xlErrValue) '#VALUE!
End Function

Answer 3

I believe this conforms to a more standard style, i am using start_num, all in lower_case, because that is what i find in the MID() function. I want the code clean and short. As always, I appreciate any helpful feedback. Thank you in advance.

Function MIDSTR(ByVal textin As String, _
                ByVal start_num As Long, _
       Optional ByVal end_num As Long = -1) As String
   'MIDSTR() is similar to MID() except it returns textin string using start_num and end_num instead of using num_chars.
   'end_num is optional, Len(textin) is used for the end_num when it's not otherwise defined or when end_enum is a negative number.
   'Null is returned when start_num is greater than end_num.
  
    If end_num < 0 Then end_num = Len(textin)
    If start_num > end_num Then
        MIDSTR = vbNullString
    Else
        MIDSTR = Mid(textin, start_num, end_num - start_num + 1)
    End If
End Function