excel vba 正则 去除重复的单词

Excel VBA (Visual Basic for Applications) 中可以结合正则表达式 (RegEx) 来处理文本数据，去除重复的单词。下面是一个简单的例子：

Sub RemoveDuplicateWords()
    Dim rng As Range
    Dim regex As Object
    Dim match As Match
    Dim wordList As Variant
    
    ' 设置需要操作的范围，例如A1:C10
    Set rng = Selection ' 如果你想从当前选区开始
    
    ' 创建一个正则表达式对象
    Set regex = CreateObject("VBScript.RegExp")
    
    ' 定义要去除重复的单词的正则表达式模式，这里假设单词由空格分隔
    regex.Pattern = "\b(\w+)\b"
    
    ' 将字符串分割成数组
    wordList = rng.Value ' 获取单元格内容，转换为字符串数组
    wordList = regex.Execute(UCaseJoin(wordList)) ' 使用大写转换并合并为单个字符串，方便比较
    
    ' 创建新的数组存储去重后的结果
    Dim uniqueWords() As String
    ReDim uniqueWords(LBound(wordList) To UBound(wordList) - LBound(wordList))
    
    ' 去重
    Dim i As Long, j As Long
    For i = 0 To UBound(wordList)
        On Error Resume Next
        j = WorksheetFunction.Index(uniqueWords, WorksheetFunction.Match(wordList(i), uniqueWords, 0))
        If Err.Number <> 0 Then
            ' 如果该单词未在uniqueWords中找到，则添加
            uniqueWords(j) = wordList(i)
        End If
        Err.Clear
    Next i
    
    ' 把去重后的结果写回原范围
    rng.Value = uniqueWords
End Sub

Function UCaseJoin(strings() As String) As String
    UCaseJoin = Join(Application.Transpose(strings), " ")
End Function