title: VBA Function to exclude parts of a string
body: <p>My sub compares two lists of strings and returns the closest matches. I've found that the sub gets tripped up over some common words such as "the" and "facility". I would like to write a function that would be supplied an array of words to exclude and check each string for these words and exclude them if found.</p>
  
  <p>Here is a sample input:</p>
  
  <pre><code>|aNames        |  bNames        | words to exclude
  |thehillcrest  |oceanview health| the
  |oceanview, the|hillCrest       | health
  </code></pre>
  
  <p>Intended Output:</p>
  
  <pre><code>|aResults     |bResuts
  |hillcrest    |hillcrest
  |oceanview    |oceanview
  </code></pre>
  
  <p>So far I have:</p>
  
  <pre><code>Dim ub as Integer
  Dim excludeWords() As String
  
  'First grab the words to be excluded
  If sheet.Cells(2, 7).Value &lt;&gt; "" Then
    For y = 2 To sheet.Range("G:G").End(xlDown).Row
      ub = UBound(excludeWords) + 1             'I'm getting a subscript out of range error here..?
      ReDim Preserve excludeWords(0 To ub)
      excludeWords(ub) = sheet.Cells(y, 7).Value
    Next y
  End If
  </code></pre>
  
  <p>Then my comparison function, using a double loop, will compare each string in column A with column B. Before the comparison, the value in column a and b will go through our function which will check for these words to exclude. It's possible that there will be no words to exclude, so the parameter should be optional:</p>
  
  <pre><code>Public Function normalizeString(s As String, ParamArray a() As Variant)
    if a(0) then           'How can I check?
      for i = 0 to UBound(a)
        s = Replace(s, a(i))
      next i
    end if
    normalizeString = Trim(LCase(s))
  End Function
  </code></pre>
  
  <p>There's probably a few parts in this code that won't work. Might you be able to point me in the right direction?</p>
  
  <p>Thank you!</p>