Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти  
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как воспроизвести функцию листа в UDF без использования аналогов на VBA?
 
#1
10 Апр 2018 11:35:50
Доброго времени суток, Планетяне!

Вопрос в заголовке. Думаю, что можно без файла-примера.
Как написать что-то вроде этого и можно ли (может что-то похожее):
Код
Public Function VPR(what, where As Range, get As Range) As Variant
VPR = FormulaLocal/Evaluate(=ИНДЕКС(get;ПОИСКПОЗ(what;where;0)))
End Function
Изменено: Jack Famous - 10 Апр 2018 11:37:01
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#2
10 Апр 2018 11:58:32
Jack Famous, это?
Код
Public Function VPR(what, where, from) As Variant
  VPR = WorksheetFunction.Index(from, WorksheetFunction.Match(what, where, 0))
End Function
get - ключевое слово Basic, в качестве имени переменной не годится.
Функции ИНДЕКС, ПОИСКПОЗ не требуют, чтобы аргументами были диапазоны, можно передавать и массивы.
 
 
 
#3
10 Апр 2018 12:05:06
Казанский, спасибо за комментарии)) да - переменные у меня другие были, а вот насчёт массивов не знал  :idea:
я имел ввиду, записать функцию не используя аналоги на VBA. Ну, знаете, как когда макросом прописывается формула через FormulaR1C1 или FormulaLocal…типа такого, только в UDF
Изменено: Jack Famous - 10 Апр 2018 12:05:38
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#4
10 Апр 2018 12:43:55
Jack Famous, Я не уверен, что это правильный ход
Код
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
 
 
 
#5
10 Апр 2018 12:46:51
БМВ, я и сам не уверен  :D спасибо большое!)))
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#6
10 Апр 2018 13:06:34
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
 
 
 
#7
10 Апр 2018 13:09:05
Казанский, ох ни хрена ж себе  8-0  решил, блин, "окунуться"  :D пожалуй, пока что мне хватит))) потом апну, если вернусь)))
спасибо вам большое!  :idea:
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#8
10 Апр 2018 13:35:52
да и FormulaLocal - лучше забывать при использовании в VBA, например её нет для массивных формул, да и универсальность при разных локализациях страдает.
 
 
 
#9
10 Апр 2018 13:38:16
БМВ, да уж - не тот случай))) спасибо  :)
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#10
10 Апр 2018 14:50:27
Цитата
Jack Famous написал:
Вопрос в заголовке.
Вот я я не пойму, о чем это,
Цитата
Как воспроизвести функцию листа в UDF без использования аналогов на VBA?
Если формулы листа, то это не UDF, а если UDF - то на VBA.
Или речь о com надстройке?
 
 
 
#11
10 Апр 2018 14:52:44
RAN, функция на листе — ИНДЕКС. Аналог в VBA — WorksheetFunction.Index
вот это я имел ввиду
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#12
11 Апр 2018 09:21:00
Цитата
RAN написал:
Вот я я не пойму, о чем это,ЦитатаКак воспроизвести функцию листа в UDF без использования аналогов на VBA?
Тоже не заметил соответствия названия темы и содержания :)
Цитата
RAN написал:
Если формулы листа, то это не UDF, а если UDF - то на VBA.Или речь о com надстройке?
не обязательно на VBA. UDF можно написать и в .xll библиотеке (win api), и в COM.dll
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#13
11 Апр 2018 10:23:27
bedvit, драсьте))
Цитата
bedvit написал:
не заметил соответствия названия темы и содержания
блин - да что ж такое-то)))) в принципе, БМВ и Казанский верно "расшифровали", что я имел ввиду. Новое название темы придумать не могу  :D то слишком длинное получается, то слишком обобщённое…
Цитата
bedvit написал:
не обязательно на VBA
разумеется, не обязательно)) но это уже совсем другая история  :)
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#14
11 Апр 2018 10:33:50
Цитата
Jack Famous написал:
Новое название темы
возможно нужно просто : Как воспроизвести формулу листа в UDF, без использования WorksheetFunction.

формулу листа  - говорит про синтаксис и набор функций
UDF - определяет что на VBA
без использования WorksheetFunction. - нужное ограничением.
 
 
 
#15
11 Апр 2018 10:53:35
БМВ, так вроде Jack Famous хотел именно с WorkSheetFunction)
 
 
 
#16
11 Апр 2018 10:56:08
Цитата
БМВ написал:
возможно нужно просто
"Как воспроизвести формулу листа в UDF, используя локальные имена функций" — вот так точнее, т.к. именно это было интересно)) спасибо  ;)
а если просто ограничение на WorkSheetFunction, то тогда можно циклами и массивами, а это не то.
А вообще я в этой теме уже достаточно узнал, чтобы отложить эту идею  :D

Anchoret, это не так)) посмотрите мои ответы (да и саму шапку с примером кода). После ответов БМВ и Казанского я пасанул перед таким подходом)) вчера, в этой своей старой теме пытался разобраться, почему СУММПРОИЗВ в VBA и на листе по-разному работает — отчасти именно из-за этого и создал эту тему, в которой пытался "запихнуть" функционал функции на листе в VBA
Изменено: Jack Famous - 11 Апр 2018 11:10:04
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#17
11 Апр 2018 10:59:50
Anchoret, я ,а потом и Казанский,  на Application.Evaluate делали, и Jack Famous, написал что угадали. Мне кажется, если совместить 6 и , и чуть подпилить то можно вопрос считать закрытым.
Я учел передачу области с другого листа , а Казанский, добавил стиль.
.Address(, ,Application.ReferenceStyle, True)
осталось только
Цитата
Казанский написал:
Вот так будет работать с числовым и текстовым what, а для дат надо еще допилить.
 
 
 
#18
11 Апр 2018 11:07:31
БМВ, абсолютно верно)), а чем такая UDF будет отличаться от ручного ввода на листе? Какие нюансы?))
Заработает ли в таком случае СУММПРОИЗВ в качестве замены ВПР, СУММЕСЛИ и СЧЁТЕСЛИ для длинных ключей?  :)
И почему у вас where.Address(, , , True), а у Казанского where.Address(, , Application.ReferenceStyle)? Я так понял, что вы сцепляете, а Казанский заменяет — в этом разница подходов? Вы написали, что он добавил стиль - а для чего это?))
Изменено: Jack Famous - 11 Апр 2018 11:14:44
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#19
11 Апр 2018 11:14:45
Jack Famous, UDF В трех случаях , Формулой листа это не сделать, формулой листа очень длинно (хотя это можно в переменную запихнуть один раз) . Формулами листа очень медленно, а такое бывает. Тут уж вам решать, нужно вам это или нет. Но вы можете использовать такую конструкцию как функцию в VBA, а не только вызывая её с листа.
нюансы - сказать не берусь, я не макрушник :-)
Цитата
Jack Famous написал:
Заработает ли в таком случае
А попробовать?  ;)  .
 
 
 
#20
11 Апр 2018 11:16:54
Цитата
БМВ написал:
А попробовать?
отпишусь в этот пост отписался ниже
Изменено: Jack Famous - 11 Апр 2018 12:36:34
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#21
11 Апр 2018 11:30:20
Цитата
Jack Famous написал:
И почему у вас where.Address(, , , True), а у Казанского where.Address(, , Application.ReferenceStyle)? Я так понял, что вы сцепляете, а Казанский заменяет — в этом разница подходов? Вы написали, что он добавил стиль - а для чего это?))
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-address-property-excel
ReferenceStyleOptionalXlReferenceStyleThe reference style. The default value is  xlA1 .
ExternalOptionalVariantTrue 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))
Изменено: БМВ - 11 Апр 2018 11:31:09
 
 
 
#22
11 Апр 2018 11:34:16
БМВ,  8-0 спасибо))
Код
Public Function ВПРка(Что_ищем, Где_ищем, Откуда_тянем)
On Error GoTo er
    If TypeName(Что_ищем) = "Range" Then
        ВПРка = Application.Evaluate("=index(" & Откуда_тянем.Address(, , Application.ReferenceStyle, True) & ",match(" & Что_ищем.Address(, , Application.ReferenceStyle, True) & " ," & Где_ищем.Address(, , Application.ReferenceStyle, True) & ",0))")
    Else
        ВПРка = Application.Evaluate("=index(" & Откуда_тянем.Address(, , Application.ReferenceStyle, True) & ",match(" & Что_ищем & " ," & Где_ищем.Address(, , Application.ReferenceStyle, True) & ",0))")
    End If
    If ВПРка = "#Н/Д" Then GoTo er
GoTo fin
er:
ВПРка = ""

fin:
On Error GoTo 0
End Function
когда Что_ищем — ссылка на ячейку, то всё ОК, а когда значением, то ошибка
Изменено: Jack Famous - 11 Апр 2018 11:37:17
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#23
11 Апр 2018 11:42:38
Jack Famous,  а можно пример, чтоб не рисовать самим данные и формулы?
 
 
 
#24
11 Апр 2018 11:55:28
Для текста там кавычек в Else вокруг "что ищем" не хватало, но тогда переставали искаться цифры...
Код
Option Explicit

Public Function ВПРка(Что_ищем, Где_ищем, Откуда_тянем)
On Error GoTo er
    If TypeName(Что_ищем) = "Range" Then
        ВПРка = Application.Evaluate("=index(" & Откуда_тянем.Address(, , Application.ReferenceStyle, True) & ",match(" & Что_ищем.Address(, , Application.ReferenceStyle, True) & " ," & Где_ищем.Address(, , Application.ReferenceStyle, True) & ",0))")
    ElseIf IsNumeric(Что_ищем) Then
        ВПРка = Application.Evaluate("=index(" & Откуда_тянем.Address(, , Application.ReferenceStyle, True) & ",match(" & Что_ищем & " ," & Где_ищем.Address(, , Application.ReferenceStyle, True) & ",0))")
    Else
        ВПРка = Application.Evaluate("=index(" & Откуда_тянем.Address(, , Application.ReferenceStyle, True) & ",match(""" & Что_ищем & """ ," & Где_ищем.Address(, , Application.ReferenceStyle, True) & ",0))")
    End If
    If ВПРка = "#Н/Д" Then GoTo er
GoTo fin
er:
ВПРка = ""
 
fin:
On Error GoTo 0
End Function
Вроде бы так работает, но на даты тоже не проверял:)
Я не волшебник, я только учусь.
 
 
 
#25
11 Апр 2018 12:08:17
Wiss, спасибо, но пока чёт никак)) более того, по ссылкам тоже теперь не тянет - может я Application.ReferenceStyle зря добавил…
БМВ, конечно))
Рабочие UDF поиска, подсчёта и сложения по длинным ключам
Изменено: Jack Famous - 11 Апр 2018 12:35:02
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#26
11 Апр 2018 12:38:32
Jack Famous, может из-за того, что все параметры функции по ссылке?)
 
 
 
#27
11 Апр 2018 12:49:46
Цитата
Anchoret написал:
все параметры функции по ссылке
…только в первом случае, когда TypeName(Что_ищем) = "Range". В остальных двух, параметр Что_ищем передаётся без ссылки. Вы же всё видите — зачем спрашивать такое? Если где-то ошибка, то поправьте пожалуйста, а то угадайка какая-то получается…
Изменено: Jack Famous - 11 Апр 2018 12:50:05
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
#28
11 Апр 2018 13:07:38
Jack Famous,что-то я не понял: "Оно работает или не работает? Что именно не работает?" Сделал файл-пример, вроде бы всё ищет, при переходе на r1c1, тоже
Я не волшебник, я только учусь.
 
 
 
#29
11 Апр 2018 13:12:34
Jack Famous, Просто даже в случае формулы листа есть ограничения у MATCH в 255 символов. так что далее все становится безрезультативным.
 
 
 
#30
11 Апр 2018 13:18:06
Wiss, вставил в файл из #25 — не работает. В вашем всё в порядке
БМВ, сейчас дополню пример с СУММПРОИЗВ — на листе она ВПРит, считает и суммирует длинные ключи
Изменено: Jack Famous - 11 Апр 2018 13:29:04
«Тот, кто несет фонарь, спотыкается чаще, чем тот, кто идет следом.»
Иоганн Пауль Фридрих Рихтер
 
 
 
Страницы: 1 2 След.
Читают тему (гостей: 1)