Regular Expressions in VBA

  • 13 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!

  • 13 years ago

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


    http://www.regexlib.com/RETester.aspx


    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


    (d+|d)


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

  • 13 years ago

    good call....wil give that a shot

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

    Input:

    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:

    30,296,5
    376,36,10
    396,39,10
    ,,2
    ,45,30
    11,,2
    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
  • 13 years ago

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


    Code:

    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

    Hello,


    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.





    Steve

Post a reply

Enter your message below

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

Contribute

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.

“C++ : Where friends have access to your private members.” - Gavin Russell Baker