Re: [ExcelVBA] Reg Ex in VBA?

 

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]

__._,_.___

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

Messages in this topic (8)

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

.

__,_._,___