Excel VBA - 替换非英文字符

Question

我目前将一些地址从一个 Excel 工作表映射到另一个 Excel 工作表，这需要我执行特定的操作，例如更改列顺序、删除重复行以及将页面剪切为 100 行工作表...我还需要替换非标准字符及其最接近的英语等效字符。

我有一些代码来完成此任务，它工作正常，但速度非常慢，如下所示:

Sub ReplaceCharacters()
'Replaces special characters for English
Dim rCell As Range
    For Each rCell In ActiveSheet.Range("A1:H10000").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        With rCell
            .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Å", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "å", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ó", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ý", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ý", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Þ", "Th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "þ", "th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Æ", "AE")
            .Value = Application.WorksheetFunction.Substitute(.Value, "æ", "ae")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ø", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ø", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ä", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ä", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "À", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "à", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "È", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "è", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ì", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ì", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ò", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ò", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ù", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ù", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ç", "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ç", "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Â", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "â", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ê", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ê", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Î", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "î", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ô", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ô", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Û", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "û", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ñ", "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ñ", "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ã", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ã", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ë", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ë", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ï", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ï", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ö", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ú", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ü", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ÿ", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ÿ", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ß", "ss")
            .Value = Application.WorksheetFunction.Substitute(.Value, "œ", "oe")
        End With
    Next rCell
End Sub

我想知道是否有一种更快的方法来实现将非标准字符与其英语等效字符交换的相同最终结果？

此外，我似乎无法理解如何要求代码对所有事件行进行采样，因此我需要高估行数:ActiveSheet.Range("A1:H10000").SpecialCells(xlCellTypeConstants, xlTextValues).Cells

有什么想法如何让它更改工作表上的所有事件行吗？

非常感谢，我对此很陌生:D

Answer 1

这会更快:

Sub ReplaceCharacters()

  Application.ScreenUpdating = False

  With ActiveSheet.Range("A1:H10000").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
      .Replace What:="Á", Replacement:="A", LookAt:=xlPart
      .Replace What:="Å", Replacement:="A", LookAt:=xlPart
  End With

  Application.ScreenUpdating = True

End Sub

屏幕更新已关闭，从而避免了所有闪烁。此外，代码不会循环遍历所有单元格，而是更改整个范围。