Формулы в VBA - Три совета (Серия VBA 16)
Вставка
- Опубліковано 29 вер 2024
- Привет! В этом видео мы поговорим на тему того, как в VBA правильно прописывать формулы, которые в дальнейшем должны вставляться в ячейки или целые области на рабочих листах Excel.
Кроме того, в этом видео будут показаны три совета о том, как удобнее всего и очень быстро писать формулы из Excel в VBA, и как в этом процессе Вам может помочь инструмент автоматической записи макросов VBA.
Всем приятного просмотра! :)
Если делать так
Range(ActiveCell.Address).FormulaLocal = "=СЕГОДНЯ()"
т.е. не Formula, а именно FormulaLocal, то с русскими именами формул работает корректно.
Проверял в 2016.
Range(ActiveCell.Address).Formula = "=TODAY()"
Скорее всего это для совместимости языковой, чтобы можно было писать универсальные макросы, которые будут работать не зависимо от локализации.
Это так же можно использовать, чтобы узнать англ. наименование. Просто пишем в какой-нибудь ячейке формулу, выделяем ее и запускаем макрос с кодом
MsgBox Range(ActiveCell.Address).Formula
В диалоге выведется эта формула на англ. Как по мне, на много удобнее и проще, чем запись макроса.
Спасибо за приятную новость.С уважением Сергей.
Добрый вечер.С удовольствием посмотрел ,Ваш урок.Я уже сталкивался с этой проблемой.Теперь стало гораздо понятней.Спасибо за уроки.
Здравствуйте, Сергей! Очень рад, что видео было для Вас полезным. И большое спасибо за Ваш комментарий! Хорошего Вам вечера :)
Автор, приветствую Вас. Чтобы формулы работали в русском написании, можно прописать так: Range("B10").FormulaLocal = "=СУММ(B3:B9)" . Тогда формула пересчитается автоматически. Будет имитация, будто вы встали в ячейку с формулой и она пересчиталась. :)
Доброе утро.Куда же ВЫ пропал? Ваши уроки очень нужны.С уважением Сергей.
Дорогой Сергей! Доброе утро!
Большое спасибо за Ваш интерес и столь длительное ожидание нового видео! Очень приятно видеть, что мои видеоуроки столь важны для Вас.
Видео не выходят продолжительное время в связи тем, что я на данный момент нахожусь в другом городе и видео снимать не могу. Тем не менее, за это время я уже подготовил два сюжета для следующих двух уроков, к съёмке которых сразу же приступлю по приезде на следующей неделе.
Ещё раз большое спасибо за Ваше ожидание! Для меня очень важен и приятен тот факт, что аудитория моего канала искренне ценит и ожидает выход следующих видео. Значит канал был создан не зря :)
Хорошего Вам дня, Сергей!
С уважением,
ХБ
Я сделал файл с тысячами макросами, с тысячами кнопок. Но не знал элементарных вещей. Как же я мучился, записывая все макросы через запись или находя похожие варианты и изменяя их методом тыка. А теперь я постиг дзен.
Большое спасибо за проделанную работу!! Невероятно доступное объяснение материала!
Спасибо за труд!
Ждем новых видео:) Особенно будет интересно увидеть разбор новых сложных примеров, с вашим пояснением. И еще было бы интересно больше узнать о классах и формах.
Здравствуйте, Михаил! Большое спасибо за Ваш комментарий! Постараюсь в следующих видео включить более комплексные, интересные примеры :)
Касательно форм и классов - мы обязательно придем к этой теме, правда чуть-чуть придется потерпеть, сейчас немного не хватает времени на съемку видео.
Большое спасибо за Ваш интерес и хорошего Вам дня, Михаил :)
Я в шоке. У меня в ячейках формула на анг SUM вообще не хочет считать. Также пишет #имя? На русском всë считает
О, спасибо большое. Полезная инфа. Недаром ни одного диза автору не поставили еще
Здравствуйте,
Очень рад, что видео было для Вас полезным😊
Хороших Вам выходных 😊
С уважением,
Билял
не подскажете, как написать, когда в поле формула тогда делать какието действия? Спасибо.
Здравствуйте!
Спасибо за Ваш отличный вопрос. Пожалуй, данный пункт даже стоило бы упомянуть в самом видео. Но да ладно, теперь ответ на данный вопрос будет содержаться в комментариях под самим видео! :)
Итак, для того чтобы узнать содержится ли в определенной ячейке формула, можно использовать свойство «HasFormula» объекта Range. То есть указываем область, и обращаемся к свойству «HasFormula» вот таким образом:
Range("A1").HasFormula
Если в ячейке содержится какая-либо формула, то данное выражение принимает значение True. В противном случае значение будет равно False.
Попробуйте для наглядного примера следующий макрос:
Sub formulaChecker()
Dim rgCellChecked As Range
Dim boolHasFormula As Boolean
Set rgCellChecked = ThisWorkbook.Worksheets(1).Range("A1")
boolHasFormula = rgCellChecked.HasFormula
If boolHasFormula = True Then
'Действия, если в проверяемой ячейке содержится формула
MsgBox rgCellChecked.Address(True, True) & " - Содержит формулу! Текст формулы: " & rgCellChecked.FormulaLocal
Else:
'Действия, если в проверяемой ячейке нет формул
MsgBox rgCellChecked.Address(True, True) & " - Не содержит формул!"
End If
Просто потестируйте формулу, сперва вписывая в ячейку A1 просто какое-нибудь значение, а затем формулу :)
Если будут какие-либо вопросы - всегда обращайтесь!
End Sub
С уважением,
Билял
Совсем недавно узнал что формулы в VBA еще можно прописывать с помощью объекта WorksheetFunction. Полный список данных формул объекта WorksheetFunction здесь: docs.microsoft.com/ru-ru/office/vba/api/excel.worksheetfunction
без watches в написании кода никуда, тут без вариантов)
Полностью с Вами согласен 😊
Здравствуйте! Ячейка с Функцией СУММЕСЛИ с закрепленным диапазоном ($:$), копируется на другие ячейки для заполнения данными по условиям СУММЕСЛИ. Закрепленный диапазон меняется регулярно (кол-во столбцов может больше быть может меньше). Несмотря на использование сочетания клавиш "выделение диапазона до последнего столбца" макрорекодер записывает как жесткий диапазон, который при следующем исходнике данных уже не актуален. Писать сам код не могу. Пробовал закрепить к переменным такой метод как остановка макроса для выбора диапазона руками (application.inputbox) - получилось (set a=application.inputbox..... потом С = a.address) Но как эту теперь переменную "C" внедрить в ту самую формулу (функцию) т.е. как бы заменить, диапазоны (например $A$5:$K$5) прописанные в формуле СУММЕСЛИ, на то что выбрал руками (на переменную "С")- не знаю! Макрос выдает ошибку (Aplication-defined or object-defined error) именно на строке работы макроса по обработке формулы с функцией. Очень прошу помочь советом уже весь интернет прошерстил ответа не могу найти.
блин так просто с формулами показано, спасибо
Уважаемый автор, помогите пожалуйста😫🙏🙏. Мне необходимо прописать формулу ПЕРЕКЛЮЧ на языке VBA чтобы она работала в Экселе 2013 года. Заранее огромное спасибо
Просто шикарный самоучитель!
Билял, возможно ли превратить значение формулы сразу в значение без формулы? в VBA.
Курс лучший из всех которые видел по VBA. Но третий совет совсем не логичен. Кто не знает английских вариантов функций ДОЛЖЕН их просто выучить, есть таблицы соотношения рус-англ функций. Так быстрее и проще.
Очень познавательное видео! Спасибо за уроки.
Добрый вечер, Алексей!
Рад, что видео Вам интересны. Спасибо за комментарий и хорошей Вам недели! :)
С уважением,
Билял
Мега-мега-мегаграмотное изложение материала и продуктивные уроки! Редкость на просторах интернета! Низкий Вам поклон за столь эффективное изложение материала по VBA! Вы педагог от Бога, продолжайте в том же духе! Низкий поклон за труды! Не поленюсь и напишу это коммент по каждым видео курса!!!
Здравствуйте, Андрей!
Со своей стороны точно также не поленюсь и поблагодарю Вас за этот приятный комментарий под каждым видео точно так же :)
С уважением и хорошего Вам дня,
ХБ
Вот бы ещё в начале видео было предупреждение - введите все таблицы которые видите на экране и моули которые видите в проекте.
Здравствуйте! Очень здорово! Крутой курс!!!
Здравствуйте, Асылбек!
Рад, что курс Вам понравился! Если будут какие-либо вопросы - всегда смело пишите :)
С уважением и хороших Вам выходных,
Билял
Билял, благодарю! Отличный урок
жаль что ничего не видно в мобильнике :(
Билял, здравствуйте! Смотрю ваш курс, очень доходчиво. У меня есть такая проблема не относящаяся к конкретному уроку. Я сделал макрос, который собирает отчёт и его подключили мои коллеги. Узнавая что-то новое, я дорабатываю макрос. Файл с макросом в формате .xlam лежит на общем сетевом диске. Как сделать так, чтобы он обновлялся у остальных пользователей автоматически?
Здравствуйте, Анвар!
Спасибо за Ваш вопрос. У меня, к сожалению, нет сейчас примера для подобного решения, но вот на этой странице stackoverflow:
stackoverflow.com/questions/11577158/how-to-get-vba-excel-addin-xlam-to-replace-itself-by-a-remote-updated-xlam
есть похожий вопрос с ответами. Попробуйте предлагаемые там варианты.
Если будут какие-то вопросы - пишите, я постараюсь как можно быстрее ответить 😊
Надеюсь, та ссылка Вам поможет!
С уважением,
Билял
Билял, добрый день! Скажите пожалуйста, а почему в этом видео не упомянули о написании формул в виде: Range("T152").FormulaLocal = "=СУММ(V76:CM76)" ? В таком формате прекрасно работает и в русском написании самих формул.
Здравствуйте, Андрей!
Извиняюсь за долгий ответ - эта неделя выдалась крайне интенсивной, и я никак не мог добраться до ответов на комментарии и до работы над каналом в принципе.
И большое спасибо за интересный вопрос!
Смотрите, Вы привели пример с формулой СУММ - и в данном примере всё и вправду отлично работает, формулу можно смело копировать прямиком из ячейки и вставлять в нужную часть кода в VBA. Однако проблемы с FormulaLocal начинаются при использовании формул включающих специальных знаки, в особенности кавычки. В качестве примера: попробуйте прописать простейшую формулу ВПР, что-то вроде =ВПР("Февраль";A1:B12;2;ЛОЖЬ). Теперь просто скопировать и вставить требуемую формулу из ячейки в код уже не получится, так как нарушается синтаксис написания кавычек. Следовательно его придётся поправлять вручную, что потенциально склонно к ошибкам ввиду человеческого фактора, и в принципе является не самым приятным занятием в случае более длинных формул.
То есть смотрите, если приведенный пример формулы ВПР крайне прост, то обработка более комплексной формулы становится намного более сложным процессом. Вот более комплексный вариант формулы, который наглядно демонстрирует суть сказанного:
=ТЕКСТ(ВПР(ТЕКСТ(СЕГОДНЯ();"ММММ");A1:B12;2;ЛОЖЬ);"+ $# ##0,00;- $# ##0,00;$0,00")
Именно поэтому тогда в видео я решил показать лишь этот, по моему мнению, более надёжный и удобный для пользователей VBA вариант написания формул в VBA.
Тем не менее, Вы очень здорово подметили существование FormulaLocal, Андрей! В принципе, его также можно было бы упомянуть как-нибудь, возможно, в одном из следующих видео. Спасибо Вам! 😊
Хороших Вам выходных, Андрей! 😊
С уважением,
Билял
А что если есть необходимость диапазон, допустим, "A3:A9" представить в VBA как "A3:A" и последняя заполоненная ячейка столбца A. Как подружить A& type as long ?
Здравствуйте!
Спасибо за Ваш комментарий! Всё достаточно просто: сперва создаёте соответствующую переменную, а затем при помощи знаков конкатенации & заменяете статично указанное в формуле число на переменную, с переменным числом. Главное сохранить правильной структуру формулы. Вот пример:
Вписываем вот эту формулу: Range("G9").Formula = "=SUM(G3:G4)" с динамичным последним числом:
Dim lngLastRowIndex As Long
lngLastRowIndex = 4
Range("G9").Formula = "=SUM(G3:G" & lngLastRowIndex & ")"
То есть закрываете разделенные части формулы кавычками, при помощи символа & и вставляете в промежуток переменную.
Надеюсь, мог помочь 😊
С уважением,
Билял
спс
А я про Таб не знал нажимал макрорекордер и всю формулу вручную прописывал. Век живи. Век учись
Это точно! 😊
Спасибо за крутой урок)
Спасибо за комментарий, Андрей! Хорошего Вам дня! :)
В некоторых уроках так и напрашивается, что бы в описании были файлы с кодом и исходные таблицы, т. к. Просто в реалиях современного мира нет времени их набирать.
Смотрел по диагонале, можно и на русском Sheets("1").Range("C1").FormulaLocal = "=СУММ(A1;B1)", не вижу проблемы, единственное нужно добавлять кавычки при выделение слова и выводе текстового значения например "ошибочно" в экселе одинарные кавычки, в макросе двойные кавычки пример Sheets("1").Range("I2:I" & D).FormulaLocal = "=ЕСЛИ(G2=0;0;ЕСЛИ(E2=""ошибочно"";0,5;ЕСЛИ(E2=""верно"";1,5;1)))*G2"
спасибо
Полезная информация! А можно чтоб макрос сразу записывал в формате Formula, а не FormulaR1C1?
Здравствуйте, Александр!
Полагаю, ссылки в формулах Macro Recorder записывает только в подобном виде.
Хороших Вам выходных! :)
С уважением
ХБ
P.S. Тем не менее, под кнопкой «Запись макроса» есть кнопка «Относительные ссылки», она позволяет изменять формат записи обычных ссылок в макросе (т.е. не в формулах). Это конечно нечто другое, но тоже можете посмотреть, чтобы знать, если вдруг когда понадобится :)
@@BilyalKhassenov Спасибо :)
У меня есть формула и нифига она не работает в VBA =ЕСЛИОШИБКА(ЕСЛИ(СЧЁТЕСЛИ([@Производитель];"*ИЭК*");ВПР([@Артикул];'C:\Users\Dias\Desktop\Айс\[ИЭК.xlsx]Прайс'!$A$13:$J$22206;10;0);ЕСЛИ(СЧЁТЕСЛИ([@Производитель];"*ТДМ*");ВПР([@Артикул];'C:\Users\Dias\Desktop\Айс\[TDM.xls]TDSheet'!$C$1:$F$65536;3;0);ЕСЛИ(СЧЁТЕСЛИ([@Производитель];"*EKF*");ВПР([Артикул];'C:\Users\Dias\Desktop\Айс\[EKF.xlsx]Продукция EKF'!$A$11:$G$17673;7;0);ЕСЛИ(СЧЁТЕСЛИ([@Производитель];"*DEKraft*");ВПР([@Артикул];'C:\Users\Dias\Desktop\Айс\[ДЭК.xlsx]Тариф Москва'!$A$4:$C$54479;3;0);ЕСЛИ(СЧЁТЕСЛИ([@Производитель];"*CHINT*");ВПР([@Артикул];'C:\Users\Dias\Desktop\Айс\[CHINT.xlsx]Тариф 14.03.2022 '!$A$4:$C$5028;3;0);ЕСЛИ(НЕ([@Производитель]="Любой");[@[Цена 1С]];))))));"ПРОВЕРЬ!")
привет! А можно записать макрос при построении диаграммы и/или сводной? Или как сделать макросс для построения диаграммы и/или сводной?
Спасибо за проделанную работу и столь грамотное изложение! Наверное, скажу банальность, но изначально работать в ENG-версии. В 2021 не знать элементарного английского просто стыдно.
Билял, тебе в очередкой раз спасибо!
Жаль VBA не принимает формулы на русском((
Здравствуйте !
С удовольствием смотрю ваши уроки. Все ясно и грамотно изложено.
Спасибо !
Добрый день! Очень необычный метод перевода формул). Спасибо!
Спасибо.