Автоматизация вычислений с помощью макросов

Макрос – это программа, автоматически составленная макрорекордером, который конвертирует все действия пользователя в соответствующие строки программного кода, записанного на языке программирования Visual Basic for Application. Макрос сохраняется как процедура VBA типа Sub(), которая не имеет аргументов, и может воспроизводиться каждый раз, когда требуется выполнить записанную последовательность команд.

При работе с макросом необходимо обратить внимание на следующее:

- в макросе отображаются все действия, выполненные пользователем, включая ошибочные;

- макрос всегда воспроизводит действия только над теми данными, которые хранились в ячейках электронной таблицы или вводились в них во время его записи;

- макрос может быть изменен средствами VBA.

Последовательность действий при записи макроса:

1. Разработать алгоритм действий пользователя, которые необходимо выполнить при решении конкретной задачи в Ехсе1.

2. Из меню Сервис вызвать команду Макрос и выполнить пункт Начать запись. На экране монитора отобразится диалоговое окно Запись макроса, которое позволяет задать параметры макроса.

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

3. Щелкнуть левой кнопкой мыши по кнопке ОК диалогового окна Запись макроса. На экране монитора отобразится панель инструментов Остановка записи. Это означает, что Ехсе1 готова к записи макроса.

4. Выполнить действия в соответствии с разработанным алгоритмом.

5. Остановить запись макроса, щелкнув левой клавишей мыши по кнопке Остановить запись на… В результате запись прекратится, и все действия пользователя будут сохранены в виде VBA-программы с именем, заданным пользователем или принятым системой по умолчанию (Макрос1, Макрос2 и т.д.).

Для вызова макроса необходимо выполнить одно из действий:

- с помощьюменю Сервис (команды Макрос - Макросы) открыть диалоговое окно Макрос либо нажать одновременно клавиши ALT+F8; выделить в нем имя требуемого макроса и щелкнуть по кнопке Выполнить;

- нажать сочетание клавиш, заданное в диалоговом окне Запись макроса.

Примечание. В диалоговом окне Макрос находится кнопка "Удалить", щелчком по которой можно удалить выделенный макрос, и кнопка "Изменить", щелчок по которой вызывает редактор Visual Basic. В окне редактора отображается текст кода макроса, который при необходимости можно отредактировать.

Пример.

Разработать макрос, позволяющий автоматизировать построение графика функции:

при изменении –3,14 £ х £ 3,26, с шагом 0,4.

Последовательность действий:

1. Включить запись макроса.

2. В ячейку А1 ввести текст "График функции".

3. В ячейку А2 ввести имя аргумента функции х и выровнить текст по центру.

4. В ячейку В2 ввести имя функции y и выровнить текст по центру.

5. В ячейку А3 ввести число –3,14 (первый член арифметической прогрессии) и обновить ее содержимое щелчком левой кнопки мыши по флажку, расположенному в строке формул.

6. Из меню Правка выполнить команду Заполнить, в открывшемся меню выбрать команду Прогрессия.

7. В диалоговом окне Прогрессия в области Расположение установить переключатель в положение По столбцам, в области Тип установить переключатель в положение Арифметическая, в текстовое поле Шаг ввести значение 0,4, в текстовое поле Предельное значение ввести число 3,26.

8. Щелкнуть по кнопке ОК диалогового окна Прогрессия.

9. В ячейку В3 ввести формулу: =ЕСЛИ(А3>=0;sin(А3);cos(А3)) и обновить содержимое ячейки щелчком левой кнопки мыши по флажку, расположенному в строке формул.

10. С помощью режима Автозаполнение записать формулу в соответствующие ячейки столбца В.

11. Выделить заполненный диапазон ячеек B3:B19.

12. Вызвать Мастер диаграмм щелчком по соответствующей кнопке панели инструментов.

13. Выполнить шаги, необходимые для построения графика, заполняя требуемыми данными поля диалоговых окон и переходя от одного диалогового окна к другому щелчком по кнопке "Далее". После заполнения полей последнего диалогового окна Шаг 4 из 4 щелкнуть по кнопке "Готово".

14. Отключить запись макроса.

Примечание. На шаге 2 Мастера диаграмм можно использовать вкладку Ряд диалогового окна для:

- изменения названия рядов графика (в текстовое поле Имя ввести y);

- изменения значений оси х, установленных средствами Ехсе1 на конкретные числовые значения (в текстовое поле Подписи оси х занести диапазон оси х, выделив соответствующий диапазон столбца А).

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

Код приложения построения графика заданной функции, составленный средствами Ехсе1 на VBA имеет следующий вид:

Sub Макрос1()

Range("A1").Select

ActiveCell.FormulaR1C1 = "График функции"

Range("A2").Select

ActiveCell.FormulaR1C1 = "х"

' Автоматическая установка ориентации текста в ячейке А2

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

Range("B2").Select

ActiveCell.FormulaR1C1 = "y"

' Автоматическая установка ориентации текста в ячейке В2

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

Range("A3").Select

ActiveCell.FormulaR1C1 = "-3.14"

' Формирование параметров арифметической прогрессии

Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=0.4, Stop:=3.26, Trend:=False

' Запись формулы в ячейку В3

Range("B3").Select

ActiveCell.FormulaR1C1 = "=IF(RC[-1]>=0,SIN(RC[-1]),COS(RC[-1]))"

' Автозаполнение диапазона B3:B19

Range("B3").Select

Selection.AutoFill Destination:=Range("B3:B19"), Type:=xlFillDefault

' Выделение диапазона ячеек B3:B19 и обращение к Мастеру диаграмм

Range("B3:B19").Select

Charts.Add

' Шаг 1 из 4 Мастера диаграмм – определение типа диаграммы

ActiveChart.ChartType = xlLine

' Шаг 2 из 4 Мастера диаграмм – расположение диаграммы

ActiveChart.SetSourceData Source:= Sheets("Лист1"). Range("B3:B19"), PlotBy:=xlColumns

ActiveChart.SeriesCollection(1).XValues = "=Лист1!R3C1:R19C1"

ActiveChart.SeriesCollection(1).Name = "=Лист1!R2C2"

' Размещение диаграммы на текущем рабочем листе

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

' Шаг 3 из 4 Мастера диаграмм – формирование заголовков

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "График функции Y"

.Axes(xlCategory, xlPrimary).HasTitle = False

.Axes(xlValue, xlPrimary).HasTitle = False

End With

End Sub

Недостатки программы:

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

2. Чтобы понять смысл операторов, используемых в макросе, необходимо знание языка программирования Visual Basic for Application.

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

Дата добавления: 2014-12-27