Как писать макросы в excel самоучитель: Самоучитель по работе с макросами в Excel

Самоучитель по работе с макросами в Excel

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

Например, самостоятельно написанный макрос можно привязать к иконке и вывести на Ленту меню. Либо Вы можете создать пользовательскую функцию (UDF) и использовать ее точно так же, как и остальные встроенные функции Excel.

Макрос — это компьютерный код, написанный для Excel на языке программирования Visual Basic for Applications (VBA). Базовые понятия языка программирования VBA рассматриваются на нашем сайте в Учебнике по VBA. Однако прежде чем приступить к написанию кода VBA, рекомендуем познакомиться с уроками, в которых рассматривается безопасность макросов Excel и редактор Visual Basic.

Настройка разрешения для использования макросов в Excel

В Excel предусмотрена встроенная защита от вирусов, которые могут проникнуть в компьютер через макросы.

Если хотите запустить в книге Excel макрос, убедитесь, что параметры безопасности настроены правильно.

Кликните эту ссылку, чтобы узнать больше о параметрах безопасности макросов в Excel

Редактор Visual Basic

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

Кликните эту ссылку, чтобы узнать больше о редакторе Visual Basic в Excel

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

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

Кликните эту ссылку, чтобы узнать больше о записи макросов в Excel

Учебник Excel VBA

Для тех, кто только начинает осваивать язык программирования Excel VBA, предлагаем небольшой вводный курс по Visual Basic for Applications.

Кликните эту ссылку, чтобы перейти к учебнику Excel VBA

Оцените качество статьи. Нам важно ваше мнение:

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

Самоучитель по Excel VBA

​Смотрите также​Samir_Baku​ и правила, конечно,​ приходится делать с​ кликом. Или нужно​Откройте рабочую книгу Excel,​ для выполнения всех​ создавать качественные макропрограммы​ знать язык программирования​ ниже действия.​ с просроченными счетами.​ импорта данных из​View Macros​ данных, при этом​— для расчёта​Начиная с этого момента,​

​ Вам не требуется​Данный учебник является введением​: Супер справочник, мне​ придётся запомнить.​ помощью макросов, но​ одновременно создать сразу​ в которой необходимо​ этих функций, в​ автоматически:​ «VBA» чтобы создавать​Выберите​ Для этого можно​ него тоже можно​(Макросы).​ макрос остался записан.​ этого значения необходимо​ ВСЕ Ваши действия​ знание языков программирования.​ в язык программирования​ как раз такой​Ну, это как​ я в них​ определенное количество копий​ использовать макрос: «РАЗРАБОТЧИК»-«Код»-«Visual​ том числе открытие​Присваивайте макросам короткие, но​

• ​ свои макро-программы с​
• ​Excel​ создать и выполнить​
• ​ записать в макрос:​
• ​В открывшемся диалоговом окне​ Нам нужно сохранить​
• ​ взять именно исходные​
• ​ с документом записываются:​
• ​Вам уже любопытно, что​ Excel VBA (Visual​
• ​ нужен! Спасибо вам​ научиться ездить на​
• ​ полный «дундук». Даже​ определенного рабочего листа.​
• ​ Basic». Или нажмите​
• ​ и соединение всех​

​ содержательные имена. Когда​ помощью инструмента записи​>​ макрос, который быстро​

​Откройте файл шаблона, в​ мы увидим строку​
​ книгу, как шаблон​
​ данные таблицы. Если​

​ изменения ячеек, пролистывание​

office-guru.ru>

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

• ​ такое макрос, и​
• ​ Basic for Applications).​ Алексей!​
• ​ автомобиле: нужно знать,​
• ​ те макросы которые​ Список потребностей пользователей​
• ​ комбинацию горячих клавиш​ листов в одну​

​ вы войдете вкус,​ макросов.​Параметры​ применяет такое форматирование​ котором мы сохранили​ с именем нашего​ Excel с поддержкой​ взять среднее значение​ таблицы, даже изменение​ как он работает?​ Изучив VBA, Вы​Я здесь давно,​ что он может​ я нашел здесь​ в автоматизации процессов​ ALT+F11. ​ комбинированную таблицу.​

​ со временем вам​Сначала надо включить панель​>​ к выделенным ячейкам.​ макрос —​ макроса​ макросов, который имеет​ из средних по​ размера окна.​

Что такое Макрос?

​ Тогда смелей вперёд​ сможете создавать макросы​ просто сейчас зарегистрировался.​ делать, какая финтифлюшечка​ на форуме и​ работы можно продолжать​Перед началом работы в​​Используйте кнопки со стрелками​​ придется создавать много​ разработчика. Для этого​Лента и панель​Windows macOS ​FormatData​FormatData​ расширение​ отдельным строкам, то​Excel сигнализирует о том,​ – далее мы​ и выполнять в​Alex_ST​ для какого действия​ вообще на «планете»​ до бесконечности.​ редакторе следует сделать​

​ для управления курсором​ макросов. При выборе​ в меню «Файл»​.​Действия перед записью макроса​.​. Выберите его и​

• ​XLTM​
• ​ результат будет другим.​ что включен режим​ шаг за шагом​
• ​ Excel практически любые​: Да мне особо​ предназначена, основные правила​ (которые мне написали​
• ​Если бы не было​
• ​ простую настройку. Выберите​ (Ctrl + Up,​ в диалоговом окне​

Создание макроса – практический пример

​ открываем группу опций​В категории​​   ​​Создайте новый макрос с​ нажмите​.​=MIN(N2:N21)​ записи макроса в​ проделаем весь процесс​ задачи. Вы очень​ не за что…​ эксплуатации (ну и​ другие участники форума,​ возможности создавать макросы​

​ инструмент в редакторе​ и т.п.). Позиционируйте​ их легче найти​ «Параметры». В появившемся​Настроить ленту​Макросы и средства VBA​ именем​Run​Важный момент!​или​

​ двух местах. Во-первых,​​ создания макроса вместе​​ скоро поймёте, что​​Главный респект -​​ движения тоже, конечно,​​ ОГРОМНОЕ ИМ СПАСИБО!),​​ во всех программах,​

​ Visual Basic: «Tools»-«Options».​ курсор, так чтобы​ с короткими и​

​ окне «Параметры Excel»​​в списке​

​ находятся на вкладке​LoadData​(Выполнить).​Если Вы сохраните​=МИН(N2:N21)​ в меню​

​ с Вами. ​ макросы могут сэкономить​ его составителю, господину​ не помешало бы,​ я могу использовать​​ входящих в пакет​​ И на вкладке​ вы могли добавить,​​ содержательными названиями. Система​​ открываем группу «Настройка​Основные вкладки​​Разработчик​​.​

​Когда макрос начнёт работать,​ файл с расширением​=MAX(O2:O21)​​Macros​​Макрос в Microsoft Office​ уйму времени благодаря​ А.Климову​ но ПДД -​ только так как​ MS Office. То​ «Editor» активируйте опцию​ изменить или удалить​ VBA предоставляет вам​ ленты». Обратите внимание​установите флажок​, которая по умолчанию​В процессе записи макроса​ Вы увидите, как​XLTX​

​или​(Макросы) – вместо​ (да, этот функционал​ автоматизации повторяющихся задач​Я, правда не​ это в программировании​

​ они написаны изначально,​ множество операций в​ «Require Variable Declaration».​ данные внутри таблицы​ возможность указать описание​ на правую колонку​Разработчик​ скрыта, поэтому сначала​LoadData​

• ​ табличный курсор прыгает​​, то макрос в​​=МАКС(O2:O21)​
• ​ строки​​ работает одинаково во​​ и обеспечить гибкое​
• ​ помню точно, откуда​​ как комментарии и​​ а вот редактировать​
• ​ процессе рутинной работы​​ Это позволит реализовать​​ по мере необходимости. ​
• ​ к имени. Обязательно​​ настроек под аналогичным​​, а затем нажмите​

​ нужно включить ее.​сделайте импорт данных​ с ячейки на​ нём работать не​=MEDIAN(B2:K21)​Record Macro​

​ многих приложениях пакета​ взаимодействие с другими​ я это скачал,​ осмысленные имена программ​

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

​ названием «Настройка ленты».​

• ​ кнопку​​ Дополнительные сведения см.​​ из файла​
• ​ ячейку. Через несколько​​ будет. Кстати, можно​​или​​(Запись макроса…) появилась​ Microsoft Office) –​ пользователями.​ но, т.к. скачано​ и переменных: желательно,​ себя уже не​ выполнять вручную (выделять​ Options Explicit в​
• ​ является более сложным​​Имя макроса обязательно должно​​ В ней следует​
• ​Сохранить​​ в статье Отображение​​data.
  csv​
• ​ секунд с данными​​ сохранить книгу как​​=МЕДИАНА(B2:K21)​​ строка​ это программный код​Этот учебник не является​ достаточно давно, то​

​ но не обязательно​ могу.​ через одну строку​ начале каждого ново​ и не так​ начинаться с букв​ отметить галочкой опцию​.​ вкладки «Разработчик».​​– как мы​​ будут проделаны те​ шаблон Excel 97-2003,​​— считаем, используя​​Stop Recording​ на языке программирования​ исчерпывающим руководством по​ есть подозрение, что​​ :-)​​Хочется хоть как-то​ кликая по каждому​​ созданного кода. А​​ надежным в момент​

​ и не может​ «Разработчик» как показано​Запись макроса​

• ​Запись макроса​
• ​ это делали в​
• ​ же операции, что​

​ который имеет формат​ исходные данные таблицы,​

​(Остановить запись).​Visual Basic for Applications​

​ языку программирования Excel​ по ссылке из​

​Не плохо просмотреть​ разобраться что это​ второму заголовку мышкой​ в поле ввода​

​ записи. Когда дело​ содержать пробелы, символы​ ниже на рисунке:​   ​   ​ предыдущей части урока.​ и при записи​XLT​ по причине указанной​Во-вторых, в нижнем левом​(VBA), сохранённый внутри​ VBA. Его цель​ одного из форумов​ какие-нибудь видео-курсы. Вот,​ за зверь такой​ или копировать вставлять​ «Tab Width:» укажите​

​ доходит до макросов,​ или знаки препинания.​Теперь нам доступна на​На вкладке​​В группе​​Когда импорт будет завершён,​ макроса. Когда всё​, он тоже поддерживает​ выше.​ углу окна Excel.​​ документа. Чтобы было​​ – помочь начинающему​

​ по Access’y.​ например, вполне приличные:​ макрос.​ по одному листу).​ значение 2 вместо​ использовать мышь лучше​ После первого символа,​ ленте новая закладка​Разработчик​​Код​​ остановите запись макроса.​

​ будет готово, таблица​​ макросы.​Теперь, когда с вычислениями​​ Иконка​​ понятнее, документ Microsoft​ специалисту освоить написание​А объектные модели​У меня есть​Если я прочитаю​ Ручная работа в​​ 4-х. Это позволит​​ только для вызова​ вы можете использовать​

​ «Разработчик» со всеми​нажмите кнопку​

Выполнение макроса в Excel

​на вкладке​Удалите все данные из​ должна выглядеть так​Когда шаблон сохранён, можно​ закончили, займёмся форматированием.​Стоп​ Office можно сравнить​

• ​ макросов в Excel​
• ​ Access и Excel​ хорошиу справочник пр​

​ книгу «Профессиональное программирование​ лучшем случаи приводить​ уменьшить ширину кода.​ меню.​ больше букв, цифр​ своими инструментами для​Запись макроса​Разработчик​ ячеек.​​ же, как и​​ спокойно закрыть Excel.​ Для начала для​(маленький квадратик) указывает​ со страницей HTML,​ при помощи кода​

​ естественно, имеют существенные​ VBA функциям в​ на VBA в​ к потере огромного​ Данная настройка редактора​Держите ваши макросы для​ или нижнее подчеркивание,​ автоматизации работы в​.​нажмите кнопку​Сохраните файл, как шаблон​ оригинал, который мы​Прежде чем раскрыть все​ всех ячеек зададим​ на то, что​ тогда макрос –​ VBA. Для желающих​ различия. Также и​ формате .chm, сейчас​​ Excel 2002″, я​​ количества времени, а​

​ распространяется на все​ небольших специфичных задач.​ но максимальная длина​​ Excel и создания​​Если необходимо, заполните поля​Запись макроса​ Excel с поддержкой​

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

​ одинаковые элементы управления​ приложу, но он​​ хоть как-то смогу​​ в худшем –​​ листы, но в​​ Чем больше программный​ имени составляет 80​​ макросов.​​Имя макроса​

​.​ макросов (расширение XLTM).​ с другими данными​ макроса, считаю правильным​​ данных. Выделите все​​ макроса. Нажатие на​ То, что Javascript​​ программирования более глубоко​​ имеют некоторые одинаковые​

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

Заглянем под капот: Как работает макрос?

​ могут и удалить…​ начать с чего-то​ или даже потере​ книги.​​ тем медленнее он​​Абсолютный адрес ячейки –​ приложения, которые берут​Сочетание клавиш​Имя макроса​ шаблон, Вы получаете​Как уже не раз​ пару важных моментов,​ для этого воспользуйтесь​ И наоборот, когда​

​ данными в формате​ по Excel VBA.​ т.к. отражают специфическую​​Главное — на​​ по проще, что-нибудь​​ ценных данных.​​Выберите инструмент: «Insert»-«Module» чтобы​​ работает, особенно если​​ это точное местонахождение​ на себя всю​и​​,​​ доступ к двум​

​ упоминалось, макрос —​​ касающихся макросов в​​ комбинацией клавиш​ режим записи не​ HTML, находящимися на​ Далее приведено содержание​ «заточку» под объектную​ первых порах уйти​ типа «. .. программирование​Возможность создавать макросы и​ создать новый стандартный​ это требуется для​ курсора, когда информация​ рутинную работу, облегчая​Описание​Сочетание клавиш​ макросам – один​ это программный код​ целом:​Ctrl+A​ включен, в этом​

Добавим ещё один шаг к нашей задаче…

​ web-странице, очень похоже​ самоучителя по Excel​​ модель приложения.​​ от общения с​ на VBA для​ автоматизировать рабочие процессы​ модуль для макросов.​ выполнения многих функций​ о его размещении​​ жизнь пользователю. Каждый​​, а затем нажмите​и​ загружает данные, другой​ на языке программирования​Макрос может нанести вред.​, либо щелкните по​ месте находится иконка​

1. ​ на то, что​ Visual Basic. Для​Например в Excel​​ объектами самого Ёкселя​​ чайников…»​
2. ​ бережет вашу работу​ В появившемся окне​​ или рассчитать много​​ записывается в макро-адреса​
3. ​ пользователь может создать​​ кнопку​​Описание​ их форматирует. ​​Visual Basic for Applications​​Ещё раз прочти предыдущий​ иконке​ для включения записи​
4. ​ макрос может делать​ начинающих программистов настоятельно​
5. ​ свойство Combobox1.ListFillRange -​ (это сложнее), необходимо​
6. ​Спасибо.​ от лишних потерь​ модуля под текстом​

​ формул в большой​ с жесткой привязкой​ макрос без знания​OK​, а затем нажмите​Если есть желание заняться​

​(VBA). Когда Вы​ пункт.​Выделить все​ макроса. Нажатие на​ с данными в​ рекомендуется начать с​​ это то же​​ освоить методы простого​​Guest​​ времени и возникновения​

​ Option Explicit введите​ электронной таблице.​
​ к конкретной ячейке​
​ языков программирования. Для​

​, чтобы начать запись​

office-guru.ru>

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

​ кнопку​​ программированием, Вы можете​ включаете режим записи​VBA-код обладает очень большими​, которая находится на​ неё даст тот​ документе Microsoft Office. ​ первого раздела учебника​ самое, что в​ ввода-вывода — работу​: Сам это читаю:​ ошибок. Далее рассмотрим,​ следующий код макроса:​Если вы запустите каждый​ в момент записи.​ этого существует макрорекодер,​ макроса.​OK​ объединить действия этих​ макроса, Excel фактически​ возможностями. В частности,​

​ пересечении заголовков строк​ же результат, что​Макросы способны выполнить практически​ и изучать их​ Access свойство Combobox1.RowSource​ с MsgBox, InputBox,​Помагает =)​ как создать макрос,​Sub MyMakros()​ процесс отдельно, вы​ Абсолютные адреса ограничивают​ который запускается с​Выполните действия, которые нужно​, чтобы начать запись​ двух макросов в​

​ записывает каждое сделанное​ он может выполнять​ и столбцов. Затем​ и включение записи​ любые действия в​ по порядку. Те,​ (ведь в Access​ Immediate Window, Debug.Print.​Serge​ который безошибочно и​Dim polzovatel As​ можете быстро просмотреть​

​ возможности макроса, если​

Процедура

​ помощью кнопки «Запись​​ автоматизировать, например ввод​ ​ макроса. ​ один – просто​​ Вами действие в​​ операции с файлами​ нажмите​ через меню.​ документе, которые Вы​ кто имеет опыт​ нет объекта Range,​

​Тогда ставьте себе​​: Читаю​ ​ автоматически выполнить рутинную​​ String​​ результаты для проверки​​ будут добавляться /​​ макроса».​​ стандартного текста или​​Выполните действия, которые нужно​ ​ скопировав код из​​ виде инструкций на​​ за пределами текущего​​Comma Style​​Теперь, когда режим записи​​ только можете пожелать.​​ в программировании на​ поэтому и источник​​ сначала какую-нибудь простую​​но ни хрена​ работу в один​ ​Dim data_segodnya As​ точности их выполнения.​ удаляться данные на​В этом режиме все​ ​ заполнение столбца данных.​​ автоматизировать, например ввод​​LoadData​​ языке VBA. По-простому,​​ документа. Например, макрос​

​(Формат с разделителями)​​ макроса включен, давайте​ ​ Вот некоторые из​ VBA, могут сразу​ данных для листа​ ​ задачу. Если алгоритм​ не помогает :(((​​ клик мышкой. Так​​ Date​​Если нельзя разбить длинный​​ листе Excel или​​ действия пользователя макрорекодер​​На вкладке​ стандартного текста или​​в начало кода​​ Excel пишет программный​ может удалять или​ ​ на вкладке​ займёмся нашей задачей.​ них (очень малая​ же перейти к​ назван по другому).​ ​ — не линейный,​Один уважаемый форумчанин​ же рассмотрим в​polzovatel = Application.UserName​

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

• ​ макрос на короткие​ список данных будет​ в Excel записывает,​Разработчик​

• ​ заполнение столбца данных. ​FormatData​ код вместо Вас.​

Процедура

​ изменять любые файлы​​Home​ ​ Первым делом, добавим​ часть):​ интересующим темам.​​Поэтому в приложении​​ нарисуйте его на​​ (The Prist) при​​ каких местах рабочей​data_segodnya = Now​ приложения, а требуется​ ​ становиться больше. Относительные​​ переводя на язык​​в группе​​На вкладке​​.​​Чтобы увидеть этот программный​​ в папке​ ​(Главная).​​ заголовки для итоговых​​Применять стили и форматирование.​​Часть 1: Оформление кода​​ к VBA Excel​​ бумажке. Пишите код​​ встрече говорил «кому-то​ книги Excel можно​​MsgBox «Макрос запустил​​ проверить его функциональность​

​ средства не привязывают​​ программирования VBA-код в​ ​Код​​Разработчик​​Урок подготовлен для Вас​​ код, нужно в​​Мои документы​ ​Далее, изменим внешний вид​​ данных. ​​Выполнять различные операции с​​Часть 2: Типы данных,​​ данную справку по​​ В СТАНДАРТНОМ МОДУЛЕ.​​ дано, кому-то нет».​ создавать и хранить​​ пользователь: » &​​ пошагово («отладкой»). Нажмите​ курсор к конкретному​ ​ автоматическом режиме. После​нажмите кнопку​щелкните​ командой сайта office-guru.ru​ ​ меню​​. По этой причине​​ заголовков столбцов и​​Далее, введите в ячейки​​ числовыми и текстовыми​​ переменные и константы​​ VBA Access нужно​

​ Отлаживайте.​​Склонен ему верить….​ ​ макросы. Как их​ polzovatel & vbNewLine​ клавишу F8 каждый​ ​ адресу ячейки.​ завершения записи мы​​Остановить запись​​Остановить запись​Источник: http://www.howtogeek.com/162975/geek-school-learn-how-to-use-excel-macros-to-automate-tedious-tasks/​Macros​ запускайте и разрешайте​​ строк:​​ формулы в соответствии​ данными. ​ ​Часть 3: Массивы​ применять осторожно.​Alex_ST​vikttur​ запустить и выполнить,​ ​ & data_segodnya​ раз, когда вы​По умолчанию в Excel​ получаем готовую программу,​

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

​.​.​Перевел: Антон Андронов​(Макросы) на вкладке​ выполнение макросов только​Жирное начертание шрифта.​

support.office.com>

Как работать с макросами в Excel 2010 без программирования кода

​ с названиями заголовков​Использовать внешние источники данных​Часть 4: Процедуры Function​Главное, чтобы модераторы​: блин…​: Читаю там, читаю​ а также как​End Sub​ хотите перейти к​ включен режим «Абсолют»,​ которая сама выполняет​

​Более подробное изучение макроса​Более подробное изучение макроса​Автор: Антон Андронов​View​ из источников, которым​Выравнивание по центру.​ (даны варианты формул​

​ (файлы баз данных,​ и Sub​ подольше не удаляли​обещанный файл не​ сям. В основном​ их максимально оптимизировать​Нажмите на кнопку в​ следующему шагу выполнения​ но вы можете​ те действия, которые​   ​   ​Примечание:​(Вид) кликнуть​ Вы доверяете. ​

​Заливка цветом.​ для англоязычной и​ текстовые документы и​Часть 5: Условные операторы​ файлик с весом​ приаттачил​ то, что нужно​

Применение VBA и макросов в Microsoft Excel

​ под свои потребности.​ редакторе «Run Macro»​ задачи. Процесс выполнения​ изменить его, включив​ выполнял пользователь при​При редактировании макроса можно​При редактировании макроса можно​ Мы стараемся как можно​View Macros​Чтобы запустить наш макрос,​И, наконец, настроим формат​ русифицированной версии Excel,​

​ т.д.)​Часть 6: Циклы​ в 125К. Пусть​NickolaDed​ в данный момент​Excel предоставляет большой и​ или клавишу F5​ программы останавливается, когда​ кнопку «Относительные ссылки»​ записи.​ немного изучить язык​ немного изучить язык​

​ оперативнее обеспечивать вас​(Макросы) и в​ форматирующий данные, откройте​

1. ​ итоговых значений.​ адреса ячеек –​
2. ​Создавать новый документ.​Часть 7: Операторы и​ побольше народу скачает,​
3. ​: Grant, общие принцепы​ для решения конкретной​ изобильный арсенал инструментов​ на клавиатуре. В​
4. ​ он видит ошибку.​ расположенную ниже под​Как записать готовый макрос​ программирования Visual Basic.​ программирования Visual Basic.​ актуальными справочными материалами​ открывшемся диалоговом окне​ файл шаблона, который​

​Вот так это должно​ всегда латинские буквы​Проделывать все вышеперечисленные действия​ встроенные функции​ попользуется.​ программирования закладываються в​ задачи или для​ для хранения и​ появившемся окне «Macros»​ Вы можете исправить​ кнопкой «Запись макроса»​ в Excel? Очень​

​Чтобы изменить макрос, на​

Как работать с макросами в Excel

​Чтобы изменить макрос, в​ на вашем языке.​ нажать​ мы создали в​ выглядеть в итоге:​ и цифры):​ в любой их​Часть 8: Объектная модель​

1 Правильные имена в макросах.

​Hugo​ школе в 10-11​ разбора простого примера.​ обработки огромного количества​ нажмите на кнопку​ ошибку, которую легко​ на панели инструментов​ просто:​ вкладке​ группе​ Эта страница переведена​Edit​ первой части этого​Если Вас все устраивает,​

​=SUM(B2:K2)​ комбинации. ​ Excel​: У меня тоже​ классе, когда изучаеться​ Читать просто так,​ информации с данными.​ «Run», чтобы посмотреть​ найти с помощью​ вкладки «Разработчик»:​На вкладке «Разработчик» нажимаем​разработчик​

2 Используйте относительные (не абсолютные) адреса ячеек

​Код​ автоматически, поэтому ее​(Изменить).​ урока. Если у​ остановите запись макроса.​или​Для примера возьмём самый​Часть 9: События в​ давно этот файл​ Basic или Pascal,​ как книгу, не​ Хотя для самой​ результат работы макроса.​ «отладки» или записать​Абсолютный отсчет ячеек, всегда​ кнопку «Запись макроса».​нажмите кнопку Макросы​на вкладке​

​ текст может содержать​Откроется окно​ Вас стандартные настройки​Поздравляем! Вы только что​=СУММ(B2:K2)​ обычный файл​ Excel​ есть. У него​ на этом этапе​

3 Всегда начинайте запись с курсором в A1

​ имеет смысла. Только​ универсальной аналитической программы​Примечание. Если в главном​ по-новому.​ ведется с исходного​В появившимся диалоговом окне​ , выберите имя​Разработчик​ неточности и грамматические​Visual Basic for Applications​ безопасности, то при​ самостоятельно записали свой​=AVERAGE(B2:K2)​CSV​Часть 10: Ошибки VBA​ есть одна фишка​ формируеться самое важное,​ разбор или реализация​ Excel – хранение​ меню отсутствует закладка​Каждый пользователь сталкивался с​ положения (адрес ячейки​ заполняем параметры макроса. ​ макроса и нажмите​нажмите кнопку​

​ ошибки. Для нас​, в котором мы​ открытии файла сверху​ первый макрос в​или​. Это простая таблица​Примеры по VBA​ — на моём​ это мышление(логическое и​ практических задач. Обалденная​ данных само по​ «РАЗРАБОТЧИК», тогда ее​ тем, что иногда​ А1) – до​ И нажимаем «ОК».​ кнопку​

4 Всегда перемещаться с клавиш направления в момент записи макроса

​Макросы​ важно, чтобы эта​ увидим программный код​ над таблицей появится​ Excel.​=СРЗНАЧ(B2:K2)​ 10х20, заполненная числами​Более подробное описание по​ рабочем компе не​

​ последовательное), которое помогает​ практика здесь, на​ себе менее интересно,​ необходимо активировать в​ в Excel не​ адреса курсора с​После завершения нажимаем на​изменить​, выделите имя макроса​

5 Создавайте макросы для конкретных небольших задач

​ статья была вам​ записанного нами макроса.​ предупреждение о том,​Чтобы использовать созданный макрос,​=MIN(B2:K2)​ от 0 до​ Excel VBA можно​ работает. Оглавление есть,​ тебе в дальнейшем​ форуме. Кто не​ чем возможность их​

​ настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту».​ найти подходящих инструментов,​ вашими данными. Если​ кнопку «Остановить запись»,​. Откроется редактор Visual​

​ и нажмите кнопку​ полезна. Просим вас​ Да, Вы правильно​ что запуск макросов​ нам нужно сохранить​или​ 100 с заголовками​ найти на сайте​ содержание не открывается.​ понять чего ты​ ленится, за пару​ обрабатывать, структурировать и​ В правом списке​ которые бы соответствовали​ вы сохранили ваш​ после чего макрос​ Basic.​

exceltable.com>

Как написать макрос в Excel на языке программирования VBA

​Изменить​ уделить пару секунд​ поняли, здесь этот​ отключен, и кнопка,​ документ Excel в​=МИН(B2:K2)​ для столбцов и​ Microsoft Office.​ Дома всё в​ хочешь и как​ месяцев вырастает над​ анализировать с презентацией​ «Основные вкладки:» активируйте​ потребностям. При всем​ макрос в книге​ будет автоматически сохранен.​

Написание макросов в Excel

​Обратите внимание на то,​. Запустится редактор Visual​ и сообщить, помогла​ код можно изменить​ чтобы включить их​ формате, который поддерживает​=MAX(B2:K2)​ строк. Наша задача​Урок подготовлен для Вас​ порядке.​ ты это можешь​

​ собой до неузнаваемости​ в отчетах. Для​ галочкой опцию «Разработчик»​ изобилии возможностей Excel​ личных макросов (рекомендуется​

​Для выполнения или редактирования​

1. ​ как в нем​ Basic.​ ли она вам,​ и даже создать​ выполнение. Так как​ макросы. Для начала​
2. ​или​ превратить этот набор​ командой сайта office-guru.ru​Почему — не​ реализовать. А уже​ :)​ этих целей служит​ и нажмите на​ иногда нельзя автоматизировать​ так и делать),​ записанного макроса нажимаем​ выглядят записанные действия.​Обратите внимание на то,​ с помощью кнопок​ новый макрос. Те​ шаблон мы сделали​ необходимо удалить все​=МАКС(B2:K2)​ данных в презентабельно​Источник: http://www.excelfunctions.net/Excel-VBA-Tutorial.html​ вникал.​ далее ты начинаешь​Serge​
3. ​ сильнейший аналитических инструмент​ кнопку ОК. ​ решения некоторых задач,​ то вы можете​ на кнопку «Макросы»​ Возможно, часть кода​ как в нем​​ внизу страницы. Для​
​ действия, которые мы​ самостоятельно и себе​
​ данные из созданной​=MEDIAN(B2:K2)​
​ отформатированную таблицу и​
​Перевел: Антон Андронов​
​Alex_ST​ спрашивать, вот я​: Виктор, чё-то не​ по обработке данных​
​​

4. ​ ведь совершенству нет​ использовать свою программу​ (или комбинацию клавиш​ будет понятной.​ выглядят записанные действия.​ удобства также приводим​ совершали с таблицей​ мы доверяем, то​

​ нами таблицы, т.е.​или​ сформировать итоги в​Автор: Антон Андронов​: Наверное, какая-то защита​ хочу сделать, допустим​ видно макросов made​ такой как «Сводные​Макросы позволяют автоматизировать процессы​ предела. Идеальное решение​

​ на других листах​

Возможности макросов в Excel

​ ALT+F8). Появится окно​Измените код, закройте редактор​ Возможно, часть кода​ ссылку на оригинал​ в этом уроке,​ нажимаем кнопку​ сделать из неё​=МЕДИАНА(B2:K2)​ каждой строке. ​Что такое Макрос?​ стоит чтобы программизмом​

​ выборку, это можно​ by vikttur, serge​ таблицы». Но и​ работы с документами​

​ – это предоставление​ с аналогичными данными.​ со списком записанных​ Visual Basic и​ будет понятной.​ (на английском языке).​ вполне можно записать​Enable Content​ пустой шаблон. Дело​Теперь выделите ячейки с​Как уже было сказано,​Создание макроса – практический​ дома занимался, а​ сделать через циклы​ 007 или Саша…​ его можно еще​

​ и не только…​ возможности пользователю самому​ Независимо от того,​ макросов и кнопками​ запустите макрос повторно.​Измените код, закройте редактор​Для автоматизации часто выполняемых​ с помощью автоматической​(Включить содержимое).​ в том, что​ формулами и скопируйте​ макрос – это​ пример​ не на работе​ или по средствам​Ленимся?​ более усовершенствовать с​ Они могут практически​ создавать свои специфические​ где ваш курсор​ для управления ими.​ Посмотрите, что произойдет.​

​ Visual Basic и​ в Microsoft Excel​ записи макроса в​Следующим шагом, мы импортируем​ в дальнейшем, работая​ их во все​ код, написанный на​Выполнение макроса в Excel​:-)​ встроенных функций в​vikttur​ помощью макросов. И​ одновременно выполнить тысячи​ инструменты. Для этого​ позиционируется, когда вы​С помощью макропрограмм можно​Узнайте о том, как​ запустите макрос повторно.​ задач можно записать​ Excel. Но более​

​ последний обновлённый набор​ с этим шаблоном,​ строки нашей таблицы,​ языке программирования VBA.​Заглянем под капот: Как​Guest​ том или ином​: Я — да.​ тогда возможности сводных​ инструментов за одну​ были созданы макросы.​ начинаете запись макроса!​ увеличить производительность труда​ создавать и запускать​ Посмотрите, что произойдет.​ макрос. Макрос представляет​ сложные макросы, с​ данных из файла​ мы будем импортировать​ потянув за маркер​ Но в Excel​ работает макрос?​: Смотрел… Познавательно =)​ языке и ищишь​Serge​

exceltable.com>

Как научится писать макросы?

​ таблиц не знают​​ операцию (даже по​
​Код макроса Excel написанный​ Даже если он​ пользователя в десятки​ макросы. Дополнительные сведения​Дополнительные сведения о создании​ собой действие (или​ тонко настроенной последовательностью​CSV​ в него самые​ автозаполнения. ​ Вы можете создать​Добавим ещё один шаг​Serge​ лучшие варианты! Спрашиваешь​: Та же беда​ границ.​ одному клику мышкой).​ на языке Visual​ уже находится в​ раз. Но чтобы​ см. в статье​ макросов см. в​ набор действий), которое​ и логикой действий​(на основе такого​ свежие и актуальные​После выполнения этого действия​ программу, не написав​ к нашей задаче…​: Здесь​ у форумчан, предлагаешь​ :)​
​grant84​ Таким образом расширяются​ Basic for Application​ ячейке A1, ваш​
​ использовать запись пользовательских​ Создание, выполнение, изменение​ статье Создание и​ можно выполнять любое​ требуют программирования вручную.​ файла мы создавали​ данные.​ в каждой строке​ и строчки кода,​Excel располагает мощнейшей, но​выкладывал ещё один​
​ свой вариант они​

​Alex_ST​​: Здравствуйте уважаемые планетяне.​
​ возможности работы с​

​ (VBA), а его​​ первый макрос лучше​
​ макросов на все​ и удаление макроса.​

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

​ должны появиться соответствующие​​ что мы и​ в то же​ справочник.​ тебе свой если​: Ребята, если вы​По роду деятельности​ программой.​ выполняет инструмент приложения,​ записывать после нажатия​ 100% следует соблюдать​Макросы позволяют существенно расширить​Сведения о запуске макросов​ создании макроса записываются​ файл с данными​При выполнении импорта данных​ от данных, щёлкните​ итоговые значения.​ сделаем прямо сейчас.​

​ время очень редко​​Посмотрите, может пригодится.​ есть более эффективный.​ хоть когда-нибудь на​ мне приходится обрабатывать​
​Читайте также: Как работать​

​ к которому он​​ клавиш должны быть​

​ простые правила, которые​​ возможности в программе​ см. в статье​

​ щелчки мышью и​​data.csv​ из CSV-файла, возможно,​ правой кнопкой мыши​Далее, мы подведем итоги​Чтобы создать макрос, откройте​ используемой, возможностью создавать​
​Alex_ST​слэн​ чём-нибудь программировали, то​ большие объемы цифр​ с макросами в​ присоединен. Большинство этих​ Ctrl + Home.​ существенно влияют на​ Excel. Они автоматизируют​ Запуск макроса.​
​ нажатия клавиш. После​создаётся автоматически каким-то​ Excel попросит Вас​ по иконке​ для всей таблицы,​View​ автоматически выполняющиеся последовательности​: Бегло просмотрел…​: начинал с чтения​ должны представлять себе​ и без excel​ Excel 2010 без​ инструментов не доступно​Пример: Представьте себе, что​ их качество в​ рабочие процессы и​Действия перед записью макроса​
​ создания макроса его​ процессом и сохраняется​ настроить некоторые параметры​
​Выделить все​ для этого делаем​(Вид) >​ действий с помощью​Вроде ничего так​ справки — хватило​ общие принципы построения​
​ тут не обойтись.​ программирования кода​ на уровне окна​ каждый месяц вы​ момент записи и​ берут большую часть​   ​ можно отредактировать, чтобы​ на диске всегда​
​ для правильной передачи​, которая находится на​ ещё несколько математических​Macros​ макросов. Макрос –​ справочник. Может пригодиться​ на написание макроса​ алгоритмов программ. ​

​ Однако операции во​​С помощью макросов пользователь​
​ программы Excel. Как​ получаете десятки таблиц​

​ эффективность при выполнении.​​ рутинной работы пользователя​Убедитесь в том, что​ изменить выполняемые им​ в одном и​ данных в таблицу.​ пересечении заголовков строк​ действий:​(Макросы) >​ идеальный выход, если​ когда-нибудь. Сохраню себе​ по утыриванию пароля​Поверьте, VB(А) на​ многом похожи друг​ может сам создать​ написать макрос.​ из всех филиалов.​​ на себя. Просто​ на ленте отображается​ действия.​ том же месте.​Когда импорт будет закончен,​ и столбцов, и​Соответственно:​Record Macro​ Вы имеете дело​ в «копилку» на​ у начальника :)​

​ среднем уровне -​​ на друга и​ свой инструмент, которого​Теперь продемонстрируем на примере​ От вас требуется​5 простых советов, которые​

​ нужно научится пользоваться​ вкладка​Допустим, что необходимо каждый​

​ Например,​ зайдите в меню​ из контекстного меню​

​=SUM(L2:L21)​​(Запись макроса…)​ с однотипной задачей,​ всякий случай. ​потом прочитал уокенбаха​
​ это очень просто.​ появляется желание некоторые​

​ ему недостает в​​ информацию о том,​ организовать данные и​
​ помогут в создании​ макросами и производительность​Разработчик​

​ месяц создавать отчет​C:\Data\data.csv​Macros​ выберите пункт​или​Дайте своему макросу имя​ которая повторяется множество​Спасибо, Серж.​ — сильно продвинуло,​
​ Есть набор общих​ из них автоматизировать.​ арсенале программы Excel.​ как писать, редактировать​ рассчитать показатели, чтобы​ макросов без программирования.​ труда возрастет в​. По умолчанию вкладка​ для главного бухгалтера.​– путь к​
​(Макросы) на вкладке​Delete​=СУММ(L2:L21)​ (без пробелов) и​ раз. Например, обработка​Guest​ совершенствуюсь на форуме..​ для всех языков​ Некоторые требования можно​ Например, когда нужно​
​ и выполнять код​ произвести еще один​ Воспользуйтесь этими простыми​ десятки раз!​Разработчик​
​ Требуется выделить красным​ файлу с обновляемыми​View​(Удалить). ​=AVERAGE(B2:K21)​ нажмите​

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

​(Вид) и выберите​​Теперь наш лист полностью​или​ОК​ документов по стандартизированному​
​ есть ответ на​

​ этого программировал на​​ операторов и правил.​

​ формул, но не​​ вторую строку одним​
​Чтобы написать макрос:​ можете записать макрос​
​ быстро и просто​

​ быть программистом и​​ необходимо выполнить указанные​
​ шрифтом имена клиентов​ этого файла и​ команду​ очищен от всех​=СРЗНАЧ(B2:K21)​
​.​

​ шаблону. При этом​​ многие вопросы​ с++..​ Эти функции, операторы​

planetaexcel.ru>

​ все. Кое что​

Учебник для начинающих по написанию макросов VBA в Excel (и почему вы должны учиться)

Макросы Excel могут сэкономить массу времени за счет автоматизации часто используемых процессов Excel

, Но макросы на самом деле довольно ограничены. С инструментом записи легко ошибиться, а процесс записи неудобен.

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

Начнем с основ.

Что такое VBA?

VBA — это Visual Basic для приложений, язык программирования, который вы можете использовать во многих приложениях Microsoft. Visual Basic — это язык программирования, а VBA — его версия для конкретного приложения. (Microsoft прекратила выпуск Visual Basic еще в 2008 году, но VBA продолжает развиваться).

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

Тем не менее, VBA требует времени, чтобы привыкнуть.

Преимущества макросов VBA в Excel

Если VBA сложнее, чем запись макроса, зачем вам его использовать? Короткий ответ: вы получаете гораздо больше возможностей от макросов VBA.

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

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

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

Давайте посмотрим на простой макрос VBA, чтобы увидеть, как он работает.

Пример макроса VBA в Excel

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

Этот макрос добавит квартальные продажи из каждого магазина и запишет эти итоги в ячейки электронной таблицы (если вы не уверены, как получить доступ к диалоговому окну VBA, ознакомьтесь с кратким описанием VBA здесь):

Sub StoreSales()
Dim Sum1 As Currency
Dim Sum2 As Currency
Dim Sum3 As Currency
Dim Sum4 As Currency
For Each Cell In Range("C2:C51")
Cell.Activate
If IsEmpty(Cell) Then Exit For
If ActiveCell.Offset(0, -1) = 1 Then
Sum1 = Sum1 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 2 Then
Sum2 = Sum2 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 3 Then
Sum3 = Sum3 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 4 Then
Sum4 = Sum4 + Cell.Value
End If
Next Cell
Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4
End Sub

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

Объявление Sub

В начале модуля у нас есть «Sub StoreSales ()». Это определяет новую подпрограмму под названием StoreSales.

Вы также можете определять функции — разница в том, что функции могут возвращать значения, а подпрограммы не могут (если вы знакомы с другими языками программирования, подпрограммы эквивалентны методам). В этом случае нам не нужно возвращать значение, поэтому мы используем подпрограмму.

В конце модуля у нас есть «End Sub», который сообщает Excel, что мы закончили с этим макросом VBA.

Объявление переменных

Все первые строки кода в нашем скрипте начинаются с «Dim». Dim — команда VBA для объявления переменной.

Таким образом, «Dim Sum1» создает новую переменную под названием «Sum1». Однако нам нужно сообщить Excel, что это за переменная. Нам нужно выбрать тип данных. В VBA существует много разных типов данных, полный список которых можно найти в справочных документах Microsoft.

Поскольку наш макрос VBA будет работать с валютами, мы используем тип данных Currency.

Оператор «Dim Sum1 As Currency» указывает Excel создать новую переменную Currency с именем Sum1. Каждая переменная, которую вы объявляете, должна иметь оператор «As», чтобы сообщить Excel ее тип.

Начиная для цикла

Циклы являются одними из самых мощных вещей, которые вы можете создать на любом языке программирования. Если вы не знакомы с циклами, посмотрите это объяснение циклов Do-While.

, В этом примере мы используем цикл For, который также рассматривается в статье.

Вот как выглядит цикл:

For Each Cell in Range("C2:C51")
[a bunch of stuff]
Next Cell

Это говорит Excel, чтобы перебирать ячейки в указанном диапазоне. Мы использовали объект Range

, который является конкретным типом объекта в VBA. Когда мы используем его таким образом — Range («C2: C51») — это говорит Excel, что мы заинтересованы в этих 50 ячейках.

«Для каждого» говорит Excel, что мы собираемся что-то сделать с каждой ячейкой в ​​диапазоне. «Следующая ячейка» идет после всего, что мы хотим сделать, и говорит Excel начать цикл с начала (начиная со следующей ячейки).

У нас также есть следующее утверждение: «Если IsEmpty (Cell), то Exit For».

Можете ли вы угадать, что он делает?

Замечания: Строго говоря, использование цикла While могло бы быть лучшим выбором

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

If-Then-Else Заявления

Ядро этого конкретного макроса находится в операторах If-Then-Else. Вот наша последовательность условных выражений:

If ActiveCell.Offset(0, -1) = 1 Then
Sum1 = Sum1 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 2 Then
Sum2 = Sum2 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 3 Then
Sum3 = Sum3 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 4 Then
Sum4 = Sum4 + Cell.Value
End If

По большей части, вы можете догадаться, что делают эти заявления. Возможно, вы не знакомы с ActiveCell.Offset. «ActiveCell.Offset (0, -1)» указывает Excel взглянуть на ячейку, которая находится в одном столбце слева от активной ячейки.

В нашем случае это говорит Excel о необходимости обратиться к колонке с номером магазина. Если Excel находит 1 в этом столбце, он берет содержимое активной ячейки и добавляет его в Sum1. Если он находит 2, он добавляет содержимое активной ячейки в Sum2. И так далее.

Excel проходит все эти утверждения по порядку. Если условное утверждение

удовлетворен, он завершает утверждение Тогда. Если нет, он переходит к следующему ElseIf. Если он дойдет до конца и ни одно из условий не будет выполнено, он не предпримет никаких действий.

Комбинация цикла и условных выражений — это то, что движет этим макросом. Цикл предписывает Excel пройти через каждую ячейку в выделении, а условные выражения сообщают ему, что делать с этой ячейкой.

Запись значений ячеек

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

Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4

Используя «.Value» и знак равенства, мы устанавливаем в каждой из этих ячеек значение одной из наших переменных.

И это все! Мы сообщаем Excel, что закончили написание этого Sub с «End Sub», и макрос VBA завершен.

Когда мы запускаем макрос с макрос кнопка в разработчик На вкладке мы получаем наши суммы:

Собираем строительные блоки VBA в Excel

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

Но с практикой вы создадите свой словарный запас VBA и сможете писать макросы быстрее, точнее и с гораздо большей мощностью, чем вы могли бы когда-либо записывать.

Когда вы застряли, запуск поиска Google — это быстрый способ получить ответы на ваши вопросы VBA. А справочник Microsoft по VBA для Excel может быть полезен, если вы хотите найти технический ответ.

Когда вы освоитесь с основами, вы можете начать использовать VBA для таких вещей, как отправка электронных писем из Excel, экспорт задач Outlook.

и отображение информации о вашем ПК

,

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

Введение

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

Макрос — это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых  нужных нам действий, которые нам не хочется выполнять вручную.

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP…), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.

• В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис — Макрос — Редактор Visual Basic (Toos — Macro — Visual Basic Editor).
• В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer). Выбираем Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer). Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic (Visual Basic Editor)
  
  :

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией  Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

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

• Модуль Эта книга — также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

• Модуль листа — доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа — команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

• Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
• Любой макрос должен заканчиваться оператором End Sub.
• Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

• Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
• Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
• Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

• в Excel 2003 и старше — выбрать в меню Сервис — Макрос — Начать запись (Tools — Macro — Record New Macro)
• в Excel 2007 и новее — нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

• Имя макроса — подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
• Сочетание клавиш — будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис — Макрос — Макросы — Выполнить (Tools — Macro — Macros — Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
• Сохранить в… — здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
• Эта книга — макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
• Новая книга — макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
• Личная книга макросов — это специальная книга Excel  с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording).

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или — в старых версиях Excel — через меню Сервис — Макрос — Макросы (Tools — Macro — Macros):

• Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).
• Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
• Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис — Настройка (Tools — Customize) и перейдите на вкладку Команды (Commands). В категории Макросы легко найти веселый желтый «колобок» — Настраиваемую кнопку (Custom button):

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar):

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

• В Excel 2003 и старше — откройте панель инструментов Формы через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms)
• В Excel 2007 и новее — откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer) 

Выберите объект Кнопка (Button):

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

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

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция — только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert — Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка — Функция) в категории Определенные пользователем (User Defined):

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

 

 

 

Запуск и создание макросов в Excel для чайников

VBA коды для простых программ макросов необходимых при работе с таблицами на каждый день.

Создание и выполнение макросов

Макрос для копирования листа в Excel c любым количеством копий.
Практический пример с описанием и исходным кодом макроса для одновременного копирования любого количества рабочих листов. Макрос для выделения и удаления пустых столбцов в Excel.
Выделение, удаление, скрытие и добавление пустых столбцов в таблицу по условию пользователя с помощью макросов. Исходные коды предоставляться с описанием и примерами в картинках. Макрос для выделения и удаления пустых строк в Excel.
Примеры макросов с исходными кодами для удаления, выделения, добавления и скрытия пустых строк в исходной таблице данных.  Макрос для выделения ячеек со снятой защитой на листе Excel.
Исходный код с примером и описанием для VBA-макроса выделения ячеек у которых, снятая защита от ввода значений данных и редактирования. Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием.
Исходный VBA-код макроса для поиска выпадающих списков на рабочем листе. Исходный код VBA-макроса для поиска ячеек с условным форматированием. Макрос для выделения ячеек Excel по условию больше меньше.
2 исходных кода макросов для выделения ячеек по условию больше равно или меньше равно относительно их числовых значений. Макрос для выборочного выделения ячеек на листе Excel.
Исходный код макроса для автоматического выделения отдельных ячеек по заданному условию в критериях поиска значений. Как быстро выделять несмежные диапазоны макросом? Макрос для добавления строк с заданной высотой в таблицу Excel.
Пошаговое руководство по написанию кода макроса VBA для умной вставки пустых строк между ячейками таблицы. Как автоматически вставить строки через одну ячейку? Как сделать границы ячеек макросом в таблице Excel.
Как автоматически рисовать границы в таблицах с помощью макроса? Автоматизированное форматирование границ ячеек по разным цветам, стилям и толщине линий используя макрос. Макрос для объединения повторяющихся ячеек в таблице Excel.
Практический пример и пошаговый разбор исходного VBA-кода макроса для автоматического объединения повторяющихся одинаковых ячеек в строках таблицы. 1 2 3 4

Как написать макрос в Excel на языке программирования VBA

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

Написание макросов в Excel

Код макроса Excel написанный на языке Visual Basic for Application (VBA), а его выполняет инструмент приложения, к которому он присоединен. Большинство этих инструментов не доступно на уровне окна программы Excel. Как написать макрос.

Теперь продемонстрируем на примере информацию о том, как писать, редактировать и выполнять код макроса.

Чтобы написать макрос:

1. Откройте рабочую книгу Excel, в которой необходимо использовать макрос: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Или нажмите комбинацию горячих клавиш ALT+F11.
2. Перед началом работы в редакторе следует сделать простую настройку. Выберите инструмент в редакторе Visual Basic: «Tools»-«Options». И на вкладке «Editor» активируйте опцию «Require Variable Declaration». Это позволит реализовать автоматическое заполнение инструкций Options Explicit в начале каждого ново созданного кода. А в поле ввода «Tab Width:» укажите значение 2 вместо 4-х. Это позволит уменьшить ширину кода. Данная настройка редактора распространяется на все листы, но в границах одной рабочей книги.
3. Выберите инструмент: «Insert»-«Module» чтобы создать новый стандартный модуль для макросов. В появившемся окне модуля под текстом Option Explicit введите следующий код макроса:

Sub MyMakros()
Dim polzovatel As String
Dim data_segodnya As Date
polzovatel = Application.UserName
data_segodnya = Now
MsgBox "Макрос запустил пользователь: " & polzovatel & vbNewLine & data_segodnya
End Sub


4. Нажмите на кнопку в редакторе «Run Macro» или клавишу F5 на клавиатуре. В появившемся окне «Macros» нажмите на кнопку «Run», чтобы посмотреть результат работы макроса.

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.



Возможности макросов в Excel

Макросы позволяют автоматизировать процессы работы с документами и не только… Они могут практически одновременно выполнить тысячи инструментов за одну операцию (даже по одному клику мышкой). Таким образом расширяются возможности работы с программой.

Читайте также: Как работать с макросами в Excel 2010 без программирования кода

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

Если бы не было возможности создавать макросы во всех программах, входящих в пакет MS Office. То множество операций в процессе рутинной работы пользователям приходилось бы выполнять вручную (выделять через одну строку кликая по каждому второму заголовку мышкой или копировать вставлять по одному листу). Ручная работа в лучшем случаи приводить к потере огромного количества времени, а в худшем – ведет к ошибкам или даже потере ценных данных.

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

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

Создание макросов в excel самоучитель с примерами

Что такое макрос?

Для начала немного о терминологии.

Макрос – это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.

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

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

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки «Разработчик» в ленте меню

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

1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
3. Нажмите «ОК».

В результате на ленте меню появится вкладка «Разработчик»

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

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».

Вот шаги для записи такого макроса:

1. Перейдите на вкладку «Разработчик».
2. В группе «Код» нажмите кнопку «Запись макроса». Откроется одноименное диалоговое окно.
   
3. В диалоговом окне «Запись макроса» введите имя для своего макроса, например «ВводТекста». Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. Например, вы не можете использовать пробелы между ними. Обычно я предпочитаю сохранять имена макросов как одно слово, с разными частями с заглавным первым алфавитом. Вы также можете использовать подчеркивание для разделения двух слов – например, «Ввод_текста».
   
4. Если вы хотите, то можете задать сочетание клавиш. В этом случае мы будем использовать ярлык Ctrl + Shift + N. Помните, что сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос).
   
5. В поле «Сохранить в» убедитесь, что выбрана опция «Эта книга». Этот шаг гарантирует, что макрос является частью рабочей книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь файлом с кем-то.
   
6. Введите описание при необходимости. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем не забыть что делает макрос.
7. Нажмите «ОК». Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку «Остановить запись» на вкладке «Разработчик», которая указывает, что выполняется запить макроса.
8. Выберите ячейку A2.
9. Введите текст «Excel» (или вы можете использовать свое имя).
10. Нажмите клавишу Enter. Вы попадете на ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».
    

Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.

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

1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет.
2. Выберите любую ячейку – кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет.
3. Перейдите на вкладку «Разработчик».
4. В группе «Код» нажмите кнопку «Макросы».
5. В диалоговом окне «Макрос» щелкните макрос «ВводТекста».
6. Нажмите кнопку «Выполнить».
   

Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь перейдем к редактору кода и посмотрим что у нас получилось.

Вот шаги по открытию редактора VB в Excel:

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

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

• Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
• Панель инструментов – похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
• Окно проектов (Project Explorer) – здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
• Окно кода – собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
• Окно свойств – вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
• Immediate Window (окно предпросмотра) – На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».

Когда мы записали макрос «ВводТекста», в редакторе VB произошли следующие вещи:

• Был добавлен новый модуль.
• Макрос был записан с именем, которое мы указали – «ВводТекста»
• В окне кода добавлена новая процедура.

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, который записан макрорекодером:

В VBA, любая строка , которая следует за ‘ (знак апострофа) не выполняется. Это комментарий, который предназначен только для информационных целей. Если вы удалите первые пять строк этого кода, макрос по-прежнему будет работать.

Теперь давайте пробежим по каждой строке кода и опишем что и зачем.

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub – сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.

• Range(«A2»).Select – эта строка выбирает ячейку A2.
• ActiveCell.FormulaR1C1 = «Excel» – эта строка вводит текст «Excel» в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
• Range(«A3»).Select – выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.

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

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

Абсолютная и относительная запись макроса

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст «Excel», то каждый раз – независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст «Excel» в ячейку A2.

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст «Excel» и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

1. Выберите ячейку A1.
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Относительные ссылки». Он будет подсвечиваться, указывая, что он включен.
   
4. Нажмите кнопку «Запись макроса».
   
5. В диалоговом окне «Запись макроса» введите имя для своего макроса. Например, имя «ОтносительныеСсылки».
   
6. В опции «Сохранить в» выберите «Эта книга».
7. Нажмите «ОК».
8. Выберите ячейку A2.
9. Введите текст «Excel» (или другой как вам нравится).
10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Макросы».
4. В диалоговом окне «Макрос» кликните на сохраненный макрос «ОтносительныеСсылки».
5. Нажмите кнопку «Выполнить».

Как вы заметите, макрос записал текст «Excel» не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel – ячейка B4 и в конечном итоге выберет ячейку B5.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

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

• Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
• Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
• Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
• Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

До Excel 2007 был достаточен один формат файла – .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете «Нет», Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете «Да», Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

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

Например, самостоятельно написанный макрос можно привязать к иконке и вывести на Ленту меню. Либо Вы можете создать пользовательскую функцию (UDF) и использовать ее точно так же, как и остальные встроенные функции Excel.

Макрос — это компьютерный код, написанный для Excel на языке программирования Visual Basic for Applications (VBA). Базовые понятия языка программирования VBA рассматриваются на нашем сайте в Учебнике по VBA. Однако прежде чем приступить к написанию кода VBA, рекомендуем познакомиться с уроками, в которых рассматривается безопасность макросов Excel и редактор Visual Basic.

Настройка разрешения для использования макросов в Excel

В Excel предусмотрена встроенная защита от вирусов, которые могут проникнуть в компьютер через макросы. Если хотите запустить в книге Excel макрос, убедитесь, что параметры безопасности настроены правильно.

Редактор Visual Basic

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

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

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

Учебник Excel VBA

Для тех, кто только начинает осваивать язык программирования Excel VBA, предлагаем небольшой вводный курс по Visual Basic for Applications.

Введение

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

Макрос – это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP. ), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно – редактор программ на VBA, встроенный в Microsoft Excel.

• В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис – Макрос – Редактор Visual Basic(Toos – Macro – Visual Basic Editor).
• В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл – Параметры – Настройка ленты (File – Options – Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
  
  :

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

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

Модуль Эта книга – также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

Модуль листа – доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа – команда Исходный текст (View Source) . Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

• Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
• Любой макрос должен заканчиваться оператором End Sub.
• Все, что находится между Sub и End Sub – тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

• Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу – запись останавливается.
• Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
• Если во время записи макроса макрорекордером вы ошиблись – ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) – во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

• в Excel 2003 и старше – выбрать в меню Сервис – Макрос – Начать запись(Tools – Macro – Record New Macro)
• в Excel 2007 и новее – нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

• Имя макроса – подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
• Сочетание клавиш – будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис – Макрос – Макросы – Выполнить(Tools – Macro – Macros – Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
• Сохранить в. – здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:

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

После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или – в старых версиях Excel – через меню Сервис – Макрос – Макросы (Tools – Macro – Macros) :

• Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
• Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
• Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис – Настройка (Tools – Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый «колобок» – Настраиваемую кнопку (Custom button) :

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

• В Excel 2003 и старше – откройте панель инструментов Формы через меню Вид – Панели инструментов – Формы (View – Toolbars – Forms)
• В Excel 2007 и новее – откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)

Выберите объект Кнопка (Button) :

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

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

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция – только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert – Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка – Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

VBA в Excel — макросы Easy Excel

VBA ( Visual Basic для приложений) — это язык программирования Excel и других программ Office.

1 Создание макроса: с помощью Excel VBA вы можете автоматизировать задачи в Excel, написав так называемые макросы. В этой главе вы узнаете, как создать простой макрос .

2 MsgBox: MsgBox — это диалоговое окно в Excel VBA, которое вы можете использовать для информирования пользователей о вашей программе.

3 Объект Workbook и Worksheet: дополнительные сведения об объекте Workbook и Worksheet в Excel VBA.

4 Объект Range: Объект Range, представляющий ячейку (или ячейки) на листе, является наиболее важным объектом Excel VBA.

5 Переменные. В этой главе рассказывается, как объявлять, инициализировать и отображать переменную в Excel VBA.

6 Оператор If Then: используйте оператор If Then в Excel VBA для выполнения строк кода, если выполняется определенное условие.

7 Цикл: Цикл — один из самых мощных методов программирования. Цикл в Excel VBA позволяет перебирать диапазон ячеек с помощью всего нескольких строк кода.

8 Макро-ошибки. В этой главе рассказывается, как работать с макрокомандами в Excel.

9 Манипулирование строками. В этой главе вы найдете наиболее важные функции для управления строками в Excel VBA.

10 Дата и время: узнайте, как работать с датой и временем в Excel VBA.

11 События: События — это действия, выполняемые пользователями, которые запускают Excel VBA для выполнения кода.

12 Массив: массив — это группа переменных. В Excel VBA вы можете ссылаться на конкретную переменную (элемент) массива, используя имя массива и номер индекса.

13 Функция и подпрограмма: в Excel VBA функция может возвращать значение, а подпрограмма — нет.

14 Объект приложения: Мать всех объектов — это сам Excel.Мы называем это объектом Application. Объект приложения предоставляет доступ ко многим параметрам, связанным с Excel.

15 элементов управления ActiveX: узнайте, как создавать элементы управления ActiveX, такие как командные кнопки, текстовые поля, списки и т. Д.

16 Пользовательская форма: в этой главе рассказывается, как создать пользовательскую форму Excel VBA.

Создание макросов за 7 простых шагов

Отслеживаете ли вы, какая часть времени, которое вы тратите на работу с Excel, уходит на небольшие и относительно несущественные, но повторяющиеся задачи?

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

В большинстве (не во всех) случаях затраты времени на эти общие, но повторяющиеся операции не дают пропорциональных результатов. Фактически, большинство из них являются прекрасными примерами принципа 80/20 в действии. Они являются частью большинства усилий, которые мало влияют на результат.

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

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

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

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

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

Точнее, в этом руководстве по Excel я покажу вам, как настроить макрос, который выполняет следующие действия:

• Введите «Это лучший учебник по Excel» в активную ячейку.
• Автоматически подогнать под ширину столбца активной ячейки, чтобы набранный текст поместился в один столбец.
• Раскрасьте активную ячейку в красный цвет.
• Измените цвет шрифта активной ячейки на синий.

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

Семи шагов, которые я объясню ниже, достаточно, чтобы настроить вас на пути к созданию базовых макросов Excel .

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

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

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

Вы готовы создать свой первый макрос Excel?

Тогда приступим к подготовке…

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

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

В Excel большинство полезных команд при работе с макросами Excel и Visual Basic для приложений находятся на вкладке «Разработчик».

Вкладка «Разработчик» по умолчанию скрыта в Excel. Поэтому, если вы (или кто-то другой) не добавили вкладку «Разработчик» на ленту, вам нужно заставить Excel отображать ее, чтобы иметь доступ к соответствующим инструментам при настройке макроса.

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

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

1. Шаг №1.

Откройте диалоговое окно «Параметры Excel» одним из следующих способов:

• Метод №1.
  
  Шаг № 1: С помощью мыши щелкните ленту правой кнопкой мыши.

  Шаг № 2: Excel отображает контекстное меню.

  Шаг № 3: Щелкните «Настроить ленту…».

  Следующее изображение иллюстрирует эти 3 шага:

• Метод №2.

  Шаг № 1: Щелкните вкладку ленты файлов.

  Шаг № 2: На панели навигации, расположенной в левой части экрана, щелкните «Параметры».

  На следующем изображении показано, как это сделать:

• Метод №3.

  Используйте сочетания клавиш, такие как «Alt + T + O» или «Alt + F + T».

2. Шаг №2.

Находясь в диалоговом окне «Параметры Excel», убедитесь, что вы находитесь на вкладке «Настройка ленты», щелкнув эту вкладку на панели навигации, расположенной слева.

3. Шаг №3.

Взгляните на список «Настроить ленту», который находится в правой части диалогового окна «Параметры Excel», и найдите «Разработчик».

Это вкладка «Разработчик», которая по умолчанию является третьей вкладкой снизу списка (чуть выше «Надстройки» и «Удаление фона»).

Поле слева от «Разработчик» по умолчанию пусто. В этом случае вкладка Разработчик не отображается на ленте. Если в этом поле установлен флажок, на ленте отображается вкладка Разработчик.

4. Шаг 4.

Если поле слева от «Разработчик» пусто, щелкните по нему, чтобы поставить галочку.

Если в поле уже стоит галочка, ничего делать не нужно (у вас уже должна быть вкладка «Разработчик» на ленте).

5. Шаг 5.

Нажмите кнопку «ОК» в правом нижнем углу диалогового окна «Параметры Excel».

Excel вернет вас к рабочему листу, над которым вы работали, и на ленте появится вкладка «Разработчик».

Как включить вкладку разработчика в изображениях

На изображении ниже вы шаг за шагом выполните описанный выше процесс:

Инструменты для создания макросов Excel

Excel позволяет создавать макросы с помощью любого из следующих инструментов:

• Регистратор макросов, позволяющий записывать выполняемые вами действия в книгу Excel.
• Редактор Visual Basic, требующий написания инструкций, которым должен следовать Excel, на языке программирования Visual Basic для приложений.

Второй вариант (требующий программирования) более сложен, чем первый, особенно если вы новичок в мире макросов и у вас нет опыта программирования.

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

Как объяснил Джон Уокенбах (один из ведущих авторитетов в Microsoft Excel) в Библии Excel 2013 , если ваша цель состоит только в записи и воспроизведении макросов:

(…) вам не нужно беспокоиться о самом языке (хотя базовое понимание того, как все работает, не вредит).

Однако, если вы хотите по максимуму использовать макросы Excel и полностью использовать их возможности, вам в конечном итоге потребуется изучить VBA.Как говорят г-н Эксель (Билл Джелен) (еще один из выдающихся мастеров Excel) и Трейси Сирстад (консультант по Excel и Access) в Excel 2013 VBA и Macros , запись макроса полезна, когда вы новичок и не имеете опыта в макропрограммировании, но…

(…) по мере того, как вы приобретаете больше знаний и опыта, вы начинаете записывать строки кода реже.

Поэтому я более подробно освещаю некоторые темы, связанные с Visual Basic для приложений, в других руководствах.

Однако на данный момент я объясню ниже, как можно записать макрос Excel с помощью рекордера:

7 простых шагов для создания вашего первого макроса

ОК…

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

Вы готовы создать свой первый макрос Excel . Для этого просто выполните 7 простых шагов, которые я объясню ниже.

1. Шаг №1.

Щелкните вкладку Разработчик.

2. Шаг №2.

Убедитесь, что запись относительных ссылок включена, установив флажок «Использовать относительные ссылки».

Если запись относительных ссылок не включена, как показано на скриншоте ниже, нажмите «Использовать относительные ссылки».

Если включена запись относительного эталона, как на скриншоте ниже, вам не нужно ничего щелкать.

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

Когда относительная запись выключена (что по умолчанию), записываются абсолютные / точные адреса ячеек. Когда запись относительных ссылок включена, любые действия, записываемые Excel, относятся к активной ячейке. Другими словами, абсолютная запись, как объяснил Билл Джелен в Excel 2013 in Depth , «чрезвычайно буквальна».

Например, предположим, что вы записываете макрос Excel, который:

• Введите «Это лучший учебник по Excel» в активную ячейку.
• Копирует только что набранный текст и вставляет его в ячейку непосредственно под ним.

Если во время записи активной ячейкой является A3, и вы не можете включить относительную запись, макрос записывает, что она должна:

• Введите «Это лучший учебник по Excel» в активной ячейке.
• Скопируйте текст и вставьте его в ячейку A4 , которая находится непосредственно под активной ячейкой в ​​момент начала записи макроса.

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

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

3. Шаг №3.

Щелкните «Запись макроса» на вкладке «Разработчик» или кнопку «Записать макрос», которая отображается в левой части строки состояния.

4.Шаг 4.

Появится диалоговое окно «Запись макроса». Этот диалог позволяет вам:

• Присвойте имя макросу.

  Excel присваивает макросам имя по умолчанию: «Макрос1», «Макро2», «Макро3» и так далее. Однако, как объяснил Джон Уокенбах в книге Excel VBA Programming for Dummies , , обычно более полезно использовать описательное имя .

  Основные правила для имен макросов заключаются в том, что они должны начинаться с буквы или символа подчеркивания (_) (не числа), не могут содержать пробелов или специальных символов, кроме подчеркивания (что разрешено), и не должны конфликтовать с ранее существовавшие имена.Я подробно освещаю тему именования макросов здесь (для процедур Sub) и здесь (для процедур Function).

  Например, «Лучшее руководство по Excel» не является приемлемым названием, но «Best_Excel_Tutorial» работает:

• Назначьте макросу сочетание клавиш.

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

  Назначаемое сочетание клавиш имеет вид «Ctrl + комбинация клавиш».В этом контексте комбинация клавиш означает либо (i) как отдельную букву, либо (ii) как комбинацию буквы и клавиши Shift.

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

  Если вы выберете ранее назначенную комбинацию клавиш (например, встроенную комбинацию клавиш), выбранная вами комбинация клавиш для макроса Excel отменяет и отключает ранее существовавшую комбинацию клавиш.Поскольку в Excel есть несколько встроенных сочетаний клавиш в виде «Ctrl + Letter», риск отключения встроенных сочетаний клавиш не так уж и мал.

  Возьмем, к примеру, сочетание клавиш «Ctrl + B», которое является встроенным сочетанием клавиш для команды Полужирный шрифт.

  Однако, если вы назначите сочетание клавиш «Ctrl + B» для определенного макроса, встроенное сочетание клавиш для команды «Полужирный» будет отключено. Как следствие, если вы нажмете «Ctrl + B», макрос будет выполнен, но шрифт выделенного текста не будет выделен жирным шрифтом.

  Один из способов решения этой проблемы, который обычно работает, — назначить сочетания клавиш в форме «Ctrl + Shift + Letter». Риск перезаписи и отключения ранее существовавшего сочетания клавиш меньше, но в любом случае я предлагаю вам перейти к , будьте осторожны с точной комбинацией клавиш, которую вы выбираете .

  Это означает, что, например, вместо выбора «Ctrl + B» в качестве сочетания клавиш мы могли бы назначить «Ctrl + Shift + B»:

• Решите, где вы хотите сохранить макрос.

  Вы можете сохранить макрос в книге, над которой вы работаете («Эта книга»), в новом файле Excel («Новая книга») или в личной книге макросов («Личная книга макросов»).

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

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

  Более продвинутым вариантом хранения является «Персональная книга макросов». В Excel 2013 Подробно Билл Джелен определяет личную книгу макросов как
  

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

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

• Создайте описание макроса.

  Наличие описания макроса не является обязательным. . Однако, как объяснил Грег Харви в книге Excel 2013 All-in-One для чайников :
  

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

  Харви также предлагает указать дату сохранения макроса и дату его создания.

5. Шаг 5.

После того, как вы назначили имя, установите место, где вы хотите сохранить макрос и (если хотите) назначили сочетание клавиш и создали описание макроса, нажмите кнопку ОК, чтобы закрыть диалоговое окно «Запись макроса».

6. Шаг 6.

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

7. Шаг 7.

Щелкните «Остановить запись» на вкладке «Разработчик» или нажмите кнопку «Остановить запись макроса», которая отображается в левой части строки состояния.

Вот и все… На самом деле для записи вашего первого макроса требуется всего 7 простых шагов. .

Пример создания макроса Excel

Если вы выполните 7 простых шагов, описанных выше, вы уже сможете приступить к созданию базовых макросов.

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

• Введите «Это лучший учебник по Excel» в активную ячейку.
• Автоматическая подгонка ширины столбца активной ячейки.
• Раскрасьте активную ячейку в красный цвет.
• Измените цвет шрифта активной ячейки на синий.

Я уже объяснял, как сделать так, чтобы вкладка «Разработчик» отображалась в Excel. Поскольку вам нужно всего лишь один раз запросить Excel для отображения вкладки «Разработчик», изображение ниже показывает только фактическую запись макроса .

В этом конкретном примере я использовал параметры, описанные выше, при работе с диалоговым окном «Запись макроса».Точнее: имя, присвоенное макросу, — «Best_Excel_Tutorial», сочетание клавиш — «Ctrl + Shift + B», а макрос Excel был сохранен в книге Excel, над которой я работал.

Готово?

Если вы закончили…

Поздравляем! Вы создали свой первый макрос Excel!

Потрясающе!

Теперь вы можете запустить новый макрос с помощью назначенного сочетания клавиш (в данном случае «Ctrl + Shift + B»).Когда вы станете более продвинутым пользователем макросов, вы увидите, что есть несколько других способов выполнить макрос, например макрос Best_Excel_Tutorial выше.

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

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

Тем не менее, это правда, что информации в предыдущих разделах этого руководства по макросам для начинающих достаточно, чтобы настроить относительно широкий спектр макросов . В Библии Excel 2013 Джон Уокенбах объясняет, что:

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

Итак, еще раз поздравляем с созданием вашего первого макроса Excel!

Следующий шаг в создании макросов Excel: введите VBA

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

Однако это не означает, что вам не следует учиться программированию. Если вы хотите раскрыть всю мощь макросов Excel, вам придется изучить Visual Basic для приложений .

Программирование макросов Excel с использованием VBA более эффективен, чем простая запись макросов по нескольким причинам, основная из которых заключается в том, что использование кода VBA позволяет выполнять задачи, которые невозможно записать с помощью Macro Recorder.

Например:

• В книге Excel 2013 Bible Джон Уокенбах перечисляет некоторые примеры задач, которые нельзя записать, таких как отображение «настраиваемых диалоговых окон» или обработка данных в серии книг и даже создание специальных надстроек.”
• В Excel 2013 VBA и макросы , Билл Джелен и Трейси Сирстад говорят нам, что «важно понимать, что средство записи макросов никогда не будет правильно записывать назначение кнопки автосуммирования».

Если бы у меня было место, я мог бы продолжить создание действительно длинного списка примеров того, как программирование с помощью VBA является лучшим способом создания макросов, чем использование Macro Recorder.

Начало обучения написанию макроса в Excel

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

Чтобы начать обучение программированию макросов, полезно взглянуть на фактические инструкции (или код), которые вы создали при записи макроса . Для этого вам необходимо активировать редактор Visual Basic.

Давайте откроем VBE, щелкнув «Visual Basic» на вкладке «Разработчик» или используя сочетание клавиш «Alt + F11».

Excel открывает редактор Visual Basic, который выглядит примерно так:

Окно VBE настраивается, поэтому (вполне) возможно, что окно, отображаемое на вашем компьютере, будет немного отличаться от приведенного выше снимка экрана.

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

1. На что я смотрю?
2. Возможно, что более важно, где код моего макроса?

Раз уж у вас могут возникнуть те же вопросы, давайте на них ответим.

Что вы смотрите в редакторе Visual Basic

Вы можете разделить VBE на 6 основных разделов:

1. Пункт №1: Строка меню.

Строка меню редактора Visual Basic во многом похожа на строки меню, которые вы используете в других программах.

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

Если вы работаете с более поздними версиями Excel (2007 или новее), вы, возможно, заметили, что в самом Excel нет строки меню, а, скорее, ленты. Причина этого в том, что в Microsoft Office 2007 Microsoft заменила меню и панели инструментов некоторых программ лентой.

2. Элемент №2: Панель инструментов.

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

Точнее, панель инструментов содержит такие элементы, как экранные кнопки, значки, меню и подобные элементы. Панель инструментов, показанная на снимке экрана выше, является стандартной панелью инструментов редактора Visual Basic по умолчанию. Как объяснил Джон Уокенбах в книге Excel VBA Programming for Dummies , большинство людей (включая самого Вокенбаха) «просто оставляют их такими, какие они есть».

Как объяснялось выше, если у вас более новая версия Excel (начиная с 2007), вы не увидите ни панели инструментов, ни строки меню в окне Excel, потому что Microsoft заменила оба этих элемента лентой.

3. Элемент №3: Окно проекта (или проводник проекта).

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

Как вы можете видеть на изображении ниже, редактор Visual Basic позволяет разворачивать или сворачивать различные разделы списка, нажимая «+» или «-» (в зависимости от обстоятельств), отображаемый слева. сторона соответствующей ветви.

Когда «VBAProject» развернут, он показывает различные папки, которые в данный момент загружены. Может быть несколько папок для разных типов элементов, таких как листы, объекты, формы и модули. Я объясняю, что все это такое, в других руководствах по VBA и макросам.

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

Например, на изображении выше есть 2 папки (объекты и модули Microsoft Excel), а в папке объектов Microsoft Excel (развернутой) есть два элемента (Sheet1 и ThisWorkbook).

Если вы не видите Project Explorer, возможно, он скрыт.

Чтобы открыть окно проекта, используйте сочетание клавиш «Ctrl + R», щелкните значок Project Explorer на панели инструментов или перейдите в меню «Просмотр» и нажмите «Project Explorer»:

4.Пункт № 4: Окно свойств.

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

Окно свойств можно скрыть или показать.

Если ваш редактор Visual Basic в данный момент не отображает окно свойств, используйте сочетание клавиш «F4», щелкните значок «Окно свойств» на панели инструментов или разверните меню «Просмотр» и нажмите «Окно свойств»:

5.Пункт № 5: Окно программирования (или кода).

Окно программирования — это то место, где появляется записанный вами код VBA.

В следующем разделе я объясню, как заставить редактор Visual Basic отображать код ваших макросов.

В дополнение к отображению кода, окно кода — это то место, где вы можете писать или редактировать код VBA.

6. Пункт № 6: Непосредственное окно.

Непосредственное окно полезно для обнаружения ошибок, проверки или отладки.

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

• Это окно по умолчанию скрыто.
• Как объяснил Джон Уокенбах в книге Excel VBA Programming for Dummies , это окно не так полезно для новичков и, следовательно, может быть более целесообразным сохранить его скрытым или, если в настоящее время отображается, скрыть его.

Чтобы показать непосредственное окно, используйте сочетание клавиш «Ctrl + G» или войдите в меню «Вид» и нажмите «Немедленное окно».

Теперь, когда вы знаете, на что смотрите при работе с редактором Visual Basic, давайте продолжим и узнаем, как можно увидеть фактический код созданного вами макроса…

Где находится ваш код макроса VBA

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

На снимке экрана выше «VBAProject» развернут и показывает две папки: Объекты Microsoft Excel и Модули.Вы можете видеть элементы внутри первой папки (объекты Microsoft Excel), но не внутри второй (модули).

Чтобы развернуть папку «Модули» и увидеть ее компоненты, нажмите «+»:

Project Explorer выглядит примерно так:

Элементы, отображаемые в папке «Объекты Microsoft Excel», могут показаться вам знакомыми. Однако вы можете задаться вопросом…

Что такое модуль?

Модуль — это, по словам Джона Уокенбаха в Excel 2013 Bible , «контейнер для кода VBA».

Другими словами, модуль — это место, где фактически хранится код VBA. Если вы следовали примеру из этого руководства по макросам Excel для начинающих, ваш код макроса находится в модуле, точнее в Module1:

.

Чтобы редактор Visual Basic отображал код VBA, дважды щелкните «Module1» или щелкните правой кнопкой мыши «Module1» и выберите «Просмотреть код»:

И VBE отображает код макроса в окне программирования. Если вы следовали примеру из этого руководства для начинающих и создали макрос Best_Excel_Tutorial, ваш код выглядит примерно так:

Есть ли в этом смысл для вас?

Хорошая новость заключается в том, что до некоторой степени , вероятно, имеет немного смысла .

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

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

Все эти переживания и вопросы нормальные.

Давайте подробнее рассмотрим код макроса, чтобы разобраться во всем этом.

Изучение VBA с нуля на примере базового макроса Excel

Сначала хорошие новости.

Как вы могли заметить, код VBA (отчасти) похож на английский . В книге VBA для Excel Made Simple Кейт Дарлингтон (опытный преподаватель программирования) объясняет, как структурированный английский (который похож на обычный английский) может быть полезным промежуточным шагом для обдумывания инструкций, которым должен следовать макрос, прежде чем фактически писать эти инструкции на Visual Basic для приложений.

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

• ActiveCell.Select.

  Активная ячейка — это ячейка, которая в данный момент выбрана на листе. Я предполагаю, что, даже если вы не знакомы с Excel или Visual Basic для приложений, вы знаете, что означает слово «выбрать».

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

• Selection.Columns.Autofit.

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

  Если вы помните, второе, что должен был сделать макрос Best_Excel_Tutorial, — это автоматически подогнать ширину столбца активной ячейки таким образом, чтобы набранный текст («Это лучший учебник по Excel») умещался в одном клетка.

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

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

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

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

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

Цель этой части руководства не в том, чтобы сделать вас экспертом в Visual Basic для приложений, а в том, чтобы дать вам общее представление о том, как работает VBA, и, что более важно, показать вам, какие инструкции выполняет Excel в чтобы написать «Это лучший учебник по Excel», автоматически подогнать столбец, покрасить активную ячейку в красный цвет и изменить цвет шрифта на синий.

Вы заметите (не только в этот раз, но обычно при записи макросов), что код VBA может включать в себя некоторые действия, которые вы на самом деле не выполняли.По словам Джона Уокенбаха, в Библии Excel 2013 это «всего лишь побочный продукт метода, который Excel использует для преобразования действий в код».

Другими словами, в настоящий момент вам не нужно беспокоиться о строках кода, которые кажутся бесполезными. В будущих уроках я могу объяснить, как их можно удалить.

Окно программирования, содержащее код созданного вами макроса, состоит из следующих частей:

Элемент №1: Sub Best_Excel_Tutorial ()

Sub означает подпроцедуру.Это один из двух типов макросов или процедур, которые вы можете использовать в Excel.

Подпроцедуры выполняют определенные действия или действия в Excel.

Другой тип процедуры — это функциональная процедура. Функциональные процедуры используются для выполнения вычислений и возврата значения.

Итак… , что делает эта строка?

Он просто сообщает Excel, что вы пишете новую процедуру Sub.

Подпроцедуры всегда должны начинаться с:

• Слово «Sub».
• Имя процедуры, в данном случае Best_Excel_Tutorial.
• Круглые скобки.

Кроме того, подпроцедуры всегда должны заканчиваться словами «End Sub», как вы можете видеть в последней строке кода, показанной на скриншоте выше (обозначенной цифрой 8).

Пункт № 2: Строки кода VBA зеленым шрифтом, начинающиеся с «

»

Я имею в виду следующие строки:

'
Макрос Best_Excel_Tutorial
'Типы "Это лучший учебник по Excel".Подходит для колонки автоматически. Цвет ячейки красный. Цвет шрифта синий.
'
'Сочетание клавиш: Ctrl + Shift + B
'
 

Это просто комментарии. Комментарии имеют следующие основные характеристики:

• Они начинаются (или обозначены) апострофом (‘).
• Visual Basic для приложений, в основном, игнорирует текст, который идет после апострофа до конца строки. Поэтому при выполнении макроса Excel просто игнорирует комментарии.
• Как следствие предыдущего пункта, основная цель комментария — показать информацию об этом конкретном макросе и помочь вам понять ее.Комментарии могут объяснить такие вещи, как цель процедуры или самые последние изменения, внесенные в процедуру.

Элемент # 3: ActiveCell.Select

Как объяснено выше, эта строка указывает Excel выбрать текущую активную ячейку. Точнее:

• «ActiveCell» ссылается на текущую активную ячейку в активном окне.
• «Выбрать» активирует объект на текущем активном листе Excel, в данном случае текущую активную ячейку, на которую ActiveCell ссылается.

Item # 4: ActiveCell.FormulaR1C1 = «Это лучший учебник по Excel»

Этот оператор указывает Excel написать «Это лучший учебник по Excel» в активной ячейке.

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

• Вы уже знаете, какова цель ActiveCell.
• «FormulaR1C1» указывает Excel установить формулу для объекта, в данном случае текущей активной ячейки, на которую ссылается ActiveCell. Последняя часть (R1C1) ссылается на нотацию R1C1, в которой ссылки на ячейки являются относительными, а не абсолютными.Я объясню нотацию R1C1 более подробно в этом руководстве. Однако помните, что в начале этого руководства я объясняю:
  • Почему следует включать запись относительного эталона; и
  • Как это сделать.
• «Это лучший учебник по Excel» указывает, какая формула (в данном случае текст) должна быть помещена в объект, в данном случае в активную ячейку.

Позиция № 5: Выбор. Столбцы. AutoFit

Как я объяснил выше, этот конкретный оператор заставляет Excel автоматически подбирать столбец активной ячейки, чтобы текст, набранный макросом, полностью помещался в нее.

Целью различных частей данного отчета является следующее:

• «Выбор» просто представляет текущий выбор, в данном случае активную ячейку.
• «Столбцы» выбирает столбцы в выделении, в данном случае столбец, в котором находится активная ячейка.
• «AutoFit» говорит само за себя; он устанавливает ширину выбранных столбцов (как в этом случае) или высоту выбранных строк до любого размера, «обеспечивающего наилучшее соответствие».

Позиция № 6: с… в конце заявления 1

Я имею в виду следующую группу строк, которая известна как оператор With… End With:

С выделением.Интерьер
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    .PatternTintAndShade = 0
Конец с
 

Созданный вами макрос Excel уже выполнил два из четырех действий, которые он должен выполнить:

• В активной ячейке напечатано «Это лучший учебник по Excel».
• Он автоматически подогнал под ширину столбца, чтобы набранный текст умещался правильно.

Как и следовало ожидать, следующее действие, которое выполняет Excel, — это окрашивание активной ячейки в красный цвет.

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

Это причина, по которой существуют операторы With… End With.

Основная цель оператора With… End With состоит в том, чтобы упростить синтаксис путем выполнения нескольких инструкций, которые все ссылаются на один и тот же объект, без необходимости каждый раз ссылаться на этот объект. В случае примера, использованного в этом руководстве для начинающих, этот объект является активной ячейкой.

Как вы можете видеть на скриншоте ниже, основной макрос, который вы записали, имеет два оператора With… End With:

Операторы

With… End With имеют следующую структуру:

• В начале они должны указать «With objectExpression». В других уроках я исследую, что означает «objectExpression». На данный момент достаточно знать, что в случае примера, включенного в это руководство, «objectExpression» — это «Selection.Interior» (для первого оператора With… End With) и «Selection.Шрифт »(для второго оператора With… End With), как я объясню ниже.
• Они могут иметь одну или несколько строк кода, которые представляют собой инструкции, выполняемые для объекта, на который была сделана ссылка.
• В конце они должны сказать «Конец с».

В случае первого оператора With… End With каждый из этих объектов выглядит следующим образом:

Теперь, когда у вас есть базовое представление о том, что делает оператор With… End With, давайте посмотрим на первую строку построчно:

1.Строка №1: С Selection.Interior.

Эта строка сообщает Excel, что она всегда должна ссылаться на внутреннюю часть активной ячейки при выполнении операторов, которые являются частью оператора With… End With.

Как этого добиться?

• «С» является началом оператора With… End With и сообщает Excel, что последующие строки кода ссылаются на объект, упомянутый в этой строке (работают с ним).
• «Selection.Interior» — это «objectExpression», о котором я упоминал выше при объяснении структуры операторов With… End With.«Выбор» представляет текущий выбор, который в этом примере является активной ячейкой, тогда как «Внутренний» обозначает внутреннюю часть объекта, в данном случае внутреннюю часть активной ячейки.

2. Строка №2: .Pattern = xlSolid.

Это первая строка оператора With… End With, который ссылается на внутреннюю часть активной ячейки.

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

• «Узор» задает узор интерьера.
• «xlSolid» указывает, что узор должен быть сплошного цвета.

3. Строка №3: ​​.PatternColorIndex = xlAutomatic.

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

• «PatternColorIndex» устанавливает цвет внутреннего узора.
• «xlAutomatic» указывает, что цвет должен быть автоматическим.

4. Строка № 4: .Color = 25.

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

«Цвет» назначает цвет ячейки, тогда как число (в данном случае 255) указывает цвет, который в макросе Best_Excel_Tutorial является красным.

5. Строка № 5: .TintAndShade = 0.

Эта строка предписывает Excel не осветлять и не затемнять цвет, выбранный для заполнения активной ячейки.

«TintAndShade» устанавливает осветление или затемнение цвета. Когда для TintAndShade установлено значение 0 (как в этом случае), свойство фиксируется на нейтральное, и, следовательно, не происходит осветления или затемнения цвета, выбранного для активной ячейки.

6. Строка № 6: .PatternTintAndShade = 0.

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

«PatternTintAndShade» устанавливает оттенок и образец оттенка для внутренней части объекта, в данном случае для выбранной ячейки.

7. Строка 7: оканчивается на.

Эта строка сигнализирует Excel о конце оператора With… End With.

Следовательно, последующие строки кода ссылаются на другой объект, нежели тот, на который ссылается этот оператор With… End With.

В случае примера, использованного в этом руководстве по макросам Excel для начинающих, конец первого оператора With… End With указывает, что последующие операторы не ссылаются на внутреннюю часть активной ячейки.

Позиция № 7: с… в конце заявления 2

Выше вы узнали, что такое оператор With… End With и какова его общая структура.

Поэтому я сразу перехожу к построчному объяснению второго оператора With… End With макроса, который, как вы, вероятно, ожидаете, выполняет последнюю из инструкций, которые вы дали при его создании: изменение цвета шрифта активной ячейки на синий.

Вы, вероятно, будете счастливы прочитать, что это второе выражение With… End With короче первого.

Приступим к построчному объяснению…

1. Строка №1: с выделенным шрифтом.

Как я объяснял выше, это начало оператора With… End With, где «With» сообщает Excel, что следующие операторы работают с отображаемым здесь объектом.

В данном случае это объект Selection.Font.

Итак, что такое «Выбор.Шрифт.»?

«Выделение» — это текущий выбор, который в макросе Best_Excel_Tutorial является активной ячейкой, тогда как «Шрифт» (что неудивительно) является шрифтом.

Другими словами, Selection.Font означает шрифт текста в активной ячейке. Следовательно, «With Selection.Font» в основном информирует Excel о том, что все строки кода, которые являются частью оператора With… End With, ссылаются на шрифт активной ячейки.

2. Строка №2: .Color = -4165632.

Эта строка кода, как и следовало ожидать, учитывая очень похожую строку в первом операторе With… End With выше, сообщает Excel, какой цвет следует использовать для шрифта в активной ячейке.

«Цвет» назначает цвет, тогда как число (в данном случае -4165632) является фактическим цветовым кодом, который в данном случае является синим.

3. Строка № 3: .TintAndShade = 0.

Этот оператор точно такой же, как одна из строк в операторе With… End With выше. Он предписывает Excel не осветлять и не затемнять цвет шрифта.

Поскольку «TintAndShade» определяет осветление или затемнение цвета, когда он равен 0 (как здесь), Excel не осветляет и не затемняет цвет шрифта активной ячейки.

4. Строка № 4: оканчивается на.

Это конец оператора With… End With.

Следовательно, любые строки кода ниже этой не ссылаются на шрифт активной ячейки.

Изделие № 8: Концевой переходник

Операторы End завершают выполнение чего-либо, в данном случае процедуры Sub.

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

Другими словами, это — это конец кода вашего первого макроса Excel .

Несколько заключительных советов относительно того, как узнать о макросах Excel

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

• Измените части кода VBA, чтобы попробовать что-то новое.

  Например, замените «ActiveCell.FormulaR1C1 =« Это лучший учебник по Excel »» на «ActiveCell.FormulaR1C1 =« Я люблю Microsoft Excel »».

  
  Вы также можете изменить числа, которые определяют заполнение ячеек и цвет шрифта. Например, измените «.Color = 255» на «.Color = 10» и «.Color = –4165632» на «.Color = 200».
  
  Вернитесь в главное окно Excel и снова запустите макрос (например, используя назначенное вами сочетание клавиш «Ctrl + Shift + B») и проверьте, что происходит.

  Результаты существенно изменились, не так ли? Разве не интересно, какое значение может иметь пара небольших элементов в коде VBA?

• Удалите определенные операторы из кода, чтобы увидеть, как они влияют на макрос.

  Например, , что, по вашему мнению, произойдет, если вы удалите «Selection.Columns.AutoFit»?

  
  Попробуйте сами.

  Снова вернитесь в Excel и запустите макрос еще раз с этим удалением.

  Что случилось? Вы этого ожидали?

• Один из лучших способов изучить код макроса Excel — повторить упражнение, содержащееся в этом руководстве, поэтому я рекомендую вам это сделать.

  Как?

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

  2. Откройте VBE и построчно просмотрите код VBA, чтобы понять, какова цель каждого оператора.

  Возможно, даже лучше, если у вас достаточно большой экран (или два монитора), — это последовать совету Джона Уокенбаха из Библии Excel 2013 и…
  

  (…) настройте свой экран так, чтобы вы могли видеть код, который создается в окнах редактора VB.

• Читай и учись. Вы можете, например, просмотреть Архивы электронных таблиц Power, чтобы найти все написанные мной руководства по Excel, касающиеся VBA и макросов.

Заключение

Еще раз поздравляю!

После изучения этого руководства по макросам Excel для начинающих, вы создали свой первый макрос и поняли, что за ним стоит код VBA .

Как вы могли заметить, настроить макрос с помощью рекордера Excel относительно просто и может быть выполнен за семь простых шагов .Если ваша основная цель — просто записывать и воспроизводить макросы Excel, вы готовы к работе!

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

Применяйте на практике последние советы о том, как узнать о макросах, которые я предоставил в разделе выше.Создавайте макросы, изучайте стоящий за ними код VBA и пробуйте разные вещи, чтобы увидеть, что происходит.

Если вы продолжите изучать и практиковать Visual Basic для приложений, включая темы, которые я освещаю в других руководствах по Excel VBA в Power Spreadsheets, вы скоро сможете, среди прочего:

• Намного лучше понять, что вы сделали при создании своего первого макроса и как работает макрос Best_Excel_Tutorial.
• Пишите и используйте гораздо более сложные и изощренные макросы Excel.

Книги, упомянутые в этом руководстве по макросам Excel для начинающих

• Книги в Библиотека электронных таблиц Power .
• Дарлингтон, Кейт (2004 г.), VBA для Excel Made Simple. Берлингтон, Массачусетс: Сделанные простые книги.
• Харви, Грег (2013). Многофункциональное устройство Excel 2013 для чайников. Хобокен, Нью-Джерси: John Wiley & Sons Inc.,
• Джелен, Билл (2013). Excel 2013 в подробностях. Соединенные Штаты Америки: Que Publishing.
• Джелен, Билл и Сирстад, Трейси (2013).Excel 2013 VBA и макросы. Соединенные Штаты Америки: Pearson Education, Inc.
• Вокенбах, Джон (2013). Библия Excel 2013. Индианаполис, IN: John Wiley & Sons Inc.,
• Вокенбах, Джон (2013). Программирование Excel VBA для чайников. Хобокен, Нью-Джерси: John Wiley & Sons Inc.,

Быстрый старт: создание макроса

Если у вас есть задачи в Microsoft Excel, которые вы выполняете неоднократно, вы можете записать макрос для автоматизации этих задач. Макрос — это действие или набор действий, которые вы можете запускать сколько угодно раз.Когда вы создаете макрос, вы записываете щелчки мыши и нажатия клавиш. После создания макроса вы можете отредактировать его, чтобы внести незначительные изменения в способ его работы.

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

Как?

Перед записью макроса Макросы и инструменты VBA можно найти на вкладке Developer , которая по умолчанию скрыта, поэтому первым делом нужно ее включить.Для получения дополнительной информации см. Показать вкладку «Разработчик».

Запись макроса В группе Code на вкладке Developer щелкните Record Macro . При желании введите имя макроса в поле Имя макроса , введите сочетание клавиш в поле Сочетание клавиш и описание в поле Описание , а затем нажмите ОК , чтобы начать запись. Выполните действия, которые вы хотите автоматизировать, например введите шаблонный текст или заполните столбец данных. На вкладке Developer щелкните Остановить запись .

Присмотритесь к макросу Вы можете немного узнать о языке программирования Visual Basic, отредактировав макрос. Чтобы отредактировать макрос, в группе Code на вкладке Developer щелкните Macros , выберите имя макроса и щелкните Edit . Это запустит редактор Visual Basic. Посмотрите, как записанные вами действия отображаются в виде кода. Часть кода, вероятно, будет вам понятна, а часть может быть немного загадочной. Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос.На этот раз посмотрите, не случится ли что-нибудь по-другому!

Следующие шаги

Как?

Перед записью макроса Убедитесь, что вкладка Developer видна на ленте.По умолчанию вкладка Developer не отображается, поэтому выполните следующие действия: Перейдите в Excel > Настройки … > Лента и панель инструментов . В категории Настройка ленты в списке Основные вкладки установите флажок Developer , а затем нажмите Сохранить .

Запись макроса На вкладке Developer щелкните Запись макроса . При желании введите имя макроса в поле Имя макроса , введите сочетание клавиш в поле Сочетание клавиш и описание в поле Описание , а затем нажмите ОК , чтобы начать запись. Выполните действия, которые вы хотите автоматизировать, например введите шаблонный текст или заполните столбец данных. На вкладке Developer в группе Code щелкните Stop Recording .

Присмотритесь к макросу Вы можете немного узнать о языке программирования Visual Basic, отредактировав макрос. Чтобы отредактировать макрос, на вкладке Developer щелкните Macros , выберите имя макроса и щелкните Edit . Это запустит редактор Visual Basic. Посмотрите, как записанные вами действия отображаются в виде кода. Часть кода, вероятно, будет вам понятна, а часть может быть немного загадочной. Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос.На этот раз посмотрите, не случится ли что-нибудь по-другому!

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

Создайте макрос за 4 простых шага

Я не могу сказать вам, сколько времени я потратил (кхм, зря), делая одно и то же снова и снова в Excel.Форматирование книги, добавление формул, форматирование ячеек и выделение строк. У этого списка нет конца.

Но что, если бы я сказал вам, что есть способ все это автоматизировать? Что, если — одним нажатием кнопки — вы могли бы легко выполнять эти рутинные задачи? Что ж, это еще одна область, в которой Excel действительно выделяется. Пришло время познакомиться с вашим новым лучшим другом: макросами.

Давайте перейдем к нашему руководству по макросам Excel для начинающих.

Расширьте возможности своей игры Excel

Загрузите нашу готовую к печати листовку с ярлыками для Excel.

Что такое макрос?

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

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

Посмотрите это пояснительное видео, чтобы узнать больше о различиях между макросами и VBA из курса GoSkills Excel Macros & VBA.

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

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

Концепция макроса может показаться сложной. Но научиться использовать их в своих интересах на самом деле на удивление просто.

Где получить доступ к макросам

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

Все они находятся на вкладке Excel «Разработчик». Однако по умолчанию Excel скрывает эту вкладку. Если вы его в настоящее время не видите, вам нужно сделать несколько шагов, чтобы эта вкладка стала частью вашей панели инструментов. Как вы это сделаете, будет зависеть от того, используете ли вы ПК или Mac.

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

Если вы используете Mac, нажмите «Excel» в верхнем левом углу экрана (рядом с логотипом Apple), а затем «Настройки». Оттуда нажмите «Просмотр», а затем установите флажок для вкладки «Разработчик».

---

---

Как создать макрос: пример из практики

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

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

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

Чтобы отличить, кто в этой огромной таблице Excel является членом правления (не прибегая к неверным номерам заказов и столов!), София и поставщик провизии согласились, что ей следует сделать следующее, чтобы явно отметить, кто за каждым столом является членом правления (так что это не пропущено установочной бригадой):

• Имя члена совета директоров выделено жирным шрифтом
• Изменить текст имени члена совета директоров на синий
• Выделите желтым цветом ячейку, содержащую имя члена совета директоров.

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

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

Давайте создадим этот макрос вместе с Софией.

1. Настройте макрос

Для начала щелкните ячейку, которая не принадлежит члену правления, перейдите на вкладку «Разработчик» и нажмите «Записать макрос».”

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

Вы также можете ввести сочетание клавиш, чтобы применить этот макрос (в случае Софии, она будет использовать option + command + b для члена правления) и описание (что полезно, если вы записываете и храните много макросов).

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

2. Запишите макрос

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

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

Когда она закончит, она нажмет клавишу «Ввод», а затем вернется на вкладку «Разработчик» и нажмет «Остановить запись».

3. Отредактируйте макрос

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

Чтобы использовать этот макрос в других местах, ей необходимо настроить диапазон макроса. К счастью, это легко сделать.

Для этого щелкните «Макросы» на вкладке «Разработчик». В этом меню макросов выберите «Шаг» (на ПК он будет называться «Шаг с заходом»).

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

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

Итак, это так же просто, как просто удалить эти две строки из этого кода, а затем закрыть это окно.

4. Используйте макрос

После удаления этого диапазона София теперь может применять этот макрос к другим ячейкам.

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

Что, если она не создала сочетание клавиш? Она также может щелкнуть нужную ячейку, выбрать «Макросы» на вкладке «Разработчик», а затем нажать «Выполнить».

Она (и вы!) Сделала это — мы создали простой макрос, который София может использовать для быстрого форматирования ячеек членов совета.

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

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

Готовы стать мастером макросов?

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

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

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

Готовы стать сертифицированным ниндзя Excel?

Пройдите курс GoSkills по макросам и VBA сегодня же!

Начать бесплатную пробную версию

Excel VBA macro tutorial для начинающих с примерами

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

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

Эта статья — ваш вход в увлекательный мир макросов Excel. Он охватывает основные основы, которые вам нужно знать, чтобы начать работу, и содержит ссылки на соответствующие подробные руководства.

Что такое макросы в Excel?

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

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

Довольно часто можно услышать, как люди называют «макрос» «VBA». Технически существует различие: макрос — это часть кода, а Visual Basic для приложений (VBA) — это язык программирования, созданный Microsoft для написания макросов.

Зачем нужны макросы Excel?

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

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

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

Как создать макрос в Excel

Существует два способа создания макросов в Excel — с помощью средства записи макросов и редактора Visual Basic.

Наконечник. В Excel большинство операций с макросами выполняется через вкладку Developer , поэтому обязательно добавьте вкладку «Разработчик» на ленту Excel.

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

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

Macro Recorder фиксирует почти все, что вы делаете, и выдает очень подробный (часто избыточный) код. После того, как вы остановили запись и сохранили макрос, вы можете просмотреть его код в редакторе Visual Basic и внести небольшие изменения. Когда вы запускаете макрос, Excel возвращается к записанному коду VBA и выполняет те же действия.

Чтобы начать запись, нажмите кнопку Record Macro либо на вкладке Developer , либо на панели состояния .

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

Написание макроса в редакторе Visual Basic

Редактор Visual Basic для приложений (VBA) — это место, где Microsoft Excel хранит код всех макросов, как записанных, так и написанных вручную.

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

Сначала нажмите Alt + F11, чтобы открыть редактор Visual Basic. А затем вставьте код в эти два быстрых шага:

1. В Project Explorer слева щелкните правой кнопкой мыши целевую книгу и выберите Insert > Module .
2. В окне кода справа вставьте код VBA.

Когда закончите, нажмите F5, чтобы запустить макрос.

Подробные инструкции см. В разделе «Как вставить код VBA в Excel».

Как запускать макросы в Excel

Есть несколько способов запустить макрос в Excel:

• Чтобы запустить макрос из рабочего листа, нажмите кнопку Macros на вкладке Developer или нажмите сочетание клавиш Alt + F8.
• Чтобы запустить макрос из редактора VBA, нажмите:
  • F5 для запуска всего кода.
  • F8, чтобы просмотреть код построчно. Это очень полезно для тестирования и устранения неполадок.

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

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

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

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

Дополнительные сведения о безопасности макросов см. В разделе «Включение и отключение макросов в Excel».

Как изменить настройки макроса

Microsoft Excel определяет, разрешить или запретить выполнение кодов VBA в ваших книгах, на основе параметра макроса, выбранного в центре управления безопасностью .

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

1. Перейдите на вкладку File и выберите Options .
2. На левой панели выберите Центр управления безопасностью , а затем щелкните Параметры центра управления безопасностью… .
3. В диалоговом окне центра управления безопасностью щелкните Параметры макроса слева, выберите нужный вариант и щелкните ОК .

На скриншоте ниже выбрана настройка макроса по умолчанию:

Для получения дополнительной информации см. Объяснение настроек макроса Excel.

Как просматривать, редактировать и отлаживать коды VBA в Excel

Любые изменения в коде макроса, независимо от того, создается ли он автоматически программой записи макросов Excel или написан вами, вносятся в редакторе Visual Basic.

Чтобы открыть редактор VB, нажмите Alt + F11 или нажмите кнопку Visual Basic на вкладке Developer .

Чтобы просмотреть и отредактировать код определенного макроса, в Project Explorer слева дважды щелкните модуль, который его содержит, или щелкните модуль правой кнопкой мыши и выберите Просмотреть код .Это откроет окно кода, где вы можете редактировать код.

Чтобы проверить и отладить макрос , используйте клавишу F8. Это проведет вас по коду макроса построчно, позволяя увидеть эффект, который каждая строка оказывает на вашем листе. Выполняемая в данный момент строка выделена желтым цветом. Чтобы выйти из режима отладки, нажмите кнопку Reset на панели инструментов (синий квадрат).

Как скопировать макрос в другую книгу

Вы создали макрос в одной книге и теперь хотите повторно использовать его и в других файлах? Есть два способа скопировать макрос в Excel:

Скопируйте модуль, содержащий макрос

Если целевой макрос находится в отдельном модуле или все макросы в модуле полезны для вас, тогда имеет смысл скопировать весь модуль из одной книги в другую:

1. Откройте обе книги — ту, которая содержит макрос, и ту, в которую вы хотите его скопировать.
2. Откройте редактор Visual Basic.
3. На панели Project Explorer найдите модуль, содержащий макрос, и перетащите его в целевую книгу.

На скриншоте ниже мы копируем Module1 из Book1 в Book2 :

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

Если модуль содержит много разных макросов, а вам нужен только один, скопируйте только код этого конкретного макроса. Вот как:

1. Откройте обе книги.
2. Откройте редактор Visual Basic.
3. На панели Project Explorer дважды щелкните модуль, содержащий макрос, который вы хотите скопировать, чтобы открыть его окно кода.
4. В окне «Код» найдите целевой макрос, выберите его код (начиная с Sub и заканчивая End Sub ) и нажмите Ctrl + C, чтобы скопировать его.
5. В проводнике проекта найдите целевую книгу и вставьте в нее новый модуль (щелкните книгу правой кнопкой мыши и выберите Вставить > Модуль ) или дважды щелкните существующий модуль, чтобы открыть окно кода.
6. В окне кода целевого модуля нажмите Ctrl + V, чтобы вставить код. Если модуль уже содержит код, прокрутите вниз до последней строки кода и вставьте скопированный макрос.
   

Как удалить макросы в Excel

Если вам больше не нужен определенный код VBA, вы можете удалить его с помощью диалогового окна Macro или редактора Visual Basic.

Удаление макроса из книги

Чтобы удалить макрос прямо из книги Excel, выполните следующие действия:

1. На вкладке Developer в группе Code нажмите кнопку Macros или нажмите сочетание клавиш Alt + F8.
2. В диалоговом окне Macro выберите макрос, который вы хотите удалить, и нажмите Удалить .

Удаление макроса с помощью редактора Visual Basic

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

Чтобы окончательно удалить модуль , выполните следующие действия:

1. В Project Explorer щелкните правой кнопкой мыши модуль и выберите Remove из контекстного меню.
2. Когда вас спросят, хотите ли вы экспортировать модуль перед его удалением, нажмите Нет .

Чтобы удалить конкретный макрос , просто удалите его исходный код прямо в окне кода. Или вы можете удалить макрос с помощью меню Tools редактора VBA:

1. В меню Инструменты выберите Макросы . Появится диалоговое окно Macros .
2. В макросах В раскрывающемся списке выберите проект, содержащий нежелательный макрос.
3. В поле Имя макроса выберите макрос.
4. Нажмите кнопку Удалить .
   

Как сохранить макросы в Excel

Чтобы сохранить макрос в Excel, записанный или записанный вручную, просто сохраните книгу с включенным макросом (* .xlms). Вот как:

1. В файле, содержащем макрос, нажмите кнопку Сохранить или нажмите Ctrl + S.
2. Откроется диалоговое окно Сохранить как . Выберите книгу Excel с поддержкой макросов (*.xlsm) из раскрывающегося списка Сохранить как тип и нажмите Сохранить :

Как экспортировать и импортировать макросы в Excel

Если вы хотите поделиться своими кодами VBA с кем-нибудь или перенести их на другой компьютер, самый быстрый способ — экспортировать весь модуль в виде файла .bas.

Экспорт макросов

Чтобы экспортировать коды VBA, вам нужно сделать следующее:

1. Откройте книгу, содержащую макросы.
2. Нажмите Alt + F11, чтобы открыть редактор Visual Basic.
3. В проводнике проекта щелкните правой кнопкой мыши модуль, содержащий макросы, и выберите Export File .
4. Перейдите в папку, в которую вы хотите сохранить экспортированный файл, назовите файл и нажмите Сохранить .

Импорт макросов

Чтобы импортировать файл .bas с кодами VBA в Excel, выполните следующие действия:

1. Откройте книгу, в которую вы хотите импортировать макросы.
2. Откройте редактор Visual Basic.
3. В Project Explorer щелкните правой кнопкой мыши имя проекта и выберите Import File .
4. Перейдите к файлу .bas и щелкните Открыть .

Примеры макросов Excel

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

Показать все листы в книге

В этом примере мы используем объект ActiveWorkbook для возврата текущей активной книги и цикл For Each для последовательного просмотра всех листов в книге. Для каждого найденного листа мы устанавливаем свойство Visible на xlSheetVisible .

Sub Unhide_All_Sheets ()
Dim wks как рабочий лист

За каждую неделю в ActiveWorkbook.Рабочие листы
wks.Visible = xlSheetVisible
Следующие недели
Конец подписки
 

Скрыть активный рабочий лист или сделать его очень скрытым

Для управления текущим активным листом используйте объект ActiveSheet . Этот пример макроса изменяет свойство Visible активного листа на xlSheetHidden , чтобы скрыть его. Чтобы сделать лист очень скрытым, установите для свойства Visible значение xlSheetVeryHidden .

Sub Hide_Active_Sheet ()
ActiveSheet.Видимый = xlSheetHidden
Конец подписки
 

Отменить объединение всех объединенных ячеек в выбранном диапазоне

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

Sub Unmerge_Cells ()
Selection.Cells.UnMerge
Конец подписки
 

Показать окно сообщения

Чтобы показать сообщение вашим пользователям, используйте функцию MsgBox .Вот пример такого макроса в простейшем виде:

Sub Show_Message ()
MsgBox («Привет, мир!»)
Конец подписки
 

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

Sub Unmerge_Selected_Cells ()
Тусклый ответ в виде строки

Answer = MsgBox («Вы уверены, что хотите разъединить эти ячейки?», VbQuestion + vbYesNo, «Разъединить ячейки»)
Если Ответ = vb Да Тогда
Выбор.Cells.UnMerge
Конец, если
Конец подписки
 

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

Ниже приведены ссылки на более сложные макросы, которые автоматизируют сложные и трудоемкие задачи:

Как защитить макросы Excel

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

Макрос блокировки для просмотра

Чтобы защитить коды VBA от несанкционированного просмотра и редактирования, выполните следующие действия:

1. Откройте редактор VBA.
2. В Project Explorer щелкните правой кнопкой мыши проект, который нужно заблокировать, и выберите VBAProject Properties…
3. В диалоговом окне Project Properties на вкладке Protection установите флажок Lock project for view , введите пароль дважды и нажмите OK .
4. Сохраните, закройте и снова откройте файл Excel.

При попытке просмотреть код в редакторе Visual Basic появится следующее диалоговое окно.Введите пароль и нажмите ОК.

Чтобы разблокировать макросы , просто откройте диалоговое окно Project Properties еще раз и снимите галочку с проекта Lock для просмотра окна .

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

Макрос защиты паролем от запуска

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

Дополнительный пароль_Protect ()
Тусклый пароль как вариант

пароль = Приложение.InputBox («Введите пароль», «Макрос, защищенный паролем»)

Выберите пароль для обращения
Case Is = False
'ничего не делать
Case Is = "пароль"
'ваш код здесь
Case Else
MsgBox «Неверный пароль»
Конец Выбрать
Конец подписки
 

Макрос использует функцию InputBox , чтобы предложить пользователю ввести пароль:

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

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

Советы по макросам Excel

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

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

Следующие строки должны быть добавлены в начало вашего кода (после строк, начинающихся с Dim или после строки Sub ):

Application.ScreenUpdating = False
Заявление.Расчет = xlCalculationManual
 

Следующие строки должны быть добавлены в конец вашего кода (до End Sub ):

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 

Как разбить код VBA на несколько строк

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

Чтобы разделить длинный оператор на несколько строк, введите пробел , за которым следует знак подчеркивания (_) в точке, в которой вы хотите разорвать строку. В VBA это называется символом продолжения строки .

Чтобы правильно продолжить код на следующей строке, пожалуйста, следуйте этим правилам:

• Не разделяйте код между именами аргументов.
• Не используйте подчеркивание в комментариях. Для многострочных комментариев введите апостроф (‘) в начале каждой строки.
• Подчеркивание должно быть последним символом в строке, за которым не должно быть ничего другого.

В следующем примере кода показано, как разбить оператор на две строки:

Answer = MsgBox ("Вы действительно хотите отключить эти ячейки?", _
vbQuestion + vbYesNo, «Разъединить ячейки»)
 

Как сделать макрос доступным из любой книги

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

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

Как отменить действие макроса

После выполнения макроса его действие нельзя отменить ни нажатием Ctrl + Z, ни нажатием кнопки Отменить .

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

Более простой способ — сохранить активную книгу из кода макроса. Для этого просто добавьте следующую строку, прежде чем разрешить вашему макросу делать что-либо еще:

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

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

Запретить Excel отображать предупреждение системы безопасности при отсутствии макросов в книге

Вы когда-нибудь оказывались в ситуации, когда Excel постоянно спрашивает, хотите ли вы включить макросы, в то время как вы точно знаете, что в этой конкретной книге макросов нет?

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

• Для ThisWorkbook и для каждого отдельного листа откройте окно кода, нажмите Ctrl + A, чтобы выделить весь код и удалить его (даже если окно кода выглядит пустым).
• Удалите все пользовательские формы и модули классов, содержащиеся в книге.

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

Вас также может заинтересовать

24 полезных примера макросов Excel для начинающих пользователей VBA (готовые к использованию)

Использование макросов Excel может ускорить работу и сэкономить много времени.

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

Поэтому стоит иметь набор полезных макрокодов VBA, которые можно носить в заднем кармане и использовать при необходимости.

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

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

Я буду продолжать обновлять это руководство, добавляя больше примеров макросов. Если вы считаете, что что-то должно быть в списке, просто оставьте комментарий.

Вы можете добавить эту страницу в закладки для дальнейшего использования.

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

Использование кода из примеров макросов Excel

Вот шаги, которые необходимо выполнить, чтобы использовать код из любого из примеров:

• Откройте рабочую книгу, в которой вы хотите использовать макрос.
• Удерживая клавишу ALT, нажмите F11. Откроется редактор VB.
• Щелкните правой кнопкой мыши любой объект в проводнике проекта.
• Перейдите в Insert -> Module.
• Скопируйте и вставьте код в окно кода модуля.

Если в примере говорится, что вам нужно вставить код в окно кода рабочего листа, дважды щелкните объект рабочего листа и скопируйте и вставьте код в окно кода.

После того, как вы вставили код в книгу, вам необходимо сохранить его с расширением.Расширение XLSM или .XLS.

Как запустить макрос

После того, как вы скопировали код в редакторе VB, выполните следующие действия для запуска макроса:

• Перейдите на вкладку «Разработчик».
• Щелкните по макросу.

• В диалоговом окне «Макрос» выберите макрос, который нужно запустить.
• Нажмите кнопку «Выполнить».

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

Связанное руководство: Различные способы запуска макроса в Excel.

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

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

Примечание. Вы найдете множество случаев использования апострофа (‘), за которым следует пара строк.Это комментарии, которые игнорируются при запуске кода и помещаются как заметки для себя / читателя.

Если вы обнаружите ошибку в статье или коде, пожалуйста, сообщите мне об этом.

Примеры макросов Excel

В этой статье рассматриваются следующие примеры макросов:

Показать все рабочие листы за один раз

Если вы работаете в книге с несколькими скрытыми листами, вам необходимо отобразить эти листы по очереди. один.Это может занять некоторое время, если есть много скрытых листов.

Вот код, который покажет все рабочие листы в книге.

 'Этот код покажет все листы в книге
Sub UnhideAllWoksheets ()
Dim ws как рабочий лист
Для каждой страницы в ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Следующий ws
End Sub 

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

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

Скрыть все рабочие листы, кроме активного листа

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

 'Этот макрос скроет весь рабочий лист, кроме активного листа
Sub HideAllExceptActiveSheet ()
Dim ws как рабочий лист
Для каждого ws в ThisWorkbook.Рабочие листы
Если ws.Name <> ActiveSheet.Name Тогда ws.Visible = xlSheetHidden
Следующий ws
End Sub 

Сортировка листов по алфавиту с помощью VBA

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

 'Этот код сортирует листы по алфавиту
Sub SortSheetsTabName ()
Заявление.ScreenUpdating = False
Dim ShCount как целое число, i как целое число, j как целое число
ShCount = Sheets.Count
Для i = 1 To ShCount - 1
Для j = i + 1 To ShCount
Если Sheets (j) .Name 
 Защитите все листы за один раз 
 Если у вас много листов в книге и вы хотите защитить все листы, вы можете использовать этот код макроса.
 Позволяет указать пароль внутри кода. Этот пароль понадобится вам, чтобы снять защиту с листа.
 'Этот код защитит все листы одновременно
Sub ProtectAllSheets ()
Dim ws как рабочий лист
Тусклый пароль в виде строки
password = "Test123" 'замените Test123 нужным паролем.
Для каждого ws в листах
ws.Protect пароль: = пароль
Следующий ws
End Sub 
 Снять защиту со всех листов за один раз 
 Если у вас есть защита некоторых или всех рабочих листов, вы можете просто использовать небольшую модификацию кода, используемого для защиты листов, чтобы снять защиту.
 'Этот код защитит все листы одновременно
Sub ProtectAllSheets ()
Dim ws как рабочий лист
Тусклый пароль в виде строки
password = "Test123" 'замените Test123 нужным паролем.
Для каждого ws в листах
ws.Unprotect пароль: = пароль
Следующий ws
End Sub 
 Обратите внимание, что пароль должен совпадать с паролем, который использовался для блокировки рабочих листов. В противном случае вы увидите сообщение об ошибке.
 Показать все строки и столбцы 
 Этот код макроса покажет все скрытые строки и столбцы.
 Это может быть действительно полезно, если вы получили файл от кого-то другого и хотите убедиться, что в нем нет скрытых строк / столбцов.
 'Этот код покажет все строки и столбцы на листе
Sub UnhideRowsColumns ()
Columns.EntireColumn.Hidden = Ложь
Rows.EntireRow.Hidden = False
End Sub 
 Разъединить все объединенные ячейки 
 Обычной практикой является объединение ячеек в одну. Пока он выполняет свою работу, при объединении ячеек вы не сможете сортировать данные.
 Если вы работаете с листом с объединенными ячейками, используйте приведенный ниже код, чтобы объединить все объединенные ячейки за один раз.
 'Этот код отключит все объединенные ячейки
Sub UnmergeAllCells ()
ActiveSheet.Cells.UnMerge
End Sub 
 Обратите внимание, что вместо «Объединить и центрировать» я рекомендую использовать параметр «Центрировать по всему выделению».
 Сохранить книгу с отметкой времени в ее имени 
 Вам может потребоваться много времени, чтобы создать версии своей работы.Они очень полезны в длинных проектах, когда вы работаете с файлом в течение долгого времени.
 Рекомендуется сохранять файл с отметками времени.
 Использование временных меток позволит вам вернуться к определенному файлу, чтобы увидеть, какие изменения были внесены или какие данные были использованы.
 Вот код, который автоматически сохранит книгу в указанной папке и добавит метку времени при каждом сохранении.
 'Этот код сохранит файл с отметкой времени в его имени
Подложка SaveWorkbookWithTimeStamp ()
Тусклая метка времени как строка
timestamp = Формат (Дата, «дд-мм-гггг») & «_» и Формат (Время, «чч-сс»)
ThisWorkbook.Сохранить как "C: UsersUsernameDesktopWorkbookName" и отметку времени
End Sub 
 Необходимо указать расположение папки и имя файла.
 В приведенном выше коде «C: UsersUsernameDesktop» - это папка, которую я использовал. Вам необходимо указать папку, в которой вы хотите сохранить файл. Кроме того, я использовал общее имя «WorkbookName» в качестве префикса имени файла. Вы можете указать то, что связано с вашим проектом или компанией.
 Сохранение каждого рабочего листа как отдельного файла PDF 
 Если вы работаете с данными за разные годы, подразделения или продукты, вам может потребоваться сохранить разные рабочие листы в виде файлов PDF.
 Хотя это может занять много времени, если выполнить его вручную, VBA действительно может его ускорить.
 Вот код VBA, который сохранит каждый рабочий лист как отдельный PDF-файл.
 'Этот код сохранит каждый рабочий лист как отдельный PDF-файл.
Sub SaveWorkshetAsPDF ()
Dim ws как рабочий лист
Для каждого ws в листах
ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf"
Следующий ws
End Sub 
 В приведенном выше коде я указал адрес папки, в которой я хочу сохранить PDF-файлы.Кроме того, каждый PDF-файл получит то же имя, что и рабочий лист. Вам нужно будет изменить расположение этой папки (если только ваше имя не Sumit и вы не сохраняете его в тестовой папке на рабочем столе).
 Обратите внимание, что этот код работает только для рабочих листов (но не для листов диаграмм).
 Сохранить каждую рабочую таблицу как отдельный PDF-файл 
 Вот код, который сохранит всю вашу рабочую книгу как PDF-файл в указанной папке.
 'Этот код сохранит всю книгу как PDF
Sub SaveWorkshetAsPDF ()
ThisWorkbook.ExportAsFixedFormat xlTypePDF, «C: UsersSumitDesktopTest» и ThisWorkbook.Name & «.pdf»
End Sub 
 Вам нужно будет изменить расположение папки, чтобы использовать этот код.
 Преобразование всех формул в значения 
 Используйте этот код, если у вас есть рабочий лист, содержащий множество формул, и вы хотите преобразовать эти формулы в значения.
 'Этот код преобразует все формулы в значения
Sub ConvertToValues ​​()
С ActiveSheet.UsedRange
.Значение =.Значение
Конец с
End Sub 
 Этот код автоматически определяет используемые ячейки и преобразует их в значения.
 Защита / блокировка ячеек с помощью формул 
 Вы можете заблокировать ячейки с помощью формул, когда у вас много вычислений, и вы не хотите случайно удалить или изменить его.
 Вот код, который блокирует все ячейки с формулами, в то время как все остальные ячейки не блокируются.
 'Этот код макроса заблокирует все ячейки с формулами
Sub LockCellsWithFormulas ()
С ActiveSheet
.Снять защиту
.Cells.Locked = Ложь
.Cells.SpecialCells (xlCellTypeFormulas) .Locked = Истина
.Protect AllowDeletingRows: = True
Конец с
End Sub 
 Связанное руководство: Как заблокировать ячейки в Excel.
 Защитите все рабочие листы в книге 
 Используйте приведенный ниже код для защиты всех рабочих листов в книге за один раз.
 'Этот код защитит все листы в книге
Sub ProtectAllSheets ()
Dim ws как рабочий лист
Для каждого ws в листах
ws.Защищать
Следующий ws
End Sub 
 Этот код будет просматривать все рабочие листы один за другим и защищать его.
 Если вы хотите снять защиту со всех листов, используйте в коде ws.Unprotect вместо ws.Protect.
 Вставить строку после каждой другой строки в выделенном диапазоне 
 Используйте этот код, если вы хотите вставить пустую строку после каждой строки в выбранном диапазоне.
 'Этот код вставит строку после каждой строки в выделенном фрагменте.
Подложка InsertAlternateRows ()
Dim rng As Range
Dim CountRow As Integer
Dim i как целое число
Установить rng = Выбор
CountRow = rng.CompleteRow.Count
Для i = 1 до CountRow
ActiveCell.EntireRow.Insert
ActiveCell.Offset (2, 0) .Select
Далее я
End Sub 
 Аналогичным образом вы можете изменить этот код, чтобы вставить пустой столбец после каждого столбца в выбранном диапазоне.
 Автоматически вставлять дату и метку времени в соседнюю ячейку 
 Метка времени - это то, что вы используете, когда хотите отслеживать действия.
 Например, вы можете захотеть отслеживать такие действия, как когда были понесены определенные расходы, в какое время был создан счет-фактура продажи, когда был произведен ввод данных в ячейку, когда последний раз обновлялся отчет и т. Д.
 Используйте этот код для вставки отметки даты и времени в соседнюю ячейку, когда делается запись или редактируется существующее содержимое.
 'Этот код вставит метку времени в соседнюю ячейку
Private Sub Worksheet_Change (ByVal Target как диапазон)
При ошибке GoTo Handler
Если Target.Column = 1 и Target.Value <> "" Тогда
Application.EnableEvents = False
Target.Offset (0, 1) = Format (Now (), «дд-мм-гггг чч: мм: сс»)
Application.EnableEvents = True
Конец, если
Обработчик:
End Sub 
 Обратите внимание, что вам нужно вставить этот код в окно кода рабочего листа (а не в окно кода модуля, как мы это делали до сих пор в других примерах макросов Excel).Для этого в редакторе VB дважды щелкните имя листа, на котором вы хотите использовать эту функцию. Затем скопируйте и вставьте этот код в окно кода этого листа.
 Кроме того, этот код заставляет работать, когда ввод данных выполняется в столбце A (обратите внимание, что в коде есть строка Target.Column = 1). Вы можете изменить это соответствующим образом.
 Выделение альтернативных строк в выделенном фрагменте 
 Выделение альтернативных строк может значительно повысить удобочитаемость ваших данных. Это может быть полезно, когда вам нужно сделать распечатку и просмотреть данные.
 Вот код, который мгновенно выделяет альтернативные строки в выделенной области.
 'Этот код выделяет альтернативные строки в выделенном
Sub HighlightAlternateRows ()
Тусклый Myrange как диапазон
Дим Майроу как диапазон
Установить Myrange = Selection
Для каждого Myrow In Myrange.Rows
Если Myrow.Row Mod 2 = 1, то
Myrow.Interior.Color = vbCyan
Конец, если
Следующий Myrow
End Sub 
 Обратите внимание, что в коде я указал цвет как vbCyan.Вы также можете указать другие цвета (например, vbRed, vbGreen, vbBlue).
 Выделение ячеек с орфографическими ошибками 
 В Excel нет проверки орфографии, как в Word или PowerPoint. Хотя вы можете запустить проверку орфографии, нажав клавишу F7, нет визуальной подсказки, когда есть орфографическая ошибка.
 Используйте этот код, чтобы мгновенно выделить все ячейки, в которых есть орфографическая ошибка.
 'Этот код будет выделять ячейки, в которых есть слова с ошибками
ПодсветкаMisspelledCells ()
Dim cl As Range
Для каждого элемента в ActiveSheet.UsedRange
Если не Application.CheckSpelling (word: = cl.Text), то
cl.Interior.Color = vbRed
Конец, если
Следующий cl
End Sub 
 Обратите внимание, что выделенные ячейки содержат текст, который Excel считает орфографической ошибкой. Во многих случаях он также выделяет названия или названия брендов, которых он не понимает.
 Обновить все сводные таблицы в книге 
 Если у вас есть несколько сводных таблиц в книге, вы можете использовать этот код для обновления всех этих сводных таблиц одновременно.
 'Этот код обновит всю сводную таблицу в книге
Sub RefreshAllPivotTables ()
Dim PT As PivotTable
Для каждого PT в ActiveSheet.PivotTables
PT.RefreshTable
Следующий PT
End Sub 
 Подробнее об обновлении сводных таблиц можно прочитать здесь.
 Изменить регистр букв в выбранных ячейках на верхний регистр 
 Хотя в Excel есть формулы для изменения регистра букв текста, это заставляет вас делать это в другом наборе ячеек.
 Используйте этот код, чтобы мгновенно изменить регистр букв в выделенном тексте.
 'Этот код изменит выбор на верхний регистр
Sub ChangeCase ()
Dim Rng As Range
Для каждого звена в выбранном элементе.
Если Rng.HasFormula = False, то
Rng.Value = UCase (Rng.Value)
Конец, если
Следующий Rng
End Sub 
 Обратите внимание, что в этом случае я использовал UCase, чтобы сделать регистр текста Upper. Вы можете использовать LCase для нижнего регистра.
 Выделить все ячейки комментариями 
 Используйте приведенный ниже код, чтобы выделить все ячейки, в которых есть комментарии.
 'Этот код будет выделять ячейки с комментариями`
ПодсветкаCellsWithComments ()
ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue
End Sub 
 В этом случае я использовал vbBlue, чтобы придать ячейкам синий цвет. Вы можете изменить его на другие цвета, если хотите.
 Выделение пустых ячеек с помощью VBA 
 Хотя вы можете выделить пустые ячейки с помощью условного форматирования или с помощью диалогового окна «Перейти к специальному», если вам приходится делать это довольно часто, лучше использовать макрос.
 После создания вы можете разместить этот макрос на панели быстрого доступа или сохранить его в своей личной книге макросов.
 Вот код макроса VBA:
 'Этот код выделит все пустые ячейки в наборе данных
Sub HighlightBlankCells ()
Тусклый набор данных как диапазон
Установить набор данных = Выбор
Набор данных.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed
End Sub 
 В этом коде я указал, что пустые ячейки будут выделены красным цветом.Вы можете выбрать другие цвета, например синий, желтый, голубой и т. Д.
 Как отсортировать данные по одному столбцу 
 Вы можете использовать приведенный ниже код для сортировки данных по указанному столбцу.
 Sub SortDataHeader ()
Диапазон ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes
End Sub 
 Обратите внимание, что я создал именованный диапазон с именем «DataRange» и использовал его вместо ссылок на ячейки.
 Также здесь используются три ключевых параметра:
 Ключ1 - это тот, по которому вы хотите отсортировать набор данных.В приведенном выше примере кода данные будут отсортированы на основе значений в столбце A.
 Порядок. Здесь вам нужно указать, хотите ли вы сортировать данные в порядке возрастания или убывания.
 Заголовок - Здесь вам нужно указать, есть ли у ваших данных заголовки или нет.
 Подробнее о сортировке данных в Excel с помощью VBA.
 Как отсортировать данные по нескольким столбцам 
 Предположим, у вас есть набор данных, как показано ниже:
 Ниже приведен код, который сортирует данные по нескольким столбцам:
 Sub SortMultipleColumns ()
С ActiveSheet.Сортировать
 .SortFields.Add Key: = Range ("A1"), Order: = xlAscending.
 .SortFields.Add Key: = Range ("B1"), Order: = xlAscending.
 .SetRange Range ("A1: C13")
 .Header = xlYes
 .Применять
Конец с
End Sub 
 Обратите внимание, что здесь я указал сначала сортировку на основе столбца A, а затем на основе столбца B.
 Результат будет примерно таким, как показано ниже:
 Как получить только числовую часть из строки в Excel 
 Если вы хотите извлечь только числовую часть или только текстовую часть из строки, вы можете создать пользовательскую функцию в VBA.
 Затем вы можете использовать эту функцию VBA на листе (как и обычные функции Excel), и она будет извлекать из строки только числовую или текстовую часть.
 Примерно как показано ниже:
 Ниже приведен код VBA, который создаст функцию для извлечения числовой части из строки:
 'Этот код VBA создаст функцию для получения числовой части из строки
Функция GetNumeric (CellRef As String)
Dim StringLength As Integer
StringLength = Len (CellRef)
Для i = 1 до StringLength
Если IsNumeric (Mid (CellRef, i, 1)), то Result = Result & Mid (CellRef, i, 1)
Далее я
GetNumeric = Результат
Конечная функция 
 Вам нужно поместить код в модуль, а затем вы можете использовать функцию = GetNumeric на рабочем листе.
 Эта функция принимает только один аргумент, который является ссылкой на ячейку, из которой вы хотите получить числовую часть.
 Аналогично, ниже приведена функция, которая получит только текстовую часть из строки в Excel:
 'Этот код VBA создаст функцию для получения текстовой части из строки
Функция GetText (CellRef As String)
Dim StringLength As Integer
StringLength = Len (CellRef)
Для i = 1 до StringLength
Если Not (IsNumeric (Mid (CellRef, i, 1))), то Result = Result & Mid (CellRef, i, 1)
Далее я
GetText = Результат
Функция завершения 
 Итак, вот некоторые из полезных кодов макросов Excel, которые вы можете использовать в своей повседневной работе для автоматизации задач и повышения производительности.
  Другие руководства по Excel, которые могут вам понравиться: 
 Как записывать и создавать свои собственные макросы Excel 
 Электронные таблицы бесконечно гибкие, особенно в Excel, одном из самых мощных приложений для работы с электронными таблицами. Однако большинство людей используют лишь небольшой процент своих, казалось бы, бесчисленных возможностей. Тем не менее, не требуются годы обучения, чтобы воспользоваться возможностями электронных таблиц и магией автоматизации макросов Excel.
 Скорее всего, вы уже используете такие функции, как  = sum (A1: A5) , простые фрагменты текста, которые складывают, усредняют и вычисляют ваши значения.Именно они делают электронные таблицы мощным инструментом для обработки чисел и текста. Следующий шаг - макросы: это инструменты, автоматизирующие простые задачи и помогающие сделать больше за меньшее время. Вот как разблокировать эту новую часть вашего набора навыков Excel, создав свои собственные макросы в Excel.
 Впервые работали с электронными таблицами? Начните сначала с нашего руководства по электронной таблице 101 - оно знакомит вас с основными функциями электронных таблиц, которые помогут вам начать работу с любым приложением для работы с электронными таблицами: Google Таблицы, Excel или любой другой инструмент для работы с электронными таблицами.
  Этот пост был первоначально опубликован в августе 2016 года, но был дополнен дополнительными советами 
 Что такое макросы Excel? 
 Макросы - это код, который автоматизирует работу в программе - они позволяют вам добавлять свои собственные крошечные функции и улучшения, которые помогут вам выполнить именно то, что вам нужно, быстро, всего одним нажатием кнопки. В таком инструменте работы с электронными таблицами, как Excel, макросы могут быть особенно мощными. Скрытые за обычным пользовательским интерфейсом, они более мощные, чем стандартные функции, которые вы вводите в ячейку (например,грамм.  = ЕСЛИ (A2 <100,100, A2) ).
 Эти макросы заставят Excel работать на вас. Они заменяют действия, которые вы выполняете вручную - все, от форматирования ячеек, копирования значений и подсчета итогов. Таким образом, с помощью нескольких щелчков мыши вы можете быстро заменить повторяющиеся задачи.
 Для создания этих макросов вы можете просто записать свои действия в Excel, чтобы сохранить их как повторяемые шаги, или вы можете использовать Visual Basic для приложений (VBA), простой язык программирования, встроенный в Microsoft Office. Мы покажем вам, как использовать и то, и другое ниже, а также поделимся примерами макросов Excel, которые помогут вам начать работу.
  Совет:  Это руководство и все примеры написаны в Excel 2016 для Windows, но принципы применимы к Excel 2007 и новее как для Mac, так и для ПК.
 Зачем нужны макросы Excel? 
 Обучение автоматизации Excel - один из самых простых способов ускорить вашу работу, особенно потому, что Excel используется во многих рабочих процессах. Допустим, каждую неделю вы экспортируете данные аналитики из своей системы управления контентом (CMS) для создания отчета о своем сайте. Единственная проблема в том, что эти экспортируемые данные не всегда в формате, удобном для Excel.Они беспорядочные и часто содержат гораздо больше данных, чем требуется в вашем отчете. Это означает, что вам нужно очистить пустые строки, скопировать и вставить данные в нужное место, а также создать свои собственные диаграммы для визуализации данных и сделать их удобными для печати. На выполнение всех этих шагов может уйти несколько часов.
 Если бы был только способ нажать одну кнопку и позволить Excel сделать это за вас в одно мгновение ... Ну, вы можете догадаться, что я собираюсь сказать дальше?
 Есть!
 Все, что для этого требуется, - это немного времени, чтобы настроить макрос, и затем этот код будет каждый раз автоматически выполнять эту работу за вас.Это даже не так сложно, как кажется.
 Как создать свой первый макрос Excel 
 Вы уже знакомы с Excel и знакомы с его сеткой ячеек, в которую вы вводите текст и функции. Однако для создания макросов Excel вам понадобится дополнительный инструмент, встроенный в Excel: редактор Visual Basic.
 Знакомство с редактором VBA 
 Excel имеет встроенный инструмент для написания макросов, который называется редактором Visual Basic или сокращенно редактором VBA. Чтобы открыть это, откройте электронную таблицу и используйте ярлык  Alt  +  F11  (для Mac:  Fn  +  Shift  +  F11 ).
 Новое всплывающее окно называется редактором VBA. Здесь вы будете редактировать и хранить все свои макросы. Его расположение может немного отличаться от этого снимка экрана, но вы можете перемещать окна в нужном вам порядке. Просто убедитесь, что панель Project Explorer открыта, чтобы вы могли легко редактировать свои макросы.
 Ваши макросы будут состоять из «модулей» или файлов с вашим кодом VBA. Вы добавите новый модуль или откроете существующий в редакторе VBA, а затем введите нужный код.Чтобы вставить модуль, нажмите «Вставить», а затем «Модуль». Затем вы увидите пустое место для написания кода справа.
 Как записать макрос Excel 
 Макрос можно создать двумя способами: закодировать или записать. Основное внимание в этой статье уделяется первому, но запись макроса настолько проста и удобна, что ее тоже стоит изучить. Запись макроса - хороший способ познакомиться с основами VBA. Позже он служит удобным хранилищем для кода, который вам не нужно запоминать.
 Когда вы записываете макрос, вы говорите Excel начать запись. Затем вы выполняете задачи, которые хотите перевести в код VBA. Когда вы закончите, скажите Excel, чтобы он прекратил запись, и вы сможете использовать этот новый макрос для повторения действий, которые вы только что выполнили, снова и снова.
 У этого есть ограничения, поэтому вы не можете автоматизировать каждую задачу или стать экспертом в автоматизации, только записывая. Иногда вам все равно придется вводить или редактировать код вручную. Но это все еще удобный способ начать работу.Вот как: 1. Перейдите на вкладку «Просмотр» на ленте и щелкните маленькую стрелку под кнопкой «Макросы». 2. Затем нажмите «Записать макрос». 3. Введите имя макроса и нажмите «ОК», чтобы начать запись. 4. Выполните действия в вашей электронной таблице, которые вы хотите превратить в макрос. 5. Когда вы закончите, перейдите на вкладку «Просмотр», снова нажмите маленькую стрелку под кнопкой «Записать макрос» и выберите «Остановить запись».
 Теперь используйте ярлык  Alt + F11  (для Mac:  Fn + Shift + F11 ), чтобы открыть редактор VBA, и дважды щелкните «Модуль 1» в проводнике проектов.
 Это ваш первый код! Удивительно, правда? Возможно, вы не написали его сами, но он все равно создан вашими действиями.
 Твой вид, вероятно, отличается от моего. Вы можете догадаться, что делает мой код?
  Sub Makebold  - это просто текст  Sub , за которым следует имя, которое я ввел, когда начал запись.
 Зеленая линия на самом деле ничего не делает - это комментарий, в который вы можете добавить объяснение того, что делает макрос.
  Selection.Font.Bold = True  делает значения в выбранных ячейках  полужирным .
  End sub  просто сообщает Excel, что макрос здесь останавливается.
 Что произойдет, если я изменю часть третьей строки  True  на  False ? Затем макрос  удалит  любое выделение жирным шрифтом из выделения вместо того, чтобы делать его жирным.
 Вот как вы записываете простой макрос.Но настоящая сила макросов проявляется тогда, когда вы можете написать их самостоятельно, поэтому давайте начнем учиться писать простой код VBA.
 Как кодировать свои собственные макросы Excel 
 Макросы - это просто фрагменты кода в Excel, которые выполняют ваши приказы. После того, как вы напишете код в редакторе VBA, вы можете запустить его и позволить коду творить чудеса с вашей электронной таблицей. Но что еще лучше, так это встроить макрос в электронную таблицу, и лучший инструмент для этого - кнопки.
 Итак, прежде чем мы начнем кодировать, давайте добавим кнопку для запуска нашего макроса.
 Добавить кнопку для запуска макроса 
 Вы можете использовать различные объекты Excel в качестве кнопок для запуска макросов, но я предпочитаю использовать форму из вкладки «Вставка». Когда вы вставили фигуру, щелкните ее правой кнопкой мыши и выберите «Назначить макрос…» Затем выберите макрос, который вы хотите запускать при щелчке фигуры - возможно, тот, который вы только что создали с записью, и сохраните его, нажав «ОК».
 Теперь, когда вы щелкаете фигуру, которую мы только что превратили в кнопку, Excel запускает макрос без необходимости каждый раз открывать код.
 Прежде чем мы начнем, следует отметить еще одну вещь: сохранение вашей электронной таблицы с помощью макросов. По умолчанию файлы электронных таблиц Excel с расширением  .xlsx  не могут содержать макросы. Вместо этого при сохранении электронной таблицы выберите формат «Excel Macro-Enabled Workbook (* .xlsm)» и добавьте имя файла как обычно.
 Сделайте это, чтобы сохранить электронную таблицу, прежде чем мы начнем кодировать.
 А теперь приступим к написанию кода!
 Копирование и вставка - это самый простой способ перемещения данных, но он по-прежнему утомителен.Что, если бы ваша таблица могла сделать это за вас? С макросом это возможно. Давайте посмотрим, как написать макрос, который будет копировать данные и перемещать их в электронной таблице.
 Откройте файл проекта, который вы скачали ранее, и убедитесь, что выбран лист «Копировать, вырезать и вставить». Это образец базы данных сотрудников с именами, отделами и зарплатами некоторых сотрудников.
 Давайте попробуем скопировать все данные из столбцов с A по C в столбцы D по F с помощью VBA. Во-первых, давайте посмотрим на код, который нам нужен:
 Копирование ячеек с VBA 
 Копирование в VBA довольно просто.Просто вставьте этот код в редактор VBA:  Range («Вставьте здесь диапазон»). Скопируйте . Вот несколько примеров:
 Помните, когда вы записывали макрос раньше? Макрос имел  Sub Nameofmacro ()  и  End sub  в верхней и нижней строке кода. Эти строки всегда должны быть включены. Excel также упрощает эту задачу: когда вы вводите «Sub», за которым следует имя макроса в начале кода,  End sub  автоматически вставляется в нижнюю строку.
  Совет:  Не забывайте вводить эти строки вручную, когда вы не используете средство записи макросов.
 Склеивание ячеек с помощью VBA 
 Склеивание может выполняться разными способами в зависимости от того, что вы хотите вставить. В 99% случаев вам понадобится одна из этих двух строк кода:
  Диапазон («Ячейка / область, в которую вы хотите вставить»). Специальная вставка  ← вставляет как обычно (формулы и форматирование)
  Диапазон («Ячейка / область, куда вы хотите вставить»). Pastespecial xlPasteValues ​​ ← вставляет только значения
 Вырезание ячеек с помощью VBA 
 Если вы хотите переместить данные вместо их копирования, вам необходимо вырезать это.Вырезать довольно легко и следует той же логике, что и копирование.
 Вот код:  Диапазон («Вставьте здесь диапазон»). Вырезать 
 При вырезании нельзя использовать команду «Специальная вставка». Это означает, что нельзя вставлять только значения или только форматирование. Следовательно, вам нужны эти строки, чтобы вставить ваши ячейки с помощью VBA: Range («Вставьте, куда вы хотите вставить»). Выберите ActiveSheet.Paste
. Например, вот код, который вам нужно вырезать из диапазона  A: C  и вставьте его в  D1 :
  Диапазон ("A: C").Вырезать 
  Диапазон ("D1"). Выберите 
  ActiveSheet.Paste 
 Копирование, вырезание и вставка - это простые действия, которые можно выполнить вручную, не беспокоясь. Но когда вы копируете и вставляете одни и те же ячейки несколько раз в день, кнопка, которая делает это за вас, может сэкономить кучу времени. Кроме того, вы можете комбинировать копирование и вставку в VBA с другим классным кодом, чтобы автоматически делать еще больше в вашей электронной таблице.
 Добавление циклов в VBA 
 Я только что показал вам, как выполнить простое действие (копирование и вставка) и прикрепить его к кнопке, чтобы вы могли делать это одним щелчком мыши. Это всего лишь одно автоматическое действие. Однако, когда у вас есть код, который повторяется, он может выполнять более длинные и сложные задачи автоматизации за секунды.
 Взгляните на лист «Петли» в файле проекта. Это те же данные, что и на предыдущем листе, но теперь каждая третья строка данных перемещена на один столбец вправо.Этот тип ошибочной структуры данных не является необычным при экспорте данных из старых программ.
 Исправление вручную может занять много времени, особенно если электронная таблица содержит тысячи строк вместо небольших выборочных данных в этом файле проекта.
 Давайте сделаем петлю, которая исправит это за вас. Введите этот код в модуль, а затем посмотрите пояснения под изображением:
 Эта строка гарантирует, что цикл начинается в верхней левой ячейке на листе и случайно не испортит данные, начав с другого места.
 Строка  For i = 1 - 500  означает, что количество запусков цикла (представленное  i ) является возрастающим числом, которое начинается с 1 и заканчивается на 500. Это означает, что цикл будет пробежать 500 раз. Количество запусков цикла зависит от действий, которые вы хотите выполнить. Используйте здесь свой здравый смысл. 500 раз - это слишком много для нашего образца набора данных, но идеально подошло бы, если бы в базе данных было 1500 строк данных.
 Эта строка распознает активную ячейку и указывает Excel переместиться на 3 строки вниз и выбрать эту ячейку, которая затем станет новой активной ячейкой.Если бы в наших данных была потеряна каждая четвертая строка, а не каждая третья, мы могли бы просто заменить 3 на 4 в этой строке.
 Эта строка сообщает Excel, что делать с этой вновь выбранной ячейкой. В этом случае мы хотим удалить ячейку таким образом, чтобы ячейки справа от ячейки сдвинулись влево. Это достигается с помощью этой линии. Если мы хотели сделать что-то еще с неуместными строками, это то место, где можно это сделать. Если бы мы хотели полностью удалить каждую третью строку, то должна была бы быть строка:  Selection.Цитадель .
 Эта строка сообщает Excel, что в цикле больше нет действий. В этом случае 2 и 5 - это кадр цикла, а 3 и 4 - действия внутри цикла.
 Когда мы запускаем этот макрос, это приведет к аккуратному набору данных без каких-либо неуместных строк.
 Добавление логики в VBA 
 Логика - это то, что оживляет фрагмент кода, делая его больше, чем просто машину, которая может выполнять простые действия и повторяться. Логика - это то, что делает Excel-лист почти человеком - он позволяет ему самостоятельно принимать разумные решения.Давайте использовать это для автоматизации!
 Этот раздел посвящен операторам IF, которые включают логику «если-то-то-то», точно так же, как функция ЕСЛИ в Excel.
 Допустим, экспорт с CMS нашего сайта оказался еще более ошибочным, чем ожидалось. Каждая третья строка по-прежнему неуместна, но теперь некоторые из неуместных строк размещаются на 2 столбца справа, а не на 1 столбец справа. Взгляните на лист "IF-statement" в файле проекта, чтобы увидеть, как он выглядит.
 Как это учесть в нашем макросе? Добавляем в цикл IF-оператор!
 Давайте сформулируем, что мы хотим, чтобы Excel делал:
 Начнем с ячейки A1.Затем мы переходим на три строки вниз (до ячейки A4, A7, A10 и т. Д.), Пока не кончатся данные. Каждый раз, когда мы опускаемся на три строки вниз, мы проверяем эту строку, чтобы увидеть, не смещены ли данные на 1 или 2 столбца. Затем переместите данные в строке на 1 или 2 столбца влево.
 Теперь давайте переведем это в код VBA. Мы начнем с простого цикла, как и раньше:
 Единственное, что нам сейчас нужно, это написать, что должно происходить внутри цикла. Это часть «на три ряда вниз», которую мы разработали еще в разделе о петлях.Теперь мы добавляем IF-оператор, который проверяет, насколько данные неуместны, и соответствующим образом исправляет их.
 Это последний код, который нужно скопировать в редактор модуля, каждый шаг описан ниже:
 Это первая часть оператора IF. Он говорит, что , если  ячейка справа от активной ячейки (или  Activecell.Offset (0,1)  в коде VBA) пусто (представлено  = "" ) , тогда  что-то делает. Это то же самое действие, что и при создании цикла: удаление активной ячейки и перемещение активной строки на одну ячейку влево (выполняется с помощью  Selection.Удалить Shift: = код xlToLeft ). На этот раз мы делаем это два раза вместо одного, потому что в левой части строки есть две пустые ячейки.
 Если вышеуказанное неверно, и ячейка справа от активной ячейки не пуста, то активная ячейка пуста. Следовательно, нам нужно только удалить активную ячейку и переместить активную строку на одну ячейку влево один раз.
 Оператор IF всегда должен заканчиваться  End If , чтобы сообщить Excel о завершении работы.После IF-оператора цикл может выполняться снова и снова, повторяя IF-оператор каждый раз.
 Поздравляем, вы только что создали макрос, который может очищать беспорядочные данные! Посмотрите анимацию ниже, чтобы увидеть, как это работает (если вы еще не пробовали это сделать).
 Автоматизация Excel без макросов 
 Макросы Excel имеют только одну проблему: они привязаны к вашему компьютеру и не могут работать в Excel Web App или на вашем мобильном устройстве. И они лучше всего работают с данными, уже находящимися в вашей электронной таблице, что затрудняет перенос новых данных из других приложений в вашу электронную таблицу.
 Инструмент интеграции приложений Zapier может помочь. Он соединяет версию Excel Office 365 для бизнеса с сотнями других приложений - Stripe, Salesforce, Slack и др. - так что вы можете автоматически записывать данные в электронную таблицу или запускать задачи в других приложениях прямо из Excel.
 Вот как это работает. Допустим, вы хотите сохранить записи формы Typeform в электронную таблицу Excel. Просто создайте учетную запись Zapier и нажмите кнопку  Make a Zap  в правом верхнем углу. Затем выберите  Typeform  в средстве выбора приложения и настройте его так, чтобы он следил за вашей формой на предмет новых записей.
  Zapier может следить за вашей формой Typeform на предмет появления новых записей 
 Протестируйте свой Zap, затем нажмите  Continue , чтобы добавить еще один шаг в свой Zap. На этот раз мы выберем приложение  Excel  и выберем  Добавить строку  в нашу электронную таблицу. Вы также можете обновить строку или выполнить поиск конкретной строки в таблице, если хотите.
  Zapier позволяет добавлять, обновлять или находить строки в электронной таблице Excel 
 Теперь выберите электронную таблицу и рабочий лист, затем щелкните значок + справа от каждой строки электронной таблицы, чтобы выбрать правильное поле формы для сохранения в эту строку электронной таблицы .Сохраните и протестируйте интеграцию с Zapier, затем включите ее. Затем каждый раз, когда ваша форма Typeform будет заполнена, Zapier будет сохранять эти данные в вашу электронную таблицу Excel.
  Zapier может добавлять данные формы непосредственно в нужную строку электронной таблицы. 
 Вот несколько отличных способов начать автоматизацию Excel с помощью Zapier за несколько щелчков мышью - или создать собственную интеграцию Excel для подключения ваших электронных таблиц к любимым приложениям.
 Управление данными электронной таблицы 
 Сохранение записей формы в электронную таблицу Excel 
 Журнал данных в электронную таблицу Excel 
 Работайте с электронной таблицей 
 Создайте свой собственный макрос! 
 Теперь вы изучили некоторые из наиболее важных инструментов VBA для создания макросов для очистки данных и автоматизации вашей работы.