МГТУ «МАМИ»

Кафедра «Автоматика и процессы управления»

Направление подготовки «Управление в технических системах»

Дисциплина «Информационные технологии»

Методические указания к расчетно-графической работе № 1

Использование средств автоматизации Excel и VBA

Создание макросов и функций-процедур

Тема: Исследование возможностей автоматизации Microsoft Excel, изучение методов создания макросов с помощью макрорекордера и непосредственно в редакторе Visual Basic for Application, исследование возможностей модификации макросов и разработки пользовательских функций-процедур, рассмотрение методов создания диалоговых окон.

Задачи:

• Создать макрос с использованием макрорекордера Excel;

• Создать макрос с использованием редактора Visual Basic for Application;

• Создать макрос, осуществляющий взаимодействие с пользователем посредством стандартных диалоговых окон;

• Изучить способы записи чисел и строк, а также методы адресации ячеек;

• Исследовать возможности языка Visual Basic для создания ветвящихся и циклических алгоритмов в макрокомандах;

• Изучить возможности разработки пользовательских функций-процедур.

• Изучить методы создания диалоговых окон

Оборудование и принадлежности:

Персональный компьютер с установленной операционной системой Microsoft Windows, система управления электронными таблицами Microsoft Excel с установленной средой Visual Basic for Applications

Ход выполнения работы:

Перед началом работы лист с базой данных, находящийся в книге Microsoft Excel "Lab2_Excel VBA_пример.xls" необходимо скопировать в файл с именем обучающегося и сохранить.

Макрокоманда (macro-command) Excel - это упорядоченная последовательность команд, объединенных в единый блок для выполнения как единой команды. Приставку макро принято переводить в данном контексте как "расширенная". В настоящее время этот термин принято сокращать до "макрос".

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

На современном этапе развития пакета Microsoft Office, система использования макросов трансформировалась в язык программирования Визуальный Бейсик для Приложений (Visual Basic for Application). Теперь, используя редактор кода скриптов, можно управлять выполнением макроса, программируя его поведение в зависимости от значений в ячейках электронной таблицы, действий пользователя, содержания внешних файлов и записей баз данных. Кроме того, появилась возможность создания диалоговых окон, содержащих визуальные элементы управления, и определять реакцию системы на действия пользователя.

Включение режима записи макроса выполняется выбором команды Сервис - Макросы - Начать запись. В результате этих действий на экране появится окно ввода параметров нового макроса, где можно определить его название и горячую комбинацию клавиш для быстрого вызова. Название макроса рекомендуется вводить латинскими символами и цифрами, заменяя пробелы на знаки подчеркивания. Специальные символы, знаки препинания и скобки в названии не допускаются. В качестве горячих рекомендуется использовать, в сочетании с клавишей Ctrl, клавиши верхнего символьного ряда (QWERTY), поскольку остальные задействуют встроенные комбинации Excel. Например, сочетания Ctrl + C и Ctrl + V используются для копирования и вставки фрагментов текста, а Ctrl + A - для выделения.

После нажатия кнопки Ок начинается запись макрокоманды. Все действия пользователя в системе Excel протоколируются и преобразуются в код на Visual Basic.

Остановка записи макроса производится командой Сервис - Макросы - Остановить запись. После остановки макрос можно запустить используя заданное сочетание клавиш, либо командой Выполнить в диалоговом окне Сервис - Макросы - Макрос, а редактирование командой Изменить в том же окне.

Первым заданием будет создание макроса по внесению формулы в выделенную ячейку.

Для этого нужно, установив курсор в ячейку H3, включить запись макроса (например, название: Vvod_Formuly_Skidki, сочетание клавиш: Ctrl + Q) и не изменяя выделение ячеек ввести с клавиатуры формулу расчета скидки:

=ЕСЛИ(G3>7;G3*F3*0,1;0)

После окончания ввода формулы необходимо остановить запись макроса.

Конечно, при записи макроса никто не мешает использовать мастер создания формул, однако код получается намного более длинным и нечитабельным.

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

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

Sub Vvod_Formuly_Skidki()

' Макрос записан 1.01.2006

' Быстрый вызов Ctrl + Q

ActiveCell.FormulaR1C1="=IF(RC[-1]>7,RC[-1]*RC[-2]*0.1,0)"

Range("H4").Select

End Sub

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

Для успешного использования средства автозаполнения при внесении формул в ячейки рабочих листов Excel необходимо понимать метод адресации R1C1. В данном примере, R[x]C[y] указывают на смещение целевой ячейки относительно активной, где R (Row) – это строка, а C (Column) – столбец в адресе ячейки. При внесении формулы в данном формате в ячейку, система автоматически подставит необходимые адреса. Для сокращения записи выражение «[0]» допускается опускать.

Второе важное различие лежит в методах задания функций и записи числовых значений между средой Excel и средой Visual Basic for Application. Дело в том, что локализованная версия Excel использует русифицированные названия функций, точку с запятой в качестве разделителя аргументов, запятую в качестве разделителя дробной части при записи чисел. А для VBA сохранена англоязычная форма записи функций, использование запятой как разделителя аргументов и точки, как разделителя дробной части в числах. Поэтому в случае возникновения затруднений при записи функций в VBA рекомендуется сначала записать их с использованием макрорекордера в среде Excel.

Попробуем модифицировать макрос таким образом, чтобы он заполнял также ячейку итоговой стоимости проката одного автомобиля в рублях. Для этого необходимо присвоить ячейке содержащей курс доллара имя «usd» (Присвоение имени ячейке выполняется по команде Вставить – Имя – Присвоить). Мы также заставим систему переключаться к следующей строке, для ожидания команды заполнения очередной пары ячеек «скидка-стоимость_проката».

Модифицируем код макроса следующим образом:

Sub Vvod_Formuly_Skidki()

' Макрос записан 1.01.2006

' Быстрый вызов Ctrl + Q

Cells(ActiveCell.Row, ActiveCell.Column).FormulaR1C1 = "=IF(RC[-1]>7,RC[-1]*RC[-2]*0.1,0)"

Cells(ActiveCell.Row, ActiveCell.Column + 1).FormulaR1C1 = "=(RC[-2]*RC[-3]-RC[-1])*usd"

Cells(ActiveCell.Row + 1, ActiveCell.Column).Select

End Sub

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

В данном макросе использована другая форма обращения к ячейкам рабочего листа Excel через массив ячеек Cells(Row,Column). Для вычисления позиции нужной ячейки используются соответствующие свойства активной ячейки. Вместо использования объекта Range().Select для выделения используется схожий метод объекта ячейки Cells().Select. Задание наименования ячейки курса доллара использовалось для избежания возни с абсолютной адресацией.

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

Примечание: Один из вариантов результирующего макроса может быть дополнен следующим кодом

Cells(ActiveCell.Row, ActiveCell.Column + 1).FormatConditions.Delete

Cells(ActiveCell.Row, ActiveCell.Column + 1).FormatConditions.Add Type:=xlExpression, Formula1:="=RC[-1]>0"

Cells(ActiveCell.Row, ActiveCell.Column + 1).FormatConditions(1).Font.ColorIndex = 4

Помимо использования макрорекордера макрос можно создать двумя способами:

В окне Сервис – Макросы – Макрос ввести имя нового макроса и нажать кнопку Создать, либо выполнить команду Сервис – Макросы – Редактор Visual Basic и непосредственно перейти в окно редактора. Различия между ними заключаются в том, что в первом случае в редактор будет вставлена «обертка» подпрограммы обработки макроса Sub – End Sub.

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

MsgBox( “Выделена ячейка (" + Str(ActiveCell.Row) + ", " + Str(ActiveCell.Column) + " )")

Новому макросу может быть назначена «горячая клавиша» через команду Параметры окна Сервис – Макросы – Макрос.

Результатом выполнения макроса должно выступить окно сообщения с соответствующим текстом, которое и вызывается использованием функции MsgBox(). Новым здесь является также использование функции Str(), преобразующей числовое значение в строковое.

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

Код макроса будет иметь следующий вид:

Dim N As Integer

Range("N16").Select

Selection.Value = ""

N = Val(InputBox("Введите номер автомобиля в списке"))

If N > 0 And N <= 23 Then

Selection.Value = Cells(2 + N, 3).Value

Else

MsgBox ("Неверный номер автомобиля")

End If

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

Кроме того, в данном примере использована функция вызова диалогового окна InputBox() и преобразования текстового значения в числовое Val(). Помимо этого, впервые мы используем дополнительную переменную N, которая объявляется как целочисленная в первой строке кода, получает значение введенное пользователем в четвертой строке, используется при проверке вхождения номера в нужный диапазон в пятой и для адресации ячейки в шестой. В дополнение к этому, здесь реализовано ветвление алгоритма обработки макроса с помощью конструкции If условие Then группа команд при истинности условия Else группа команд при ложности условия End If.

Дополнительное задание: Доработать макрос так, чтобы заполнялась также ячейка цены автомобиля, причем в зависимости от типа валюты в прайс-листе происходило необходимое преобразование.

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

Range("N17").Select

If Cells(2+N,4) = “usd” Then

Selection. Value = "=E" + Trim(Str(2 + N)) +”*usd”

Else

If Cells(2+N,4) = “eur” Then

Selection. Value = "=E" + Trim(Str(2 + N)) +”*eur”

Else

Selection. Value = "=E" + Trim(Str(2 + N))

End If

End If

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

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

Для задания функции-процедуры в редакторе Visual Basic используется директива Function вместо Sub. Кроме того, необходимо помнить, что функция-процедура не имеет права изменять никакие ячейки, а должна только выдавать какое-нибудь значение. Только в этом случае среда Excel разрешить ее вставку в ячейку.

Функция, выполняющая расчет по предложенной схеме может иметь следующий вид:

Function Raschet_Kredita(Cost As Double, God_Procent As Double, Srok As Integer, Plateg As Double) As Double

Dim Mesiats_Procent As Double

Dim I As Integer

Dim Sm As Double

Mesiats_Procent = Exp(Log(1 + God_Procent) / 12) ' Корень 12 степени

Sm = Cost

For I = 1 To Srok

Sm = Sm * Mesiats_Procent - Plateg

Next I

Raschet_Kredita = Sm

End Function

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

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

Дополнительное задание: Доработать функцию-процедуру таким образом, чтобы она учитывала сумму предварительного платежа при расчете остатка и определить ежемесячный платеж по второму типу кредитной программы.

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

Примечание: В Visual Basic запуск процедуры подбора параметра осуществляется следующим образом

Range("N21").GoalSeek Goal:=0, ChangingCell:=Range("N18")

Элементы диалоговых окон

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

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

Для создания диалогового окна необходимо открыть редактор Visual Basic for Application (команда Сервис – Макросы – Редактор Visual Basic) и вставить в проект Пользовательскую форму (Insert – UserForm). Объект UserForm – это заготовка будущего диалогового окна. Рядом с окном конструктора диалогового окна появляется инструментальная панель с элементами управления (Controls). Выбрав необходимый элемент управления его можно поместить на форму (выполнив щелчок на элементе управления, а затем выделив прямоугольник в позиции его будущего положения; или просто двойным щелчком на инструментальной панели). Дополнительные элементы управления можно подключить через меню Tools – Additional controls.

В инспекторе свойств (окно Properties) можно изменить свойства диалогового окна, например, цвет фона (BackColor), текст заголовка (Caption), внутреннее наименование (Name).

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

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

Private Sub UserForm_Click()

UserForm1.Hide

End Sub

Данный код приведет к тому, что при щелчке на форме она будет становиться невидимой. Запустить форму на исполнение для проверки функциональности можно командой Run – Sub/UserForm. Для запуска формы из среды Excel необходимо создать макрос, вызывающий команду показа формы, например, следующего вида:

Sub FormStarter()

UserForm1.Show

End Sub

Дополнительное задание: Разработать форму, меняющую свой фоновый цвет при щелчке мышью.

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

Private Sub CommandButton1_Click()

Dim N As Integer

N = Val(TextBox1.Text)

If N > 0 And N <= 23 Then

TextBox2.Text = Cells(2 + N, 3).Value

Else

MsgBox ("Неверный номер автомобиля")

End If

End Sub

Модификация кода в данном случае заключается в том, что номер автомобиля мы получаем из свойства Text первого текстового бокса, а наименование автомобиля выводим в аналогичное свойство второго текстового бокса. Для удобства восприятия формы лучше заменить надпись на кнопке (свойство Caption кнопки) и разместить на форме несколько меток (Label), с помощью которых можно оформить подписи к текстовым боксам (метки используются для «нанесения» надписей на форме через их свойства Caption).

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

Часть 2

Использование средств автоматизации Excel и vba

Фигуры в слое векторной графики

Указания к выполнению лабораторной работы

Тема: Исследование возможностей автоматизации Microsoft Excel, создание макросов с помощью в среде Visual Basic for Application для создания и модификации объектов в слое векторной графики, исследование методик использования динамических массивов, изучение операторов ветвления.

Задачи:

• Создать в слое векторной графики группу фигур по заданным параметрам;

• Создать диалоговое окно для ввода и модификации фигур в слое векторной графики.

Оборудование и принадлежности:

Персональный компьютер с установленной операционной системой Microsoft Windows, система управления электронными таблицами Microsoft Excel с установленной средой Visual Basic for Applications

Ход выполнения работы:

VBA поддерживает общую модель объектов слоя векторной графики в Microsoft Office. Верхним уровнем этой модели является коллекция Shapes, содержащая все графические объкты (Shape-объекты), которые включаются в слой векторной графики. Кроме коллекции Shapes, можно в качестве коллекции нескольких Shape-объектов использовать коллекцию ShapeRange, объединяющую некоторое подмножество фигур в векторном слое (часть коллекции Shapes).

Добавление к коллекции Shapes «автофигур»

Прежде чем работать с Shape-объектом, необходимо добавить его к коллекции Shapes, используя ее метод AddShape:

Expression.AddShape(Type, Left, Top, Width, Height)

Здесь expression – любое Shapes-выражение, Type – константа (тип Long), определяющая тип фигуры и принимающая одно из msoAutoShapeType-значений (например, msoShapeFlowchartData, msoShapeLeftBracket – всего около 140 констант), Left – положение левого угла автофигуры (измеряется в «поинтах»), Top – положение верхнего угла автофигуры, Width – ширина автофигуры, Height – высота автофигуры.

При добавлении в коллекцию Shapes фигура получает имя (свойство Name) по умолчанию, но его можно назначить, используя следующий синтаксис:

Expression.AddShape(Type, Left, Top, Width, Height).Name=имя_фигуры

Для получения ссылки на всю коллекцию объектов в слое векторной графики необходимо использовать следующий синтаксис:

mDocument.Shapes.SelectAll

Задание 1

Создать макрос, помещающий в слой векторной графики три фигуры (прямоугольник, треугольник и «скручивающийся лист») (для определения типа фигур можно использовать константы msoShapeRectangle, msoShapeRightTriangle, msoShapeHorizontalScroll), а затем выделяющий все фигуры в коллекции

Задание 2

Модифицировать предыдущий макрос так, чтобы фигуры помещались на разные листы (использовать коллекцию WorkSheets - листы)

Чтобы получить коллекцию ShapeRange, являющуюся подмножеством коллекции Shapes, необходимо использовать следующий синтаксис:

Shapes.Range(index)

Например, следующий пример устанавливает градиентную заливку для дввух фигур слоя:

ActiveSheet.Shapes.Range(Arrayy(1,2)).Fill.PresetGradient _ msoGradientHorisontal, 1, msoGradientLateSunset

Для обращения к фигуре из выделенного диапазона нежно использовать следующий синтаксис:

Selection.ShapeRange(index)

Добавление к коллекции специальных фигур

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

AddCallout - выноска без границ

AddCurve - кривая Безье

AddDiagram - диаграмма заданного типа

AddPicture - картинка из файла

AddPolyline - ломаная линия или многоугольник

AddTextBox - текстовый бокс

AddTextEffect - объект WordArt

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

Текст в прямоугольник можно следующим образом:

Set myDocument=Worksheets(1)

With myDocument.Shapes.AddShape(msoShapeRectangle, 0, 0, 100, 40).TextFrame

.Characters.Text = "Пример текста в прямоугольнике"

.MarginBottom = 10

.MarginLeft = 10

.MarginRight = 10

.MarginTop = 10

End With

В данном примере дополнительно используются две возможности: задание псевдонима для объекта (Set ...) и задание объекта по умолчанию внутри коммандных скобок With ... End With. Внутри таких скобок в качестве родительского объекта для всех методов сначала используется тот, который указан после With.

Задание 3

Создать макрос, осуществляющий построение набора графических объектов на основании списка, введенного в ячейки листа Excel, начиная с ячейки (1,1). Каждая строка таблицы должна содержать тип фигуры, координаты и размер объекта.

Задание 4

Модифицировать макрос так, чтобы создавалось несколько копий объектов. Количество копий должно запрашиваться через окно диалога. Каждая следующая копия фигур должна сдвигаться на 5 пунктов вправо. По окончании вывода, программа должна запрашивать необходимость повторения операц (с использованием MsgBox).

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

Задание 5

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

Реализовать возможность выбора объекта среди существующих и редактирования его свойств.

Примечание. Выбор объекта может осуществляться либо перебором, либо с помощью ComboBox. Добавлять варианты выбора можно с помощью метода ComboBox.AddItem(text)

Примечание. В случае использования ComboBox желательно предусмотреть на форме кнопку "Обновить" для повторного считывания коллекции фигур и заполнения списка.

Примечание. При необходимости реализовать запуск макроса при открытии документа, его необходимо вызвать из подпрограммы в модуле рабочей книги с описанием: Private Sub WorkBook_Open() ….необх.действия … End Sub.

16