Итог: ознакомьтесь с 3 советами по написанию и созданию формул в макросах VBA с помощью этой статьи и видео.
Уровень мастерства: Средний
Автоматизировать написание формул
Написание формул может быть одной из самых трудоемких частей вашей еженедельной или ежемесячной задачи Excel. Если вы работаете над автоматизацией этого процесса с помощью макроса, вы можете попросить VBA написать формулу и ввести ее в ячейки.
Поначалу написание формул в VBA может быть немного сложнее, поэтому вот три совета, которые помогут сэкономить время и упростить процесс.
Совет № 1: Свойство Formula
Свойство Formula является членом объекта Range в VBA. Мы можем использовать его для установки / создания формулы для отдельной ячейки или диапазона ячеек.
Есть несколько требований к значению формулы, которые мы устанавливаем с помощью свойства Formula:
Вот простой пример формулы в макросе.
Свойство Formula также можно использовать для чтения существующей формулы в ячейке.
Совет № 2: Используйте Macro Recorder
Если ваши формулы более сложные или содержат специальные символы, их будет сложнее написать в VBA. К счастью, мы можем использовать рекордер макросов, чтобы создать код для нас.
Вот шаги по созданию кода свойства формулы с помощью средства записи макросов.
Если ваша формула содержит кавычки или символы амперсанда, макрос записи будет учитывать это. Он создает все подстроки и правильно упаковывает все в кавычки. Вот пример.
Совет № 3: Нотация формулы стиля R1C1
Если вы используете средство записи макросов для формул, вы заметите, что он создает код со свойством FormulaR1C1.
Нотация стиля R1C1 позволяет нам создавать как относительные (A1), абсолютные ($A$1), так и смешанные ($A1, A$1) ссылки в нашем макрокоде.
R1C1 обозначает строки и столбцы.
Относительные ссылки
Для относительных ссылок мы указываем количество строк и столбцов, которые мы хотим сместить от ячейки, в которой находится формула. Количество строк и столбцов указывается в квадратных скобках.
Следующее создаст ссылку на ячейку, которая на 3 строки выше и на 2 строки справа от ячейки, содержащей формулу.
Отрицательные числа идут вверх по строкам и столбцам слева.
Положительные числа идут вниз по строкам и столбцам справа.
Абсолютные ссылки
При создании смешанных ссылок относительный номер строки или столбца будет зависеть от того, в какой ячейке находится формула.
Проще всего использовать макро-рекордер, чтобы понять это.
Свойство FormulaR1C1 и свойство формулы
Свойство FormulaR1C1 считывает нотацию R1C1 и создает правильные ссылки в ячейках. Если вы используете обычное свойство Formula с нотацией R1C1, то VBA попытается вставить эти буквы в формулу, что, вероятно, приведет к ошибке формулы.
Если ваша электронная таблица изменяется в зависимости от условий вне вашего контроля, таких как новые столбцы или строки данных, импортируемые из источника данных, то относительные ссылки и нотация стиля R1C1, вероятно, будут наилучшими.
Я надеюсь, что эти советы помогут. Пожалуйста, оставьте комментарий ниже с вопросами или предложениями.
‘Средняя прочность серии образцов, Мпа в графе (13)
Gustav
Дата: Понедельник, 25.01.2021, 15:35 | Сообщение № 2
lebensvoll
Дата: Понедельник, 25.01.2021, 17:20 | Сообщение № 3
Gustav, спасибо огромное за ответ.
Прошу меня простить за то что создал тему не со своего профиля (не смог вспомнить свой старый). А когда вспомнил и во становил, то тему решил не удалять (((((
Gustav, спасибо огромное за ответ.
Прошу меня простить за то что создал тему не со своего профиля (не смог вспомнить свой старый). А когда вспомнил и во становил, то тему решил не удалять (((((
Сообщение Gustav, спасибо огромное за ответ.
Прошу меня простить за то что создал тему не со своего профиля (не смог вспомнить свой старый). А когда вспомнил и во становил, то тему решил не удалять (((((
Gustav
Дата: Понедельник, 25.01.2021, 18:24 | Сообщение № 4
А фиг знает. честно говоря, сам первый раз с таким сталкиваюсь. И поэтому я бы советовал в данном случае не пользоваться макрорекордером, а создавать формулы с помошью свойства FormulaLocal объекта Range. Преимущество, в первую очередь, состоит в том, что Вы берете за основу формулу в ее, так сказать, естественном виде, в котором она отображается в окне формул (ну, если только, конечно, Вы не ярый фанат адресации R1C1). Сопровождать формулу в таком естественном виде значительно проще, чем в формате R1C1. Пожалуй, при этом единственное, но вполне преодолимое, неудобство заключается в необходимости удвоения в коде VBA двойных кавычек, если таковые имеются в формуле.
А фиг знает. честно говоря, сам первый раз с таким сталкиваюсь. И поэтому я бы советовал в данном случае не пользоваться макрорекордером, а создавать формулы с помошью свойства FormulaLocal объекта Range. Преимущество, в первую очередь, состоит в том, что Вы берете за основу формулу в ее, так сказать, естественном виде, в котором она отображается в окне формул (ну, если только, конечно, Вы не ярый фанат адресации R1C1). Сопровождать формулу в таком естественном виде значительно проще, чем в формате R1C1. Пожалуй, при этом единственное, но вполне преодолимое, неудобство заключается в необходимости удвоения в коде VBA двойных кавычек, если таковые имеются в формуле.
А фиг знает. честно говоря, сам первый раз с таким сталкиваюсь. И поэтому я бы советовал в данном случае не пользоваться макрорекордером, а создавать формулы с помошью свойства FormulaLocal объекта Range. Преимущество, в первую очередь, состоит в том, что Вы берете за основу формулу в ее, так сказать, естественном виде, в котором она отображается в окне формул (ну, если только, конечно, Вы не ярый фанат адресации R1C1). Сопровождать формулу в таком естественном виде значительно проще, чем в формате R1C1. Пожалуй, при этом единственное, но вполне преодолимое, неудобство заключается в необходимости удвоения в коде VBA двойных кавычек, если таковые имеются в формуле.
lebensvoll
Дата: Понедельник, 25.01.2021, 18:51 | Сообщение № 5
Gustav, СПАСИБО за такой подробнейший подход к записи Я делал грубо. 1. Формулы и так создавались с трудом и долго правились и поправлялись 2. В VBA я не силен (((( форум во многом помогал. Но я эту процедуру делал так: копировал формулу вставал на нужную ячейку, включал «запись» и вставлял скопированную формулу, останавливал запись и потом уже получишвийся код-формулу подставлял куда нужно. Если во многом формулы так получались на ура. То именно с этими тремя формулами возникали проблемы (((( Буду пробовать Ваше предложенное
Но у меня же будет не одна формула а много их ((((
СПАСИБО ВАМ ОГРОМНЕЙШЕЕ
Gustav, СПАСИБО за такой подробнейший подход к записи Я делал грубо. 1. Формулы и так создавались с трудом и долго правились и поправлялись 2. В VBA я не силен (((( форум во многом помогал. Но я эту процедуру делал так: копировал формулу вставал на нужную ячейку, включал «запись» и вставлял скопированную формулу, останавливал запись и потом уже получишвийся код-формулу подставлял куда нужно. Если во многом формулы так получались на ура. То именно с этими тремя формулами возникали проблемы (((( Буду пробовать Ваше предложенное
Но у меня же будет не одна формула а много их ((((
СПАСИБО ВАМ ОГРОМНЕЙШЕЕ lebensvoll
Кто бы ты ни был, мир в твоих руках
Сообщение Gustav, СПАСИБО за такой подробнейший подход к записи Я делал грубо. 1. Формулы и так создавались с трудом и долго правились и поправлялись 2. В VBA я не силен (((( форум во многом помогал. Но я эту процедуру делал так: копировал формулу вставал на нужную ячейку, включал «запись» и вставлял скопированную формулу, останавливал запись и потом уже получишвийся код-формулу подставлял куда нужно. Если во многом формулы так получались на ура. То именно с этими тремя формулами возникали проблемы (((( Буду пробовать Ваше предложенное
Но у меня же будет не одна формула а много их ((((
RAN
Дата: Понедельник, 25.01.2021, 18:55 | Сообщение № 6
Gustav
Дата: Понедельник, 25.01.2021, 19:18 | Сообщение № 7
RAN
Дата: Понедельник, 25.01.2021, 20:33 | Сообщение № 8
lebensvoll
Дата: Среда, 27.01.2021, 21:31 | Сообщение № 9
With Range(«O31:P36») .FormulaR1C1 = _ «=IF(R[-13]C[3]=»»Раствор»»,IF(VALUE(R[-5]C)>27,ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE),ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE)),IF(VALUE(R[-5]C)>27,(IF(ROUND(RC[-1]*0.8,)>—VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»B»»&ROUND(RC[-1]*0.8,)&»» > B»»&VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»В»»&ROUND(RC[-1]*0.8,)&»»
With Range(«O31:P36») .FormulaR1C1 = _ «=IF(R[-13]C[3]=»»Раствор»»,IF(VALUE(R[-5]C)>27,ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE),ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE)),IF(VALUE(R[-5]C)>27,(IF(ROUND(RC[-1]*0.8,)>—VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»B»»&ROUND(RC[-1]*0.8,)&»» > B»»&VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»В»»&ROUND(RC[-1]*0.8,)&»»
With Range(«O31:P36») .FormulaR1C1 = _ «=IF(R[-13]C[3]=»»Раствор»»,IF(VALUE(R[-5]C)>27,ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE),ROUND(RC[-1],2)*100/VLOOKUP(R[-12]C[3],Класс!R[-26]C[-11]:R[-13]C[-9],2,FALSE)),IF(VALUE(R[-5]C)>27,(IF(ROUND(RC[-1]*0.8,)>—VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»B»»&ROUND(RC[-1]*0.8,)&»» > B»»&VLOOKUP(R[-12]C[3],Класс!R[-26]C[-14]:R[-13]C[-12],3,FALSE),»»В»»&ROUND(RC[-1]*0.8,)&»»
Надо было сделать массовую вставку формулы корреляции, которые просто так не протянуть, поэтому написал код:
Как реализовать введение формулы в ячейку через макрос без знака доллара Добрый день! Есть такая проблема. где-то нашел как можно вводить формулы, вот пример.
Макрос для объединения значений строки в одну ячейку и вставка между каждым числом точки с запятой Здравствуйте! Имеется код(автор art1287), в который необходимо вставить Private Sub, исполняющий.
как у Вас все в голову помещается. =/
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Вставка строки через макрос Добрый день. Пытаюсь вставлять строку через макрос. Мне нужно, чтобы происходила вставка.
Ассемблерная вставка в С++ через макрос define Здравствуйте. Попался мне код. Скомпилил, собрал девайс, работает. Не сталкивался раньше с.
Запись в ячейку формулы Имеется три ячейки, начальная цена, процент уценки, цена после уценки, как можно сделать, чтобы.
Запись формулы в ячейку Всем привет! Собственно вопрос, не очень понял, что я делаю не так: Formula =.
Возвращает или задает значение Variant, которое представляет неявно пересекаемую формулу объекта в нотации в стиле A1.
Синтаксис
выражения. Формула
выражение: переменная, представляющая объект Range.
Примечания
В динамических массивах Excel, Range.Formula2 суперсерии Range.Formula. Range.Formula будет по-прежнему поддерживаться для поддержания обратного совместимости. Обсуждение динамических массивов и Range.Formula2 можно найти здесь.
См. также
Это свойство не доступно для источников данных OLAP.
Если ячейка содержит константу, это свойство возвращает константу. Если ячейка пуста, это свойство возвращает пустую строку. Если ячейка содержит формулу, свойство Formula возвращает формулу в виде строки в том же формате, который будет отображаться в панели формул (включая равный знак ( = )).
Если вы установите значение или формулу ячейки на дату, Microsoft Excel проверяет, что ячейка уже отформатирована с одним из форматов номеров даты или времени. Если нет, Excel изменяет формат номеров в формат коротких номеров по умолчанию.
Если диапазон состоит из одного или двух измерений, можно установить формулу для массива Visual Basic с теми же размерами. Аналогично, можно поместить формулу в массив Visual Basic.
Набор формул с помощью Range.Formula может вызвать неявное пересечение.
Настройка формулы для диапазона нескольких клеток заполняет все ячейки в диапазоне формулой.
Пример
В следующем примере кода устанавливается формула ячейки A1 на листе1.
В следующем примере кода формулу ячейки A1 на листе1 задает для отображения сегодняшней даты.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Создание макросов и пользовательских функций на VBA
Введение
В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.
Способ 1. Создание макросов в редакторе Visual Basic
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Чтобы включить запись необходимо:
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись(Stop Recording).
Запуск и редактирование макросов
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Макрос для объединения значений строки в одну ячейку и вставка между каждым числом точки с запятой
