Why does my spreadsheet function behave differently than when called from code?

Question

When I call the function from Excel (in a cell):

=allVlookup(O24,A:D,3,"")

vs via vba

MsgBox allVlookup(Range("O24"), Range("A:D"), 3, "")

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 Range to the arguments to allow the sub to run), I get the full results (which is more than one).

The function I am using is:

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 <> "") 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

I am at a loss to explain why this is happening.

What can I do to get the outputs to be identical?

Answer 1

Change .Find line into:

Set c = .Find(what:=lookupRange.Value2, after:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)

and additionally change .FindNext into:

Set c = .Find(what:=lookupRange.Value2, after:=c, LookIn:=xlValues, LookAt:=xlWhole)

Please keep also in mind that tableRange range should have column titles. If not, the results order would not be as expected.

Additional (EDITED) explanation for last sentence. If you have table of that type:

    |  A  |  B  |  C  |  D  |
  --+-----+-----+-----+-----+
  1 | ABC    1     2     A
  2 | ABC    3     4     B
  3 | ABC    5     6     C

when searching of ABC in range("A1:D3") to get data from column D you would get as result: BCD. To get ABC there should be column titles in the first row.

Answer 2

The reason why it only gives the first match is because of a bug. Please see this link (SECTION 5) at the bottom.

I have already filed it as a bug long time ago. If you read the above link then I have suggested an alternative code as well.

Extract from that link in case the link ever dies (which it shouldn't)

.FindNext doesn’t work in a User-Defined Function as expected. You can use it in a normal function.

Lets Say We have this data in Sheet1:

A1 → Colt
A2 → Holt
A3 → Dolt
A4 → Hello

and in

B1 → olt

Now if we paste the below code in a module and run it then we will get the expected result as $A$1:$A$3

Sub Test()
    Sample Sheets("Sheet1").Range("B1"), Sheets("Sheet1").Range("A1:A4")
End Sub
 
Sub Sample(FirstRange As Range, ListRange As Range)
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange
 
        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange)
 
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        MsgBox aCell.Address
    Else
        MsgBox "Not Found"
    End If
End Sub

However it would not work as expected if you paste this function in a module and call it from a worksheet as (Say in Cell C1) =FindRange(A1,A1:A5)

The code will only give you the 1st instance of the value found and ignore the rest

And hence the result that you will get is $A$2!!!

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange
 
        Do While ExitLoop = False
            Set oRange = ListRange.FindNext(After:=oRange)
 
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function

We need to approach this from a different angle.

Instead of using .FindNext we use .Find again till we get the desired result ($A$1:$A$3). See the code below which works:

Function FindRange(FirstRange As Range, ListRange As Range) As String
    Dim aCell As Range, bCell As Range, oRange As Range
    Dim ExitLoop As Boolean
    Set oRange = ListRange.Find(what:=FirstRange.Value, LookIn:=xlValues, _
    lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    ExitLoop = False
    If Not oRange Is Nothing Then
        Set bCell = oRange: Set aCell = oRange
 
        Do While ExitLoop = False
            Set oRange = ListRange.Find(what:=FirstRange.Value, After:=oRange, LookIn:=xlValues, _
            lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not oRange Is Nothing Then
                If oRange.Address = bCell.Address Then Exit Do
                Set aCell = Union(aCell, oRange)
            Else
                ExitLoop = True
            End If
        Loop
        FindRange = aCell.Address
    Else
        FindRange = "Not Found"
    End If
End Function