title: Case-insensitive Regular Expression - VBA
body: <p><sub><strong><em>Background:</em></strong></sub></p>
  
  <p>Just now, I was answering a question and was playing around with <code>RegEx</code> within <code>VBA</code>. The goal is to create a list of names that exist within a string. <code>RegEx</code> was the go-to solution since we want to prevent <code>VBA</code> to stumble over punctuation marks and substrings that look similar e.g.: <code>Jack</code> or <code>Jacky</code>.</p>
  
  <hr>
  
  <p><sub><strong><em>Sample Data:</em></strong></sub></p>
  
  <p>Let me give a simple sample. Imagine we have a string like:</p>
  
  <pre><code>Dim str As String: str = "Jack's turn, Becky's or Frank?"
  </code></pre>
  
  <p>we want to know which names in a certain array are mentioned within the string, for example:</p>
  
  <pre><code>Dim arr As Variant: arr = Array("Jack", "Frank")
  </code></pre>
  
  <hr>
  
  <p><sub><strong><em>Sample Code:</em></strong></sub></p>
  
  <p>To prevent an iteration over the array, I went with the following code:</p>
  
  <pre><code>Sub Test()
  
  Dim str As String: str = "Jack's turn, Becky's or Frank?"
  Dim arr As Variant: arr = Array("Jack", "Frank", "Beth")
  Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
  
  regex.Pattern = "\b(" &amp; Join(arr, "|") &amp; ")\b"
  regex.Global = True
  
  Set hits = regex.Execute(str)
  For Each hit In hits
      Debug.Print hit
  Next hit
  
  End Sub
  </code></pre>
  
  <hr>
  
  <p><sub><strong><em>Problem:</em></strong></sub></p>
  
  <p>Whereas the above would neatly return the two hits, it would not work case-insensitive. For example, changing the following line will only return <code>Jack</code>:</p>
  
  <pre><code>Dim str As String: str = "Jack's turn, Becky's or frank?"
  </code></pre>
  
  <p>I thought I could counter that by turning off case-sensitivity using <code>(?i)</code>:</p>
  
  <pre><code>regex.Pattern = "(?i)\b(" &amp; Join(arr, "|") &amp; ")\b"
  </code></pre>
  
  <p>But the problem is that this would work perfectly for most languages (<a href="https://regex101.com/r/Cq0P39/2" rel="noreferrer">test here</a>), however <code>VBA</code> seems to have a problem with it and generates an <code>Error 5017</code> upon execution.</p>
  
  <hr>
  
  <p><sub><strong><em>Question:</em></strong></sub></p>
  
  <p>Does anybody know why? Is this not supported within <code>VBA</code> or is my syntax wrong? If not supported, what is the alternative to get hits case-insensitive while retaining the possibility to <code>Join</code> the array of names?</p>
  
  <p><sub><strong><em>Bonus-Question:</em></strong></sub></p>
  
  <p>Ultimately I would like to <code>Join</code> the <code>Hits</code> together through a delimiter, for example like:</p>
  
  <pre><code>Debug.Print Join(regex.Execute(str),", ")
  </code></pre>
  
  <p>However, I realized execution returns a collection and needs iteration first which I would like to avoid.</p>