Re: [ExcelVBA] Reg Ex in VBA?

Hi Derek,

Even i am not able to work with this:

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=xlEqual, Formula1:="=a1"

Also, I have to use excel as least as possible.

And i can use a 7-digit number with leading zeros.

One question from my side - What is the use of '¬' operator?

Also, please let me know if you come across any solution.

Many Thanks!


On Thu, Sep 27, 2012 at 2:47 PM, Derek Turner <g4swy@yahoo.com> wrote:

> **
>
>
> It's Microsoft Scripting Runtime in Tools, References.
>
> However.
>
> Having spent a considerable amount of time on this I am thinking that
> custom validation cannot accept User Defined Functions.
>
> See http://support.microsoft.com/kb/160523 which applies to XL97 but the
> symptoms are the same.
>
> They provide this workaround which adds yet more complexity.
> "To work around this behavior, type the custom function in a cell on the
> worksheet, and then type a formula that refers to this cell in the Formula
> box. For example, type the custom function in cell A1, and then type "=A1"
> (without the quotation marks) in the Formula box in the Data Validation
> dialog box."
>
> I used this code to slightly de-mystify :-
> Const QUOTE = """"
>
> sFormula =
> Replace("=IF(AND(EXACT(J2,RegExpSubstitute(J2,¬[^0-9]¬,¬¬)),LEN(J2)=1
> ),TRUE,FALSE)", "¬", QUOTE)
> Debug.Print sFormula
> Cells(1, 1).Formula = sFormula ' fails here if formula bad
> then
> .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
> Operator:=xlEqual, Formula1:=sFormula ' this fails with UDFs
>
> So the solution may be to replace the Regex UDF call with in-line code.
>
> Or try the workaround (Something like this - I could not make it work) :-
> .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
> Operator:=xlEqual, Formula1:="=a1"
>
> Question - Can the 7 digit number have leading zeros ?
>
> Regards
>
> Derek +++
>
> >________________________________
> > From: David Grugeon <yahoo@grugeon.com.au>
> >To: ExcelVBA@yahoogroups.com
> >Sent: Thursday, 27 September 2012, 3:45
> >Subject: 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
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
>
>



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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/