title: Use regex to enforce cell validation?
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I created a named range called THIS_CELL, so that the current cell can be passed to <code>isValidRegex()</code>, as follows:</p>
  
  <pre><code>=INDIRECT(ADDRESS(ROW(),COLUMN()))
  </code></pre>
  
  <p>I set a custom validation for the cell, using this formula:</p>
  
  <pre><code>=isValidRegex(THIS_CELL,"(my|regex)patt[ern]")
  </code></pre>
  
  <p>This generated the following error:</p>
  
  <pre><code>A named range you specified cannot be found.
  </code></pre>
  
  <p>According to <a href="http://www.helpinfo.com/index.jsp?k2dockey=1021024569555613607&amp;formwassubmitted=true" rel="nofollow">this article</a>, 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.</p>
  
  <p>I also tried creating a named range called <strong>isValidRegexPattern</strong>, defining it as <code>=isValidRegex(THIS_CELL,"(my|regex)patt[ern]")</code>, and setting the Custom formula to <code>=isValidRegexPattern</code>, but this didn't work either; putting a breakpoint in <code>isValidRegex()</code> showed that the function wasn't even being called.</p>
  
  <p>So, how can I use a UDF for cell validation?</p>