Converting to sentence case using VBA

Question

I've been trawling through page after page on Google and here looking for a solution to this seemingly simple request, but to no avail. Does anyone know a reliable way to convert a string to sentence case using vba?

Ideally I would build it into a sub rather than a function, so it is easier to call from the GUI.

For reference, I would want:

HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY.

to become:

Here is a long, ugly uppercase sentence. Please amend me immediately.

Converting to Title Case I found extremely simple (as there's a built-in function for that) but converting to sentence case has proven really difficult indeed.

I have tried some of the following methods but come up with errors at every turn:

• http://www.vbforums.com/showthread.php?t=536912
• http://vbamacros.blogspot.com/2007/09/sentence-case.html

How can I get this to work?

Answer 1

You could use a RegExp to more efficiently run the parsing

Something like this

Sub Tested()
    Call ProperCaps("HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY." & vbCrLf & "next line! now")
End Sub

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 = "(^|[\.\?\!\r\t]\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
        MsgBox strIn
    End With
End Function

Answer 2

Thanks for this, useful bit of code. Why VB has proper case and not sentence case is very strange. I have tweaked it for my purpose, as the original won't capitalise the first letter if there is a space in front of it, hope you don't mind me sharing my few changes.

To remove any unwanted spaces at the start or end of the sentence, I have added another function that is called from the above.

Public Function DblTrim(vString As String) As String
Dim tempString As String
tempString = vString

Do Until Left(tempString, 1) <> " "
   tempString = LTrim(tempString)
Loop
Do Until Right(tempString, 1) <> " "
   tempString = RTrim(tempString)
Loop

DblTrim = tempString

End Function

Public 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 = DblTrim(strIn)
    strIn = LCase$(strIn)

    With objRegex
        .Global = True
        .ignoreCase = True
         .Pattern = "(^|[\.\?\!\r\t]\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
        ProperCaps = strIn
    End With
End Function

You can call ProperCaps(Yourstring) to get the sentence back with the first letter as a capital, with all spaces removed.

You can also use DblTrim(Yourstring) to remove all spaces at the front and back of the string (without altering the sentence case), regardless of how many spaces there are.