Using Regular Expressions with VBA

Discussion in 'Microsoft Excel Programming' started by Guest, Nov 21, 2006.

  1. Guest

    Guest Guest

    I have done a fair bit of scripting with javascript and php and am learning
    VBA.

    Is it possible to use regular expressions in VBA / Excel in such a way that
    other users can open a workbook and the regular expressions will work without
    them having to download mrefunc.xll or to alter the Tools / References list
    in the VBA editor.

    Looks to me like the answer is no but thought I would ask.

    Andrew
     
    Guest, Nov 21, 2006
    #1
    1. Advertisements

  2. Guest

    NickHK Guest

    Andrew,
    If you use early binding, you can set a reference to "MS VBScript Regular
    Expression x.x". The reference is associated with the workbook so the user
    will not to change it, assuming:
    - that reference, or a later version, is available on the system
    - scripting has not been disabled, by Admin/Policy

    Then
    Dim mRegEx As RegExp

    If you mean the above may be problematic, then use late binding and trap the
    error. so no reference is set and

    Dim mRegEx As Object
    Set mRegEx = CreateObject("VBScript.RegExp")

    If mRegEx Is Nothing Then
    MsgBox "Could create RegExp object"
    End If

    NickHK
     
    NickHK, Nov 21, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    That sounds very promising, thanks, where can I read something about binding.

    Andrew
     
    Guest, Nov 21, 2006
    #3
  4. Guest

    NickHK Guest

  5. Guest

    Bob Phillips Guest

    Here is a simple example that validates email addresses.

    '-----------------------------------------------------------------
    Public Function ValidEmail(Adress As String) As Boolean
    '-----------------------------------------------------------------
    Dim oRegEx As Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    With oRegEx
    .Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
    ' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
    ValidEmail = .Test(Adress)
    End With
    Set oRegEx = Nothing
    End Function


    --

    HTH

    Bob Phillips

    (replace xxxx in the email address with gmail if mailing direct)
     
    Bob Phillips, Nov 21, 2006
    #5
  6. Guest

    Guest Guest

    Thank you both for your replies, all is working fine. It can be pretty
    frustrating working with a new language, being able to ask questions makes
    all the difference.

    Andrew
     
    Guest, Nov 21, 2006
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.