В начало

Приложение. Ответы

1-й час. Знакомство с Visual Basic for Applications

Добро пожаловать в сообщество разработчиков приложений с использованием Excel и Visual Basic for Applications! Как опытный пользователь Excel вы чувствуете себя уверенно и комфортно при работе с этой программой. Сейчас у вас появился шанс подняться на следующий уровень - автоматизировать свою работу в Excel. Даже если вы никогда ранее не программировали, то легко освоите методы решения задач автоматизации с помощью Visual Basic for Applications. На сегодняшний день Visual Basic for Applications - один из самых простых в изучении и применении языков программирования для автоматизации приложений (его даже можно назвать языком макросов). В этом часе будет заложен фундамент ваших знаний VBA, а начнем мы с хорошо знакомого вам процесса записи макросов.

В этом часе будут рассмотрены следующие вопросы.

• Что такое Visual Basic for Applications.
• В чем польза автоматизации в среде Excel.
• Запись простых макросов.
• Выполнение макросов.
• Редактирование макросов.
• В чем проявляется ограниченность макросов.

Что такое Visual Basic for Applications

До начала 1990-х годов идея автоматизации приложений вызывала большие сомнения и ее реализация была достаточно трудным делом даже для специалистов. Для каждого приложения, подлежащего автоматизации, необходимо было изучать свой язык программирования, с помощью которого осуществлялась автоматизация. Например, для автоматизации Excel использовался язык макросов Excel, для автоматизации Microsoft Word - язык WordBasic и т.д. Microsoft решила, что иметь для каждого разрабатываемого ею приложения свой язык автоматизации не эффективно, и предложила общий язык программирования, подходящий для всех приложений, - язык Visual Basic for Applications (VBA). Язык Visual Basic for Applications является подмножеством популярного языка разработки приложений Visual Basic. Можно сказать, что VBA является версией Visual Basic, ориентированной на приложения. Вместе с тем VBA имеет отличия от Visual Basic, которые заключаются в следующем.

• Язык Visual Basic разрабатывался для создания законченных самостоятельных приложений, VBA используется для автоматизации существующих приложений.
• Visual Basic имеет собственную среду разработки, VBA использует среду приложения.
• Для выполнения приложения, созданного в Visual Basic, пользователю не обязательно иметь доступ к Visual Basic, так как разработанные приложения, как правило, исполняемые. Выполнение VBA-приложений требуют доступа к "родительскому" приложению, в котором они созданы (например, Excel).

Несмотря на описанные различия, структуры языков Visual Basic и VBA очень похожи. Если вы знакомы с Visual Basic, то легко перейдете на VBA. Верно и обратное утверждение: после изучения VBA вы получите прочный фундамент для освоения Visual Basic. Более того, после изучения VBA на примере решения задач в Excel вы будете иметь все необходимые знания для применения VBA в программах Word, Access, Outlook, FoxPro и PowerPoint.

Одним из основных свойств языка VBA является то, что изучение его на примере одного программного продукта Microsoft (или изучение языка Visual Basic) дает основу для применения его к другим продуктам Microsoft.

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

Автоматизация среды Excel

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

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

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

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

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

Среда Excel предоставляет преимущества не только начинающим программистам. Например, опытные программисты получают в свое распоряжение мощные средства Excel доступа к внешним базам данных. В частности, Excel позволяет легко использовать такие средства, как Microsoft SQL Server, включая OLAP Services, или средства создания отчетов и форм Microsoft Access.

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

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

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

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

1. Откройте новую рабочую книгу. Удостоверьтесь, что все другие рабочие книги закрыты - в этом случае вам легче будет работать с записываемым макросом.

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

2. В ячейку A1 введите ваше имя, в ячейку B2 - фамилию. Далее в ячейку C1 введите название города, в котором живете, а в ячейку D1 - название страны. Это будут данные, с которыми мы будем работать далее.
3. Перейдите в ячейку A1.
4. Выполните команду Сервис > Макрос > Начать запись. Появится диалоговое окно Запись макроса, показанное на рис. 1.1.
5. Введите название макроса, например БольшойШрифт, и нажмите клавишу <Enter> для начала записи макроса. Заметьте, что в строке состояния окна Excel отобразилось слово Запись. Обычно также на экране появляется панель инструментов Остановить запись.

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

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

Рис. 1.1. Диалоговое окно, предлагающее ввести имя макроса и его описание

6. Выполните команду Формат > Ячейки. Появится диалоговое окно Формат ячеек. Перейдите на вкладку Шрифт.
7. Установите размер шрифта 16 пунктов, цвет - красный. Щелкните на кнопке ОК.
8. Щелкните на кнопке Остановить запись одноименной панели инструментов. Сеанс записи макроса закончен.

Если панель инструментов Остановить запись не отображена на экране, выполните команду Сервис > Макрос > Остановить макрос.

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

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

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

1. Выделите ячейку B1.
2. Выполните команду Сервис > Макрос > Макросы. Появится диалоговое окно Макрос (рис. 1.2).

Диалоговое окно Макрос можно также отобразить с помощью комбинации клавиш <Alt+F8>.

3. Выделите макрос БольшойШрифт (если вы так назвали свой макрос) и щелкните на кнопке Выполнить. Шрифт в ячейке B1 станет красным и размером 16 пунктов.
4. Выделите ячейки C1 и D1 и снова выполните макрос БольшойШрифт. Заметьте, что в обеих ячейках шрифт стал красным и размером 16 пунктов, хотя при создании макроса была выделена только одна ячейка.

Рис. 1.2. В этом диалоговом окне можно выбрать макрос для выполнения или редактирования

Просмотр кода макроса

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

1. Выберите команду Сервис > Макрос > Макросы. Отобразится диалоговое окно Макрос.
2. Выделите макрос БольшойШрифт и щелкните на кнопке Изменить. Откроется окно редактора Microsoft Visual Basic, показанное на рис. 1.3.

Рис. 1.3. Редактор Microsoft Visual Basic используется для просмотра и редактирования кода VBA

Окно редактора Microsoft Visual Basic имеет много различных компонентов, которые мы подробно рассмотрим в 4-м часе "Переменные и константы". Сейчас мы сосредоточим свое внимание на коде макроса. Ваш код должен походить на код, представленный в листинге 1.1.

Листинг 1.1. Процедура БольшойШрифт

Sub БольшойШрифт()

'

' БольшойШрифт Макрос

' Макрос записан 16.10.2007 (Иванов)

'

'

 With Selection.Font

  .Name = "Arial Cyr"

  .Size = 16

  .Strikethrough = False

  .Superscript = False

  .Subscript = False

  .OutlineFont = False

  .Shadow = False

  .Underline = xlUnderlineStyleNone

  .ColorIndex = xlAutomatic

 End With

 Selection.Font.ColorIndex = 3

End Sub

Первая строка кода - Sub БольшойШрифт() - представляет начальную точку макроса и его имя. Следующие строчки, в начале которых стоит одиночная кавычка, являются комментариями, документирующими макрос (в данном случае - имя макроса, когда он был записан и кем).

"Работающая" часть макроса начинается со слова With. Отметим, что выражение Selection, точка и далее название чего-то в VBA обозначают все, что в данный момент выделено (в данном случае - это Font, шрифт). Вы, вероятно, уже заметили, что количество строк в коде макроса не соответствует тому незначительному количеству действий, которые совершены при записи макроса. Дело в том, что хотя мы изменили только размер и цвет шрифта, в коде макроса сохранена вся информация, представленная на вкладке Шрифт диалогового окна Формат ячеек.

Редактирование кода макроса

1. Редактировать код можно непосредственно в редакторе Visual Basic. Здесь можно добавлять, удалять или изменять строки кода. Первое, что мы сделаем с кодом макроса - удалим лишние строки.
2. Выделите строку, которая начинается с .Name.
3. Удалите эту строку. Не бойтесь, если появится пустая строка - VBA игнорирует пустые строки.
4. Продолжайте удалять строки, пока у вас не получится следующее:

Sub БольшойШрифт()

 With Selection.Font

  .Size = 16

 End With

 Selection.Font.ColorIndex = 3

End Sub

5. Закройте окно редактора Visual Basic и вернитесь в свою рабочую книгу.
6. В ячейке E1 введите слово Тест.
7. Не снимая выделения с ячейки E1, выполните макрос БольшойШрифт. Заметьте, что макрос работает точно так же, как и до удаления строк кода.
8. Выполните команду Сервис > Макрос > Макросы.
9. В диалоговом окне Макрос выделите макрос БольшойШрифт и щелкните на кнопке Изменить.
10. Измените размер шрифта с 16 пунктов на 24. Код макроса должен выглядеть следующим образом:

Sub БольшойШрифт()

 With Selection.Font

  .Size = 24

 End With

 Selection.Font.ColorIndex = 3

End Sub

11. Закройте окно редактора Visual Basic и вернитесь в рабочую книгу.
12. Выделите ячейку A1 и выполните макрос БольшойШрифт. Шрифт в ячейке A1 стал заметно большим.
13. Сохраните рабочую книгу под именем Час1.

Как видите, редактировать макросы достаточно просто. Существует много причин, объясняющих необходимость редактирования макросов. Во-первых, вы можете сделать ошибки при записи макроса. Во-вторых, часто возникает необходимость изменить какие-либо параметры макроса (как в нашем примере - изменение размера шрифта с 16 пунктов на 24). В любом случае коды макросов редактируются в редакторе Visual Basic.

Ограниченность макросов

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

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

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

Резюме

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

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

Вопросы и ответы

Вопрос. Почему для программирования в VBA необходимо знать, как работать с макросами?

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

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

Ответ. Нет! Основное условие для начала программирования на VBA - умение работать с Excel. Эта книга предлагает путь изучения VBA, опираясь на ваши знания Excel.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Язык VBA применяется только в Excel?
2. На чем основывается язык VBA?
3. Истинно или ложно следующее утверждение: встроенные функции Excel можно использовать в приложениях VBA?
4. При редактировании кода макроса вы работаете в ______________ (три слова).
5. Назовите два ограничения записанных макросов.

Упражнение

Создайте новый макрос (назовите его Заголовок) для ввода вашей фамилии в ячейку A1 и текущей даты в ячейку В1. Этот макрос должен также установить размер шрифта в 14 пунктов в ячейках A1 и В1. После записи макроса просмотрите его код.