Use regex to enforce cell validation?

Question

Using Excel 2010. I want to only allow values in a cell that fit a given regex pattern. So I created a UDF module as follows:

Public re as RegExp

Public Function isValidRegex(rng As Range, pattern As String) As Boolean

If re Is Nothing Then
    Set re = New RegExp
End If

re.pattern = pattern

isValidRegex = re.Test(rng.value)

End Function

I created a named range called THIS_CELL, so that the current cell can be passed to isValidRegex(), as follows:

=INDIRECT(ADDRESS(ROW(),COLUMN()))

I set a custom validation for the cell, using this formula:

=isValidRegex(THIS_CELL,"(my|regex)patt[ern]")

This generated the following error:

A named range you specified cannot be found.

According to this article, UDFs cannot be used in Custom validation formulas. The solution suggested in the article (putting the formula in another cell, making that cell into a named range, and referencing that cell in the Custom formula) won't work, because I need to be able to pass THIS_CELL as an argument to the function.

I also tried creating a named range called isValidRegexPattern, defining it as =isValidRegex(THIS_CELL,"(my|regex)patt[ern]"), and setting the Custom formula to =isValidRegexPattern, but this didn't work either; putting a breakpoint in isValidRegex() showed that the function wasn't even being called.

So, how can I use a UDF for cell validation?

@brettdj, attempting to enter invalid data should result in restoration of whatever was in the cell before the attempted change. Which AFAIK is impossible using worksheet events; say I copy A1:A5 and paste into B1. Worksheet_SelectionChange will catch the pre-change value of B1, but not B2:B5, and because it's a VBA call, Undo won't retrieve the previous values. Or am I missing something? — sigil
– sigil, Commented Mar 8, 2015 at 8:10
you should use the Worksheet_Change event that would work for you — Jeanno
– Jeanno, Commented Mar 8, 2015 at 8:25
Also do you really need to use RegEx, is your pattern so complicated that you can't use the Like operator — Jeanno
– Jeanno, Commented Mar 8, 2015 at 8:26
@jeanno, Worksheet_Change doesn't capture the pre-change state of the cell, as I said in my comment to brettdj. As for RegEx complexity, there are several patterns that involve repeated capturing groups, e.g. "^(\d{3}(,|$))+". These are somewhat complicated to express with Like, and would probably each require their own VBA string-processing algorithm if we don't use Regex. — sigil
– sigil, Commented Mar 8, 2015 at 8:50

brettdj · Accepted Answer · 2015-03-08 09:25:44Z

You can use a static variable with the Worksheet_Change event to keep a snapshot of the prior values

The code below tracks the values in A1:A10 and uses a Regexp like yours to reject any non-numeric entries

The example below tries top copy and paste B1:B10 over A1:A10, only A6and A8 are allowed as they are numeric

to set the range initially change a cell outside the range of interest to trigger If IsEmpty(X) Then X = [a1:a10].Value2

enter image description here

change event

Private Sub Worksheet_Change(ByVal Target As Range)

Static X As Variant
Dim rng2 As Range
Dim rng3 As Range

If IsEmpty(X) Then X = [a1:a10].Value2

Set rng2 = Intersect([a1:a10], Target)
If rng2 Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each rng3 In rng2
    If Not isValidRegex(rng3, "\d+") Then rng3.Value = X(rng3.Row, 1)
Next
Application.EnableEvents = True

X = [a1:a10].Value2

End Sub

regexp

Function isValidRegex(rng As Range, pattern As String) As Boolean
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.pattern = pattern
isValidRegex = re.Test(rng.Value)
End Function

score 2 · Accepted Answer · 2015-03-08 19:46:04Z

You seem to be reluctant to move over to a WorksheetChange event macro because you believe it does not 'capture the pre-change state of the cell'. That may be correct in the strictest definition but that doesn't mean you cannot capture the changed state, undo the change, determine whether the change is valid and only re-apply the change if it meets criteria.

I'm not going to produce a full regex validating function. This simply tests if the number typed into column E is less than zero or blank. If not then the cell reverts to its prechange state.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Columns(5)) Is Nothing Then
        If Not IsEmpty(Target) Then
            On Error GoTo Safe_Exit
            Application.EnableEvents = False
            Dim vNEW As Variant
            vNEW = Target.Value
            Application.Undo
            If bIs_It_Valid(vNEW) Then
                Target = vNEW
            Else
                ' put stuff like idiot warnings here
            End If
        End If
    End If
Safe_Exit:
    Application.EnableEvents = True
End Sub

Private Function bIs_It_Valid(val As Variant) As Boolean
    If IsNumeric(val) Then _
        bIs_It_Valid = CBool(val < 0)
    Debug.Print bIs_It_Valid
End Function

That Worksheet_Change could probably be adjusted to work on a range of cells if pasting a number of values is important.

Kevin Brock · Accepted Answer · 2019-03-19 18:25:20Z

Here's how I accomplished this without using the `Worksheet_Change` event

Define a Public REGEX Function in a new Module

'Public REGEX Formula
Public Function REGEX(pattern As String, cel As Range) As Boolean
    Dim re As New RegExp
    re.pattern = pattern
    REGEX = re.Test(cel.Value)
End Function

I added this Sub to a module I named Validations. This Sub requires not only the range to validate and the regular expression pattern, but also another range to apply the REGEX formula to. The actual validation applied actually only checks that separate cell for a True or False value. This is a simplified version that assumes the validationColumn is an entire column.

'Validations Module
Sub regexValidation(cells As Range, pattern As String, validationColumn As Range, defaultValue As String)
    Dim cel As Range, regexFormula As String, validationCell As Range

    cells.Value = defaultValue

    'Need to match true on default value or validation will always fail
    pattern = "(" & defaultValue & ")|(" & pattern & ")"

    For Each cel In cells
        regexFormula = "=REGEX(""" & pattern & """," & cel.address & ")"
        Set validationCell = validationColumn.cells(cel.Row, 1)
        validationCell.Formula = regexFormula
        cel.Validation.Delete
        cel.Validation.Add xlValidateCustom, Formula1:="=" & Trim(validationCell.address)
    Next cel
End Sub

This is how I'm calling it. In my case, this is a UserForm with a TextBox called regexPattern that contains the regular expression to apply.

'Form
Private Sub applyRegexValidation(cells As Range)
    Validations.regexValidation cells, regexPattern.text, validationColumn:=cells.Parent.Range("AA:AA"), defaultValue:="Required Custom"
End Sub

Collectives™ on Stack Overflow

Use regex to enforce cell validation?

3 Answers 3

Comments

Comments

Here's how I accomplished this without using the `Worksheet_Change` event

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Here's how I accomplished this without using the Worksheet_Change event

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Here's how I accomplished this without using the `Worksheet_Change` event