VBA中的多重替换

Question

我该如何在工作表中替换多个单词？

例如：Da...da，Do...do，Dos，De... de..等。

如何将其应用到名为“Customers3”的电子表格中？

Public Function MyProper(MyString As String, Optional exceptions As Variant)

Dim c As Variant
If IsMissing(exceptions) Then
    exceptions = Array("a", "as", "e", "o", "os", "da", "das", "de", "di", "do", "dos",  _
      "CPF", "RG", "E-Mail")
End If

MyString = Application.Proper(MyString)

For Each c In exceptions
    MyString = Replace(" " & MyString & " ", " " & c & " ", " " & LCase(c) & " ", , , vbTextCompare)
Next c

MyProper = MyString

End Sub

- Fernando F

---

你打算如何使用这个函数？ - BigBen

我有几个包含客户数据、姓名和地址的电子表格，我使用了“ProperFunction”来使其正确，但是粒子也被大写了第一个字母，这不应该发生。-抱歉，我的英语不太好。 - Fernando F

当你在Excel中使用它时会发生什么？例如，=MyProper(A1)？ - VBasic2008

我需要在每个电子表格中进行更改，但由于我仍在学习，我不知道如何将其插入到代码中。您能否请示我如何操作？ - Fernando F

Answer 1

这里有一个问题：

MyString = Replace(" " & MyString & " ", " " & c & " ", " " & LCase(c) & " ", , , vbTextCompare)

每次通过循环时，您都会添加更多的空格...

另外，您使用的是End Sub而不是End Function

请尝试以下代码：

Public Function MyProper(MyString As String, Optional exceptions As Variant)

    Dim c As Variant
    If IsMissing(exceptions) Then
        exceptions = Array("a", "as", "e", "o", "os", "da", _
                           "das", "de", "di", "do", "dos", _
                           "CPF", "RG", "E-Mail")
    End If
    
    MyString = " " & Application.Proper(MyString) & " " 'in case exception at start/end
    
    For Each c In exceptions
        MyString = Replace(MyString, " " & c & " ", " " & LCase(c) & " ", , , vbTextCompare)
    Next c
    
    MyProper = Trim(MyString) 'remove any added spaces

End Function

- Tim Williams

---

好的，等我回家后我会尝试。但是提前感谢你。 - Fernando F

嗨，我完全按照你发布的内容复制了，但是没有任何反应。我使用的是下面那个，但它非常“沉重”，执行时间很长（有许多“替换”）：.....> - Fernando F

子 ProperCase()Dim rnge As range For Each rnge In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells rnge.Value = StrConv(rnge.Value, vbProperCase) rnge.Value = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(rnge.Value, _ " Da ", " da "), " Das ", " das "), " De ", " de "), " Dos ", " dos "), " Do ", " do "), " A ", " a "), " O ", " o "), _ " Os ", " os "), "Cpf", "CPF"), "Rg", "RG"), "E_mail", "E_Mail"), " E ", " e ") Next rngeEnd Sub - Fernando F

这对我来说是一个函数 - 它本身并不会“做”任何事情。 - Tim Williams

Answer 2

适当的葡萄牙语

Option Explicit

Function MyProper(ByVal MyString As String) As String

    Const ExceptionsList As String _
        = "a,as,e,o,os,da,das,de,di,do,dos,CPF,RG,E-Mail"
    Dim Exceptions() As String: Exceptions = Split(ExceptionsList, ",")
    
    Dim SubStrings() As String
    SubStrings = Split(Application.Proper(MyString), " ")
    
    Dim cIndex As Variant
    Dim n As Long
    For n = 0 To UBound(SubStrings)
        cIndex = Application.Match(SubStrings(n), Exceptions, 0)
        If IsNumeric(cIndex) Then
            SubStrings(n) = Exceptions(cIndex - 1)
        End If
    Next n
    
    MyProper = Join(SubStrings, " ")

End Function


Sub MyProperAllWorksheets()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet
    Dim rg As Range
    Dim Data As Variant
    Dim rCount As Long, cCount As Long
    Dim r As Long, c As Long
    
    For Each ws In wb.Worksheets
        Set rg = ws.UsedRange
        rCount = rg.Rows.Count
        cCount = rg.Columns.Count
        If rCount > 1 Or cCount > 1 Then
            Data = rg.Value
        Else
            ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
        End If
        For r = 1 To rCount
            For c = 1 To cCount
                On Error Resume Next
                Data(r, c) = MyProper(Data(r, c))
                On Error GoTo 0
            Next c
        Next r
        rg.Value = Data
    Next ws

End Sub

- VBasic2008

---

嗨。我复制并粘贴了您发送的内容，但没有任何反应。程序甚至没有思考。 - Fernando F

过程MyProperAllWorksheets是为ThisWorkbook编写的，即包含此代码的工作簿。如果您想将其用于另一个工作簿，则应将ThisWorkbook替换为ActiveWorkbook，或者更好的方法是使用工作簿名称，例如Workbooks（“Test.xlsx”）。 - VBasic2008

非常感谢！（Thank you very much）。始终戴上口罩。愿上帝与你同在。 - Fernando F

完美，朋友！它成功了！非常非常感谢您。上帝将永远照顾你！ - Fernando F