4
\$\begingroup\$

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
\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – TinMan
    Mar 30, 2021 at 23:44

3 Answers 3

5
\$\begingroup\$

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.

\$\endgroup\$
5
  • \$\begingroup\$ Raising a runtime error from a public function invoked from a worksheet cell would have Excel swallow the error and the cell would contain a #VALUE! error, without needing to explicitly return CVErr(xlErrValue). Not sure duplicating the function is a good idea, basically - upvoted for everything else! \$\endgroup\$ Mar 31, 2021 at 15:03
  • 1
    \$\begingroup\$ @MathieuGuindon Yes, but dangeerous if you call the UDF using the Evaluate method from VBA (for whatever reason). Always best to make sure no error is raised within a UDF. An error raised during an Evaluate call will stop any VBA code in it's track. \$\endgroup\$ Mar 31, 2021 at 15:08
  • \$\begingroup\$ ...and that would be the expected behavior, yes :) the most likely outcome otherwise is a type mismatch resulting from capturing the return value into a String and getting a Variant/Error into it - throwing from the guard clauses would point to the real cause of the problem without any indirection. \$\endgroup\$ Mar 31, 2021 at 15:11
  • 1
    \$\begingroup\$ @MathieuGuindon It is, I won't argue with that, but there are cases where one would want to make sure that the Evaluate call does not kill VBA without gracefully cleaning up. It's a matter of coding style I guess \$\endgroup\$ Mar 31, 2021 at 15:17
  • \$\begingroup\$ Thank you for the suggestions. I will take the advice given by many to use StartIndex and EndIndex instead of LeftPos and RightPos. My current design allows any number to pass, and as long as StartPos <= EndPos then some character(s) will be returned, if not then NULL. But, I agree, defaulting to zero eliminates some error potential, and it does not limit the code at all, so I'll make that change. I also really appreciate the comments regarding PascalCase vs camelCase vs UPPER_CASE. I've never seen a dialog on the subject and so I plan to change to what is more standard. \$\endgroup\$
    – Mark Main
    Apr 1, 2021 at 22:22
2
\$\begingroup\$

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
\$\endgroup\$
7
  • \$\begingroup\$ I've semi-recently adopted PascalCaseEverywhere and haven't looked back since (had adopted camelCaseForLocals from years of C# influencing my VB style!). As for MS fixing it, I could be wrong but my understanding is that it's not something that's possible without rewriting how VBA caches in-scope names ...to make them case-sensitive. \$\endgroup\$ Mar 31, 2021 at 23:06
  • \$\begingroup\$ @MathieuGuindon you would know better that I do. I suspect that they originally did this to improve performance. In truth, I would hate to be the one responsible for fixing this nuisance and taking the heat for any new bug that comes along. Thanks for the ↑vote↑. \$\endgroup\$
    – TinMan
    Mar 31, 2021 at 23:40
  • 1
    \$\begingroup\$ To be fair, having a clean source control diff wasn't much of a concern back then :) \$\endgroup\$ Mar 31, 2021 at 23:47
  • 1
    \$\begingroup\$ Indeed, absolutely annoying to have VBA lowercasing method names because a variable with same name is defined somewhere or vice versa. I found myself adding a _ at the end of the common variable names (value_, key_) but is ugly and I don't like it. I will consider PascalCase for everything \$\endgroup\$ Apr 1, 2021 at 10:08
  • 1
    \$\begingroup\$ Thank you for the suggestions. I will take the advice given by many to use StartIndex and EndIndex instead of LeftPos and RightPos. My current design allows any number to pass, and as long as StartPos <= EndPos then some character(s) will be returned, if not then NULL. But, I agree, defaulting to zero eliminates some error potential, and it does not limit the code at all, so I'll make that change. I also really appreciate the comments regarding PascalCase vs camelCase vs UPPER_CASE. I've never seen a dialog on the subject and so I plan to change to what is more standard. \$\endgroup\$
    – Mark Main
    Apr 1, 2021 at 22:23
1
\$\begingroup\$

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
\$\endgroup\$
3
  • \$\begingroup\$ (moderator hat on) Since the site has to adhere to its Q&A format (and also because actual "answer" posts can go much more in-depth than any comment), it would be better to re-frame this "answer" (it's technically flaggable as "not an answer") as a follow-up question. Cheers! \$\endgroup\$ Apr 2, 2021 at 2:08
  • \$\begingroup\$ (moderator hat off) I'd make the function explicitly Public and either 1) use language-standard PascalCase and return a String to leverage VBA error handling (cells get #VALUE!), or 2) use the Excel-inspired SCREAMCASE and return a Variant to emphasize intended use as a UDF. Do you know about Rubberduck? \$\endgroup\$ Apr 2, 2021 at 2:15
  • \$\begingroup\$ Rubberduck inspections would flag the implicit/default access modifier, and would suggest using the stringly-typed Mid$ function over the variant-returning Mid, for example. And you could reword that comment into a @Description annotation just above the procedure, and then that documentation string could appear in the Object Browser. \$\endgroup\$ Apr 2, 2021 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.