• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Understanding RegExp

Hi all,

I have been picking apart an Excel file with a lot of code in it, in an attempt to recreate the file and understand the vba behind it. It seems the public functions are all being initialized with the same structure, declaring a variable as a "RegExp" which I believe stands for regular expression, but the content I have found in my research has left me very confused. Can someone explain to me (or point me in the right direction) what RegExp does and if there is a better way to rewrite the following code?

Thanks!

>>The function below is used to check whether a field is alphanumeric or not using regular expression
>>If format matches then returns true else returns false

Code:
Public Function CheckAlphaNumericPart1(inputData As String) As Boolean

    Dim objRegExp As RegExp
    Dim blnValid As Boolean
    
    Set objRegExp = New RegExp
    
    With objRegExp
    .Pattern = "^[0-9]+[a-zA-Z]+[a-zA-Z0-9]*$"
    .IgnoreCase = True
    .Global = True
    End With
    
    blnValid = objRegExp.test(inputData)
       Set objRegExp = Nothing
       CheckAlphaNumericPart1 = blnValid

End Function
 
After first finding that RegExp is not included in the default set of references, I found this document. Quite helpful, IMO.
 
Hi Jeffrey ,

I doubt that the function whose code you have posted can be rewritten in any simpler manner ; all it does is set the essential parameters for testing , and then test ! What ever is the result of the test is returned ; how much simpler can it get ?

To add to what Luke has already posted , you might find these links helpful :

1. http://www.mcpher.com/Home/excelquirks/regular-expressions

2. http://nielsbosma.se/2011/03/06/working-with-regular-expressions-in-excel/

If you are really looking for an alternative to regular expressions , see if this helps :

http://searchengineland.com/advanced-filters-excels-amazing-alternative-to-regex-143680

Narayan
 
Back
Top