I have the working VB macro that validates the content. It takes two string parameters (cell value and regular expression) and returns either True or False. It uses CreateObject("VBscript.regexp") internally.

I need to use this macro to validate the cell. When I try to set it as a custom validator the way

=Rex(E2, "\d{1,2}\:\d{2}")

where Rex is my function defined on the same spread sheet (Alt-F11 and type) and E2 is a cell I am trying to validate, I get the message "A named range you specified cannot be found".

If I insert the same function anywhere else on the same spreadsheet (say at the top corner cell A1):

=Rex(E2, "\d{1,2}\:\d{2}")

the it shows me nicely TRUE if the cell E2 correct the right value like 15:07 and FALSE if it does not like 15xx - half solved but really not till the end. I really need to integrate my validator in a standard way.

I am using MS Excel 2010. The macro itself is

Public Function Rex(ByVal vsStringIn As String, ByVal vsPattern As String) As Boolean
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBscript.regexp")

    objRegEx.Global = True
    objRegEx.Pattern = vsPattern

    Rex = objRegEx.Test(vsStringIn)
    Set objRegEx = Nothing
End Function

And the problem looks like

MS Excel validator problem

  • Can you share the code of your UDF? Jan 28 '13 at 13:40
  • Surely I can, it is very simple.
    – h22
    Jan 28 '13 at 13:47

Interesting problem. Seems like UDFs don't always work in Data Validation! :-(

Following alternatives:

  • Use Conditional Formatting instead and simply highlight any wrong value
  • Use a normal Excel formula instead of your regex to do the validation. In your case,
    will do the job. Not nice, but at least functional! ;-)

Side note: neither your UDF nor the above formula will work, if the value to be be checked is a number (with the Time format) and not a string!

  • Conditional formatting probably would work for me. Thanks.
    – h22
    Jan 28 '13 at 15:45

I know it is a old question, but I don't want to duplicate the question.

Solution: You can apply the UDF formula in a different cell, and then use that value in the Data Validation. You can hide the temporary value by just changing the color to white.

For more information: http://support.microsoft.com/kb/160523

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.