Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Can you share the code of your UDF? –  Peter Albert Jan 28 '13 at 13:40
    
Surely I can, it is very simple. –  Audrius Meškauskas Jan 28 '13 at 13:47

2 Answers 2

up vote 1 down vote accepted

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,
    =IFERROR(IF(SEARCH(":",D3),AND(ISNUMBER(VALUE(LEFT(D3,SEARCH(":",D3)-1))),ISNUMBER(VALUE(RIGHT(D3,LEN(D3)-SEARCH(":",D3))))),FALSE),FALSE)
    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!

share|improve this answer
    
Conditional formatting probably would work for me. Thanks. –  Audrius Meškauskas 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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