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]
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
----------------------------------
Tidak ada komentar:
Posting Komentar