Re: [ExcelVBA] Reg Ex in VBA?

 

In short, please tell me how to use Data Validation with a User-Defined
function?

On Wed, Sep 26, 2012 at 5:07 PM, Saurabh Kushwaha <saurabh.rdb@gmail.com>wrote:

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

--
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 (4)

Recent Activity:

• New Members 2

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

.

__,_._,___