Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
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, например её нет для массивных формул, да и универсальность при разных локализациях страдает.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
RAN, функция на листе — ИНДЕКС. Аналог в VBA — WorksheetFunction.Index вот это я имел ввиду
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
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 — не работает. В вашем всё в порядке БМВ, сейчас дополню пример с СУММПРОИЗВ — на листе она ВПРит, считает и суммирует длинные ключи
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄