Regular Expressions in VBA

  • 14 years ago

    So I have regular expressions working in my VBA code.  For the example input below I would like to match the following:

    Input - FNB 3.875 2/10  30 /296 ( 5/10)    5YR \End Input    What I would like matched 2 and 10 and 5 from 5YR  
    Input - MCR 4.50  1/15  376/36  ( 5/5 )    \End Input     10Y  What I would like matched 376 and 36 and 10 from 10YR  
    Input - FNB 5.00  4/15  396/39  ( 5/10)    \End Input    10Y  What I would like matched 396 and 39 and 10 from 10YR  

    This is what I currently have:

    Dim objRegExp As RegExp
    Dim objMatch As Match
    Dim colMatches As MatchCollection
    Dim RetStr As String
    Dim myarray
    Dim Txt As String
    RegExp = " "

    'Pattern for Bid/Offer

    mypattern = "\w+|w/d|d+\b (d|s|d+s/d+)|(s|d|d+)')'\B\b(d+|d)Y|YR"

    'Create a regular expression object.
      Set objRegExp = New RegExp

    'Set the pattern by using the Pattern property.
      objRegExp.Pattern = mypattern

    'Set Case Insensitivity.
      objRegExp.IgnoreCase = True

    'Set global applicability.
      objRegExp.Global = True

    'Test whether the String can be compared.
      If (objRegExp.Test(Lkp) = True) Then
    Set colMatches = objRegExp.Execute(Lkp)   ' Execute search.

    Set oMatch = colMatches(0)
     ' Create the results string.
     RetStr = RetStr & oMatch.SubMatches(0)
     RetStr = RetStr & oMatch.SubMatches(1)
     SubMatchTest = RetStr
       MsgBox SubMatchTest
    End If

    Nothing Appears in the Return String!

  • 14 years ago

    Have you tried using an online regex tester like the following?

    This will let you experiment with different inputs and regex strings, so you should be able to break down the pattern and figure out what's going on? Just having a quick look at your string, for one thing you can replace things like


    with just (d+)  as the + sign means "one or more"

  • 14 years ago

    good call....wil give that a shot

  • 14 years ago
    The real issue boils down to coming up with a regular expression for this pattern.


    FNB 3.875 2/10  30/296 ( 5/10)    5YR
    MCR 4.50  1/15  376/36  ( 5/5 )   10YR
    FNB 5.00  4/15  396/39  ( 5/10)   10YR
    CMC 3.75  8/07     /    (  /      2YR
    FNB 7.125 1/30     /45  (  /5 )    30Y
    HLG 2.625 O/06  11 /    ( 5/      2YR
    HLG 3.625 1/08  25 /246 ( 5/5 )    3YR

    Desired output:

    25,246,3 (notice the space between 25 and the /)

    The current regular expression I have is:

    [A-Z]{3}\s[\d+\.]+\s+\w+/\d+\s+(\d+)/(\d+)\s+[ \(]+\d+/\d+[\)]\s+(\d+)Y|YR

    This will not cover all the cases.  Let me know if you guys have any thoughts
  • 14 years ago

    Rather than using regular expressions, you could use this VB to extract the values:


    Dim strTest As String
    Dim strRes1 As String
    Dim arrBits() As String
    Dim arrOne() As String
    Dim arrTwo() As String
    Dim arrThree() As String
    Dim arrFour() As String

    strTest = "HLG 3.625 1/08  25 /246 ( 5/5 )    3YR "
    arrBits() = Split(strTest, "/")
    arrOne() = Split(arrBits(1))
    strRes1 = Trim(arrOne(2)) & ","
    arrTwo() = Split(arrBits(2))
    strRes1 = strRes1 & Trim(arrTwo(0)) & ","
    arrThree() = Split(arrBits(3), ")")
    arrFour() = Split(arrThree(1), "Y")
    strRes1 = strRes1 & Trim(arrFour(0))

    Not the most elegant piece of code, but it returns the correct results from the different test strings you have posted!

  • 13 years ago


    Not sure if you solved the problem yet, but here's what you need:

    s/^[^\/]\/[^ ] ([0-9]) \/ *([0-9])[^(](. ([0-9])[Yy][Rr]$/$1,$2,$3/
    Note:   \/ is an escaped forward slash (backslash then a forward slash)
    I had to put the \ in so I could could test the expression in perl.
    I originally wrote it in sed, so it's pass 2 tests.

    If you want a detailed description (as it is quite obscure) the let me know.


Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The most exciting phrase to hear in science, the one that heralds new discoveries, is not 'Eureka!' but 'That's funny...'” - Isaac Asimov