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

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

Задача 1 Планирование личного бюджета

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

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

Выполнение:

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

• Для активации MacroRecorder выберите команду Сервис / Макрос / Начать запись. Появится диалоговое окно Запись макроса (рис. 2).

• В диалоговом окне Запись макроса в поле Имя макроса введите Расходы, а в поле Описание - Расчет месячных расходов и нажмите ОК. Появится плавающая панель инструментов с кнопкой  Остановить запись. Теперь все происходящие действия будут записываться до тех пор, пока не будет нажата эта кнопка. Построим шаблон таблицы расходов по следующему алгоритму:

1 В ячейку В1 введите Расходы.

2 В ячейку А2 введите Транспорт.

3 В ячейку А3 введите Коммунальные.

4 В ячейку А4 введите Еда.

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

6 В ячейку А6 введите Одежда.

7 В ячейку А7 введите Компьютер.

8 В ячейку А8 введите Машина.

9 В ячейку А9 введите Прочие.

10 В ячейку А10 введите Итого.

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

12 Выделите диапазон В2:В9, и при помощи раскрывающегося списка Внешние границы панели инструментов Форматирование создайте рамку, окаймляющую этот диапазон.

13 Выделите диапазон А10:В10, и при помощи раскрывающегося списка Цвет заливки панели инструментов Форматирование окрасьте этот диапазон в желтый цвет.

14 Ячейку В1 окрасьте в желтый цвет.

15 Диапазон А2:А9 окрасьте в светло-бирюзовый цвет.

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

17 Выделите диапазон А2:В9, и при помощи мастера диаграмм, вызываемого кнопкой Мастер диаграмм панели инструментов Стандартная, создайте диаграмму.

18 Остановите запись макроса, нажав кнопку   Остановить запись.

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

20 Для просмотра записанной процедуры необходимо выбрать команду Сервис / Макрос / Макросы, которая вызовет диалоговое окно Макрос (рис. 3).

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

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

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

Sub Расходы()

'

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

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

'

'

    Range("B1").Select

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

    Range("A2").Select

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

    Range("A3").Select

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

    Range("A4").Select

    ActiveCell.FormulaR1C1 = "Еда"

    Range("A5").Select

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

    Range("A6").Select

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

    Range("A7").Select

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

    Range("A8").Select

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

    Range("A9").Select

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

    Range("A10").Select

    ActiveCell.FormulaR1C1 = "Итого"

    Range("B10").Select

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

    Range("B2:B9").Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .Weight = xlThin

        .ColorIndex = xlAutomatic

    End With

    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Range("A10:B10").Select

    With Selection.Interior

        .ColorIndex = 6

        .Pattern = xlSolid

    End With

    Range("B1").Select

    With Selection.Interior

        .ColorIndex = 6

        .Pattern = xlSolid

    End With

    Range("A2:A9").Select

    With Selection.Interior

        .ColorIndex = 34

        .Pattern = xlSolid

    End With

    Columns("A:A").EntireColumn.AutoFit

    Range("A2:B9").Select

    Charts.Add

    ActiveChart.ChartType = xlColumnClustered

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

        xlColumns

    ActiveChart.Location Where:=xlLocationAsObject, Name:="Лист1"

    ActiveChart.HasLegend = False

    ActiveSheet.Shapes("Диагр. 1").IncrementLeft 26.25

    ActiveSheet.Shapes("Диагр. 1").IncrementTop -30#

    ActiveSheet.Shapes("Диагр. 1").ScaleWidth 1.16, msoFalse, msoScaleFromTopLeft

    ActiveSheet.Shapes("Диагр. 1").ScaleHeight 1.34, msoFalse, msoScaleFromTopLeft

    ActiveSheet.Shapes("Диагр. 1").ScaleWidth 1.13, msoFalse, msoScaleFromTopLeft

    ActiveSheet.Shapes("Диагр. 1").ScaleHeight 1.1, msoFalse, _

        msoScaleFromBottomRight

End Sub

• В настоящий момент с этой программой можно работать, не понимая записанные в ней коды. Пока о программе надо знать только ее имя - Расходы – и то, что рабочий лист, на котором при помощи этого макроса будет строиться шаблон таблицы с диаграммой, должен иметь имя Лист1. Поэтому, чтобы воспользоваться макросом, надо перед его выполнением переименовать рабочий лист, присвоив ему имя Лист1. После построения таблицы можно изменить имя рабочего листа на новое, например, на имя месяца, для которого строится текущий отчет по расходам.

22 Переименуйте Лист1 в Январь.

23 Перейдите на Лист2 и переименуйте его в Лист1.

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

25 Переименуйте этот лист (Лист1) в Февраль.

26 Проделайте действия пунктов 23–25 для нескольких листов, присваивая каждый раз новому листу перед выполнением макроса имя Лист1, и получите план годового личного бюджета.