Создание макросов и пользовательских функций на vba

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

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

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

Для включения макрорекордера на запись необходимо произвести следующие действия:

• в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»>;>
• в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:

• поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
• поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое сочетание горячих клавиш, то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы»>;
• поле «Сохранить в…» — вы можете задать то место, куда будет сохранен (но не послан) текст макроса, а это 3 варианта:
  • «Эта книга» — макрос будет записан в модуль текущей книги и сможет быть выполнен только в случае, когда данная книга Excel будет открыта;
  • «Новая книга» — макрос будет сохранен в тот шаблон, на основе которого в Excel создается пустая новая книга, а это значит, что макрос станет доступен во всех книгах, которые будут создаваться на этом компьютере с этого момента;
  • «Личная книга макросов» — является специальной книгой макросов Excel, которая называется «Personal.xls» и используется как специальное хранилище-библиотека макросов. При старте макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любой момент.
• поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он создавался и какие функции несет, это чисто информативное поле, что называется на память.

После того как вы запустили и записали свой макрос, выполнив все нужные действия, запись можно прекратить командой «Остановить запись» и ваш макрос с помощью макрорекордера будет создан.

Применение макросов в игре

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

В качестве примера, приведу посадку растений в ММОРПГ Life is Feudal. Для этого требуется огород – скажем, 10х10. Алгоритм действий следующий (принимаем как условие, что грядка уже вскопана):

• Кликнуть по игровой клетке;
• Выбрать пункт «посадить»;
• Выбрать в инвентаре желаемое растение;
• Опять кликнуть по этой же клетке;

• Выбрать пункт «Добавить удобрения»;
• Выбрать в инвентаре желаемый тип удобрений;
• Переместиться на соседнюю клетку;
• Повторить.

В общем итоге придется выполнять такой алгоритм 100 раз. Как думаете, насколько быстро вам наскучит эта игра?

В защиту Life is Feudal могу добавить, что это не симулятор колхозника, как вы могли подумать, а вполне динамичная и заточенная под ПВП-баталии «песочница» в средневековом сеттинге.

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

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

Атанас Йонков Блоггер, Веб-разработчик yonkov.atanas@gmail.com

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

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

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

2. Отображение скрытых строк

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

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

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

Виды макросов для игр

Условно делятся на две категории:

Упрощающие игровой процесс.
Создающие преимущество над другими геймерами.

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

Вторая — макросы для игры, нивелирующие недостатки оружия: отметка точки на экране Warface и регулировка отклонения мышки создают «автонавод» для стрельбы противнику в голову.

Отсутствие отдачи — еще одно преимущество, регулируемое опциями курсора.

Как добавить готовый макрос в рабочую книгу

Тем пользователям, которые не имеют серьезного опыта в программировании или же не хотят самостоятельно составлять код для определенного алгоритма действий, необходимо научиться добавлять уже готовый текст в рабочие таблицы. Процедура добавления готового макроса:

1. Изначально необходимо найти желаемую команду в интернете. После этого выделить полученный код.
2. Запустить рабочую книгу, активировать комбинацию клавиш Alt+F
3. Перед пользователем должно открыться окно VBA.

Окно для настройки, добавления макросов вручную при наличии готового кода

1. По левую сторону необходимо найти окно “Project – VBA Project”. Из появившегося списка нужно выбрать рабочую книгу.
2. Открыть меню “Insert”, активировать функцию “Module”.
3. После этого в левом окне появится новая папка под названием “Moduels”.
4. Открыть новый объект внутри созданной паки, в свободное окно вставить скопированный код по комбинации клавиш “Ctrl+V”.

Добавление кода и его сохранение для дальнейшего использования алгоритма действий

Останется закрыть запущенный редактор, вернуться к рабочей книге.

Макросы эксель для чайников

Макрос — это программа, автоматизирующая выполнение различных часто повторяющихся операций. Понятие макроса и управление его записью и применением аналогичны рассмотренным в текстовом процессоре Word.

Основной задачей пользователя является аккуратное выполнение требуемой последовательности операций в реальной таблице при включенном макрорекордере. Макрорекордер преобразует выполняемые действия в последовательность команд языка VBA.

Работая с книгой Excel, пользователь может записать несколько макросов. Все они сохраняются в VBA-модуле. Запуск макроса выполняется одним из трех способов:

• с помощью “горячих” клавиш;
• посредством выбора имени макроса в меню;
• щелчком мыши по графическому объекту, связанному с макросом.

Начало записи макроса

1. Обратитесь к меню Сервис/Запись макроса. /Начать запись. .
2. В окне “Запись макроса” укажите имя макроса.
3. Если запуск макроса планируется через меню или “горячие” клавиши, то нажмите кнопку “Параметры” и задайте имя пункта меню или (и) сочетание клавиш.
4. Нажмите “Ok” . Признак начала записи — появление небольшого окна с кнопкой “Остановить запись” .

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

Начинать запись макроса следует после предварительного планирования действий. Рекомендуется потренироваться в выполнении записываемых операций без запуска макрорекордера. Если при записи выполнено неверное действие, следует отменить его, нажав кнопку “Отменить” на основной панели инструментов.

Завершение записи

1. Нажмите кнопку “Остановить запись” .
2. Если запуск макроса планируется с помощью графического объекта, то выберите требуемый объект на панели “Формы” и разместите его на рабочем листе. Для вызова указанной панели следует обратиться к меню Вид/Панели инструментов. .После размещения объекта типа “Кнопка” откроется окно “Назначить макрос объекту” , в котором требуется указать имя макроса и нажать “Ok” . Для других объектов необходимо щелкнуть по нему правой кнопкой мыши, после чего из выпадающего меню следует выбрать пункт “Назначить макросу” и указать связь с макросом.
3. Опробуйте действие макроса, предварительно восстановив исходное состояние таблицы.

Пример записи макроса

Пусть требуется для выделенного диапазона ячеек, содержащих числа, установить режим форматирования в числовом виде с точностью до одного десятичного знака. Соответствующий макрос должен запускаться с помощью графического объекта “Кнопка”.

Выделить диапазон клеток, в котором задается формат

Важно сделать это до запуска макрорекордера, а не после, поскольку макрос должен быть применим для любого выделенного участка. В противном случае он будет форматировать только один диапазон.
Запустить макрорекордер.
Обратиться к меню Формат/Ячейки. и на вкладке “Число” выбрать числовой формат и “Число десятичных знаков” — 1.
Щелкнуть мышью на любой ячейке, чтобы снять выделение диапазона.
Нажать кнопку “Остановить запись” .
Вызвать панель “Элементы управления” , щелкнуть по объекту “Кнопка” и, установив указатель мыши на свободное место листа, “растянуть” объект

Сменить надпись на кнопке можно двойным щелчком мыши по стандартной надписи, но если объект выделен. Выделяется объект правой кнопкой мыши и нажатием клавиши Esc для закрытия всплывающего меню форматирования объекта.
В окне “Назначить макрос объекту” указать имя макроса, связываемого с кнопкой и нажать “Ok” .
Щелкнуть мышью вне объекта “Кнопка” , чтобы подготовить макрос к работе.
Восстановить исходные данные и опробовать работу макроса для различных выделяемых диапазонов.

и на вкладке “Число” выбрать числовой формат и “Число десятичных знаков” — 1.
Щелкнуть мышью на любой ячейке, чтобы снять выделение диапазона.
Нажать кнопку “Остановить запись” .
Вызвать панель “Элементы управления” , щелкнуть по объекту “Кнопка” и, установив указатель мыши на свободное место листа, “растянуть” объект. Сменить надпись на кнопке можно двойным щелчком мыши по стандартной надписи, но если объект выделен. Выделяется объект правой кнопкой мыши и нажатием клавиши Esc для закрытия всплывающего меню форматирования объекта.
В окне “Назначить макрос объекту” указать имя макроса, связываемого с кнопкой и нажать “Ok” .
Щелкнуть мышью вне объекта “Кнопка” , чтобы подготовить макрос к работе.
Восстановить исходные данные и опробовать работу макроса для различных выделяемых диапазонов.

На рисунке показан диапазон C1:E2, преобразованный в требуемый формат макросом, запускаемым кнопкой с надписью “Десятичный формат”.

Краткое руководство: создание макроса

​ отдельным строкам, то​​ изменения ячеек, пролистывание​ Вам не требуется​.​ применяет такое форматирование​ книгу с помощью​ в поле​ макрос, который сначала​ редактора Microsoft Visual​ макроса.​ инструкциям ниже.​При записи макроса все​ автоматизировать рабочие процессы​ книги.​data.csv​ табличный курсор прыгает​Если Вы сохраните​ результат будет другим.​ таблицы, даже изменение​ знание языков программирования.​В категории​

​ к выделенным ячейкам.​ редактора Microsoft Visual​Сочетание клавиш​ обновляет таблицу в​ Basic.​Хотя поле «Описание» является​На вкладке​ необходимые действия записываются​ бережет вашу работу​Выберите инструмент: «Insert»-«Module» чтобы​– как мы​ с ячейки на​ файл с расширением​=MIN(N2:N21)​ размера окна.​

​Вам уже любопытно, что​Настроить ленту​Windows macOS ​ Basic.​введите любую строчную​ Excel, а затем​Назначение макроса объекту, фигуре​ необязательным, рекомендуется его​Разработчик​ в виде кода​ от лишних потерь​ создать новый стандартный​

Процедура

Дальнейшие действия

• ​ Option Explicit введите​Удалите все данные из​ и при записи​ сохранить книгу как​

• ​=МАКС(O2:O21)​ в меню​ шаг за шагом​

Процедура

Дальнейшие действия

​ макросы.​ исходные данные таблицы,​(Запись макроса…) появилась​ (да, этот функционал​   ​ в статье Отображение​

support.office.com>

Работа с макросами

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

Включение/выключение макросов с помощью меню разработчика

В этой статье преимущественно будет рассматриваться работа с автоматическими алгоритмами в Excel 2010, так как на сегодня именно эта версия является самой популярной у пользователей.

Проще всего работать с макросами с помощью меню разработчика, но эта опция обычно отключена в программе по умолчанию. Для того чтобы ее активировать, выполните такие действия:

1. Зайдите в блок «Файл» и откройте раздел «Параметры».

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

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

Кликните «ОК», для того чтобы сохранить настройки.

Отключение программных алгоритмов осуществляется в той же вкладке. На выбор пользователя предлагается 3 варианта. Вы можете отключить все макросы без уведомления и с соответствующим оповещением или разрешить утилите работать с файлами, которые имеют цифровую подпись. Для внесения изменений необходимо нажать «ОК».

Работа с макросами через настройки софта

Разработчики утилиты предусмотрели еще один метод работы с макросами через параметры программы. Для того чтобы изменить настройки алгоритмов, проделайте такие шаги:

1. Зайдите в меню «Файл», после чего перейдите в раздел «Параметры».
2. Откройте блок «Центр управления безопасностью» и кликните по строке «Параметры центра…».
3. Таким образом, вы попадете непосредственно в «Центр управления безопасностью», где во вкладке «Параметры макросов» можно будет регулировать настройки программных алгоритмов. Как это сделать, подробно рассматривалось в предыдущем способе.

Работа с макросами в других версиях Excel

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

Обычно трудности возникают только при работе с Excel 2007. Для того чтобы отключить/включить макросы, необходимо нажать на логотип программы, после чего перейти в блок «Параметры». После этого вы сможете установить необходимые настройки макросов. В более ранних версиях утилиты необходимо будет перейти в блок «Сервис» и открыть раздел «Макрос». Во вкладке «Безопасность» можно указать необходимые настройки работы программных алгоритмов. На выбор доступно 4 варианта безопасности: низкая, средняя, высокая и очень высокая. Каждый из пунктов соответствует настройкам в Excel 2010.

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

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

Чтобы записать макрос в Excel нужно в нижней панели нажать на кнопку «Запись макроса» или перейти в панели инструментов на вкладку «Вид» в группу «Макросы» и выбрать «Запись макроса»:

Создание макроса

Настраиваем поля записываемого макроса (можно оставить поля пустыми):

Выбор имени макроса и сочетания клавиш

1. Имя макроса — произвольное имя на русском или английском языке;
2. Сочетание клавиш — комбинация клавиш, которой в дальнейшем будет запускаться макрос;
3. Сохранить в — книга в которой будет сохраняться макрос.

Остановить запись макроса можно двумя способами: в нижней панели нажать на кнопку «Остановить запись» или перейти в панели инструментов на вкладку «Вид» в группу «Макросы» и выбрать «Остановить запись»:

Конец записи макроса