RE: [ExcelVBA] Reg Ex in VBA?

 

You have to tick the reference to "Microsoft VBScript Regular Expressions"

In the VBA editor it is in Tools/References

Best Regards
David Grugeon
Excel VBA Group Moderator

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of Saurabh Kushwaha
Sent: Wednesday, 26 September 2012 9:38 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Reg Ex in VBA?

Hi,

I am using Reg Ex in my recent project. I have to make a validation to check
whether the figure in a particular column is a 7-digit integer. But somehow,
it is showing me an error. I am not able to Call the function of reg ex from
my subroutine. Here is the code:

Sub RegExp()

Range("J2:J15").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=IF(AND(EXACT(J2,
RegExpSubstitute(J2,""[^0-9]"","")), LEN(J2) = 7),TRUE,FALSE)"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Function RegExpSubstitute(ReplaceIn, ReplaceWhat As String, ReplaceWith As
String)

#If Not LateBind Then
Dim RE As RegExp
Set RE = New RegExp
#Else
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
#End If
RE.Pattern = ReplaceWhat
RE.Global = True
RegExpSubstitute = RE.Replace(ReplaceIn, ReplaceWith)

End Function

Please help!!!

On Tue, Sep 25, 2012 at 4:44 PM, Saurabh Kushwaha
<saurabh.rdb@gmail.com>wrote:

> Thanks Ashok!
>
>
> On Tue, Sep 25, 2012 at 3:19 PM, ashok kumar.s.v
<ashokkumarsv@yahoo.com>wrote:
>
>> **
>>
>>
>> hope this link will be useful
>>
>> http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/
>>
>> Regards,
>>
>> Ashok kumar.S.V
>>
>>
>>
>> ________________________________
>> From: Saurabh Kushwaha <saurabh.rdb@gmail.com>
>> To: ExcelVBA@yahoogroups.com
>> Sent: Tuesday, September 25, 2012 2:00 PM
>> Subject: [ExcelVBA] Reg Ex in VBA?
>>
>>
>>
>> Hi,
>>
>> Does anyone know how to use Regular Expressions in Visual Basic? Any
>> useful link or tutorial would be of great help.
>>
>> Also, how to use 'Like' operator in VBA?
>>
>> Thanks and Regards
>> --
>> Saurabh Kushwaha
>> Class of 2011, NSIT, Delhi University, India
>>
>> [Non-text portions of this message have been removed]
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>
>
>
> --
> Saurabh Kushwaha
> Class of 2011, NSIT, Delhi University, India
>
>
>

--
Saurabh Kushwaha
Class of 2011, NSIT, Delhi University, India

[Non-text portions of this message have been removed]

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___