title: Why does my spreadsheet function behave differently than when called from code?
body: <p>When I call the function from Excel (in a cell):</p>
  
  <pre><code>=allVlookup(O24,A:D,3,"")
  </code></pre>
  
  <p>vs via vba </p>
  
  <pre><code>MsgBox allVlookup(Range("O24"), Range("A:D"), 3, "")
  </code></pre>
  
  <p>I get different results. When called from Excel, I only get the first match, but when calling from a vba test sub with identical parameters (except adding <code>Range</code> to the arguments to allow the sub to run), I get the full results (which is more than one).</p>
  
  <p>The function I am using is:</p>
  
  <pre><code>Public Function allVlookup(lookupRange As Range, tableRange As Range, colIndex As Integer, Optional delimiter As String = "") As String
  
      Dim c As Range
      Dim firstAddress As String
      'MsgBox tableRange.Address  ' this is correct
      'With Sheets(4).Range("A1:C12").Columns(1)
      'With Range("A1:C12").Columns(1)
  
      'this doesn't allow things to work right either (???)
      'Set tableRange = Range("A:D")
      'Set lookupRange = Range("O24")
  
      'search only the first column for matches
      With tableRange.Columns(1)
          Set c = .Find(what:=lookupRange.Value, LookIn:=xlValues)
  
          If Not c Is Nothing Then
  
              firstAddress = c.Address
  
              Do
                  'add the delimiter
                  If (allVlookup &lt;&gt; "") Then
                      allVlookup = allVlookup + delimiter
                  End If
  
                  'append value to previous value
                  allVlookup = allVlookup + c.Offset(0, colIndex).Value
  
  
                  Set c = .FindNext(c)
                  'exit conditions
                  'no match found
                  If (c Is Nothing) Then
                      Exit Do
                      'we're back to start
                  ElseIf (c.Address = firstAddress) Then
                      Exit Do
                  End If
  
              Loop
          End If
      End With
  
  End Function
  </code></pre>
  
  <p>I am at a loss to explain why this is happening.</p>
  
  <p>What can I do to get the outputs to be identical?</p>