title: 'Undefined function' when using DAO QueryDef in VBA
body: <p>I'm assigning an Access 2007 query to a QueryDef in Excel VBA.  My query calls a user-defined function, because it performs a calculation on the results of evaluating a field with a regular expression.  I'm using a QueryDef because I'm collecting values in a UserForm and want to pass them to the query as parameters.</p>
  
  <p>When I run my VBA code, I get an error: "Run-time error '3085': Undefined function 'regexFunc' in expression."</p>
  
  <p><a href="https://stackoverflow.com/questions/2828609/query-with-udf-works-in-access-but-gives-undefined-function-in-expression-err-30">This question</a> suggests that the problem is that DAO is unable to call Access UDFs from Excel, so I copied my UDF into the Excel VBA module, but I still get the error.</p>
  
  <p>Access query:</p>
  
  <pre><code>select field1 from dataTable where regexFunc(field1)=[regexVal]
  </code></pre>
  
  <p>Here's the Excel VBA code:</p>
  
  <pre><code>'QueryDef function
  Sub makeQueryDef (str As String)
  
  Dim qdf As QueryDef
  Dim db As Database
  
  Set db = OpenDatabase(DBpath)
  Set qdf = db.QueryDefs("paramQuery")
  qdf.Parameters("regexVal") = (str="test")
  doSomething qdf
  
  End Sub
  
  'Regex function copied from Access VBA module to Excel VBA module
  Function regexFunc(str As String) As Boolean
  
  Dim re As RegExp
  Dim matches As MatchCollection
  
  regexFunc = False
  Set re = New RegExp
  re.Pattern = "\reg[ex](pattern)?"
  Set matches = re.Execute(str)
  If matches.Count &lt;&gt; 0 Then
      regexFunc = True
  End If
  
  End Function
  </code></pre>