title: Returning a regex match in VBA (excel)
body: <p>I am trying to write a function for excel 2010 that will take a cell of unstructured text, look for something called an sdi value and, if found, return that number. The sdi value will appear as sdi ####. What I want is to return sdi and the sepecific numbers that follow it, so if the cell contains "some text sdi 1234 some more text" the function will return sdi 1234. </p>
  
  <p>This is my function:</p>
  
  <pre><code>Function SdiTest(LookIn As String) As String
    Dim temp As String
    Dim STA As Object
    temp = ""
  
    Set SDI = CreateObject("VBScript.RegExp")
    SDI.IgnoreCase = True
    SDI.Pattern = "sdi [1-9]*"
    SDI.Global = True
  
    If SDI.Test(LookIn) Then
      temp = SDI.Execute(LookIn)
    End If
  
    SdiTest = temp
  End Function
  </code></pre>
  
  <p>If there is no sdi number it never enters the if statement and dutifully returns the empty string. If there is an sdi number I get #VALUE!</p>
  
  <p>What am I missing?</p>
  
  <p>Yes, VBScript is enabled. Additionally, I am finding it frustrating to use regex in VBA, and hard to find useful info online. Links to good online resources would be appreciated.</p>
  
  <p>Thank you</p>