Re: [ExcelVBA] Reg Ex in VBA?

Okay.

Any clue about how to make UDFs work with Data Validation?

Regards

On Fri, Sep 28, 2012 at 3:03 PM, Derek Turner <g4swy@yahoo.com> wrote:

> **
>
>
> Hi
>
> In :-
>
> sFormula
> = Replace("=IF(AND(EXACT(J2,RegExpSubstitute(J2,¬[^0-9]¬,¬¬)),LEN(J2)=1),TRUE,FALSE)",
> "¬", QUOTE)
>
> the actual formula is enclosed by Replace( ... ,"¬", QUOTE) so any single
> ¬ (tilde) character inside the expression may be thought of as a single
> quote character, thus you don't have to worry about getting ", "", """ or
> """" right.
>
> Here is something less complex :-
>
> sFormula = "=IF(AND(value(j2)=abs(int(value(j2))),LEN(j2)=7 ),TRUE,FALSE)"
>
> To make it work with leading zeros you have to format the range as Text.
>
> (Abs removes minus sign and Int truncates decimals, Value tries to convert
> text to a number)
>
> Regards
>
> Derek +++
>
> >________________________________
>
> > From: Saurabh Kushwaha <saurabh.rdb@gmail.com>
> >To: ExcelVBA@yahoogroups.com
> >Sent: Thursday, 27 September 2012, 14:03
>
> >Subject: 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
> >
> >
> >
> >
> >
> >
>
> [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/