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

Зачем нужен VBA



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

Рис. В.1. Таблица ежемесячных расходов

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

Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro). Появится диалоговое окно Запись макроса (Record Macro) (рис. В.2). Это диалоговое окно позволяет задать параметры макроса.

Поля Имя макроса (Macro Name) и Описание (Description) предназначены для задания имени макроса и его описания. Последнее важно для многократно используемых макросов. Наша память не долговечна и, не имея подсказки в виде описания, через некоторое время бывает трудно вспомнить, для чего тот или иной макрос создавался. По умолчанию макросам присваиваются имена макрос1, Макрос2 и т. д. Чтобы было легче разпознать макрос, лучше не оставлять стандартное, а присвоить ему какое-нибудь уникальное имя, поясняющее его назначение. В данном случае присвоим макросу, например, имя Расходы. Поле Сочетание клавиш (Shortcut Key) позволяет назначить макросу комбинацию клавиш, т. е. указать клавишу, которая в сочетании с клавишей <Ctrl> будет служить для запуска его на выполнение. Назначать комбинацию клавиш макросу совсем не обязательно. Это стоит делать только для постоянно используемых макросов, для быстрого доступа к ним.
Макрос всегда можно вызвать командой Сервис, Макрос (Tools, Macro). Раскрывающийся список Сохранить в книге (Store Macro in) предназначен для выбора книги, в которой будет сохранен макрос. Если выбрать Личная книга макросов (Personal Macro Workbook), то макрос будет сохранен в специальной скрытой книге. Эта книга хотя и скрыта, но является открытой, и записанные в ней макросы доступны для других рабочих книг. Команда Окно, Отобразить (Window, Unhide) позволяет отобразить личную книгу макросов. Если в раскрывающемся списке выбрать Эта книга (This Workbook) (этот выбор по умолчанию предлагает компьютер), то макрос сохранится на новом листе модуля в активной рабочей книге, а если выбрать Новая книга (New Workbook) — в новой рабочей книге.



Рис. В.2. Диалоговое окно Запись макроса

Итак, в диалоговом окне Запись макроса (Record Macro) в поле Имя макроса (Macro Name) введем Расходы, а в поле Описание (Description) — Расчет месячных расходов и нажмем кнопку ОК. Появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Построим шаблон таблицы расходов по следующему алгоритму:



  • Активизируем ячейку B1 и введем в нее Расходы.

  • Активизируем ячейку А2 и введем в нее транспорт.

  • Активизируем ячейку A3 и введем в нее коммунальные.

  • Активизируем ячейку А 4 и введем в нее Еда.

  • Активизируем ячейку А5 и введем в нее Развлечения.

  • Активизируем ячейку Aб и введем в нее Одежда.

  • Активизируем ячейку A7 и введем в нее компьютер.

  • Активизируем ячейку А8 и введем в нее машина.

  • Активизируем ячейку A9 и введем в нее прочие.

  • Активизируем ячейку A10 и введем в нее итого.

  • Активизируем ячейку вю и введем в нее формулу =СУМM(В2:В9), вычисляющую суммарные расходы.

  • Выберем диапазон В2:В9 и при помощи раскрывающегося списка Границы (Borders) панели инструментов Форматирование (Formatting) создадим рамку, окаймляющую этот диапазон.

  • Выберем диапазон дю:вю и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в желтый цвет.



  • Выберем ячейку Bi и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим эту ячейку в желтый цвет.

  • Выберем диапазон А2:Д9 и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в светло-бирюзовый цвет.

  • Выберем столбец д, изменим его ширину так, чтобы введенный в диапазон А2: Д9 текст помещался в этом столбце.

  • Выберем диапазон А2:В9 и при помощи мастера диаграмм, вызываемого кнопкой Мастер диаграмм (Chart Wizard) панели инструментов Стандартная (Standard), создадим диаграмму.

    Рабочий лист теперь будет выглядеть так, как показано на рис. В.1. Остановим запись макроса, нажав кнопку Остановить запись (Stop Recording).

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

    Для просмотра записанной процедуры необходимо выбрать команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro) (рис. В.З).

    В этом диалоговом окне в списке выделим макрос и нажмем кнопку Изменить (Edit). Это вызовет появление главного окна редактора VBA (рис. В.4). Ниже приведен полный текст записанного макроса.



    Рис. В.З. Диалоговое окно Макрос



    Рис. В.4. Главное окно редактора VBA

    Sub Расходы()

    '

    ' Расходы Макрос

    ' Расчет месячных расходов '

    '

    Range("Bl").Select

    ActiveCell.FormulaRlCl = "Расходы"

    Range("A2").Select

    ActiveCell.FormulaRlCl = "Транспорт"

    Range("A3").Select

    ActiveCell.FormulaRlCl = "Коммунальные"

    Range("A4").Select

    ActiveCell.FormulaRlCl = "Еда"

    Range("A5").Select

    ActiveCell.FormulaRlCl = "Развлечения"

    Range("A6").Select

    ActiveCell.FormulaRlCl = "Одежда"

    Range("A7").Select

    ActiveCell.FormulaRlCl = "Компьютер"

    Range("A8").Select



    ActiveCell.FormulaRlCl = "Машина"

    Range("A9").Select

    ActiveCell.FormulaRlCl = "Прочие"

    Range("A10").Select

    ActiveCell.FormulaRlCl = "Итого"

    Range("BIO").Select

    ActiveCell.FormulaRlCl = "=SUM(R[-8]C:R[-1]C)"

    Range("A10:BIO").Select

    Selection.Interior.Colorlndex = 36

    Range("B1").Select

    Selection.Interior.Colorlndex = 36

    Range("A2:A9").Select

    Selection.Interior.Colorlndex = 34

    Columns("A:A").ColumnWidth = 13.86

    Range("A2:B9").Select

    Charts.Add

    ActiveChart.ChartType = xlColumnClustered

    ActiveChart.SetSourceData

    Source:=Sheets("Лист1").Range("A2:B9"), PlotBy:=xlColumns

    ActiveChart.Location Where:=xlLocationAsObject, Nаmе:="Лист1"

    With ActiveChart

    .HasTitle = False

    .Axes(xlCategory, xlPrimary)

    .HasTitle = False

    .Axes(xlValue, xlPrimary)

    .HasTitle = False

    End With

    End Sub

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

    Итак, активизируем новый рабочей лист, временно присвоим ему имя лист1. Выберем команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro).В этом окне в списке выделим исходный макрос и нажмем кнопку Выполнить (Run). Диалоговое окно закроется и выполнится процедура, создающая на активном рабочем листе шаблон таблицы. Теперь в нее остается ввести новые данные, а расчет суммарных расходов и построение диаграммы будет происходить автоматически.




    Содержание раздела