string - VBA 函数排除字符串的某些部分

Question

我的子程序比较两个字符串列表并返回最接近的匹配项。我发现潜艇会被一些常见词绊倒，例如“the”和“facility”。我想编写一个函数，该函数将提供一个单词数组来排除和检查每个字符串中是否有这些单词，如果找到则将其排除。

这是一个示例输入:

|aNames        |  bNames        | words to exclude
|thehillcrest  |oceanview health| the
|oceanview, the|hillCrest       | health

预期输出:

|aResults     |bResuts
|hillcrest    |hillcrest
|oceanview    |oceanview

到目前为止我已经:

Dim ub as Integer
Dim excludeWords() As String

'First grab the words to be excluded
If sheet.Cells(2, 7).Value <> "" Then
  For y = 2 To sheet.Range("G:G").End(xlDown).Row
    ub = UBound(excludeWords) + 1             'I'm getting a subscript out of range error here..?
    ReDim Preserve excludeWords(0 To ub)
    excludeWords(ub) = sheet.Cells(y, 7).Value
  Next y
End If

然后我的比较函数使用双循环，将 A 列中的每个字符串与 B 列进行比较。在比较之前，a 列和 b 列中的值将通过我们的函数，该函数将检查要排除的这些单词。可能没有要排除的单词，因此该参数应该是可选的:

Public Function normalizeString(s As String, ParamArray a() As Variant)
  if a(0) then           'How can I check?
    for i = 0 to UBound(a)
      s = Replace(s, a(i))
    next i
  end if
  normalizeString = Trim(LCase(s))
End Function

这段代码中可能有一些部分无法工作。你能指出我正确的方向吗？

谢谢!

Answer 1

要将列表存储在数组中，可以这样做

Sub Sample()
    Dim excludeWords As Variant
    Dim lRow As Long

    With Sheet1 '<~~ Change this to the relevant sheet
        '~~> Get last row in Col G
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        excludeWords = .Range("G2:G" & lRow).Value

        'Debug.Print UBound(excludeWords)

        'For i = LBound(excludeWords) To UBound(excludeWords)
            'Debug.Print excludeWords(i, 1)
        'Next i
    End With
End Sub

然后将数组传递给您的函数。上面的数组是一个二维数组，因此需要进行相应的处理(请参阅上面代码中的注释部分)

就像我在上面的评论中提到的

How does oceanview, the become Oceanview? You can replace the but that would give you oceanview, (notice the comma) and not Oceanview.

您可能必须将这些特殊字符传递给工作表中的 Col G，或者您可以使用循环在函数中处理它们。为此，您必须使用 ASCII 字符。请参阅this

评论跟进

这是我很快写的东西，因此没有经过广泛的测试。这是您要找的吗？

Sub Sample()
    Dim excludeWords As Variant
    Dim lRow As Long

    With Sheet1
        lRow = .Range("G" & .Rows.Count).End(xlUp).Row

        excludeWords = .Range("G2:G" & lRow).Value

        '~~> My column G has the word "habilitation" and "this"
        Debug.Print normalizeString("This is rehabilitation", excludeWords)

        '~~> Output is "is rehabilitation"
    End With
End Sub

Public Function normalizeString(s As String, a As Variant) As String
    Dim i As Long, j As Long
    Dim tmpAr As Variant

    If InStr(1, s, " ") Then
        tmpAr = Split(s, " ")

        For i = LBound(a) To UBound(a)
            For j = LBound(tmpAr) To UBound(tmpAr)
                If LCase(Trim(tmpAr(j))) = LCase(Trim(a(i, 1))) Then tmpAr(j) = ""
            Next j
        Next i
        s = Join(tmpAr, " ")
    Else
        For i = LBound(a) To UBound(a)
            If LCase(Trim(s)) = LCase(Trim(a(i, 1))) Then
                s = ""
                Exit For
            End If
        Next i
    End If

    normalizeString = Trim(LCase(s))
End Function