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

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

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

Поэтому неудивительно, что возможность создания макросов в Excel является одной из основных функций программы электронных таблиц. Для этого существует встроенный инструмент записи макросов, использующий язык сценариев Visual Basic for Application (VBA), который также используется в программах Microsoft Office Word, PowerPoint, Access и Outlook. Благодаря этому языку можно легко создавать собственные макросы Excel, которые автоматически выполняют заданные рутинные команды или добавляют новые функции для вычислений в электронной таблице (т.е. алгоритмы для анализа данных).

Excel предоставляет различные элементы пользовательского интерфейса в виде иерархически организованных программных объектов. Каждый из них обладает определенными свойствами и методами. Все имеющиеся объекты связаны друг с другом в иерархической объектной модели и приблизительно отражены в визуальном пользовательском интерфейсе, который предоставляет кнопки и поля ввода для взаимодействия с приложением. Устанавливая свойства и вызывая их методы, вы можете редактировать различные объекты. Например, для объекта «Рабочая книга» существует метод «Close», который можно использовать для закрытия выбранной рабочей книги, а также свойство «ActiveSheet», которое представляет лист, активный в данный момент в рабочей книге.

С помощью коллекций макросы также могут выполнять действия над целой группой объектов. Например, объект коллекции «Worksheets» приводит к тому, что утверждения макроса применяются ко всем рабочим листам. Для выполнения макроса у вас есть следующие три варианта:

• Выбор в меню макроса
• Щелчок по индивидуально созданной кнопке
• Индивидуальные комбинации клавиш

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

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

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

• Макросы расширяют возможности Excel: С помощью VBA можно не только программировать макросы, объединяющие команды, но и разрабатывать новые функции. Это расширяет встроенную коллекцию функций вашей пользовательской рабочей книги и значительно упрощает формулы. Excel также представляет ваши настроенные функции так, как если бы они были встроенными функциями. Кроме того, у вас есть возможность связать все созданные макросы с кнопкой на панели инструментов, которую затем можно использовать для запуска их функций.

Чтобы создать собственную коллекцию команд, сначала необходимо активировать редактор VBA, который входит в состав инструментов разработчика. По умолчанию они недоступны в списке меню (также называемом «лентой» в приложениях Microsoft Office), поэтому на первом этапе необходимо добавить соответствующую вкладку.

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

Установите флажок напротив «Разработчик» и подтвердите изменение, нажав «ОК». Теперь вкладка появится в ленте меню.

Второй шаг заключается в создании новой папки Excel, которая послужит основой для макросов, которые вы собираетесь записать. Для этого нажмите кнопку «Макрос» на вкладке «Разработчик», введите имя папки (в данном примере «Hello») в поле «Имя макроса» и подтвердите, нажав кнопку «Создать», после чего откроется редактор VBA, включающий следующий код:

Sub здесь означает подпрограмму, а также макрос, который сам по себе является своего рода подпрограммой для Excel. Для соответствующего макроса выполняется весь код между Sub и End Sub. Если вы хотите провести небольшой тест, вы можете расширить код примера макроса «Hello» следующим образом:

Сохраните результат в формате .xslm, вернитесь в интерфейс программы Excel, снова активируйте кнопку «Макросы» и выберите макрос «Hello» в открывшемся списке. Теперь, если вы нажмете на кнопку «Выполнить», появится небольшое информационное окно с только что заданным текстовым содержанием.

Нажмите на кнопку «ОК», чтобы закрыть окно и остановить запуск макроса. Затем сохраните рабочую книгу макроса в редакторе VBA.

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

1. Перейдите на вкладку «Файл».
2. Откройте параметры и выберите пункт меню «Панель инструментов быстрого доступа».
3. В списке «Выбрать команду» щелкните по пункту «Макросы» и найдите ранее созданный макрос «Привет».
4. Выберите его и нажмите «Добавить», после чего перенесите его в список кнопок быстрого доступа.
5. Прежде чем подтвердить новую кнопку через «ОК», можно сначала выбрать символ, который будет показан позже. Просто выберите макрос и нажмите «Изменить».

Теперь рядом со стандартными кнопками «Сохранить», «Отменить» и «Повторить» на панели инструментов. Там же находится кнопка быстрого доступа к макросу «Привет»:

Примечание: Вы также можете задать комбинацию клавиш для запуска макроса вместо этого («Разработчик» -> «Макросы» -> «Параметры»).

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

1. В инструментах разработчика выберите кнопку «Записать макрос».
2. В следующем диалоговом окне назовите макрос «RenameWorksheets» и запустите запись нажатием кнопки «OK».
3. Теперь переименуйте «Table1» в «New Name» и завершите запись макроса, нажав на кнопку «End recording».

Если теперь снова перейти в редактор VBA («Разработчик» -> «Макросы» -> «Изменить»), то должен появиться следующий код:

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

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

Переключитесь обратно в Excel и переименуйте рабочий лист в «Table1». Теперь запустите макрос RenameWorksheets в качестве теста, чтобы увидеть, изменится ли имя автоматически. Обратите внимание, что макрос придется адаптировать к новому имени, если вы захотите запустить его снова.

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

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

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

В следующей строке создайте объект диаграммы, на который будет ссылаться переменная mydiagram:

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

Последний шаг — заполнить объект диаграммы данными, которые затем можно соответствующим образом визуализировать. Для этого требуется ввод значений, а также дополнительная настройка макроса Excel, поскольку в нем по-прежнему отсутствует информация о том, где должна отображаться информация. Теперь в конструкции With … End With используйте метод SetSourceData и укажите значение Selection, в результате чего будут учитываться значения всех ячеек, отмеченных при запуске макроса. Код полного макроса выглядит следующим образом:

Для проверки присвойте ячейкам A1 — A5 значения от 1 до 5 и запустите макрос, чтобы увидеть данные, представленные в виде гистограммы. Этот тип диаграммы создается в Excel по умолчанию, если не задавать дополнительных сведений о ChartType.

С помощью инструмента макросов можно также создать форму ввода, через которую пользователь может взаимодействовать с Excel. Например, чтобы создать окно взаимодействия, вводимое значение которого автоматически записывается в определенную ячейку, начните с создания и именования макроса, как обычно. В нашем примере мы присваиваем коллекции команд имя «InputForm». Затем вы называете подпрограмму местом назначения значения, введенного позже пользователем:

Макрос обеспечивает передачу введенного пользователем значения в ячейку A1 таблицы 1. Значение поля (Value) теперь должно быть присвоено во второй части оператора. В качестве примера мы остановились на команде InputBox, которая очень похожа на команду MsgBox, используемую в макросе «Hello». В сочетании со следующими тремя аргументами, заключенными в кавычки в коде VBA, создается подходящее окно ввода.

• Подсказка: С помощью первого аргумента определите текст диалогового окна, которое поддерживает пользователя во время ввода.
• Заголовок: Аргумент «Заголовок» отображает заголовок формы ввода.
• По умолчанию: Наконец, с помощью аргумента «Default» можно задать стандартный ввод.

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

Если запустить следующий макрос Excel, появится соответствующее диалоговое окно:

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

Найти опции для активации и деактивации макросов можно в «Центре доверия» («Центр безопасности» в старых версиях): Просто перейдите на вкладку «Файл», выберите «Параметры», «Центр доверия» и нажмите на кнопку «Параметры центра доверия». Там вы найдете пункт меню «Параметры макроса», который содержит настройки, упомянутые здесь.

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