Excel VBA句子案例功能需要微调 [英] Excel VBA Sentence Case Funtion Needs Fine Tuning

问题描述

以下是其他人构建的功能，可以将文本更改为句子大小写(每个句子的首字母大写).该函数运行良好，但它不会将第一个单词的首字母大写.另一个问题是，如果在所有大写字母中输入一个句子，则该函数将不执行任何操作.我正在寻求一些帮助，以调整功能以纠正这些问题.

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 = "(^|[\.\?\!\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
End With

ProperCaps = strIn
End Function

谢谢，加里

推荐答案

我将函数重命名为SentenceCase()并进行了一些其他调整:

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 = "(^|[\n\f\r\t\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")

结果:

您可以在此处找到更多详细信息: Microsoft-正则表达式

You can find more details here: Microsoft - Regular Expressions

这篇关于Excel VBA句子案例功能需要微调的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！