Re: [ExcelVBA] Reg Ex in VBA?

 

Hi David,

Thanks for your reply!

I have already ticked the that reference from Tools.

Also, there is no problem with 4 quotes in my formula because if i wrote my
formula as:

(J2,"[^0-9]","")

I get a compilation error with expected end of statement as the 2nd quote
encountered marks the end of statement:

"=IF(AND(EXACT(J2, RegExpSubstitute(J2,"[^0-9]",""))

In this case, the quote encountered just after comma of 2nd J2 is taken as
End of statement by Visual Basic and you cannot write anything after that.
To overcome this problem, 4 quotes are used in this way:

"=IF(AND(EXACT(J2, RegExpSubstitute(J2,""[^0-9]"","""")), LEN(J2) =
7),TRUE,FALSE)"

So basically there is problem with the formula.

The problem is RegExpSubstitute is user-defined function and i am using
this UDF in my Data Validation. Various internet blogs say that you can't
use UDF in Data Validation. So, i was just figuring out any way to that.

I will be really thankful for any help.

Regards

On Thu, Sep 27, 2012 at 8:15 AM, David Grugeon <yahoo@grugeon.com.au> wrote:

> **
>
>
> 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
>
>
>

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

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

__._,_.___

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

Messages in this topic (9)

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

.

__,_._,___