Автоматизация задач с помощью средства записи макросов — Excel
Для автоматизации повторяющихся задач в Microsoft Excel можно быстро записать макрос. Предположим, у вас есть даты в различном формате и вы хотите применить ко всем из них один формат. Это можно сделать с помощью макроса. Вы можете записать макрос, который применяет нужный формат, а затем запускать его при необходимости.
При записи макроса все действия в записи макроса записываются Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов или даже импорт данных из внешнего источника, скажем, Microsoft Access. Visual Basic Приложение (VBA) — это подмножество мощного Visual Basic программирования, которое входит в большинство Office приложений. Хотя VBA позволяет автоматизировать процессы как в приложениях, так и между Office, необязательно знать код VBA или программирование на компьютере, если оно делает то, что вам нужно.
Важно знать, что при записи макроса регистрируются почти все ваши действия. Поэтому если вы допустите ошибку, например нажмете не ту кнопку, средство записи макросов зарегистрирует это действие. В таком случае можно снова записать всю последовательность или изменить код VBA. Поэтому перед записью процесса следует хорошо проработать его. Чем точнее вы запишете последовательность, тем более эффективно будет работать макрос.
Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки «Разработчик».
Запись макроса
Перед записью макросов полезно знать следующее:
Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.
Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.
В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.
Чтобы записать макрос, следуйте инструкциям ниже.
На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.
В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.
Примечание: Первым символом имени макроса должна быть буква. Последующие символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не должно содержаться пробелов; в качестве разделителей слов следует использовать знаки подчеркивания. Если используется имя макроса, являющееся ссылкой на ячейку, может появиться сообщение об ошибке, указывающее на недопустимое имя макроса..
Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+ SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции «Отменить» в данном экземпляре Excel.
В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.
В поле Описание при необходимости введите краткое описание действий макроса.
Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.
Чтобы начать запись макроса, нажмите кнопку ОК.
Выполните действия, которые нужно записать.
На вкладке Разработчик в группе Код нажмите кнопку Остановить запись 
.
Работа с макросами, записанными в Excel
На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.
Внимание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.
Ниже приведены дополнительные сведения о работе с макросами в Excel.
Сведения о параметрах безопасности макросов и их значении.
Макросы можно запускать различными способами, например с помощью сочетания клавиш, графического объекта, панели быстрого доступа, кнопки или даже при открытии книги.
С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.
Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу
Щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому вы хотите назначить существующий макрос, и выберите пункт Назначить макрос.
В поле Назначить макроса выберите макрос, который вы хотите назначить.
Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Вы можете назначать макросы формам и элементам ActiveX на листе.
Узнайте, как включать и отключать макросы в файлах Office.
Открытие редактора Visual Basic
Нажмите клавиши ALT+F11.
Узнайте, как найти справку по элементам Visual Basic.
Работа с записанным кодом в редакторе Visual Basic (VBE)
С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.
Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.
Запись макроса
Перед записью макросов полезно знать следующее:
Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.
Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.
В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.
Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее.
Перейдите в Excel > параметры. > ленты & панель инструментов.
В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.
Чтобы записать макрос, следуйте инструкциям ниже.
На вкладке Разработчик нажмите кнопку Запись макроса.
В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.
Примечание: Первым символом имени макроса должна быть буква. Последующие символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не должно содержаться пробелов; в качестве разделителей слов следует использовать знаки подчеркивания. Если используется имя макроса, являющееся ссылкой на ячейку, может появиться сообщение об ошибке, указывающее на недопустимое имя макроса..
В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.
Как правило, макрос сохраняется в расположении Эта книга, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите Личная книга макроса. При выборе личнойкниги макроса Excel создает скрытую личную книгу макроса (PERSONAL.XLSB), если она еще не существует, и сохраняет макрос в этой книге. Книги в этой папке открываются автоматически при Excel, и любой код, сохраненный в личной книге макроса, будет указан в диалоговом окну Макрос, которое объясняется в следующем разделе.
Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.
В поле Описание при необходимости введите краткое описание действий макроса.
Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Полезно ввести содержательное описание со всеми сведениями, которые могут быть полезны вам или другим пользователям, которые будут запускать макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.
Чтобы начать запись макроса, нажмите кнопку ОК.
Выполните действия, которые нужно записать.
На вкладке Разработчик щелкните Остановить запись.
Работа с макросами, записанными в Excel
На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. При этом откроется диалоговое окно Макрос.
Примечание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.
Ниже приведены дополнительные сведения о работе с макросами в Excel.
Узнайте, как включать и отключать макросы в Excel для Mac.
Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу
Щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому вы хотите назначить существующий макрос, и выберите пункт Назначить макрос.
В поле Назначить макроса выберите макрос, который вы хотите назначить.
Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Вы можете назначать макросы формам и элементам ActiveX на листе.
Открытие редактора Visual Basic
На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.
Узнайте, как найти справку по элементам Visual Basic.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Запись, редактирование и создание сценариев Office в Excel в Интернете
В этом учебнике вы ознакомитесь с основами записи, редактирования и создания сценария Office для Excel в Интернете. Вы запишите сценарий, применяющий форматирование к листу продаж. После этого вы измените записанный сценарий, чтобы применить дополнительное форматирование, создать таблицу и отсортировать ее. Эта шаблон записи с последующим изменением является важным инструментом для просмотра ваших действий Excel в виде кода.
Необходимые компоненты
Перед началом работы с этим учебником у вас должен быть доступ к сценариям Office. Для этого требуется следующее:
Этот учебник предназначен для пользователей с начальным и средним уровнем знаний по JavaScript или TypeScript. Если вы впервые работаете с JavaScript, рекомендуем начать с учебника Mozilla по JavaScript. Чтобы получить дополнительные сведения о среде сценариев, ознакомьтесь со статьей Среда редактора кода сценариев Office.
Добавление данных и запись простого сценария
Сначала нам потребуются некоторые данные и небольшой начальный сценарий.
Создайте книгу в Excel в Интернете.
Скопируйте следующие данные о продаже фруктов и вставьте их на лист, начиная с ячейки A1.
| Фрукты | 2018 | 2019 |
|---|---|---|
| Апельсины | 1000 | 1200 |
| Лимоны | 800 | 900 |
| Лаймы | 600 | 500 |
| Грейпфруты | 900 | 700 |
Откройте вкладку Автоматизация. Если вы не видите вкладку Автоматизация, проверьте переполнение ленты, нажав стрелку раскрывающегося списка. Если нужного элемента по-прежнему нет, выполните рекомендации из статьи Устранение неполадок в сценариях Office.
Нажмите кнопку Записать действия.
Выделите ячейки A2:C2 (строка «Апельсины») и установите оранжевый цвет заливки.
Чтобы остановить запись, нажмите кнопку Остановить.
Ваш лист должен выглядеть, как показано ниже (не волнуйтесь, если цвет отличается):
Редактирование существующего сценария
Предыдущий сценарий окрасил строку «Апельсины» в оранжевый цвет. Давайте добавим желтый цвет для строки «Лимоны».
В открывшейся области Сведения нажмите кнопку Изменить.
Должен отобразиться примерно такой код:
Этот код получает текущий лист из книги. Затем он настраивает цвет заливки диапазона A2:C2.
Диапазоны — это фундаментальная часть сценариев Office в Excel в Интернете. Диапазон — это непрерывный прямоугольный блок ячеек, содержащий значения, формулы и форматирование. Они представляют собой базовую структуру ячеек, в которой можно выполнять большинство задач сценариев.
Добавьте следующую строку в конце сценария (между местом настройки значения color и закрывающей скобкой > ):
Протестируйте сценарий, нажав Запустить. Книга должна выглядеть следующим образом:
Создание таблицы
Давайте преобразуем эти данные продаж фруктов в таблицу. Мы воспользуемся собственным сценарием для всего процесса.
Добавьте следующую строку в конце сценария (перед закрывающей скобкой > ):
Ваш сценарий должен выглядеть так:
Запустите сценарий. Вы увидите следующую таблицу:
При повторном запуске сценария возникнет ошибка. Это связано с тем, что вы не можете создать таблицу поверх другой таблицы. Однако вы можете запустить этот сценарий на другом листе или в другой книге.
Повторный запуск сценария
Дальнейшие действия
Выполните инструкции учебника Чтение данных книги с помощью сценариев Office в Excel в Интернете. С его помощью вы научитесь читать данные из книги с помощью сценариев Office.
Как записать макрос в Excel? Пошаговая инструкция.
Что такое макрос?
Для начала немного о терминологии.
Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.
Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.
Отображение вкладки «Разработчик» в ленте меню
Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку «Разработчик». Для этого выполните следующие шаги:
В результате на ленте меню появится вкладка «Разработчик»
Запись макроса в Excel
Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».
Вот шаги для записи такого макроса:
Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.
Теперь давайте рассмотрим код который записал макрорекодер. Выполните следующие действия, чтобы открыть редактор кода:
Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.
Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.
Что записывает макрос?
Теперь перейдем к редактору кода и посмотрим что у нас получилось.
Вот шаги по открытию редактора VB в Excel:
Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.
Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.
Когда мы записали макрос «ВводТекста», в редакторе VB произошли следующие вещи:
Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.
Вот код, который записан макрорекодером:
Теперь давайте пробежим по каждой строке кода и опишем что и зачем.
Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.
Абсолютная и относительная запись макроса
Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст «Excel» и затем перейдет к ячейке K5.
Теперь давайте запишем макрос в режиме относительных ссылок:
Макрос в режиме относительных ссылок будет сохранен.
Теперь сделайте следующее.
Вот код, который записал макрорекодер:
Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.
Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.
Что нельзя сделать с помощью макрорекодера?
Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.
Расширение файлов Excel, которые содержат макросы
Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).
Начало работы с VBA в Office
Постоянно возникает проблема, вызванная повторяющейся очисткой данных в 50 таблицах в Word? Хотите, чтобы при открытии определенного документа пользователи получали запрос на добавление данных? Не знаете, как быстро и легко перенести список контактов из Microsoft Outlook в таблицу Microsoft Excel?
Можно выполнить эти задачи и достичь гораздо большего с помощью Visual Basic для приложений (VBA) для Office. Это простой и в то же время мощный язык программирования, применение которого позволит расширить возможности приложений Office.
Данная статья предназначена для опытных пользователей Office, которые желают познакомиться с VBA и получить некоторое представление о том, как программирование может помочь им настроить Office.
Набор приложений Office обладает множеством возможностей. Существует множество разных способов создания, форматирования и управления документами, электронной почтой, базами данных, формами, электронными таблицами и презентациями. Значительное преимущество программирования на VBA в Office заключается в том, что почти каждое действие, осуществляемое с мышью, клавиатурой или диалоговым окном, можно выполнить с помощью VBA. Если действие можно выполнить с помощью VBA, значит в дальнейшем его можно легко осуществлять сотни раз. (На самом деле автоматизация повторяющихся задач — одно из наиболее частых применений VBA в Office).
Помимо возможности написания скрипта VBA для ускорения повседневных задач, VBA можно использовать для добавления новых функций в приложения Office или создания запросов и взаимодействия с пользователем ваших документов в соответствии с потребностями вашей организации. Например, можно написать код VBA, выводящий всплывающее сообщение, которое напоминает пользователям о необходимости сохранения документа на определенном сетевом диске при первой попытке его сохранения.
В этой статье описываются некоторые основные причины для использования возможностей программирования на VBA. Здесь описывается язык VBA и встроенные средства, которые можно использовать для работы со своими решениями. И, наконец, в данной статье можно найти советы и приемы, чтобы избежать некоторых распространенных ошибок программирования.
Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.
Причины использования VBA
Есть несколько основных причин использовать программирование на VBA в Office.
Автоматизация и повторяемость
Программирование на VBA эффектно и эффективно при создании решений для устранения повторяющихся проблем форматирования и исправлений. Например, вам когда-нибудь приходилось изменять стиль абзаца в верхней части каждой страницы в Word? Или менять формат нескольких таблиц, скопированных из Excel в документ Word или электронную почту Outlook? А вносить одинаковые изменения в несколько контактов Outlook?
Если имеется изменение, которое нужно внести более десяти или двадцати раз, возможно, стоит реализовать его с помощью VBA. Если изменение нужно внести несколько сотен раз, определенно следует рассмотреть VBA. Почти все изменения форматирования или редакторские правки, которые можно внести вручную, можно реализовать на VBA.
Расширения возможностей взаимодействия с пользователем
Иногда некоторые действия пользователей с документом или приложением Office обязательны или желательны, но не доступны в стандартном приложении. Например, нужно уведомлять пользователей о необходимости что-то сделать при открытии, сохранении или печати документа.
Взаимодействие приложений Office
Нужно скопировать все контакты из Outlook в Word и отформатировать их определенным способом? Или же нужно переместить данные из Excel в набор слайдов PowerPoint? Иногда простое копирование и вставка не работают так, как надо, или же это происходит слишком медленно. Можно использовать программирование на VBA для работы с данными в двух или более приложениях Office одновременно, а затем изменить содержимое в одном приложении с учетом содержимого в другом.
Другой способ
Программирование на VBA — это мощное решение, но данный подход не всегда является оптимальным. Иногда, чтобы достичь поставленных целей, имеет смысл воспользоваться другими методами.
Важнейший вопрос состоит в поиске более легкого способа. Перед запуском проекта VBA обратите внимание на встроенные инструменты и стандартные функциональные возможности. Например, если необходимо выполнить времяемкую задачу редактирования или размещения элементов, для решения проблемы следует рассмотреть возможность использования стилей или сочетания клавиш. Можете ли вы выполнить задачу единожды, а затем повторить ее с помощью сочетания клавиш CTRL+Y («Повторить»)? Можно ли создать документ правильного формата или с нужным шаблоном и затем скопировать содержимое в новый документ?
Приложения Office обладают множеством возможностей. Возможно, нужное решение уже в них предусмотрено. Узнайте больше об Office, прежде чем начинать программирование.
Перед созданием проекта VBA убедитесь, что у вас есть время на работу с VBA. Программирование требует внимания и может оказаться непредсказуемым. Если вы начинающий программист, тем более не стоит увлекаться программированием, если не хватает времени на внимательную работу. Попытки написать «скрипт на скорую руку» для решения проблемы в сжатые сроки может привести к напряженной ситуации. Если вы торопитесь, воспользуйтесь более привычными методами, даже если они сопряжены с монотонностью и повторениями.
Введение в программирование на VBA
Использование кода для выполнения операций приложениями
Написание кода может казаться очень сложным и непонятным процессом. На самом деле, его базовые принципы основаны на применении повседневной логики и вполне доступны. Объекты в приложениях Microsoft Office, созданные для получения инструкций, можно сравнить с кнопками телефона. Когда вы нажимаете кнопку, телефон распознает команду и включает соответствующую цифру в набираемую последовательность. При программировании вы взаимодействуете с приложением, отправляя инструкции различным объектам. Эти объекты эффективны, но у них есть свои ограничения. Они смогут делать только то, для чего были разработаны, и выполнять только ваши инструкции.
Например, представьте себе пользователя, который открывает документ Word, вносит несколько изменений, а затем сохраняет и закрывает его. Для программирования на VBA приложение Word предоставляет объект Document. Используя код VBA, можно заставить объект Document выполнять такие действия, как открытие, сохранение и закрытие.
В следующем разделе описывается организация объектов.
Объектная модель
Разработчики организуют объекты программирования в виде иерархии, и такая иерархия называется объектной моделью приложения. В Word, например, есть объект верхнего уровня Application, который содержит объект Document. Объект Document содержит объекты Paragraph и т. д. В объектных моделях приблизительно отражено то, что вы видите в пользовательском интерфейсе. Они являются концептуальной картой приложения и его возможностей.
Определение объекта называется классом, и вы, возможно, увидите, как два этих термина используются попеременно. С технической точки зрения класс — это описание или шаблон, используемый для формирования или создания экземпляра объекта.
Уже существующим объектом можно управлять, задавая его свойства и вызывая его методы. Если представить объект в виде имени существительного, свойства станут прилагательными, описывающими существительное, а методы — глаголами, которые приводят его в действие. Изменение свойства приводит к модификации определенной характеристики внешнего вида или поведения объекта. Вызов одного из методов объекта заставляет последний выполнить какое-либо действие.
Код VBA в этой статье взаимодействует с приложением Office, в котором многие объекты, которыми управляет код, уже настроены и работают (например, объект Application приложения, Worksheet в Excel, Document в Word, Presentation в PowerPoint, объекты Explorer и Folder в Outlook). Узнав основную структуру объектной модели и некоторые ключевые свойства объекта Application, которые предоставляют доступ к его текущему состоянию, можно расширить возможности приложения Office с помощью VBA в Office.
Методы
В Word, например, можно изменить свойства и вызвать методы текущего документа Word с помощью свойства ActiveDocument объекта Application. Это свойство ActiveDocument возвращает ссылку на объект Document, активный в приложении Word. «Возвращает ссылку на» означает «предоставляет доступ к».
В приведенном ниже коде выполняется именно то, что сказано; т. е. активный документ приложения сохраняется.
Прочитайте код слева направо: «В этом приложении (Application) с документом (Document), на который ссылается активный документ (ActiveDocument), вызовите метод сохранения (Save). Помните, что Save — самая простая форма метода; ему не нужны подробные инструкции от разработчика. Объекту Document дается команда сохранения (Save), а дополнительные данные вводить не нужно.
Если методу требуются дополнительные данные, они называются параметрами. В приведенном ниже коде выполняется метод SaveAs, для которого требуется указать новое имя файла.
Значения, которые указываются в скобках после имени метода — это параметры. Здесь новое имя файла — параметр метода SaveAs.
Свойства
Для задания свойства используйте такой же синтаксис, что и для чтения свойства. В приведенном ниже коде выполняется метод для выбора ячейки A1 в Excel и затем задается свойство для записи данных в ячейку.
Первая задача при программировании на VBA — осознать объектную модель каждого приложения Office и научиться читать синтаксис объекта, метода и свойства. Во всех приложениях Office объектные модели похожи, но каждая из них обладает особенными характеристиками в соответствии с типом документов и объектов, которыми управляет.
В первой строке фрагмента кода указан объект Application, на этот раз это приложение Excel, а затем объект ActiveSheet, который предоставляет доступ к активному листу. После этого указан термин Range, который означает «определить диапазон ячеек следующим способом». Код указывает объекту Range создать себя с одной ячейкой A1. Другими словами, в первой строке кода объявляется объект Range и запускается метод для его выбора. Результат автоматически сохраняется в другом свойстве объекта Application с именем Selection.
Во второй строке кода задается значение свойства Value объекта Selection, равное «Hello World» и отображающееся в ячейке A1.
Самый простой код VBA может предоставлять доступ к объектам в приложении Office, с которым вы работаете, и задавать их свойства. Например, можно получить доступ к строкам в таблице Word и изменить их форматирование в скрипте VBA.
Это звучит просто, но может быть невероятно полезно. Написав такой код, можно освоить всю мощь программирования для внесения одинаковых изменений в несколько таблиц или документов в соответствии с определенной логикой или каким-то условием. Сделать 1000 изменений для компьютера почти то же самое, что 10, поэтому при работе с большими документами и при решении множества проблем VBA может оказаться очень полезен и сэкономить много времени.
Макросы и редактор Visual Basic
Теперь вы кое-что знаете об объектных моделях в приложениях Office. Возможно, вам не терпится попробовать вызвать методы объекта, задать его свойства и отреагировать на события объекта. Для этого необходимо написать свой код так, чтобы он распознавался в Office. Обычно это делается с помощью редактора Visual Basic. Несмотря на то, что он установлен по умолчанию, многие пользователи бывают не осведомлены даже о его наличии до тех пор, пока не включат его на ленте.
Все приложения Office используют ленту. Одной из вкладок на ленте является вкладка Разработчик, где можно вызвать редактор Visual Basic и другие инструменты разработчика. Так как в Office вкладка Разработчик не показана по умолчанию, необходимо вывести ее на экран, выполнив указанные ниже действия.
Включение вкладки «Разработчик»
На вкладке Файл выберите Параметры, чтобы открыть диалоговое окно Параметры.
Выберите пункт Настроить ленту в левой части диалогового окна.
В разделе Выбрать команды, расположенном слева в окне, выберите Часто используемые команды.
В разделе Настроить ленту, который находится справа в диалоговом окне, выберите Основные вкладки в раскрывающемся списке, а затем установите флажок Разработчик.
Нажмите кнопку OK.
В Office 2007 показ вкладки Разработчик выполняется путем нажатия кнопки Office, выбора пункта Параметры, а затем установки флажка Показать вкладку «Разработчик» на ленте в категории Популярные диалогового окна Параметры.
После включения вкладки Разработчик можно легко найти кнопки Visual Basic и Макрос.
Рисунок 1. Кнопки на вкладке «Разработчик»
Проблемы безопасности
Для защиты документов Office от вирусов и вредоносных макросов не сохраняйте код макросов в стандартных документах Office со стандартным расширением файла. Следует сохранить код в файле с особым расширением. Например, сохраняйте макросы не в стандартном документе Word с расширением DOCX, а в специальном документе Word с поддержкой макросов и расширением DOCM.
При открытии DOCM-файла система безопасности Office все равно может запретить запуск макросов в документе, сообщая или не сообщая об этом. Изучите параметры и настройки центра управления безопасностью во всех приложениях Office. По умолчанию макросы отключены, но пользователь оповещается об этом и может включить их.
Можно указать специальные папки, в которых можно выполнять макросы, создав надежные расположения, надежные документы или надежных издателей. Самый универсальный способ — использовать надежных издателей. Этот способ подходит для работы с документами, у которых есть распространяемые вами цифровые подписи. Чтобы получить дополнительные сведения о параметрах безопасности в определенном приложении Office, откройте диалоговое окно Параметры, а затем выберите Центр управления безопасностью и Параметры центра управления безопасностью.
Некоторые приложения Office, такие как Outlook, сохраняют макросы по умолчанию в основном шаблоне на локальном компьютере. Хотя это позволяет уменьшить количество проблем с безопасностью на локальном компьютере при выполнении собственных макросов, для распространения своих макросов в этом случае потребуется стратегия развертывания.
Запись макроса
Если нажать кнопку Макрос на вкладке Разработчик, открывается диалоговое окно Макрос, которое предоставляет доступ к подпрограммам или макросам VBA, которые можно использовать из определенного документа или приложения. Кнопка Visual Basic открывает редактор Visual Basic, где можно создавать и редактировать код VBA.
На вкладке Разработчик в Word и Excel также есть кнопка Запись макроса, которая автоматически создает код VBA, позволяющий воспроизводить ваши действия в приложении. Запись макроса — это великолепное средство, которое можно использовать для изучения VBA. Читая такой код, можно понять язык VBA и объединить знания пользователя и программиста Office. Только помните о том, что этот код может быть запутанным, так как редактор макросов делает допущения насчет ваших намерений, которые не всегда точны.
Запись макроса
Создайте книгу в Excel и откройте вкладку Разработчик на ленте. Нажмите кнопку Запись макроса и оставьте все параметры по умолчанию в диалоговом окне Запись макроса, в том числе имя Макрос1 и расположение Эта книга.
Нажмите кнопку ОК, чтобы начать запись макроса. Обратите внимание, что текст кнопки изменяется на Остановить запись. Нажмите эту кнопку, когда выполните все записываемые действия.
Выберите ячейку B1 и введите классическую первую строку программиста — Hello World. Прекратите ввод текста и посмотрите на кнопку Остановить запись. Она серая, так как Excel ждет, пока вы завершите ввод значения ячейки.
Выберите ячейку B2, чтобы завершить действие в ячейке B1, а затем нажмите кнопку Остановить запись.
Выберите пункт Макросы на вкладке Разработчик, выберите макрос Макрос1, если он еще не выбран, и нажмите кнопку Изменить, чтобы просмотреть код Макрос1 в редакторе Visual Basic.
Рисунок 2. Код макроса в редакторе Visual Basic
Созданный макрос должен выглядеть указанным ниже образом.
Учтите схожие моменты с предыдущим примером кода, в котором выбирается ячейка A1, и отличия от него. В этом примере кода выбирается ячейка B1, а затем строка «Hello World» записываются в активную ячейку. Кавычки вокруг текста обозначают строковое значение (в противоположность числовому значению).
Вспомните, что для отображения кнопки Остановить запись нужно было выбрать ячейку B2. Это действие также задается в строке кода. Средство записи макроса записывает каждое нажатие клавиши.
Строки кода, начинающиеся с апострофа и выделенные зеленым цветом — это комментарии, которые поясняют код или напоминают другим программистам его предназначение. VBA игнорирует любую строку или ее часть, которые начинаются с одинарной кавычки. Написание понятных и подходящих комментариев в коде очень важно, но в этой статье данная тема не затрагивается. В следующих ссылках на этот код в данной статье эти четыре строки комментариев отсутствуют.
Когда средство записи макроса генерирует код, оно использует сложный алгоритм для определения нужных методов и свойств. Если данное свойство незнакомо, пользователю могут помочь множество ресурсов. Например, в записанном макросе средство записи макроса сгенерировало код, который ссылается на свойство FormulaR1C1. Не знаете, что это значит?
Помните, что объект Application используется во всех макросах VBA. В записанном коде объект Application. используется в начале каждой строки.
Использование справки разработчика
Выберите FormulaR1C1 в записанном макросе и нажмите F1. Система справки запустит быстрый поиск, определит, что нужные темы есть в разделе «Разработчик» справки Excel, и укажет свойство FormulaR1C1. Можно щелкнуть ссылку, чтобы прочитать описание свойства, но прежде обратите внимание на ссылку Справочник по объектной модели Excel в нижней части окна. Щелкните ее, чтобы просмотреть список объектов, которые Excel использует в объектной модели для описания листов и их компонентов.
Выберите любой из них, чтобы просмотреть свойства и методы, которые применяются к этому объекту, а также ссылки на другие связанные с ними параметры. Многие записи справки содержат короткие примеры кода, которые могут быть полезны. Например, можно перейти по ссылкам в описании объекта Borders, чтобы узнать, как задать границу в VBA.
Редактирование кода
Код с границами отличается от записанного макроса. В объектной модели есть несколько способов адресации любого объекта (ячейки A1 в этом примере), что может вызывать затруднения.
Иногда самый лучший способ изучить программирование — вносить небольшие изменения в определенный рабочий код и смотреть, что получается. Попробуйте сделать это сейчас. Откройте Макрос1 в редакторе Visual Basic и измените код указанным ниже образом.
Используйте копирование и вставку, где это возможно, чтобы избежать опечаток.
Не нужно сохранять код, чтобы запустить его, поэтому вернитесь в документ Excel, нажмите кнопку Макросы на вкладке Разработчик, выберите Макрос1 и нажмите кнопку Выполнить. Теперь в ячейке A1 есть текст Wow!, а вокруг него размещена граница из двойных линий.
Рисунок 3. Результаты первого макроса
Только что вы записали макрос, прочитали документацию по объектной модели и написали простую программу на VBA, которая что-то делает. Поздравляем!
Программа не работает? Читайте дальше и узнайте о методах отладки в VBA.
Советы и приемы программирования
Начните с примеров
Сообщество программистов на VBA очень большое; поиск в Интернете почти всегда может дать пример кода на VBA, который выполняет почти те же действия, которые необходимы вам. Если не удается найти хороший пример, попробуйте разбить задачу на более мелкие модули и выполнить поиск каждого из них. Или попробуйте найти более общую, но аналогичную задачу. Если начать с примеров, это может сэкономить много времени.
Это не означает, что в Интернете всегда доступен бесплатный и качественный код. В действительности, в найденных программах могут быть ошибки. Идея в том, что примеры из Интернета или документации по VBA помогают начать работу. Помните, что для изучения программирования требуется время и умственные усилия. Перед тем как использовать очередное решение для решения проблемы, спросите себя, подходит ли VBA для этого.
Упрощение задачи
Программирование может быстро стать сложной задачей. Важно, особенно для новичка, разбивать задачу самые маленькие логические модули, затем отдельно писать и проверять каждую часть. Если перед вами слишком много кода и вы запутались, остановитесь и отложите задачу. Когда вы к ней вернетесь, скопируйте небольшую часть задачи в новый модуль, разберитесь с ней, напишите работающий код и проверьте его. Затем займитесь следующей частью.
Ошибки и отладка
Существует два основных вида ошибок программирования: синтаксические ошибки, которые нарушают грамматические правила языка программирования и ошибки времени выполнения, которые синтаксически правильны, но вызывают сбой, когда VBA пытается выполнить код.
Хотя исправлять синтаксические ошибки неприятно, их легко обнаружить; редактор Visual Basic выдает сопровождающееся звуковым сигналом сообщение об ошибке и меняет цвет текста, если при вводе кода найдена синтаксическая ошибка.
Например, в VBA строковые значения должны быть заключены в двойные кавычки. Чтобы узнать, что происходит при использовании одинарных кавычек, вернитесь в редактор Visual Basic и замените строку «Wow!» на ‘Wow!’ (т. е. слово Wow в одинарных кавычках). Если выбрать следующую строку, среагирует редактор Visual Basic. Сообщение об ошибке «Ошибка компиляции. Ожидается: выражение» не особо помогает, но строка, содержащая ошибку, становится красной, что говорит о синтаксической ошибке в этой строке. В результате программа не будет запускаться.
Нажмите кнопку ОК и измените текст на «Wow!».
Ошибки времени выполнения обнаружить сложнее, так как синтаксически все выглядит правильно, но при попытке выполнить код возникает сбой.
Например, откройте редактор Visual Basic и измените имя свойства Value на ValueX в макросе, намеренно вызывая ошибку среды выполнения, так как у объекта Range нет свойства ValueX. Вернитесь к документу Excel, откройте диалоговое окно Макрос и запустите Макрос1 еще раз. Появится сообщение Visual Basic, в котором описывается ошибка во время выполнения: «Объект не поддерживает это свойство или метод». Хотя текст и так понятен, нажмите кнопку Отладка для получения дополнительных сведений.
После возвращения в редактор Visual Basic будет включен специальный режим отладки, в котором желтым цветом выделяется строка кода с ошибкой. Как и ожидалось, желтым выделена строка со свойством ValueX.
Можно внести изменения в исполняемый код VBA, поэтому измените ValueX на Value и нажмите маленькую зеленую кнопку воспроизведения в меню Отладка. Программа должна запуститься без ошибок.
Будет полезно узнать, как использовать отладчик для решения более сложных задач. Хотя бы изучите, как устанавливать точки останова, чтобы прекращать выполнение программы там, где нужно взглянуть на код, как добавлять контрольные значения, чтобы просматривать значения разных переменных и свойств во время выполнения кода, и как пошагово, строка за строкой, выполнять код. Все эти возможности доступны в меню Отладка, а серьезный пользователь отладчика обычно запоминает соответствующие сочетания клавиш.
Правильное использование справочных материалов
Чтобы открыть справочник разработчика, встроенный в справку Office, откройте справку в любом приложении Office, выбрав вопросительный знак на ленте или нажав клавишу F1. Затем справа от кнопки Поиск выберите стрелку раскрывающегося меню для фильтрации содержимого. Выберите Справочник разработчика. Если на левой панели не отображается содержание, щелкните маленький значок в виде книги, чтобы открыть его, и раскройте справочник по объектной модели.
Рисунок 5. Фильтрация справки разработчика работает во всех приложениях Office
Время, потраченное на исследование справочника по объектной модели, быстро окупится. Когда вы изучите базовый синтаксис VBA и объектную модель приложения Office, с которым вы работаете, можно перейти от догадок к методичному программированию.
Конечно, центр разработки Microsoft Office — это прекрасный портал со статьями, советами и форумами сообщества.
Поиск в форумах и группах
Рано или поздно все программисты попадают в ситуацию, когда невозможно решить какую-либо проблему, даже прочитав все найденные статьи и проведя много бессонных ночей в поисках ее решения. К счастью, в Интернете появилось сообщество разработчиков, которые помогают друг другу при решении задач программирования.
Если поискать в Интернете «форум разработчиков Office», можно найти несколько таких групп обсуждений. Можно поискать «разработка Office» или описание проблемы и найти нужные форумы, блоги и статьи.
Если сделано все, что возможно, для решения проблемы, не бойтесь задать вопрос на форуме разработчиков. В таких форумах приветствуются вопросы от новичков-программистов, и многие опытные разработчики с радостью помогут им.
Далее описаны некоторые вопросы этикета, которых следует придерживаться при публикации на форуме разработчиков.
Перед публикацией поищите на сайте часто задаваемые вопросы или инструкции, которые следует соблюдать. Убедитесь, что публикация соответствует этим инструкциям и расположена в нужном разделе форума.
Представьте ясный и полный пример кода, и если он является частью более сложного кода, укажите это.
Опишите проблему четко и кратко и укажите действия, выполненные для решения проблемы. Не спешите и напишите вопрос, как можно лучше, даже если вы взволнованны или вам не хватает времени. Опишите ситуацию так, чтобы читатели поняли ее после первого прочтения.
Будьте вежливы и выражайте благодарность за все ответы.
Более подробное изучение программирования
Хотя это короткая статья, и в ней представлена лишь малая часть сведений о VBA и программировании, надеюсь, ее будет достаточно для начала.
В этом разделе кратко описываются некоторые другие важные темы.
Переменные
В примерах в этой статье использовались объекты, которые уже создало приложение. Иногда требуется создать собственные объекты для хранения значений или ссылок на другие объекты. Они называются переменными.
Чтобы использовать переменную в VBA, необходимо сообщить VBA, какой тип представляет переменная, с помощью оператора Dim. Затем задается значение переменной, и она используется для установки других переменных и свойств.
Ветвление и циклы
В примерах простых программ в этой статье код выполняется строка за строкой, сверху вниз. Реальные возможности программирования состоят в том, что можно определять, какие строки кода выполнять, в зависимости от одного или нескольких указанных условий. Эти возможности можно расширить, если повторять операцию нужное количество раз. Например, приведенный ниже пример кода расширяет Макрос1.
Введите или вставьте код в редакторе Visual Basic и запустите его. Следуйте инструкциям в появившемся окне сообщений и измените текст в ячейке A1 с «Wow!» на «Yes!», а затем выполните программу еще раз, чтобы ознакомиться с возможностями циклов. В этом примере кода показаны переменные, ветвление и циклы. Просмотрите его внимательно после выполнения и попробуйте определить, что происходит после выполнения каждой строки.
Все приложения на вкладке «Мой Office»: примеры кода
Вот несколько скриптов, которые можно попробовать. Каждый из них решает реальную задачу Office.
Создание электронной почты в Outlook
Помните, что бывают случаи, когда нужно автоматизировать электронную почту в Outlook или использовать шаблоны.
Удаление пустых строк на листе Excel
Помните, что можно выбрать столбец ячеек и запустить этот макрос, чтобы удалить все строки в выбранном столбце с пустыми ячейками.
Удаление пустых текстовых полей в PowerPoint
Имейте в виду, что в коде выполняется цикл по всем слайдам и удаляются все текстовые поля, в которых нет текста. Переменная-счетчик уменьшается, а не увеличивается, так как при каждом удалении объекта он удаляется из коллекции, что уменьшает счетчик.
Копирование контакта из Outlook в Word
Обратите внимание, что этот код копирует открытый контакт из Outlook в открытый документ Word. Этот код работает, только если в Outlook открыт контакт.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.