Как создать макрос в эксель: примеры и как сделать / Skillbox Media

Как написать формулы с помощью макросов в Excel подробное руководство

Итог: ознакомьтесь с 3 советами по написанию и созданию формул в макросах VBA с помощью этой статьи и видео.

Уровень мастерства: Средний

Написание формул может быть одной из самых трудоемких частей
вашей еженедельной или ежемесячной задачи Excel. Если вы работаете над
автоматизацией этого процесса с помощью макроса, вы можете попросить VBA
написать формулу и ввести ее в ячейки.

Поначалу написание формул в VBA может быть немного сложнее,
поэтому вот три совета, которые помогут сэкономить время и упростить процесс.

Совет № 1: Свойство Formula

Свойство Formula является членом объекта Range в VBA. Мы можем использовать его для установки / создания формулы для отдельной ячейки или диапазона ячеек.

Есть несколько требований к значению формулы, которые мы устанавливаем с помощью свойства Formula:

  1. Формула представляет собой строку текста, заключенную в кавычки. Значение формулы должно начинаться и заканчиваться кавычками.
  2. Строка формулы должна начинаться со знака равенства = после первой кавычки.

Вот простой пример формулы в макросе.

Sub Formula_Property()
  ' Формула представляет собой строку текста, заключенную в кавычки
  ' Начинается со знака =
  Range("B10").Formula = "=SUM(B4:B9)"
End Sub

Свойство Formula также можно использовать для чтения существующей формулы в ячейке.

Совет № 2: Используйте Macro Recorder

Если ваши формулы более сложные или содержат специальные
символы, их будет сложнее написать в VBA. К счастью, мы можем использовать
рекордер макросов, чтобы создать код для нас.

Вот шаги по созданию кода свойства формулы с помощью средства записи макросов.

  1. Включите средство записи макросов (вкладка «Разработчик»> «Запись макроса»)
  2. Введите формулу или отредактируйте существующую формулу.
  3. Нажмите Enter, чтобы ввести формулу.
  4. Код создается в макросе.

Если ваша формула содержит кавычки или символы амперсанда, макрос записи будет учитывать это. Он создает все подстроки и правильно упаковывает все в кавычки. Вот пример.

Sub Macro10()
' Используйте средство записи макросов для создания кода для сложных формул с
' специальны символы и относительные ссылки
  ActiveCell.FormulaR1C1 = "=""Total Sales: "" & TEXT(R[-5]C,""$#,###"")"
    
End Sub

Совет № 3: Нотация формулы стиля R1C1

Если вы используете средство записи макросов для формул, вы
заметите, что он создает код со свойством FormulaR1C1.

Нотация стиля R1C1 позволяет нам создавать как относительные (A1), абсолютные ($A$1), так и смешанные ($A1, A$1) ссылки в нашем макрокоде.

R1C1 обозначает строки и столбцы.

Относительные ссылки

Для относительных ссылок мы указываем количество строк и
столбцов, которые мы хотим сместить от ячейки, в которой находится формула.
Количество строк и столбцов указывается в квадратных скобках.

Следующее создаст ссылку на ячейку, которая на 3 строки выше
и на 2 строки справа от ячейки, содержащей формулу.

R[-3]C[2]

Отрицательные числа идут вверх по строкам и столбцам слева.

Положительные числа идут вниз по строкам и столбцам справа.

Абсолютные ссылки

Мы также можем использовать нотацию R1C1 для абсолютных ссылок. Обычно это выглядит как $A$2.

Для абсолютных ссылок мы НЕ используем квадратные скобки. Следующее создаст прямую ссылку на ячейку $A$2, строка 2, столбец 1

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

Проще всего использовать макро-рекордер, чтобы понять это.

Свойство FormulaR1C1 и свойство формулы

Свойство FormulaR1C1 считывает нотацию R1C1 и создает
правильные ссылки в ячейках. Если вы используете обычное свойство Formula с
нотацией R1C1, то VBA попытается вставить эти буквы в формулу, что, вероятно,
приведет к ошибке формулы.

Поэтому используйте свойство Formula, если ваш код содержит
ссылки на ячейки ($ A $ 1), свойство FormulaR1C1, когда вам нужны относительные
ссылки, которые применяются к нескольким ячейкам или зависят от того, где
введена формула.

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

Я надеюсь, что эти советы помогут. Пожалуйста, оставьте
комментарий ниже с вопросами или предложениями.

Создание макросов в excel (на примере)

Опубликовано от Admin — Оставить комментарий

Создание макросов в excel — это задача, которая требует определенного уровня знаний и навыков. Но один раз разобравшись с их написанием, можно очень сильно облегчить себе работу. Так как использование макросов позволяет автоматизировать монотонные операции и передать excel всю работу по выполнению одних и тех же действий.
В статье рассмотрены основы создания макросов, благодаря которым вы напишите свой первый макрос и поймете, захотите ли дальше изучать данную тему.

Вкладка «Разработчик»

Для начала нужно в экселе отобразить вкладку «Разработчик»:

  • Выполнить команды: Файл  ➜  Параметры ➜  Настроить ленту ➜ вкладка «Настроить ленту».
  • Справа на панели  установить флажок «Разработчик»  ➜  Нажать «ОК».

Переход в окно макросов

  • Перейти на вкладку «Разработчик» -> В группе «Код» нажать кнопку «Visual Basic».

Интерфейс окна макросов Excel

Окно кода макросов

Чтобы разобраться, как работает макрос, рассмотрим код.

Sub programm()

Cells(4, 2) = «Расчет значений» ‘ текст в ячейке
Cells(6, 5) = 5 ‘ число в ячейке
Cells(8, 3) = Cells(6, 3) * Cells(7, 3) ‘ формула в ячейке

Cells(4, 2).Font.Size = 14       ‘ размер шрифта
Cells(4, 2).Font.Bold = True    ‘ жирный
Cells(4, 2).Font.Italic = True   ‘ курсив
Cells(4, 2).Font.Underline = xlUnderlineStyleSingle  ‘ подчеркивание
Cells(4, 2).Interior.Color = 65535 ‘заливка ячейки

End Sub

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

Начало создания макроса

  • Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (если аргументов нет, то скобки надо оставить пустыми).
  • Любой макрос должен заканчиваться оператором End Sub.
  • Все, что находится между Sub и End Sub — тело макроса, т. е. команды, которые будут выполняться при запуске макроса. 
  • Комментарий пишется после знака апострофа. Он нужен для того, чтобы делать пометки для себя, так как открыв макрос через n-ное количество времени можно не вспомнить о чем он. Также они могут понадобится при написании другого макроса в качестве шпаргалки по командам.

Ячейки в макросе

  • Ячейка в макросе обозначается Cells(4, 1), где 4-номер строки, 1-номер столбца
  • Каждой ячейке можно присвоить разные значения, например:
    • Cells(4, 1) = «Расчет значений»  ➤ текст (пишется в кавычках)
    • Cells(4, 1) =  5  ➤ значение
    • Cells(4, 1) =  Cells(2, 1) + Cells(3, 1) ➤ сумма других ячеек
    • Cells(4, 1) =  Cells(2, 1) * 20 + Cells(3, 1) * 80 ➤ значение, полученное при вычислении формулы
  • Диапазон ячеек обозначается  Range(Cells(6, 1), Cells(8.2)), где Range — диапазон ячеек
  • Диапазон ячеек удобно выбирать при форматировании группы ячеек.  

Формат ячеек

  • Задать формат ячейке можно задать через свойства ячейки. В нашем примере это размер шрифта, формат шрифта и заливка ячейки.
  • В файле примера макроса рассмотрены заливка ячеек, границы ячеек и диапазона ячеек для всей таблицы, отдельных границ с применением разной толщины границ (скачать по ссылке).
  • Другие команды для форматирования ячеек можно найти в интернете.

Кнопка для запуска макроса

  • Вкладка Разработчик ➜  группа Элементы управления ➜  кнопка Вставить
  • В группе Элементы управления формы нажать «Кнопка» (изображение кнопки) .
  • Щелкнуть на листе место, где должен быть расположен левый верхний угол кнопки. Появится всплывающее окно Назначение макроса.
    Назначьте кнопке макрос и нажмите кнопку ОК.
  • Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.

 

Если у вас получилось применить данный материал, то смело можно переходить к изучению макросов excel. Настройку работы макросов excel и их создание лучше осваивать на конкретном примере, где понятна цель написания программы. Изучить основы макросов можно на курсе  «Excel макросы: генератор простых примеров по математике«.

Рубрика: Таблицы Excel

Метки Эксель, Excel

Сделайте макросы доступными во всех книгах Excel

Советы и руководства по Excel

Редактировать

Добавить в избранное

Избранное

Автор: Дон | Правки: дон

Курс Excel Macro & VBA (от новичка до эксперта) (скидка 80 %)

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

Для этого нам нужно создать так называемую «личную книгу макросов», а затем сохранить ее. Все макросы, которые мы хотим использовать в Excel, будут храниться в этой личной книге макросов, а затем будут доступны для любого файла Excel.

Шаги, чтобы сделать любой макрос доступным во всех файлах Excel

  1. Откройте файл Excel, а затем перейдите к записи фиктивного макроса, запись этого фиктивного макроса — это то, что создаст «личную книгу макросов», которая нам нужна для хранения макросы.
  2. Чтобы записать макрос, перейдите на вкладку Developer , если она видна, или просто посмотрите в нижний левый угол окна Excel. Оттуда нажмите кнопку Record Macro :
  3. На появившемся экране обязательно выберите Personal Macro Workbook в раскрывающемся списке Store macro in: , а затем нажмите кнопку OK. Ничто другое здесь не имеет значения, потому что нам просто нужно записать простой макрос, что угодно.
  4. Выберите любую ячейку на листе, а затем просто щелкните значок Остановить запись Кнопка макроса, которая находится в том же месте, что и кнопка Запись макроса на шаге 2.
  5. Теперь нажмите Alt + F11 на клавиатуре, чтобы мы могли перейти в окно редактора VBA/Macro. Теперь вы увидите, что файл PERSONAL.XLSB появится в левой верхней части окна.
  6. Все, что вам нужно сделать сейчас, это добавить модули и макросы в этот файл, как в любой обычный файл Excel. Дважды щелкните Модуль 1, и мы увидим макрос, который мы только что записали:

Как видите, этот новый файл PERSONAL.XLSB ведет себя как обычный файл в окне VBA. Здесь вы будете хранить любые макросы, к которым вы хотите иметь доступ из всех файлов Excel.

Примечания

Файл личной книги макросов НЕ перемещается вместе с вашими файлами Excel при их отправке; этот файл PERSONAL.XLSB остается на вашем компьютере только для вашего использования. Таким образом, если вы отправите книгу другому человеку, он не сможет получить доступ к тем же макросам, что и у вас, если только вы не поместите эти макросы в отправленную вами книгу.

Вы должны выполнить описанные выше шаги, чтобы создать личную книгу макросов, но после ее создания вы можете получить к ней доступ из любой книги на своем компьютере, просто перейдя в окно редактора VBA/Macro ( Alt + F11 ). Таким образом, после его создания добавление макросов в этот новый файл такое же, как и в любой другой файл Excel: откройте любую книгу, перейдите в окно VBA, добавьте модуль в файл PERSONAL.XLSB, а затем добавьте макрос. .

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

Версия Excel:

Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов обучения
Более 200 справочников Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса. (Опыт работы с VBA не требуется.)

Посмотреть курс

Аналогичный контент на TeachExcel

Функция Vlookup, которая выполняет поиск по всей книге в Excel — UDF

Макрос: с помощью этой функции VLOOKUPWORKBOOK вам нужно будет быстрее и проще анализировать…

Скопируйте и вставьте данные с помощью макроса VBA в Excel

Учебное пособие: как копировать и вставлять данные с помощью макроса в Excel. Я покажу вам несколько способов сделать это,…

Получить имя рабочего листа в макросах VBA в Excel

Учебник: Как получить имя рабочего листа в Excel с помощью VBA и макросов, а также как сохранить его…

Выделение, сортировка и группировка лучших и худших исполнителей в списке в Excel

Руководство:
Как выделить строки лучших и худших исполнителей в списке данных.
Это позволяет…

Сортировка данных без заголовков в порядке возрастания в Excel

Макрос: С помощью этого макроса сортируйте данные без заголовков в порядке возрастания в Excel. Это…

Цикл по диапазону ячеек в UDF в Excel

Руководство:
Как перебрать диапазон ячеек в пользовательской функции, определяемой пользователем, в Excel. Это …

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Детали руководства

Версия Excel:

Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365


Курс Excel VBA — от новичка до эксперта

200+ видеоуроков

50+ часов видео

Более 200 справочников Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса. (Опыт работы с VBA не требуется.)

Посмотреть курс

Макрос для Excel 2007 — Программное обеспечение для добровольцев

Прилагается дословный раздел файла справки Microsoft по Excel 2007 с добавлением нескольких снимков экрана для иллюстрации инструкций. Процесс прост, а вознаграждение представляет собой настраиваемую электронную таблицу, соответствующую потребностям вашей организации, с единовременной настройкой и бессрочным использованием. Настройка — одно из преимуществ отправки отчетов в форматы Microsoft Office. Используйте макрос, если вы хотите выйти за рамки дизайна электронных таблиц Reporter «Сделай сам».

Excel 2007 Раздел справки Microsoft

Чтобы автоматизировать повторяющуюся задачу, вы можете быстро записать *макрос в Microsoft Office Excel. (Можно также создать макрос с помощью **редактора Visual Basic в Microsoft Visual Basic, чтобы написать собственный сценарий макроса или скопировать весь макрос или его часть в новый макрос.) После создания макроса можно назначить его объекту (например, кнопке на панели инструментов, изображению или элементу управления), чтобы его можно было запустить, щелкнув объект. Если вы больше не используете макрос, вы можете удалить его.

Термины:
*макрос: действие или набор действий, которые можно использовать для автоматизации задач. Макросы записываются на языке программирования Visual Basic для приложений.
**Редактор Visual Basic: среда, в которой вы пишете новый и редактируете существующий код и процедуры Visual Basic for Applications. Редактор Visual Basic содержит полный набор инструментов отладки для поиска синтаксических, рабочих и логических проблем в коде.

Запись макроса

Когда вы записываете макрос, средство записи макросов записывает все шаги, необходимые для выполнения действий, которые должен выполнять ваш макрос. Навигация по ленте не включена в записанные шаги. Перенесемся к шагам по записи после включения вкладки «Разработчик».

  1. Если вкладка «Разработчик» недоступна, выполните следующие действия, чтобы отобразить ее:

Нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel.

2. В категории «Популярные» в разделе «Основные параметры работы с Excel» установите флажок «Показать разработчик» на ленте и нажмите «ОК».

3. Нажмите OK.

  • Чтобы временно установить уровень безопасности для включения всех макросов, выполните следующие действия:
  • На вкладке Разработчик в группе Код щелкните Безопасность макросов.

4. В разделе «Параметры макросов» нажмите «Включить все макросы» (не рекомендуется, может выполняться потенциально опасный код), а затем нажмите «ОК».

Примечание. Чтобы предотвратить запуск потенциально опасного кода, мы рекомендуем вам вернуться к любому из параметров, отключающих все макросы, после того, как вы закончите работу с макросами. Дополнительные сведения об изменении параметров см. в разделе «Изменение параметров безопасности макросов в Excel» в файле справки Excel. Щелкните значок вопросительного знака в Excel.

Шаги для записи

На вкладке Разработчик в группе Код щелкните Запись макроса.

В поле Имя макроса введите имя макроса.

 

  • Примечание. Первым символом имени макроса должна быть буква. Последующие символы могут быть буквами, цифрами или символами подчеркивания. В имени макроса нельзя использовать пробелы; символ подчеркивания хорошо работает в качестве разделителя слов. Если вы используете имя макроса, которое также является ссылкой на ячейку, вы можете получить сообщение об ошибке, что имя макроса недопустимо.
  • Чтобы назначить сочетание клавиш CTRL для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву, которую вы хотите использовать.
    Примечание. Клавиша быстрого доступа* переопределяет любую эквивалентную клавишу быстрого доступа Excel по умолчанию, пока открыта рабочая книга, содержащая макрос. Список сочетаний клавиш CTRL, которые уже назначены в Excel, см. в разделе «Сочетания клавиш и функциональные клавиши Excel».
    *Определение клавиши быстрого доступа: — функциональная клавиша или комбинация клавиш, например F5 или CTRL+A, используемая для выполнения команды меню. Напротив, клавиша доступа — это комбинация клавиш, например ALT+F, которая перемещает фокус на меню, команду или элемент управления.
  • В списке Сохранить макрос в выберите книгу, в которой вы хотите сохранить макрос.
    Совет: — Если вы хотите, чтобы макрос был доступен при каждом использовании Excel, выберите «Личная книга макросов». Когда вы выбираете Личная книга макросов, Excel создает скрытую личную книгу макросов (Personal.xlsb), если она еще не существует, и сохраняет макрос в этой книге.
    — В Windows Vista эта книга сохраняется в папке C:\Users\имя пользователя\AppData\Local\Microsoft\Excel\XLStart.
    — В Microsoft Windows XP эта книга сохраняется в папке C:\Documents and Settings\имя пользователя\Application Data\Microsoft\Excel\XLStart.
    — книги в папке XLStart автоматически открываются при запуске Excel.
    — Если вы хотите, чтобы макрос в личной книге макросов автоматически запускался в другой книге, вы также должны сохранить эту книгу в папке XLStart, чтобы обе книги открывались при запуске Excel

    • .