vba - 如何将 VBA 代码中的单词列入黑名单

Question

我有以下脚本 matches any word in a cell to any other word in a range of cells 。但是，我希望代码省略某些常见术语，例如“和、或、大、小”。

我不太擅长 VBA，所以我希望有人能够告诉我将其添加到代码中的位置:

Sub FindSimilar()
Dim phrases As Range, phrase As Range
Dim terms As Range, term As Range
Dim matches As String
Dim words() As String

'ensure this has the correct sheet names for your workbook
Set phrases = ThisWorkbook.Worksheets("Export").Range("B2:B2000")
Set terms = ThisWorkbook.Worksheets("Topics").Range("D100:D3000")

For Each term In terms
    matches = ""
    words() = Split(term.Value)

    For i = 0 To UBound(words, 1)
        For Each phrase In phrases
            If InStr(1, phrase.Value, words(i)) Then
                matches = matches & phrase & "/"
            End If
        Next phrase
    Next i

    If matches <> vbNullString Then
        term.Offset(0, 6).Value = Left(matches, Len(matches) - 1)
    Else
        term.Offset(0, 6).Value = "No match"
    End If
Next term
End Sub

如何将代码中的术语列入黑名单？非常感谢您的积极投入和支持。

Answer 1

如果我正确理解您想要的内容，您可以在 For Each 循环 中放置一个 Select Case 语句

For i = 0 To UBound(words, 1)
    Select Case words(i)
        Case = "and","or","big","small","whatever else you want to add"
        Case Else
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
    End Select
Next i

但是，如果有大量术语，这会变得很麻烦。

如果有大量术语，您可以将列入黑名单的术语存储在工作表上，然后执行如下操作:

For i = 0 To UBound(words, 1)
    If ThisWorkbook.Worksheets("Topics").Range("BlackList").Find(words(i),lookat:=xlWhole) is Nothing Then 
            For Each phrase In phrases
                If InStr(1, phrase.Value, words(i)) Then
                    matches = matches & phrase & "/"
                End If
            Next phrase
    End If
Next i

假设 BlackList 是一个命名范围。如果不是，只需替换为 A1:A100 或任何范围。