Excel VBA - RegExp specification advise

Question

I created the following REGEX to markup an Excel file with certain scripting tags (^Joe Bloggs+JoeBloggs^).

However, the problem is that I am not knowledgeable enough to change the VBA script below so that the user can specify which particular Excel column to parse.

At the moment, the code below is restricted to running on Column A:A only, and I would like to be able to run it for Column D:D, or as per the user input.

Sub RegExp_1()
  Dim objRegex
  Dim RegMC
  Dim RegM
  Set objRegex = CreateObject("vbscript.regexp")

With CreateObject("VBScript.RegExp")
    .MultiLine = True
    .Global = True
    .IgnoreCase = True
    .Pattern = "\^[\w\W\s]*\^|<[\W]{2,3}\^[\w\W\s]*\^[\W]{1,3}\>"

  For i = 1 To 10000

     If .test(Cells(i, 1).Value) Then
          Set RegMC = .Execute(Cells(i, 1).Value)
          For Each RegM In RegMC
             Cells(i, 1).Characters(RegM.firstindex, RegM.Length + 1).Font.FontStyle = "Bold"
             Cells(i, 1).Characters(RegM.firstindex, RegM.Length + 1).Font.Size = 12
             Cells(i, 1).Characters(RegM.firstindex, RegM.Length + 1).Font.ColorIndex = 5
          Next

      End If
   Next i
 End With

 Call RegExp_2
 Call RegExp_3
 Call RegExp_4

 MsgBox "Scripting tags have now been marked-up!"

 End Sub

Thanks.

I'm no VBA expert either, but won't changing `Cells(i, 1)` to something like `Cells(i, n)` do the trick? — Kilazur, May 27 '14 at 12:23

score 1 · Answer 1 · edited May 23 '17 at 12:23

To run this on Column D, you just need to change every instance of Cells(i,1) to Cells(i,4). The first parameter in Cells represents the row number, the second parameter represents the column. MSDN Worksheet.Cells Property

Changing this to work on any given column is a bit more involved, but not terribly so. Let's assume the user runs your sub when they click a button and you want it to run on whatever they currently have selected. (I'm kind of excited that there's a legitimate reason to use the Selection object. You should normally Avoid using select.)

I'm not sure what your regex is matching, so I was unable to test this beyond compiling. It should at least give you the idea.

Sub RegExp_1()
    Dim objRegex
    Dim RegMC
    Dim RegM
    Dim i As Long 'counter
    Dim rng As Range 'store the current selection

    Set rng = Selection
    Set objRegex = CreateObject("vbscript.regexp")

    With CreateObject("VBScript.RegExp")
        .MultiLine = True
        .Global = True
        .IgnoreCase = True
        .Pattern = "\^[\w\W\s]*\^|<[\W]{2,3}\^[\w\W\s]*\^[\W]{1,3}\>"
        For i = 1 To 10000 ' should change this to last row
            If .test(rng.Value) Then
            Set RegMC = .Execute(rng.Value)
                For Each RegM In RegMC
                    rng.Characters(RegM.firstindex, RegM.Length + 1).Font.FontStyle = "Bold"
                    rng.Characters(RegM.firstindex, RegM.Length + 1).Font.Size = 12
                    rng.Characters(RegM.firstindex, RegM.Length + 1).Font.ColorIndex = 5
                Next
            End If
        Next i
    End With

    'Call RegExp_2
    'Call RegExp_3
    'Call RegExp_4

    MsgBox "Scripting tags have now been marked-up!"
 End Sub

It's kind of rude to edit an answer with your comments. I rejected the edit. Please comment in the comments. — RubberDuck, May 27 '14 at 14:36
Thanks to everyone for their input so far. I tried the revised code, but it only works for one selected cell in another column. — user2034450, May 27 '14 at 14:38
Sorry mean to say the REGEX will match: "^whatever texts is enclosed^" — user2034450, May 27 '14 at 15:42

Excel VBA - RegExp specification advise

1 Answers1