Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Курилка
Страницы: 1
RSS
Сокращение (адаптация) длинной формулы для макроса в VBA, При активации записанной макрорекордером формулы появляется ошибка: "Run-time error '1004' Application-defined or object-defined error"
 
#1
18.04.2023 10:00:49
Доброго времени суток!

Есть довольно длинная формула (1580 знаков), которая никак не хочет работать в VBA, даже после обработки макрорекордером – вылетает ошибка 1004.

Формула, вставляемая вручную (есть в файле-примере):

=ЕСЛИ(ИЛИ(ПСТР([@Номер];1;2)="05";ПСТР([@Номер];1;2)="06";ПСТР([@Номер];1;2)="07";ПСТР([@Номер];1;5)="08";ПСТР([@Номер];1;5)="09";ПСТР([@Номер];1;2)="10";ПСТР([@Номер];1;5)="11.32";ПСТР([@Номер];1;4)="17.9";ПСТР([@Номер];1;4)="17.44";ПСТР([@Номер];1;2)="18.50";ПСТР([@Номер];1;2)="18.74";ПСТР([@Номер];1;2)="19.27";ПСТР([@Номер];1;2)="14";ПСТР([@Номер];1;2)="15";ПСТР([@Номер];1;2)="16";ПСТР([@Номер];1;2)="17";ПСТР([@Номер];1;2)="19";ПСТР([@Номер];1;2)="20";ПСТР([@Номер];1;2)="21";ПСТР([@Номер];1;2)="22";ПСТР([@Номер];1;4)="23.50.16";ПСТР([@Номер];1;4)="23.70.20";ПСТР([@Номер];1;4)="23.80";ПСТР([@Номер];1;2)="24";ПСТР([@Номер];1;2)="25";ПСТР([@Номер];1;2)="26";ПСТР([@Номер];1;2)="27";ПСТР([@Номер];1;2)="28";ПСТР([@Номер];1;2)="29";ПСТР([@Номер];1;2)="30";ПСТР([@Номер];1;2)="31";ПСТР([@Номер];1;2)="32";ПСТР([@Номер];1;2)="33";ПСТР([@Номер];1;4)="34.15.17";ПСТР([@Номер];1;4)="35.2";ПСТР([@Номер];1;8)="35.30.1.1";ПСТР([@Номер];1;8)="35.50.12.7";ПСТР([@Номер];1;8)="35.60.13";ПСТР([@Номер];1;8)="35.60.15";ПСТР([@Номер];1;5)="38.31";ПСТР([@Номер];1;7)="38.32.2";ПСТР([@Номер];1;7)="38.32.3";ПСТР([@Номер];1;7)="38.32.7";ПСТР([@Номер];1;5)="42.42.42";ПСТР([@Номер];1;2)="45";ПСТР([@Номер];1;2)="46";ПСТР([@Номер];1;2)="47";ПСТР([@Номер];1;4)="52.1";ПСТР([@Номер];1;2)="56";ПСТР([@Номер];1;7)="70.78.8";ПСТР([@Номер];1;8)="71.80.12";ПСТР([@Номер];1;8)="71.90.14";ПСТР([@Номер];1;4)="75.13";ПСТР([@Номер];1;4)="77.29";ПСТР([@Номер];1;8)="77.39.22";ПСТР([@Номер];1;5)="87.21";ПСТР([@Номер];1;4)="98.25";ПСТР([@Номер];1;5)="96.01";ПСТР([@Номер];1;5)="105.02.98");"Проверка";"-")

И вот как ее записал макрорекордер:

Код
ActiveCell.FormulaR1C1 = _
        "=IF(OR(MID([@Номер],1,2)=""05""," & Chr(10) & "MID([@Номер],1,2)=""06""," & Chr(10) & "MID([@Номер],1,2)=""07""," & Chr(10) & "MID([@Номер],1,5)=""08""," & Chr(10) & "MID([@Номер],1,5)=""09""," & Chr(10) & "MID([@Номер],1,2)=""10""," & Chr(10) & "MID([@Номер],1,5)=""11.32""," & Chr(10) & "MID([@Номер],1,4)=""17.9""," & Chr(10) & "MID([@Номер],1,4)=""17.44""," & Chr(10) & "MID([@Номер],1,2)=""18.50""," & Chr(10) & "MID([@Номер],1,2)=""18.74""," & Chr(10) & "MID([@Номер],1,2)=""19.27""," & Chr(10) & "MID([@Номер],1,2)=""14""," & Chr(10) & "MID([@" & _
        ",2)=""15""," & Chr(10) & "MID([@Номер],1,2)=""16""," & Chr(10) & "MID([@Номер],1,2)=""17""," & Chr(10) & "MID([@Номер],1,2)=""19""," & Chr(10) & "MID([@Номер],1,2)=""20""," & Chr(10) & "MID([@Номер],1,2)=""21""," & Chr(10) & "MID([@Номер],1,2)=""22""," & Chr(10) & "MID([@Номер],1,4)=""23.50.16""," & Chr(10) & "MID([@Номер],1,4)=""23.70.20""," & Chr(10) & "MID([@Номер],1,4)=""23.80""," & Chr(10) & "MID([@Номер],1,2)=""24""," & Chr(10) & "MID([@Номер],1,2)=""25""," & Chr(10) & "MID([@Номер],1,2)=""26""," & Chr(10) & "MID([@Номер],1,2)=""27""," & Chr(10) & "MID" & _
        ",1,2)=""28""," & Chr(10) & "MID([@Номер],1,2)=""29""," & Chr(10) & "MID([@Номер],1,2)=""30""," & Chr(10) & "MID([@Номер],1,2)=""31""," & Chr(10) & "MID([@Номер],1,2)=""32""," & Chr(10) & "MID([@Номер],1,2)=""33""," & Chr(10) & "MID([@Номер],1,4)=""34.15.17""," & Chr(10) & "MID([@Номер],1,4)=""35.2""," & Chr(10) & "MID([@Номер],1,8)=""35.30.1.1""," & Chr(10) & "MID([@Номер],1,8)=""35.50.12.7""," & Chr(10) & "MID([@Номер],1,8)=""35.60.13""," & Chr(10) & "MID([@Номер],1,8)=""35.60.15""," & Chr(10) & "MID([@Номер],1,5)=""38.31""," & Chr(10) & "MID(" & _
        "1,7)=""38.32.2""," & Chr(10) & "MID([@Номер],1,7)=""38.32.3""," & Chr(10) & "MID([@Номер],1,7)=""38.32.7""," & Chr(10) & "MID([@Номер],1,5)=""42.42.42""," & Chr(10) & "MID([@Номер],1,2)=""45""," & Chr(10) & "MID([@Номер],1,2)=""46""," & Chr(10) & "MID([@Номер],1,2)=""47""," & Chr(10) & "MID([@Номер],1,4)=""52.1""," & Chr(10) & "MID([@Номер],1,2)=""56""," & Chr(10) & "MID([@Номер],1,7)=""70.78.8""," & Chr(10) & "MID([@Номер],1,8)=""71.80.12""," & Chr(10) & "MID([@Номер],1,8)=""71.90.14""," & Chr(10) & "MID([@Номер],1,4)=""75.13""," & Chr(10) & "" & _
        "ер],1,4)=""77.29""," & Chr(10) & "MID([@Номер],1,8)=""77.39.22""," & Chr(10) & "MID([@Номер],1,5)=""87.21""," & Chr(10) & "MID([@Номер],1,4)=""98.25""," & Chr(10) & "MID([@Номер],1,5)=""96.01""," & Chr(10) & "MID([@Номер],1,5)=""105.02.98"")," & Chr(10) & """Проверка"",""-"")"

Примечательно, что есть формула на 2800 знаков, которая имеет конструкцию ЕСЛИ(ИЛИ(ПСТР([@НОМЕР]...(ЕСЛИ(ПСТР([@НОМЕР]...))))), содержащую в себе 40 уровней ЕСЛИ, но при этом записалась с первого раза и в макросе работает отлично.

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

Может есть возможность как-то упростить формулу что бы она корректно вставлялась макросом в умную таблицу в разных книгах?

Чат жпт не особо помог (вероятно не хватило моих компетенций), поэтому надежда на опыт матерых ребят с планеты эксель :)
Пример приложил
 
 
 
#2
18.04.2023 10:30:15
i molecule, здравствуйте
    А вам именно формула нужна в ячейке или результат её работы? Если второе, то гораздо проще строковыми на VBA разобрать и вернуть результат. Но, в любом случае, нужно понять, как должна работать функция. Сделайте пример исходных данных и того, что из них должна вернуть функция. Опишите словами алгоритм.
Изменено: Jack Famous - 18.04.2023 10:33:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#3
18.04.2023 10:38:37
Jack Famous, результат, конечно, просто в некоторых номерах есть исключения. Строковыми это имеется ввиду добавление вспомогательных столбцов?  

А Вы не знаете, в чем может быть причина ошибки? просто вот есть формула намного массивнее, но с ней нет проблем
 
 
 
#4
18.04.2023 10:42:47
i molecule, если не хотите делать нормальный пример, то введите формулу на листе и скопируйте её текст в ActiveCell.FormulaLocal = "=текст формулы".
    Возможно, придётся заменить разделители на другие, но это не точно. Я так не делаю. Пробуйте сами.
Изменено: Jack Famous - 18.04.2023 10:44:20
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#5
18.04.2023 11:20:50
Jack Famous, постарался сделать адекватный для восприятия пример, вы просто изменили сообщение, изначально отвечал на другое :)

Есть перечень многоуровневых номеров:
01.02.10.73    
05.05.17.93
05.05.26.90
...
Сейчас проверка идет в два этапа. На первом этапе вписывается формула из шапки темы. Происходит первичная фильтрация. Затем, номера, получившие статус "проверка" проверяются второй формулой учитывающей исключения (структура формулы такая же):

В результате к каждому номеру присваивается статус. Например:

номер                формула                                                                                                         статус
01.02.10.73   |    =ЕСЛИ(ИЛИ(ПСТР([@Номер];1;2)="05";"проверка";"–"               |                  –
05.05.17.93   |    =ЕСЛИ(ИЛИ(ПСТР([@Номер];1;2)="05";"проверка";"–"               |                  –
05.05.05.90   |    =ЕСЛИ(ИЛИ(ПСТР([@Номер];1;8)="05.05.05";"проверка";"–"     |     номер должен попасть в исключение

Надеюсь получилось понятно объяснить. Посмотрите, пожалуйста, пример, там сократил формулу для удобного восприятия.
Таких номеров много, и исключений тоже очень много, которые потом надо будет еще расширять.
Изменено: i molecule - 18.04.2023 11:21:26
 
 
 
#6
18.04.2023 14:01:36
Общий принцип
Изменено: Jack Famous - 18.04.2023 14:18:07
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#7
18.04.2023 14:35:41
Jack Famous, офигеть! буду вникать, спасибо!
 
 
 
#8
18.04.2023 15:46:44
i molecule, пожалуйста, обращайтесь  ;)
    Исключения также легко добавить. Как определитесь с правилами - обновляйте тему.
    Кстати, у вас в формуле полно ошибок - вы проверяете 2 символа с пятью, например, — и наоборот. Также, не нужно добавлять переносы Chr(10), если формула в VBA. Возможно, ошибка была [в том числе] в этом.
Изменено: Jack Famous - 18.04.2023 15:48:01
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#9
18.04.2023 16:49:17
Еще формула, которую можно записать и макросом
Код
=ЕСЛИ(--ЕТЕКСТ(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИОШИБКА(
ВПР(ПСТР([@Номер];1;2);{"05":"06":"07":"10":"14":"15":"16":"17":"18":"19":"20":"21":"22":"24":"25":"26":"27":"28":"29":"30":"31":"32":"33":"45":"46":"47":"48":"56"};1;);
ВПР(ПСТР([@Номер];1;4);{"23.5":"23.7":"23.8":"34.1":"35.2":"52.1":"75.1":"77.2":"98.2"};1;));
ВПР(ПСТР([@Номер];1;5);{"105.0":"11.32":"38.31":"42.42":"87.21":"96.01"};1;));
ВПР(ПСТР([@Номер];1;7);{"38.32.2":"38.32.3":"38.32.7":"70.78.8"};1;));
ВПР(ПСТР([@Номер];1;8);{"35.30.1.":"35.50.12":"35.60.13":"35.60.15":"71.80.12":"71.90.14":"77.39.22"};1;)));
"Проверка";"-")
НА МЯСО В ПРОФИЛЕ
 
 
 
#10
18.04.2023 17:32:28
Михаил Л, работает, большое спасибо!
Jack Famous, по поводу переносов Chr(10), я их удалял, но, вероятно, накосячил с синтаксисом. Завтра буду подробнее разбираться с вашим чудо кодом, спасибо еще раз!
 
 
 
#11
19.04.2023 17:09:37
Цитата
Static st&, aComp$(), aNum&()
Jack Famous, А можно узнать, Num понятно - number, а от чего произошли st и Comp?
И почему назвали модуль PRDX?   :)  
 
 
 
#12
19.04.2023 17:19:53
Static, Compare
PRDX (PaRADoX)- моё "кодовое" имя разработчика. В свой время хотел сменить тут на него, но не дали  :D
Изменено: Jack Famous - 19.04.2023 17:20:20
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#13
19.04.2023 20:48:02
Цитата
написал:
UDF не очень любит словари
а можно с этого момента поподробнее? Не помню особых каких-то требований к словарям внутри UDF.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#14
20.04.2023 09:47:41
Дмитрий(The_Prist) Щербаков, приветствую!
    У меня неоднократно были проблемы с попыткой реализации статичного словаря в UDF. То, перед закрытием книги словарь обнулялся и долго пересчитывались все вхождения UDF на его основе, то — при открытии книги, то - при вставке/удалении строк/столбцов.
    В итоге создал функцию, которая определяет координаты всех вставок и проставляет значениями. Получилось стабильно и намного быстрее.
    Возможно, конечно, я что-то не учёл и делал неправильно, но возвращаться к этому пока что желания вообще нет  :)

UPD: кажется, словарь (и массив) был глобальным, а не статичным и пересчитывался при деактивации листа с данными для него (ну и при открытии книги, конечно), а UDF проверяла его на наличие, вызывала процедуру наполнения, если словаря не было и в конце, по ключу получала номер строки и тянула из массива данные из нужного столбца.
Изменено: Jack Famous - 20.04.2023 10:04:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#15
20.04.2023 19:30:06
Цитата
Jack Famous написал:
пересчитывался при деактивации листа с данными для него (ну и при открытии книги, конечно)
не стал все цитаты выхватывать :) Тут думаю ситуация была такова, что данные для словаря были слишком часто изменяемыми и в таком случае смысла в нем было мало. Хотя опять же - не видя пациента диагноз не поставить :) А так у меня никаких проблем со словарями в UDF замечено не было. Как минимум тех, которые бы проявлялись только в UDF и никак себя не выдавали в обычных процедурах.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#16
21.04.2023 09:36:21
Дмитрий(The_Prist) Щербаков, спасибо за обратную связь)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1
Наверх