title: Setting the name of cells inside a vba function
body: <p>While inside a VBA function I'm attempting to change the name of certain cells to flag them and use them later. I have a searching function that searches excel docs for keywords from a .txt file and need to know which cells don't include any of the search terms. To do this I was going to name each cell I query and include all unnamed cells in the results column for "Other". But whenever I try to name a cell the name doesn't get updated. I've tried the following:</p>
  
  <blockquote>
    <p>ThisWorkbook.Names.Add "QUERIED", RefersTo:=foundRange</p>
  </blockquote>
  
  <p>and </p>
  
  <blockquote>
    <p>foundRange.name = "QUERIED" </p>
  </blockquote>
  
  <p>The function is here:</p>
  
  <pre><code>Function Single_word_occurrences(datatoFind As String, resultsCol As String) As Integer
      'Initializations
      Dim strFirstAddress As String
      Dim foundRange As Range
      Dim currentSheet As Integer, sheetCount As Integer, LastRow As Integer, loopedOnce As Integer, FoundCount As Integer
  
      FoundCount = 0
      currentSheet = ActiveSheet.Index
      sheetCount = ActiveWorkbook.Sheets.Count
  
      Sheets("Sheet1").Activate
      Set foundRange = Range("F2:F30000").Find(What:=datatoFind, After:=Cells(2, 6), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
      Sheets("List Results").Cells(1, resultsCol).Value = datatoFind
      'if datatoFind is found in search range
      If Not foundRange Is Nothing Then
          'save the address of the first occurrence of datatoFind, in the strFirstAddress variable
          strFirstAddress = foundRange.Address
          Do
              'Find next occurrence of datatoFind
              Set foundRange = Range("F2:F30000").Find(What:=datatoFind, After:=foundRange.Cells, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
              'Place the value of this occurrence in the next cell down in the column that holds found values (resultsCol column of List Results worksheet)
              LastRow = Sheets("List Results").Range(resultsCol &amp; Rows.Count).End(xlUp).Row + 1
              Sheets("List Results").Range(resultsCol &amp; LastRow).Value = foundRange.Address
              ThisWorkbook.Names.Add "QUERIED", RefersTo:=foundRange
              If loopedOnce = 1 Then
                  FoundCount = FoundCount + 1
              End If
              If loopedOnce = 0 Then
                  loopedOnce = 1
              End If
              'The Loop ends on reaching the first occurrence of datatoFind
          Loop While foundRange.Address &lt;&gt; strFirstAddress And Not foundRange Is Nothing
          Msgbox(foundRange.Name)
      End If
      Single_word_occurrences = FoundCount
      Application.ScreenUpdating = True
      Sheets(currentSheet).Activate
  End Function
  </code></pre>