Use Regex to update VBA code

Question

I have a VBA source code containing many hard coded references to cells. The code is part of the Worksheet_Change sub, so I guess hard coding the range references was necessary and you will see many assignment statements like the following:

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

I would like insert 2 additional rows on top of the worksheet, so basically all the row references will shift by 2 rows. So for example the above assignment statement will be changed to Set cell = Range("B9").

Given the large number of hard coded row references in the code, I thought of using Regex to increment all the row references by 2. So I have developed the following code.

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

The above VBA code works, except it fails if there are two row references in the same line, so for instance if we have If Range("B15").Value <> Range("B12").Value Then, after the line gets processed I get If Range("B14").Value <> Range("B14").Value Theninstead of If Range("B17").Value <> Range("B14").Value Then. The problem is in the vStr(i) = rex.Replace(vStr(i), mtch.SubMatches(0) & IncrementString(mtch.SubMatches(1)) & mtch.SubMatches(2)) statement, because it is getting called more than once if a line has more than Regex match.

Any ideas? Thanks in advance

aucuparia · Accepted Answer · 2014-12-17 21:11:01Z

2

I think what you are trying to do is a bad idea, for two reasons:

Hard-coded cell references are almost always poor practice. A better solution may be to replace hard-coded cell references with named ranges. You can refer to them in the code by name, and the associated references will update automatically if you insert/delete rows or columns. You have some painful upfront work to do but the result will be a much more maintainable spreadsheet.
You are effectively trying to write a VBA parser using regexes. This is pretty much guaranteed not to work in all cases. Your current regex will match lots of things that aren't cell references (e.g. "123", "_12", and "A00") and will also miss lots of hard-coded cell references (e.g. "A1" and Cell(3,7)). That may not matter for your particular code but the only way to be sure it's worked is to check each reference by hand. Which is IMHO not much less effort than refactoring (e.g. replace with named ranges). In my experience you don't fix a regex, you just make the problems more subtle.

That said, since you asked...

<cthulu>

There are only two choices when using RegExp.Replace() - either replace the first match or replace all matches (corresponding to setting RegExp.Global to False or True respectively). You don't have any finer control than that, so your logic has to change. Instead of using Replace() you could write your own code for the replacements, using the FirstIndex property of the Match object, and VBA's string functions to isolate the relevant parts of the string:

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

Note that I still haven't fixed the problems with the regex pattern in the first place. I recommend that you just go and use named ranges instead...

Oops, nearly forgot - </cth

answered Dec 17, 2014 at 21:11

aucuparia

2,05121 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aucuparia Over a year ago

Too ḽ̬̱̫̲̫aͪt̗̜͉͓̬̖͈̽ẻ͕͇̬͉̮!̜̟͍ͬ T̻̙ͤö̱̞̫̟̯͔́͋ͧͫ̉͊̌ͬ̌o̗̼͎̭͓̙̖̺ͮͬ l̵̛͍͓̻̭̭̭͙̯ͬ̂̔̓̋ͦ̓̃̿̈́ͪͩ̉̇̓ͩ́͜ȃ̶̧͐̏̇ͫͥ̄̀͏̳̱͙͍t̵̹̹̙̻̜̥͙͙͙̠͇̜̹͕͚̝͔͔̂͆̒̉͒͐͐͛̒̽̾̊ͯ̇̎̔͒́̚̚ę̵̜̝̯͕̲͚͕̦͆̔̿͛̏̓̋̐ͣͦ̎ͥ̾͐ͬ̊!̨̧̝̹̩̳̞̅̓͊̾ͨͥ̄ͤ̂̈́̾͝

Jeanno Over a year ago

Thanks for your answer! I do realize that Name ranges is the way to go. I did not write the VBA code originally. The spreadsheet is used as form to standardize data entry, there is at least 100 rows being standardized, and tons of VBA. It will be a considerable amount to rewrite using named ranges, but I do agree it is the way to go. Thanks for the elegant solution provided, I haven't done string manipulation in a while, it is a good refresher. the rex pattern is a problem in the general case, but does work in my case since standardization starts at the 12th row. Thanks again.

Collectives™ on Stack Overflow

Use Regex to update VBA code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related