为什么我的电子表格函数的行为与从代码调用时的行为不同? [英] Why does my spreadsheet function behave differently than when called from code?

问题描述

当我从 Excel 调用函数时(在单元格中):

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

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

vs 通过 vba

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

我得到了不同的结果.当从 Excel 调用时，我只得到第一个匹配项，但是当从具有相同参数的 vba 测试子调用时(除了将 Range 添加到参数以允许子运行)，我得到了完整的结果(不止一个).

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

我使用的函数是:

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?

推荐答案

它只给出第一个匹配的原因是因为一个错误.请参阅此链接(第 5 节)在底部.

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.

从该链接中提取，以防链接失效(它不应该)

.FindNext 不能按预期在用户定义的函数中工作.您可以在普通函数中使用它.

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

假设我们在 Sheet1 中有这些数据:

Lets Say We have this data in Sheet1:

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

并在

B1 → olt

现在，如果我们将下面的代码粘贴到一个模块中并运行它，那么我们将得到预期的结果 $A$1:$A$3

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

但是，如果您将此函数粘贴到模块中并从工作表中将其调用为(在单元格 C1 中说)=FindRange(A1,A1:A5)

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

因此你得到的结果是 2 澳元！！！

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.

我们不再使用 .FindNext，而是再次使用 .Find，直到得到所需的结果 ($A$1:$A$3).请参阅下面的代码:

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

这篇关于为什么我的电子表格函数的行为与从代码调用时的行为不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！