Страницы: 1
RSS
Как создать в VBA формулу с функциями Exel
 
Здравствуйте! Помогите, пожалуйста:
Сделал для работы формулу, которая будет выводить "снизу-вверх" выделенные строки и заменять в них определенные слова (направления света), записал ее себе в текстовом виде, чтобы не забыть и вставлять в файлы, если пригодится:
=ЕСЛИ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ИНДЕКС(Область которую надо копировать и переворачивать;СТРОКА(последняя строка в области ФИКСИРОВАННАЯ ПО СТРОКЕ)-СТРОКА(строка в очереди «сверху-вниз» НЕФИКСИРОВАННАЯ)+1);" северном ";" южном ");" северо-восточном ";" юго-западном ");" восточном ";" западном ");" юго-восточном ";" северо-западном ");" по течению.";" против течения.")= ИНДЕКС(Область, которую надо копировать и переворачивать;СТРОКА(последняя строка в области ФИКСИРОВАННАЯ ПО СТРОКЕ)-СТРОКА(строка в очереди «сверху-вниз» НЕФИКСИРОВАННАЯ)+1);ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ИНДЕКС(Область в другом файле, которую надо копировать и переворачивать;СТРОКА(последняя строка в области ФИКСИРОВАННАЯ ПО СТРОКЕ)-СТРОКА(строка в очереди «сверху-вниз» НЕФИКСИРОВАННАЯ)+1);" южном ";" северном ");" юго-западном ";" северо-восточном ");" западном ";" восточном ");" северо-западном ";" юго-восточном ");" против течения.";" по течению.");ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ИНДЕКС(Область, которую надо копировать и переворачивать;СТРОКА(последняя строка в области ФИКСИРОВАННАЯ ПО СТРОКЕ)-СТРОКА(строка в очереди «сверху-вниз» НЕФИКСИРОВАННАЯ)+1);" северном ";" южном ");" северо-восточном ";" юго-западном ");" восточном ";" западном ");" юго-восточном ";" северо-западном "))

Но, как можно заметить, удобства в использовании в ней не предусмотрено =) Несколько раз приходится вставлять одни и те же области в места, которые выделил голубым цветом.
Решил, что наверное в exel есть возможность записать эту формулу как "шаблон", а после просто указать необходимые области для вывода результата, как это работает с обычными стоковыми формулами.
Хотел для этого использовать VBA, прочитал инструкцию по созданию пользовательских формул. Однако там в качестве примеров используются обычные рассчеты, типа сложить/вычесть/разделить и т.д. А у меня же используются именно Екселевские команды.

Собственно, вопрос таков: как в VBA вводить формулы, которые уже есть в exel? Или же есть какие-то более простые способы сохранить данную формулу в программе для дальнейшего использования в других файлах?

Заранее благодарю за советы! =)
Изменено: Дмитрий Величко - 30.09.2020 13:58:35
Angels show up in the Strangest of places
 
Цитата
Дмитрий Величко написал:
Заранее благодарю за советы! =)
для таких случаев проще делать UDF которая выдает результат исходя из параметров указанных.

Ну а если уж хотите просто вставить формулу то смотрите в сторону
ActiveCell.Formula="сама формула"
ActiveCell.FormulaR1C1="сама формула в стиле R1C1"
и не рекоменованные, о позволяющие писать названия функций на привычном языке
ActiveCell.FormulaLocal="сама формула"
ActiveCell.FormulaLocalR1C1="сама формула в стиле R1C1"
Не рекомендованные по тому что могут не сработать на другом ПК, например на моем :-)

Но для последнего или нужно писать мастер , который спросит адреса диапазонов для обработки или они фиксируются абсолютной или относительной ссылкой, что врядли приемлемо.

Что касается конкретной формулы - замените диапазоны на именованный диапазон. формула и станет легче и изменить его  - это изменить в именах в одном месте.
Диапазону можно дать конкретное название, которое будет понятно, за что он отвечает.
Дальше хоть в текстовом файле формулу храните.
Изменено: БМВ - 30.09.2020 14:11:53
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
для таких случаев проще делать UDF которая выдает результат исходя из параметров указанных.
То есть, как я понимаю: написать такую же формулу, но на языке VB?
Цитата
БМВ написал:
ActiveCell.FormulaR1C1="сама формула в стиле R1C1"
это для случая, когда формула пишется на русском языке?
Angels show up in the Strangest of places
 
Цитата
Дмитрий Величко написал:
это для случая, когда формула пишется на русском языке?
нет, это когда используются ссылки в формате R1C1 - https://www.planetaexcel.ru/techniques/11/113/
Изменено: Mershik - 30.09.2020 14:28:31
Не бойтесь совершенства. Вам его не достичь.
 
Цитата
Дмитрий Величко написал:
написать такую же формулу, но на языке VB
не совсем, скорее написать функцию, которая средствами VB дает результат. Хотя Если использовать функции листа или просто Evaluate, то можно и почти в лоб использовать вышеуказанную формулу. Почти ,но не просто .
Цитата
Дмитрий Величко написал:
когда формула пишется на русском языке
это как раз Local но дело в том что для меня Local это Eng, и он равен простому варианту, а в Нидерландах будет Дачь .... Но если делать только для себя, то это тот язык на котором обычно пишете на листе формулы.

Цитата
Mershik написал:
нет это когда используются относительные ссылки
в смысле?  Это просто иной стиль записи адресации на листе. А абсолютные  $A$1 R1C1 и относительные  наприме a1 и RС[-1] есть и там и там
Изменено: БМВ - 30.09.2020 14:35:08
По вопросам из тем форума, личку не читаю.
 
Mershik, Понял, спасибо)
Angels show up in the Strangest of places
 
БМВ, Сделал вот такую вот функцию, на пробу. Но в экселе выдает ошибку #ЗНАЧ. Не подскажете: что тут не так?
Код
Function Переворачиватель(массив, последняя, текущая)
ActiveCell.Formula = "ИНДЕКС(массив;СТРОКА(последняя)-СТРОКА(текущая)+1)"
End Function

Подозреваю, что аргументы надо как-то выделить, чтобы он их понял?
Angels show up in the Strangest of places
 
Дмитрий Величко, функция должна возвращать что-то, а не проставлять
Не проверял, но как-то так должно быть:
Код
Function Переворачиватель(массив, последняя, текущая)
   Переворачиватель = worksheetfunction.index(массив,последняя.row-текущая.row+1)
End Function
Изменено: Jack Famous - 30.09.2020 15:02:38
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,
Оооооо, заработало!)
Спасибо! Но это еще пол беды, конечно...
Я так понимаю, командой worksheetfunction как раз-таки можно задавать формулы Экселя?
Angels show up in the Strangest of places
 
Дмитрий Величко, пожалуйста))
Цитата
Дмитрий Величко: командой worksheetfunction как раз-таки можно задавать формулы Экселя?
да — во всяком случае, те из них, которые есть в VBA (не факт, что все)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous,
О, да. То, что нужно!
Надеюсь "ПОДСТАВИТЬ" у него там тоже есть. Тогда смогу сваять то, что задумал.
Спасибо большое! =)
Angels show up in the Strangest of places
 
Цитата
Дмитрий Величко: Надеюсь "ПОДСТАВИТЬ" у него там тоже есть
WorksheetFunction.Substitute(), но в 90% случаев при использовании VBA лучше/логичнее/быстрее НЕ использовать функции листа
Например, вместо =ПОДСТАВИТЬ() там есть VBA.Replace(), вместо =ИНДЕКС() - просто брать нужный элемент из массива без функций и так далее — в общем, как тут часто говорят, "сформулируйте задачу, а не методы решения"  ;)
Изменено: Jack Famous - 30.09.2020 16:07:18
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, Да вот чет дааа... команда получается больше 1024 знаков и все улетает...   :)  
Angels show up in the Strangest of places
 
Дмитрий Величко, просто сделайте файл-пример с 2мя столбцами, в котором руками покажите диапазоны "было" и "стало" (результат работы функции) и вам помогут
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Дмитрий Величко,
не обьясняйте как решаете, обьясните что решаете
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Jack Famous,
ЙЭС! Все работает!
Обе формулы заработали!   8-0  Похвастаюсь, уж, может кому и пригодится:
1 - переворачивает содержимое ячеек снизу-вверх по столбцу:
Код
Function Переворачиватель(массив, последняя, текущая)
   
Переворачиватель = WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1)

End Function
2 - мощщщный аддон к первой, которая, помимо "переворачивания" будет также заменять стороны света и направления течения на  противоположные!
Странное дополнение, возможно кто-то скажет. Но я по специальности - инженер-землеустроитель. Когда возникает ситуация: описать границу поселка от точки до точки (которых там тысяча), которую ты уже описывал у смежного поселка, только в обратную сторону - эта функция просто жизненно необходима!   8)
Код
Function Переворачиватель_для_описания(массив, последняя, текущая)
    
Переворачиватель_для_описания = IIf(Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения.") = WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " южном ", " северном "), " юго-западном ", " северо-восточном "), " западном ", " восточном "), " северо-западном ", " юго-восточном "), " против течения.", " по течению."), Replace(Replace(Replace(Replace(Replace(WorksheetFunction.Index(массив, последняя.Row - текущая.Row + 1), " северном ", " южном "), " северо-восточном ", " юго-западном "), " восточном ", " западном "), " юго-восточном ", " северо-западном "), " по течению.", " против течения."))

End Function

Огромное спасибо за помощь! Осталось только одна загвоздка...
Теперь, когда я открываю документ exel, у меня вылетает окно VBA. Это можно как-то убрать? :D  
Изменено: Дмитрий Величко - 30.09.2020 17:04:31
Angels show up in the Strangest of places
 
Цитата
Jack Famous написал:
просто сделайте файл-пример с 2мя столбцами, в котором руками покажите диапазоны "было" и "стало" (результат работы функции) и вам помогут
Ну ну
было 1 стало 11  - а почему нет желания узнать? а 11 это 11 или 2?
По вопросам из тем форума, личку не читаю.
 
Jack Famous, это здорово, конечно. Но интереснее самому разобраться)
Angels show up in the Strangest of places
 
Решение

Дмитрий Величко, создание перекрёстных ссылок — обязанность автора перекрёстных тем
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх