excel - 如何在 Excel VBA 中使用 LIKE 运算符突出显示子字符串

Question

我有看起来像这样的字符串:

DTTGGRKDVVNHCGKKYKDK
RKDVVNHCGKKYKDKSKRAR

我想要做的是用粗体和红色字体突出显示该区域。
结果如下:



我在 Excel VBA 中使用 LIKE 运算符尝试了以下代码，但它中断了
在此行 Set MC = .Execute(C.Text)

Option Explicit
Sub boldSubString()
    Dim R As Range, C As Range
    Dim MC As Object    

    Set R = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))

    For Each C In R
        C.Font.Bold = False
        If C.Text Like "KK*K" Or C.Text Like "KR*R"  Then
            Set MC = .Execute(C.Text)
            C.Characters(MC(0).firstindex + 1, MC(0).Length).Font.Bold = True
        End If
    Next C    

End Sub

正确的方法是什么？
我正在使用 Mac Excel 版本 15.31

Answer 1

如果没有正则表达式，您可以尝试以下操作。我没有对它进行广泛的测试，但即使在同一个字符串中有多个匹配的子字符串，它似乎也可以工作。

检查正在使用的函数的 VBA HELP，以便了解其工作原理，以及如何构造与 Like 一起使用的正确模式。运算符，以防您需要扩展可能的模式列表。

Option Explicit
Sub boldSS()
    Dim WS As Worksheet
    Dim R As Range, C As Range
    Dim sPatterns(1) As String
    Dim I As Long, J As Long

sPatterns(0) = "KR?R"
sPatterns(1) = "KK?K"

Set WS = Worksheets("sheet1")
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each C In R

        'Reset to default
        With C.Font
            .Bold = False
            .Color = vbBlack
        End With

    For I = 0 To UBound(sPatterns)
        If C Like "*" & sPatterns(I) & "*" Then
            For J = 1 To Len(C) - Len(sPatterns(I)) + 1
                If Mid(C, J, Len(sPatterns(I))) Like sPatterns(I) Then
                    With C.Characters(J, Len(sPatterns(I))).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                    If J < Len(C) - 3 Then
                        J = J + 3
                    Else
                        Exit For
                    End If
                End If
            Next J
        End If
    Next I
Next C
End Sub

将您的正则表达式模式等效用于 Like运算符，您可以将上面的内容重写如下。请注意，您的正则表达式模式也将匹配 KKAR , 和 KRAK (与下面的宏一样，但不是上面的宏)。

Option Explicit
Sub boldSS()
    Dim WS As Worksheet
    Dim R As Range, C As Range
    Dim sPattern As String
    Dim I As Long

sPattern = "K[KR]?[KR]"

Set WS = Worksheets("sheet1")
With WS
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each C In R
        With C.Font
            .Bold = False
            .Color = vbBlack
        End With
        If C Like "*" & sPattern & "*" Then
            For I = 1 To Len(C) - 4 + 1
                If Mid(C, I, 4) Like sPattern Then
                    With C.Characters(I, 4).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                    If I < Len(C) - 3 Then
                        I = I + 3
                    Else
                        Exit For
                    End If
                End If
            Next I
        End If
Next C
End Sub