Лабораторная работа №2. Лабораторная работа 2 среда программирования vba в Excel процедуры и функции Цель занятия узнать
 Скачать 1.02 Mb.
|
|
Лабораторная работа №2 СРЕДА ПРОГРАММИРОВАНИЯ VBA в Excel: процедуры и функции Цель занятия узнать: структуру программ на VBA; создание проекта в MS Excel; процесс добавления модулей; написание пользовательских функций; сохранение проекта в MS Excel; удаление модуля. научиться: освоить программирование в среде VBA пользовательских процедур; создавать новый модуль и сохранять модуль; создавать пользовательские функции; открывать программу с помощью языка Visual Basic for Application (VBA); редактировать код программы; запускать программу на исполнение и прекращать выполнение. Теоретический минимум Структура программ на VBA Программы на VBA хранятся в проектах (VBAProject). Проект содержит модули различных типов. Имеется три типа модулей: 1) стандартные модули (Module) — это модули, в которых можно описать доступные во всем проекте процедуры; 2) модули класса (Class Module) содержат описание объекта, который является членом класса. Процедуры, написанные в модуле класса, используются только в этом модуле; 3) модули форм и отчетов (UserForm), которые связаны с конкретной формой или отчетом. Модули форм и отчетов часто содержат процедуры обработки событий, которые срабатывают в ответ на событие в форме или отчете. Процедуры обработки событий используются для управления реакцией на действия пользователя, например, щелчок мыши на кнопке. Модули содержат описания и процедуры. Существует три типа процедур: 1) процедура Sub (подпрограмма) — набор команд, с помощью которого можно решить определенную задачу. При ее запуске выполняются команды процедуры, а затем управление передается в приложение пакета MS Office или процедуру, которая вызвала данную процедуру. 2) процедура Function (функция) представляет собой набор команд, который решает определенную задачу. 3) процедура Property используется для ссылки на свойство объекта. Данный тип процедур применяется для установки или получения значения пользовательских свойств форм и модулей. Процесс создания проекта программы можно разделить на следующие этапы: – постановка задачи — составление точного и полного словесного описания работы программы, определение исходных и выходных данных, использование экранной формы; – разработка пользовательского интерфейса — создание экранной формы в среде VBА со всеми находящимися на этой форме объектами и их свойствами; – программирование — определение событий, происходящих в процессе работы программы, составление процедур этих событий и написание программных кодов этих процедур; – отладка программы — устранение логических ошибок в процедурах и достижение правильности работы программы; – сохранение проекта. Добавление модулей и запись в них макросов. Рассмотрим на примерах некоторые часто используемые возможности языка программирования Visual Basic (в связке со средой Excel) и визуальной среды программирования VBA. Каждый файл (рабочая книга) MS Excel Visual Basic является проектом. Т.к. в Excel может быть открыто сразу несколько файлов, то в окне Visual Basic Editor может быть открыто сразу несколько проектов. Содержимое проектов можно видеть в окне Project Explorer.  Программы проектов размещаются в модулях. Каждый модуль может содержать в себе несколько программ. Модули, которые располагаются в папке MS Excel Objects, связаны с листами рабочей книги или с самой рабочей книгой. Обычно в них размещаются программы, которые должны запускаться автоматически при наступлении какого-либо события в листе или во всей книге, например, при открытии книги, при переходе на какой-нибудь лист и т.д. Создав модуль, Вы можете вводить в модуль свои макросы, процедуры и функции, написанные на языке программирования VBA. Выполните описанные действия. Создайте новый модуль и макрос в нем. Для этого: 1. Войдите в среду Microsoft Visual Basic.  2. Выполните Insert -> Module, при этом в окне "Project - VBAProject" список объектов Вашей рабочей книги должен пополниться еще одним объектом класса Modules. 3. Введите в текст модуля три текстовых строки: Sub MyProgram() ActiveCell.Formula="Hello, world!" End Sub Слово Sub указывает, что это процедура – тип подпрограммы на языке VBA. В скобках после имени процедуры может быть список передаваемых параметров. Если список пуст, то такая процедура – макрос, и он может быть запущен из меню Сервис рабочего листа. Слово MyProgram – это имя макроса, которое надо выбрать для запуска макроса в рабочем листе. При выполнении макроса MyProgram в активной ячейке рабочего листа должен появиться текст Hello world! 4. Для запуска макроса MyProgram выполните возврат в MS Excel (File ->Close and Return to Microsoft Excel), а затем выберите Вид -> Макросы -> Макрос -> MyProgram и нажмите кнопку "Выполнить". Для более понятного чтения программ в них добавляют комментарии или примечания, комментарии начинаются с апострофа. Текст, следующий за апострофом до конца строки, редактор VBA автоматически выделяет зелёным цветом и игнорирует при выполнении макроса. Добавьте в макрос MyProgram комментарии. Для этого: 1. В строку после заголовка введите любой текст, например, 'программа-приветствие. 2. Убедитесь, что цвет строки автоматически поменялся на зелёный. 3. Запустите макрос на выполнение. Написание функций, определённых пользователем. С помощью VBA имеется возможность написания функций, которые автоматически добавляются к списку стандартных функций рабочего листа. Функции отличаются от процедур тем, что обязаны возвращать одно значение и при вызове могут являться частью арифметических выражений, в то время как процедуры ничего не возвращают и не могут использоваться как часть арифметических выражений. Например, это может быть корень из суммы квадратов двух чисел. Если функция, созданная пользователем, выполняет только вычисления, то она может использоваться как функция рабочего листа (Вставка -> Функция). Однако, если пользовательская функция форматирует ячейки, изменяет их содержимое или удаляет-добавляет рабочие листы, то такая функция может быть вызвана лишь из другой функции или процедуры на VBA. Ещё одно отличие функций от процедур — это то, что при вызове функций подставляемые значения аргументов указываются в скобках, а у процедур – без скобок. Для создания функции, вычисляющей корень из суммы квадратов двух чисел, выполните: 1. Войдите в Редактор Visual Basic 2. Установите курсор в конце какого-нибудь модульного листа и введите текст: Function Гипотенуза(Катет1,Катет2) Гипотенуза =sqr(Катет1^2+Катет2^2) End Function Стандартная функция Sqr(аргумент) возвращает значение квадратного корня. 3. Вернитесь в Microsoft Excel ( File -> Close and Return to Microsoft Excel ). 4. Наберите в свободном месте рабочего листа в одной ячейке любое число, в соседней слева – ещё одно, передвиньте курсор ещё на ячейку влево, куда будет вставлена наша функции. 5. Выберите Вставка -> Функция, а затем в списке Категория выберите опцию Определённые пользователем. После этого надо в списке Выберите функцию выделить функцию Гипотенуза и нажать ОК. 6. Появляется диалоговое окно, которое необходимо заполнить: переменной Катет1 присвойте адрес ячейки с первым числом, а переменной Катет2 – адрес ячейки со вторым числом, и нажмите OK. Условные операторы Оператор условного перехода (условный оператор) имеет несколько форм записи. 1. Оператор If УСЛОВИЕ Then БЛОК ОПЕРАТОРОВ1 Else БЛОК ОПЕРАТОРОВ2 End If Если блоки содержат по одному оператору, то условный оператор записывается на одной строке и имеет вид: If УСЛОВИЕ Then ОПЕРАТОР1 Else ОПЕРАТОР2 В условном операторе могут проверяться и несколько условий: If УСЛОВИЕ1 Then БЛОК ОПЕРАТОРОВ1 ElseIf УСЛОВИЕ2 Then БЛОК ОПЕРАТОРОВ2 Else …. End If 2. Оператор выбора Select Case. Оператор удобно использовать, когда в зависимости от значения некоторого выражения, имеющего конечное множество допустимых значений, необходимо выполнить разные действия. Select Case ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ Case ЗНАЧЕНИЯ 1 ОПЕРАТОРЫ 1 Case ЗНАЧЕНИЯ 2 ОПЕРАТОРЫ 2 … Case ЗНАЧЕНИЯ N ОПЕРАТОРЫ N … Case Else ОПЕРАТОРЫ ПРИ НЕСОВПАДЕНИИ End Select ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ имеет любой скалярный тип, кроме вещественного. ЗНАЧЕНИЯ состоят из произвольного количества значений или диапазонов (n to m), отделенных друг от друга запятыми. Тип ЗНАЧЕНИЙ должен совпадать с типом ПРОВЕРЯЕМОГО ВЫРАЖЕНИЯ. Сначала вычисляется ПРОВЕРЯЕМОЕ ВЫРАЖЕНИЕ. Если его значение совпадает с одним из значений ЗНАЧЕНИЯ I, то выполняются ОПЕРАТОРЫ_I, и управление передается оператору, стоящему после End Select. Если его значение не совпадает ни с одним из значений ЗНАЧЕНИЯ_I, то выполнятся ОПЕРАТОРЫ ПРИ НЕСОВПАДЕНИИ и управление передается оператору, стоящему после End Select. Операторы цикла Операторы цикла тоже имеют несколько форм записи. 1. Оператор For…Next. Используется для выполнения набора инструкций указанное число раз. Циклы For используют в качестве счетчика переменную, значение которой увеличивается или уменьшается при каждом выполнении цикла. For СЧЕТЧИК = НАЧАЛО To КОНЕЦ [Step ШАГ] БЛОК_ОПЕРАТОРОВ [Exit For] БЛОК_ОПЕРАТОРОВ Next [СЧЕТЧИК] Повторяет выполнение блока операторов, пока переменная СЧЕТЧИК изменяет свое значение от начального до конечного с указанным шагом. Если ШАГ не указан, то по умолчанию он полагается равным 1. Немедленный выход из цикла осуществляется оператором Exit For. Квадратные скобки обозначают необязательность конструкции в синтаксисе оператора. 2. Оператор While…Wend While УСЛОВИЕ БЛОК_ОПЕРАТОРОВ Wend Выполняет блок операторов, пока заданное условие имеет значение True. Условие проверяется перед выполнением операторов, расположенных внутри цикла. 3. Оператор Do While…Loop Do [While УСЛОВИЕ] БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop Выполняет блок операторов, пока заданное условие имеет значение True. Условие проверяется перед выполнением операторов, расположенных внутри цикла. Если условие отсутствует, то получаем бесконечный цикл. Оператор Exit Do позволяет немедленно выйти из цикла. 4. Оператор Do…Loop While Do БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop [While УСЛОВИЕ] Выполняет блок операторов, пока заданное условие имеет значение True. Условие проверяется после выполнения операторов, расположенных внутри цикла. 5. Оператор Do Until…Loop Do [Until УСЛОВИЕ] БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop Выполняет блок операторов, пока заданное условие имеет значение False. Условие проверяется перед выполнением операторов, расположенных внутри цикла. 6. Оператор Do…Loop Until Do БЛОК_ОПЕРАТОРОВ [Exit Do] БЛОК_ОПЕРАТОРОВ Loop [Until УСЛОВИЕ] Выполняет блок операторов, пока заданное условие имеет значение False. Условие проверяется после выполнения операторов, расположенных внутри цикла. 7. Оператор For Each…Next For Each ЭЛЕМЕНТ In ГРУППА БЛОК_ОПЕРАТОРОВ [Exit For] БЛОК_ОПЕРАТОРОВ Next [ЭЛЕМЕНТ] Повторяет выполнение блока операторов для элементов группы. Группой может быть, как массив, так и набор объектов (ячейки, диаграммы, формы и т.д.). VBA автоматически задает значение переменной ЭЛЕМЕНТ во время каждой итерации цикла. Контрольные задания В соответствии с вариантом (№ п/п), указанным преподавателем, создать пользовательскую функцию. X b
Пример 2.1. Составить таблицу начисления премиальных по итогам работы сети 4-х магазинов за три месяца по следующему правилу: – если продукции продано не меньше чем на 60000 рублей, то премиальные составляют 2 % от суммарной выручки магазина; – за первое место дополнительно начисляется 4 % премиальных, за второе — 2 %, за третье — 1 % от суммарной выручки магазина. Сформируем таблицу в Excel и заполним значениями, например, так, как показано на рис. 2.1. Исходная таблица  Рис. 2.1 Исходная таблица Для удобства будущей работы в VBA можно сменить стиль ссылок на ячейки: буквы столбцов заменить на цифры (см. рис. 2.5). Для этого исполните команду: Файл | Параметры |Формулы | Работа с формулами | Стиль ссылок R1С1 (поставить галочку)). Теперь адрес ячейки D5 будет выглядеть как R5C4, а в формулах адрес используемой ячейки, будет задаваться сдвигом относительно той ячейки, где вводится формула. Удобство этого вы оцените, когда будете работать с объектом Cells. Создадим в редакторе VBA процедуру обработки события Click (щелчок мыши по кнопке), которая проводит вычисления по заданному алгоритму и заполняет пустые ячейки таблицы. Сначала создадим кнопку (в группе Элементы управления выполнить Вставить | Кнопка (Элементы ActiveX)). На листе Excel рисуем мышкой кнопку с именем по умолчанию CommandButton1. Изменить имя можно несколькими способами. Оба способа работают при нажатой кнопке Режим конструктора в Элементах управления. Способ 1. Вызвать на кнопке контекстно-зависимое меню и выбрать опцию Свойства (рис. 2.2). В поле Caption заменить имя CommandButton1 на Заполнение таблицы.  Рис. 2.2. Окно Свойства кнопки Способ 2. Вызвать контекстно-зависимое меню и выбрать опцию Объект CommandButton | Edit. В поле кнопки появляется маркер — можно изменить текст. Двойной щелчок по созданной кнопке, или выбор опции Исходный текст в контекстно-зависимом меню, или щелчок по кнопке Visual Basic осуществляет переход в редактор VBA (рис. 2.3), в правом окне которого уже появился шаблон процедуры обработки события Click.  Рис. 2.3. Интерфейс редактора VBA Напишем следующий код для этой процедуры с использованием циклических структур. Текст, начинающийся с кавычки, — это примечание. Private Sub CommandButton1_Click() 'Цикл суммирования выручки за 3 месяца For i = 2 To 5 Cells(i, 5).Value = Cells(i, 2).Value + Cells(i, 3).Value + Cells(i, 4).Value Next 'Создание вспомогательного массива box 'и заполнение его значениями выручки Dim box(4) As Double box(1) = Cells(2, 5) box(2) = Cells(3, 5) box(3) = Cells(4, 5) box(4) = Cells(5, 5) 'Сортировка выручки за 3 месяца по убыванию методом «пузырька». 'При этом в box(1) окажется максимальное значение выручки For i = 1 To 4 For j = i + 1 To 4 If box(i) < box(j) Then q = box(i) box(i) = box(j) box(j) = q End If Next Next 'Начисление процентов в зависимости от занятого места For i = 2 To 5 Cells(i, 7) = 0 If Cells(i, 5) = box(1) Then Cells(i, 7) = 4 If Cells(i, 5) = box(2) Then Cells(i, 7) = 2 If Cells(i, 5) = box(3) Then Cells(i, 7) = 1 Next 'Начисление процентов, если выручка за 3 месяца больше плановой выручки For i = 2 To 5 If Cells(i, 5).Value >= Cells(1, 11).Value Then Cells(i, 6) = 2 Else: Cells(i, 6) = 0 End If Next 'Суммирование процентов For i = 2 To 5 Cells(i, 8).Value = Cells(i, 6).Value + Cells(i, 7).Value Next 'Расчет итоговой премии For i = 2 To 5 Cells(i, 9).Value = Cells(i, 5).Value * Cells(i, 8).Value / 100 Next End Sub Закроем редактор и нажмем на кнопку «Заполнение таблицы», отключив предварительно Режим конструктора. Получим заполненную таблицу (рис. 2.4).  Рис.2.4. — Результирующая таблица: значение премии в зависимости от выручки магазина В результате в столбце Е окажется сумма выручек за 3 месяца, в столбце F — процент, назначенный за перевыполнение плана, в столбце G — процент, назначенный в зависимости от занятого места, в столбце Н — итоговый процент, в столбце I — величина премии. Пример 2.2. Вычислить значение  где а — вектор из m компонентов, с — матрица размерности n×n, при a=[3;1;2;3],  Откроем лист Excel и введем исходные данные. В диапазон (A1:D1) введем элементы вектора а, в диапазон (A3:C5) — элементы матрицы с. Способ 1. Непосредственный ввод формулы в ячейку с использованием стандартных функций рабочего листа Excel. В ячейку Е3 введем формулу: S=((2*СУММ(A1:D1)+СУММ(A3:C5))/((1+СУММ(A1:D1))*(1+СУММКВ(A1:D1)))) и нажмем клавишу Enter. В результате в ячейке Е3 получим искомое значение S (рис. 2.5.).  Рис. 2.5. — Результирующая таблица Способ 2. Программирование в VBA с использованием стандартных функций для работы с массивами. Для этого в редакторе VBA добавляем модуль с помощью команды Insert | Module (рис. 2.6.).  Рис. 2.6. — Выбор команды Insert | Module Запишем в модуле код: Public Function FirstFunction(a, c As Variant) As Variant Dim s1, s2, s3 As Variant 'Сумма элементов вектора а s1 = WorksheetFunction.Sum(a) 'Сумма элементов матрицы с s2 = WorksheetFunction.Sum(c) 'Сумма квадратов элементов вектора а s3 = WorksheetFunction.SumSq(a) 'Расчет результата FirstFunction = (2 * s1 + s2) / ((1 + s1) * (1 + s3)) End Function Теперь в списке функций Excel в категории, Определенные пользователем появилась функция FirstFunction. Ее вызов на лист Excel (Формулы | группа Библиотека функций | Вставить функцию или щелчок по соответствующей кнопке на строке формул) приводит к появлению диалогового окна Аргументы функции (рис. 2.7), где вводятся адреса массивов, а и с.  Рис. 2.7. — Диалоговое окно Аргументы функции После нажатия кнопки ОК в той ячейке, из которой вызвали функцию (здесь Е4), получаем результат (рис. 2.5). Способ 3. Программирование в VBA с использованием циклических конструкций. Для этого в том же модуле запишем новую функцию, где расчет вычисления искомого значения S оформим без использования стандартных функций, используя циклы (для разнообразия — трех разных типов). Public Function SecondFunction(a, c As Variant) As Double Dim s1, s2, s3 As Double, i, j, n, m As Integer 'Нахождение количества элементов вектора а m = a.Columns.Count 'Нахождение количества строк матрицы с (оно же – количество столбцов, 'если матрица квадратная) n = c.Rows.Count 'Найдем сумму элементов вектора а (цикл типа While… Wend) s1 = 0 i = 1 While i <= m s1 = s1 + a(i) i = i + 1 Wend 'Найдем сумму элементов матрицы с (цикл типа For… Next) s2 = 0 For i = 1 To n For j = 1 To n s2 = s2 + c(i, j) Next j Next i 'Найдем сумму квадратов элементов вектора а (цикл типа Do… Loop) s3 = 0 i = 1 Do Until i > m s3 = s3 + a(i) ^ 2 i = i + 1 Loop 'Расчет результата SecondFunction = (2 * s1 + s2) / ((1 + s1) * (1 + s3)) End Function Теперь в списке функций Excel в категории, Определенные пользователем появилась еще одна функция SecondFunction. Ее вызов в ячейке Е5 приводит к появлению третьего ответа (рис. 2.5). Все три решения совпали, что и следовало ожидать. Пример 5.4. Построить квадратную матрицу G размером n×n, элементы которой определяются по формуле:  Сформируем на новом листе массив вектора C в ячейках А2:D2, в ячейку B4 занесем значение n (рис.2.8).  Рис. 2.8. Способ 1. Создание процедуры построения матрицы, запускаемой кнопкой. Создадим кнопку «Старт!», как это было сделано в предыдущей работе. Затем щелкнем два раза по кнопке и в окне редактирования кода модуля запишем процедуру обработки щелчка по кнопке — процедуру построения матрицы. Private Sub CommandButton1_Click() 'Определяем переменную, в которой будет храниться размер матрицы Dim n As Integer 'Определяем массив С для исходных данных и массив g 'для результирующей квадратной матрицы Dim C(), g() 'Задаем значение переменной n, выбирая его из ячейки В4 n = Cells(4, 2) 'Выделяем память для массивов С и g ReDim C(1 To n), g(1 To n, 1 To n) 'Вводим в массив С значений из ячеек A2:D2 For k = 1 To n C(k) = Cells(2, k) Next 'Проводим расчет элементов матрицы 'по заданной формуле, 'организуя двойной цикл (по строкам и столбцам) For i = 1 To n For j = 1 To n If i <= j Then g(i, j) = Sin(C(i)) ^ 2 Else g(i, j) = C(i – j) + Cos(C(i)) End If 'Ввод элементов полученной матрицы g в ячейки A7:D10 листа Excel Cells(i + 5, j) = g(i, j) Next j Next i End Sub Затем, нажав на кнопку «Старт!», в ячейках A7:D10 получим искомую матрицу G (рис. 2.8). Задание 5.1. Исключить лишнее входное данное Измените код так, чтобы в примере, рассмотренном в способе 1 примера 5.4, можно было обойтись без использования ячейки со значением n, т.е. убрать его из списка входных данных. Способ 2. Создание пользовательской функции. По аналогии с примером 3 создадим функцию, аргументом которой является массив и которая после работы возвращает также массив. Function T(C As Variant) As Variant 'Определяем локальные переменные Dim n, i, j As Integer 'Определяем массив R для исходных данных Dim R() As Variant 'Определяем число элементов вектора, расположенного в строке n = C.Columns.Count 'Выделяем память для вспомогательного двумерного массива R 'размером n×n ReDim R(1 To n, 1 To n) 'Вычисляем элементы матрицы For i = 1 To n For j = 1 To n If i <= j Then R(i, j) = Sin(C(i)) ^ 2 If i > j Then R(i, j) = C(i – j) + Cos(C(i)) Next Next 'Возвращаем элементы матрицы T = R End Function Теперь в списке пользовательских функций появилась функция Т. Вызов этой функции отличается от вызова функции, результатом которой является одно число. Сначала надо выделить на листе Excel массив ячеек, куда будет занесен ответ. В нашем случае это массив ячеек A7:D10 размером 4×4. Затем после вызова функции и занесения данных (массив ячеек A2:D2) следует выйти из диалогового окна функции, нажав сочетание клавиш Shift+Ctrl+Enter (а не ОК или Enter, как обычно). Задание. Процедуры и функции Пусть C — одномерный массив, состоящий из n элементов, а G — квадратная матрица n × n. В задании (a) необходимо вычислить квадратную матрицу G и вывести значения ее элементов на рабочий лист. В задании (б) нужно вычислить значение s тремя способами: 1) написать функцию, используя различные операторы цикла; 2) написать функцию, используя стандартные функции VBA; 3) ввести формулу в ячейку с помощью стандартных функций рабочего листа Excel.           |
