Public Function VPR(what, where, from) As Variant
VPR = WorksheetFunction.Index(from, WorksheetFunction.Match(what, where, 0))
End Function
get - ключевое слово Basic, в качестве имени переменной не годится. Функции ИНДЕКС, ПОИСКПОЗ не требуют, чтобы аргументами были диапазоны, можно передавать и массивы.
Казанский, спасибо за комментарии)) да - переменные у меня другие были, а вот насчёт массивов не знал я имел ввиду, записать функцию не используя аналоги на VBA. Ну, знаете, как когда макросом прописывается формула через FormulaR1C1 или FormulaLocal…типа такого, только в UDF
Public Function VPR(what, where As Range, Source As Range) As Variant
VPR = Application.Evaluate("=index(" & Source.Address(, , , True) & ",match(" & what & " ," & where.Address(, , , True) & ",0))")
End Function
Jack Famous, в данном случае это неудобно. Вот так будет работать с числовым и текстовым what, а для дат надо еще допилить.
Код
Public Function VPR(ByVal what, where As Range, from As Range) As Variant
If VarType(what) = vbString Then
what = """" & what & """"
Else: what = Trim(Str(what))
End If
VPR = Evaluate(Replace(Replace(Replace( _
"INDEX(@,MATCH(#,~,0))" _
, "@", from.Address(, , Application.ReferenceStyle)) _
, "~", where.Address(, , Application.ReferenceStyle)) _
, "#", what))
End Function
да и FormulaLocal - лучше забывать при использовании в VBA, например её нет для массивных формул, да и универсальность при разных локализациях страдает.
bedvit написал: не заметил соответствия названия темы и содержания
блин - да что ж такое-то)))) в принципе, БМВ и Казанский верно "расшифровали", что я имел ввиду. Новое название темы придумать не могу то слишком длинное получается, то слишком обобщённое…
"Как воспроизвести формулу листа в UDF, используя локальные имена функций" — вот так точнее, т.к. именно это было интересно)) спасибо а если просто ограничение на WorkSheetFunction, то тогда можно циклами и массивами, а это не то. А вообще я в этой теме уже достаточно узнал, чтобы отложить эту идею
Anchoret, это не так)) посмотрите мои ответы (да и саму шапку с примером кода). После ответов БМВ и Казанского я пасанул перед таким подходом)) вчера, в этой своей старой теме пытался разобраться, почему СУММПРОИЗВ в VBA и на листе по-разному работает — отчасти именно из-за этого и создал эту тему, в которой пытался "запихнуть" функционал функции на листе в VBA
Anchoret, я ,а потом и Казанский, на Application.Evaluate делали, и Jack Famous, написал что угадали. Мне кажется, если совместить 6 и , и чуть подпилить то можно вопрос считать закрытым. Я учел передачу области с другого листа , а Казанский, добавил стиль. .Address(, ,Application.ReferenceStyle, True) осталось только
Цитата
Казанский написал: Вот так будет работать с числовым и текстовым what, а для дат надо еще допилить.
БМВ, абсолютно верно)), а чем такая UDF будет отличаться от ручного ввода на листе? Какие нюансы?)) Заработает ли в таком случае СУММПРОИЗВ в качестве замены ВПР, СУММЕСЛИ и СЧЁТЕСЛИ для длинных ключей? И почему у вас where.Address(, , , True), а у Казанского where.Address(, , Application.ReferenceStyle)? Я так понял, что вы сцепляете, а Казанский заменяет — в этом разница подходов? Вы написали, что он добавил стиль - а для чего это?))
Jack Famous, UDF В трех случаях , Формулой листа это не сделать, формулой листа очень длинно (хотя это можно в переменную запихнуть один раз) . Формулами листа очень медленно, а такое бывает. Тут уж вам решать, нужно вам это или нет. Но вы можете использовать такую конструкцию как функцию в VBA, а не только вызывая её с листа. нюансы - сказать не берусь, я не макрушник :-)
Jack Famous написал: И почему у вас where.Address(, , , True), а у Казанского where.Address(, , Application.ReferenceStyle)? Я так понял, что вы сцепляете, а Казанский заменяет — в этом разница подходов? Вы написали, что он добавил стиль - а для чего это?))
True to return an external reference. False to return a local reference. The default value is False .
я не подумал про R1C1 а Казанский, про то что лист нужно указать, что и делает TRUE По поводу сцепить или заменить. Ну это скорее для наглядности. мой вариант менее читаем, проще прочесть INDEX(@,MATCH(#,~,0)) хотя я уже давно даже на листе порой делаю так: INDEX(%1%,MATCH(%2%,%3%,0))" и заменяю эти %X% на то что нужно. порой прямо %Name% пишу Когда лень скрипт на PowerShell, а надо однотипные операции делать то в AC4 константа "get-adgroup -filter {name -like "FS_SPB_HR_%GName%_*"} | Remove-ADGroupMember -members "%Uname%" -confirm: $false" а потом замена =SUBSTITUTE(SUBSTITUTE(IF(C8="";$AC$4;IF(C8="r";$AE$4;$AD$4));"%GName%";$A8);"%Uname%";TRIM(C$1))
Wiss, спасибо, но пока чёт никак)) более того, по ссылкам тоже теперь не тянет - может я Application.ReferenceStyle зря добавил… БМВ, конечно))
Рабочие UDF поиска, подсчёта и сложения по длинным ключам
Код
'=========================================================================================================================
Public Function Подтянуть(Что_ищем, Где_ищем, Откуда_тянем)
Dim cl As Range
Dim n&
On Error GoTo er
If Len(Что_ищем) <= 255 Then
n = Application.WorksheetFunction.Match(Что_ищем, Где_ищем, 0): GoTo nx
Else
For Each cl In Где_ищем
If cl.Value2 = Что_ищем Then n = cl.Row - Где_ищем(1).Row + 1: GoTo nx
Next cl
End If
nx:
Подтянуть = Откуда_тянем(n).Value2
GoTo fin
er:
Подтянуть = ""
fin:
On Error GoTo 0
End Function
'=========================================================================================================================
Public Function СчитатьЕсли(Что_считаем, Где_считаем)
Dim cl As Range
Dim i&
On Error GoTo er
If Len(Что_считаем) <= 255 Then
СчитатьЕсли = Application.WorksheetFunction.CountIf(Где_считаем, Что_считаем)
Else
For Each cl In Где_считаем
If cl.Value2 = Что_считаем Then i = i + 1
Next cl
СчитатьЕсли = i
End If
If СчитатьЕсли = 0 Then СчитатьЕсли = ""
GoTo fin
er:
СчитатьЕсли = ""
fin:
On Error GoTo 0
End Function
'=========================================================================================================================
Public Function СуммироватьЕсли(Что_ищем, Где_ищем, Откуда_суммируем)
Dim cl As Range
Dim i
On Error GoTo er
If Len(Что_ищем) <= 255 Then
СуммироватьЕсли = Application.WorksheetFunction.SumIf(Где_ищем, Что_ищем, Откуда_суммируем)
Else
For Each cl In Где_ищем
If cl.Value2 = Что_ищем Then i = i + Откуда_суммируем(cl.Row - Где_ищем(1).Row + 1)
Next cl
СуммироватьЕсли = i
End If
If СуммироватьЕсли = 0 Then СуммироватьЕсли = ""
GoTo fin
er:
СуммироватьЕсли = ""
fin:
On Error GoTo 0
End Function
…только в первом случае, когда TypeName(Что_ищем) = "Range". В остальных двух, параметр Что_ищем передаётся без ссылки. Вы же всё видите — зачем спрашивать такое? Если где-то ошибка, то поправьте пожалуйста, а то угадайка какая-то получается…
Jack Famous,что-то я не понял: "Оно работает или не работает? Что именно не работает?" Сделал файл-пример, вроде бы всё ищет, при переходе на r1c1, тоже
Wiss, вставил в файл из #25 — не работает. В вашем всё в порядке БМВ, сейчас дополню пример с СУММПРОИЗВ — на листе она ВПРит, считает и суммирует длинные ключи