VBA: функция работает внутри подпрограммы, но не на листе

У меня есть эта странная проблема, когда моя функция отлично работает в подпрограмме, но когда я пытаюсь использовать ее в ячейке, она возвращает только первое значение. Предполагаемая функция состоит в том, чтобы работать аналогично vlookup, но давать мне разделенную запятыми строку со всеми уникальными значениями.

Я сузил его до цикла do. Я также попытался переписать цикл, используя цикл while, но получил те же результаты.

Option Explicit

Function vconc(ByVal val_ As String, ByVal rng As Range, ByVal offset_ As Integer) As String
Dim s As String
Dim col_ As Variant
Dim c As Range
Dim firstAddress As String

Set col_ = New Collection
' combination of the .find function and a collection to get a unique list of values
' works similar to a vlookup, but adds all the unique values to a collection
With rng
    Set c = .Find(val_, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        col_.Add c.Offset(0, offset_), CStr(c.Offset(0, offset_).value)

        Do
            ' adding a value with the same key to the collection gives us an error
            ' but I am interested in a list of unique values, so we simply ignore it
            On Error Resume Next
            Set c = .FindNext(c)
            col_.Add c.Offset(0, offset_).value, CStr(c.Offset(0, offset_).value)

            ' this debug line only runs if the function is run within a subroutine
            Debug.Print c.Offset(0, offset_).value

        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

' concatenate the strings, seperate by ,
Dim item_ As Variant
For Each item_ In col_
    If s = "" Then
        s = item_
    Else
        s = s & ", " & item_
    End If
Next item_

vconc = s

End Function

EDIT: следуя совету SJR, я смог решить эту проблему, заменив строку findnext

Set c = .FindNext(c)

с этой строкой

Set c = .Find(val_, after:=c, LookIn:=xlValues)

excel vba do-loops
person doge    schedule 05.12.2016    source источник
comment
По какой-то причине FindNext не работает должным образом в пользовательских функциях. Вы должны использовать Поиск.   -  person SJR    schedule 05.12.2016
comment
Аллилуйя! Большое спасибо. Не догадался погуглить... Интересно, почему это еще не исправлено...   -  person doge    schedule 05.12.2016

Ответы (1)


arrow_upward
0
arrow_downward

Могу поспорить на виртуальное пиво, что ошибка в

With rng

записывать:

debug.print rng.address
debug.print rng.parent.name

В обоих вариантах (в модуле и в листе) и проверьте результаты.

person Vityata    schedule 05.12.2016
comment
Хорошая догадка, но, к сожалению, я получаю тот же диапазон, $B:$B, и то же имя листа. Продолжайте поступать предложения, эта вещь сводит меня с ума. - person doge; 05.12.2016