VBA转换为适当的大小写，跳过某些单词

Question

目前我可以将一系列单元格转换为适当的大小写。我需要在转换为正确大小写时跳过单元格中的某些单词。

示例：">Get Of From Here"将转换为">Get Of From Here">

但是">的"一词不应转换为适当的大小写。能做到吗？

这是我编写的代码，用于将范围转换为适当的大小写。

Sub Processproper()
Dim Rng As Range
Dim WorkRng As Range
Dim xTitleId
On Error Resume Next
xTitleId = "SelectRange"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If IsEmpty(Rng) = True Then
Else
    Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next
end sub

Answer 1

Sub Processproper()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim xTitleId
    On Error Resume Next
    xTitleId = "SelectRange"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select Range", xTitleId, WorkRng.Address, Type:=8)
    For Each Rng In WorkRng
    If IsEmpty(Rng) = True Then
    Else
        Rng.Value = Title(Rng)
    End If
    Next
End Sub

Function Title(ByVal ref As Range) As String
    Dim vaArray As Variant
    Dim c As String
    Dim i As Integer
    Dim J As Integer
    Dim vaLCase As Variant
    Dim str As String
    ' Array contains terms that should be lower case
    vaLCase = Array("a", "an", "and", "in", "is", _
      "of", "or", "the", "to", "with")
    c = StrConv(ref, 3)
    'split the words into an array
    vaArray = Split(c, " ")
    For i = (LBound(vaArray) + 1) To UBound(vaArray)
        For J = LBound(vaLCase) To UBound(vaLCase)
            ' compare each word in the cell against the
            ' list of words to remain lowercase. If the
            ' Upper versions match then replace the
            ' cell word with the lowercase version.
            If UCase(vaArray(i)) = UCase(vaLCase(J)) Then
                vaArray(i) = vaLCase(J)
            End If
        Next J
    Next i
  ' rebuild the sentence
    str = ""
    For i = LBound(vaArray) To UBound(vaArray)
        str = str & " " & vaArray(i)
    Next i
    Title = Trim(str)
End Function