Excel VBA Sentence Case Funtion Needs Fine Tuning

Question

Below is a function built by others that changes text into sentence case (first letter of each sentence capitalized). The function works nicely except it doesn't capitalize the first letter of the first word. Another issue is that if a sentence is entered in all caps, the function does nothing. I'm looking for some assistance in tweaking the function to correct these issues.

Option Explicit 
Function ProperCaps(strIn As String) As String

Dim objRegex As Object
Dim objRegMC As Object
Dim objRegM As Object

Set objRegex = CreateObject("vbscript.regexp")
strIn = LCase$(strIn)

With objRegex
    .Global = True
    .ignoreCase = True
    .Pattern = "(^|[.?!
	]s?)([a-z])"

    If .test(strIn) Then
        Set objRegMC = .Execute(strIn)

        For Each objRegM In objRegMC
            Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
        Next
    End If
End With

ProperCaps = strIn
End Function

Thanks, Gary

Answer 1

I renamed the function to SentenceCase() and made a few more adjustments:

---

Public Function SentenceCase(ByVal str As String) As String
    Dim regEx As Object, regExM As Object, indx As Object, indxs As Object
    Set regEx = CreateObject("VBScript.RegExp")
    str = Replace$(str, vbNullChar, vbLf)
    str = Replace$(str, vbBack, vbLf)
    str = LTrim$(LCase$(str))
    With regEx
        .IgnoreCase = True
        .MultiLine = True
        .Global = True
        .Pattern = "(^|[
f
	v.!?]s*)(w)"
        If .Test(str) Then
            Set indxs = .Execute(str)
            For Each indx In indxs
                Mid$(str, indx.FirstIndex + 1, indx.Length) = UCase$(indx)
            Next
        End If
    End With
    SentenceCase = str
End Function

---

This is what I tested it with:

MsgBox SentenceCase(" UPPER CASE SENTENCE." & _
                    vbCrLf & "next line!nEXT sENTENCE" & _
                    vbCr & "cr ! lower case" & _
                    vbLf & "lf .new sentence" & _
                    vbNullChar & " null?null char" & _
                    vbNullString & "nullString  spaces" & _
                    vbTab & "TAB CHAR.ttt" & _
                    vbBack & "back?  back char" & _
                    vbFormFeed & "ff  ff words" & _
                    vbVerticalTab & "vertical tab.| lower .case words")

Results:

You can find more details here: Microsoft - Regular Expressions