This VBA macro does not work - Why? - (expected to capitalize first letter of first word in sentence, following period & space)

[abramo]

The following VBA macro that Bing AI provided does not work in excel 2010 - Why?
It is supposed to capitalize first letter of first word in sentence, following period & space in text- ex :

the rain in Spain. stays mainly in the plain.
result:
The rain in Spain. Stays mainly in the plain.

Sub UppercaseAfterPeriodMultipleCells()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Long

    ' Set the range to the cells you want to change
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A10")

    For Each cell In rng
        txt = cell.Value

        ' Convert the first character of the text to uppercase
        txt = UCase(Left(txt, 1)) & Right(txt, Len(txt) - 1)

        ' Loop through the rest of the text
        For i = 1 To Len(txt)
            ' If the character is a period, convert the next character to uppercase
            If Mid(txt, i, 1) = "." Then
                txt = Left(txt, i) & " " & UCase(Mid(txt, i + 2, 1)) & Right(txt, Len(txt) - i - 2)
            End If
        Next i

        ' Write the changed text back to the cell
        cell.Value = txt
    Next cell
End Sub

While there seems nothing wrong with this (none that I can say) it throws errors, ex. "Invalid procedure call or argument".
Any assistance much appreciated.
Thanks!

 

[Joe4]

Yeah, AI can be pretty horrible for this kind of stuff (which is why we have that banner at the top of our forum).

The issue is with the loop:

For i = 1 To Len(txt)

which is looping through each character of the string and how it interacts with this part of the formula (in red):

txt = Left(txt, i) & " " & UCase(Mid(txt, i + 2, 1)) & Right(txt, Len(txt) - i - 2)

Think of it. The last character in your string is a "." and the length of the string is 45.
So when i is 45, this is what that part evaluates to:
Len(txt) - i - 2
45-45-2
-2

-2 is NOT a valid length to use in the second argument of a RIGHT function!
It must be an integer greater than 0!

 

[Joe4]

To do what you want, see this article here MrExcel put together: Sentence Case in Excel

All you have to do is copy the three functions in that link over to a General VBA module in your project, then you can use the SENTENCECASE function like any other excel function.

So, if your string is in cell A1, then in cell B1 you could enter this formula:

=SENTENCECASE(A1)

and this would be your result:

 

[abramo]

@Joe4 - many thanks, let me work on this, and see what I can do!

 

[abramo]

According to
Sentence Case in Excel

following vba should work:

Sub doSentenceCase()
Dim rng As Range
Dim cll As Range
Dim resArr() As String
Dim newArr1(), newArr2(), newArr3() As Variant

    Set rng = Selection
    For Each cll In rng.Cells
        ReDim resArr(0)
        resArr(0) = cll.Value
        newArr1 = splitAndTransform(cll.Value, ".")
        If Not IsEmpty(newArr1) Then
            For Each par1 In newArr1
                newArr2 = splitAndTransform(par1, "?")
                If Not IsEmpty(newArr2) Then
                    For Each par2 In newArr2
                        newArr3 = splitAndTransform(par2, "!")
                        If Not IsEmpty(newArr3) Then
                            For Each par3 In newArr3
                                resArr(UBound(resArr)) = par3
                                ReDim Preserve resArr(UBound(resArr) + 1)
                            Next par3
                        End If
                    Next par2
                End If
            Next par1
        End If
        cll.Value = Join(resArr, " ")
    Next cll
End Sub

Function splitAndTransform(text, delimiter)
Dim tmpArr
Dim newArr
    tmpArr = Split(text, delimiter)
    If UBound(tmpArr) >= 0 Then
        ReDim newArr(UBound(tmpArr))
        For i = 0 To UBound(tmpArr)
            If tmpArr(i) <> "" Then
                newArr(i) = Trim(tmpArr(i))
                newArr(i) = UCase(Left(newArr(i), 1)) & _
                            LCase(Right(newArr(i), Len(newArr(i)) - 1))
                If Not isPuncMarked(newArr(i)) Then
                    newArr(i) = newArr(i) & delimiter
                End If
            End If
        Next i
    Else
        ReDim newArr(0)
    End If
    splitAndTransform = newArr
End Function

Function isPuncMarked(sentence) As Boolean
Dim rightMost As String
    rightMost = Right(sentence, 1)
    If rightMost = "." Or _
        rightMost = "?" Or _
            rightMost = "!" Then
            isPuncMarked = True
    Else
        isPuncMarked = False
    End If
End Function

Sub and its two Functions

This, though, returns error "Invalid procedure call or argument".
Apparently, I am doing something wrong - but what?
Cannot pinpoint the issue.
Can you?

Thanks!
.

 

[Joe4]

There are 3, not 2 functions. You are missing one.

I did not use the Sub. I just used the main Function right on the worksheet itself.
You can do it either way, but you need the "SENTENCECASE" you missed from the link!

 

[Rick Rothstein]

I did not look at any of the links, but this is how I would write a SentenceCase function...

Function SentenceCase(Txt As String) As String
  Dim X As Long
  Dim Arr As Variant
  Arr = Split(Txt, ". ")
  For X = 0 To UBound(Arr)
    Mid(Arr(X), 1, 1) = UCase(Arr(X))
  Next
  SentenceCase = Join(Arr, ". ")
End Function

 

[abramo]

Here is an alternative vba macro found at MrExcel at Sentence Case which works fine for me for "Sentence Case in Excel" :

Sub Sentence_Case_v2()
  Dim RX As Object, itm As Object
  Dim cell As Range
  Dim s As String
  
  Const Patt1 As String = "(^|\.|\!|\?)( *)([a-z])"
  Const Patt2 As String = "\bi\b"
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  
  Application.ScreenUpdating = False
  For Each cell In Selection
    s = LCase(cell.text)
    RX.Pattern = Patt1
    For Each itm In RX.Execute(s)
      Mid(s, itm.firstindex + 1, itm.Length) = UCase(itm)
    Next itm
    RX.Pattern = Patt2
    s = RX.Replace(s, "I")
    cell.Value = s
  Next cell
  Application.ScreenUpdating = True
End Sub

Hope this helps someone looking for this functionality.

AND MANY THANKS INDEED @Joe4 - who was so kind to spend valuable time to assist me with this issue.
.

 

[abramo]

@Rick Rothstein >> we just cross posted!
I will now happily test your suggested code and revert here with my results.
Many thanks!

 

[abramo]

@Rick Rothstein >> How would the proposed function go in a Sub?
Thanks!

(I am not lazy, just not knowledgeable enough!)