使用正则表达式更新 VBA 代码

Question

我有一个VBA源代码，其中包含许多对单元格的硬编码引用。该代码是 Worksheet_Change sub 的一部分，所以我想对范围引用进行硬编码是必要的，您将看到许多赋值语句，如下所示：

Set cell = Range("B7")
If Not Application.Intersect(cell, Range(Target.Address)) Is Nothing Then

我想在工作表顶部插入另外 2 行，因此基本上所有行引用都将移动 2 行。因此，例如，上面的赋值语句将更改为 Set cell = Range("B9") 。

鉴于代码中有大量硬编码的行引用，我想使用 Regex 将所有行引用增加 2。所以我开发了以下代码。

Sub UpdateVBACode()
    '*********************Read Text File Containing VBA code and assign content to string variable*************************
    Dim str As String
    Dim strFile As String: strFile = "F:Preprocessed_code.txt"
    Open strFile For Input As #1
    str = Input$(LOF(1), 1)
    Close #1
    '*********************Split string variables to lines******************************************************************
    Dim vStr As Variant: vStr = Split(str, vbCrLf)
    '*********************Regex work***************************************************************************************
    Dim rex As New RegExp
    rex.Global = True
    Dim i As Long
    Dim mtch As Object
    rex.Pattern = "(""w)([0-9][0-9])("")" ' 3 capturing groups to reconstruct the replacement string
    For i = 0 To UBound(vStr, 1)
        If rex.Test(vStr(i)) Then
            For Each mtch In rex.Execute(vStr(i))
                vStr(i) = rex.Replace(vStr(i), mtch.SubMatches(0) & IncrementString(mtch.SubMatches(1)) & mtch.SubMatches(2))
            Next
        End If
    Next i
    '********************Reconstruct String*********************************************************************************
    str = ""
    For i = 0 To UBound(vStr, 1)
        str = str & vbCrLf & vStr(i)
    Next i
    '********************Write string to text file******************************************************************************
    Dim myFile As String
    myFile = "F:Processed_code.txt"
    Open myFile For Output As #2
    Print #2, str
    Close #2
    '
End Sub
Function IncrementString(rowNum As String) As String '
    Dim num As Integer
    num = CInt(rowNum) + 2
    IncrementString = CStr(num)
End Function

上面的 VBA 代码有效，但如果同一行中有两个行引用，它会失败，因此例如，如果我们有 If Range("B15").Value <> Range("B12").Value Then ，在该行处理后，我会得到If Range("B14").Value <> Range("B14").Value Then而不是If Range("B17").Value <> Range("B14").Value Then。问题出在 vStr(i) = rex.Replace(vStr(i), mtch.SubMatches(0) & IncrementString(mtch.SubMatches(1)) & mtch.SubMatches(2)) 语句中，因为如果一行有多个正则表达式匹配项，则会多次调用它。

有什么想法吗？提前致谢

Answer 1

我认为您要做的是一个坏主意，原因有两个：

1. 硬编码的单元格引用几乎总是不好的做法。 更好的解决方案可能是将硬编码的单元格引用替换为命名区域。您可以在代码中按名称引用它们，如果您插入/删除行或列，关联的引用将自动更新。您有一些痛苦的前期工作要做，但结果将是一个更易于维护的电子表格。
2. 您正在有效地尝试使用正则表达式编写 VBA 解析器。 这几乎可以保证在所有情况下都不起作用。 您当前的正则表达式将匹配许多不是单元格引用的内容(例如 "123"、"_12" 和 "A00" (，并且还会遗漏大量硬编码的单元格引用(例如 "A1"和Cell(3,7)(。这对于您的特定代码可能无关紧要，但确保它正常工作的唯一方法是手动检查每个引用。恕我直言，这并不比重构少多少(例如用命名范围替换(的工作量。 根据我的经验，你不会修复正则表达式，你只是让问题更加微妙。

也就是说，既然你问了...

<cthulu>

使用RegExp.Replace()时只有两种选择 - 替换第一个匹配项或替换所有匹配项(对应于分别将RegExp.Global设置为 False 或 True(。 没有比这更好的控制了，所以你的逻辑必须改变。 您可以使用 Match 对象的 FirstIndex 属性和 VBA 的字符串函数来隔离字符串的相关部分，而不是使用 Replace() 编写自己的替换代码：

Dim rex As Object
Set rex = CreateObject("VBScript.RegExp")
rex.Global = True
Dim i As Long
Dim mtch As Object
Dim newLineText As String
Dim currMatchIndex As Long, prevPosition As Long
rex.Pattern = "(""w)([0-9][0-9])("")" ' 3 capturing groups to reconstruct the replacement string
For i = 0 To UBound(vStr, 1)
    If rex.Test(vStr(i)) Then
        currMatchIndex = 0: prevPosition = 1
        newLineText = ""
        For Each mtch In rex.Execute(vStr(i))
            'Note that VBA string functions are indexed from 1 but Match.FirstIndex starts from 0
            currMatchIndex = mtch.FirstIndex
            newLineText = newLineText & Mid(vStr(i), prevPosition, currMatchIndex - prevPosition + 1) & _
                    mtch.SubMatches(0) & IncrementString(mtch.SubMatches(1)) & mtch.SubMatches(2)
            prevPosition = currMatchIndex + Len(mtch.Value) + 1
        Next
        vStr(i) = newLineText & Right(vStr(i), Len(vStr(i)) - prevPosition + 1)
    End If
Next i

请注意，我仍然没有首先解决正则表达式模式的问题。 我建议你直接使用命名范围......

哎呀，差点忘了——</cth