Laboratornye_raboty_po_KITu / VBA / Л_р_1_vba__инструкция
.docИнструкция к выполнению лабораторной работы №6
Создание функции пользователя с помощью
Visual Basic for Applications (VBA). (задания 1, 2, 3)
-
Открыть или создать рабочую книгу, в которой будем создавать функцию пользователя
-
Запустить VBA можно:
-
или через Alt+F11; это же сочетание клавиш переводит нас из VBA на рабочий лист
-
или пункт меню Сервис, Макрос, Редактор Visual Basic
-
После запуска VBA необходимо добавить модуль. Это можно сделать следующими способами:
-
в главном меню необходимо выбрать пункт меню Insert и выбрать Module
-
в левой части окна редактора VBА расположено окно проектов Project Explorer (если его нет, то нужно CTRL+R). В этом окне откроете проект VBA PROJECT (Книга 1)
-
щёлкните правой клавишей мыши на элементе Эта книга.
-
в контекстном меню выберите команду Insert – Module. Будет добавлен модуль в наше приложение.
-
Далее следует снова щелкнуть пункт меню Insert в главном меню и выбрать уже Procedure... - при этом появится окошко Add Procedure (Добавить Процедуру)
-
В этом окне в текстовом поле Name следует ввести имя функции, например, fun , а затем выбрать в поле Type (тип) - Function, а в поле Scope (сфера действия) - Public
-
Затем сформировать нужную процедуру.
Пример 1: (линейный алгоритм)
Для функции z=(x+y)/(x-y) процедура - функции будет выглядеть следующим образом:
Public Function fun(x, y)
f = (x +y) / (x+ y)
End Function
Пример 2: (разветвляющийся алгоритм)
Для функции
2x2 - 4x + 5 , х<-1
y= 10sin(x2) + 5cos(x), -1<=x<=1
ex – 17 , x>1
процедура - функции будет выглядеть следующим образом:
Public Function Y(x)
If x <-1 Then
y = 2 * x ^ 2 - 4 * x + 5
ElseIf x <= 1 Then
y = 10 * Sin(x ^ 2) + 5 * Cos(x)
Else
y = Exp(x) - 17
End If
End Function
Пример 3: (циклический алгоритм)
процедуры-функции для табулирования функции y(x)
на [ a; b] с шагом h = (b - a) / n
Public Function TbF(a, b, n)
ReDim xy(n, 1)
' xy - массив из двух столбцов
' xy(i,0) - столбец значений х
' xy(i,1) - столбец значений y
h = (b - a) / n : x=a
For i = 0 To n
xy(i, 0) = x
xy(i, 1) = y(x)
x = x + h
Next i
TbF = xy
End Function
Замечание1: функцию с атрибутом Public можно использовать в любом модуле, a Private - только в данном модуле
Замечание2. При задании функции нескольких переменных в процедуре VBA аргументы разделяются запятыми, а при вызове этой функции, задаваемом при записи в ячейке соответствующей формулы аргументы разделяются символом; (точка с запятой).
Теперь, если вызвать мастер функций, то появляется дополнительная категория функций Определенные пользователем в которой появится созданная функция, а также имя функции будет видно в полном алфавитном перечне. Это означает, что созданной функцией можно пользоваться наравне с обычными встроенными функциями рабочего листа.
Создание пользовательской функции в VBA:
1. Открыть рабочую книгу Лабораторной работы № 4: Сервис, Макрос, Редактор VB (переключаться в Excel можно, не выходя из VB – ALT+F11);
2. Создаем новый модуль для открытой книги: п.м. Insert, Module;
3. В созданном модуле создаем новую процедура – п.м. Insert, Procedure…, в появившемся диалоговом окне Add Procedure задается:
-
в поле Name – имя функции (например, skidka),
-
тип процедуры – Function,
-
область видимости – Public.
После выполнения п.3 в окне Code (окно просмотра исходного кода VBA) появятся ключевые слова:
Public Function skidka()
… в теле которых задается текст программы.
End Function
3. Выполняется команда Compile из п.м. Debug (отладка).
4. Сохраняется модуль – п.м. File, Save, сохраняя изменения всех объектов и сохраняя текущий модуль с уникальным именем (например, skidka).
В результате должны получить следующий код процедуры:
Пример 4:
Функция пользователя должна включать расчет скидки и результирующей стоимости. Наценка берется как переменная
Public Function stoimost(X, nacenka, cena)
' Х - количество заказанного товара
' a, b, c - границы количества для увеличения скидки
a = 500: b = 1000: C = 1500
If X <= a Then
skidka = 0.09
ElseIf X <= b Then
skidka = 0.15
ElseIf X <= C Then
skidka = 0.2
Else
skidka = 0.3
End If
stoimost = X * cena - X * cena * skidka + nacenka
End Function
Чтобы воспользоваться функцией, созданной в VBA, необходимо выбрать ее в Мастере функций из Функций в категории Определенные пользователем.
Дополнительная информация.
Тема: Программы с ветвлениями
Условный оператор
Различают однострочный и многострочный условные операторы If:
а) однострочный оператор
If <условие> Then <операторы> [Else <операторы>]
При выполнении оператора If проверяется условие и, если оно истинно, то выполняется действие, указанное после опции Then. Если выражение ложно, то выполняется действие, указанное после опции Else. Затем управление передается на оператор, следующий за оператором If.
После опций Then и Else может быть указано несколько операторов, разделенных двоеточием. Однако число операторов ограничено длиной строки.
б) многострочный оператор If:
-
а) простой
If <условие> Then
<первая группа операторов>
[Else
<вторая группа операторов>]
End If
б) расширенный
If <условие> Then
<первая группа операторов>
[ElseIf <условие> Then
<вторая группа операторов> ]
[Else
<третья группа операторов> ]
End If
При записи операторов следует обращать внимание на структуру записи, структура должна соответствовать той, что указана в примере.
Достоинство многострочного оператора If состоит в том, что число операторов в группах не ограничено.
Пример
Составить VBA-программу для вычисления значений разветвляющейся функции.
1. с помощью функции пользователя
2. с помощью процедуры:
исходные данные к расчету будут располагаться прямо на рабочем листе; процедура будет считывать (вводить) их с листа;
результаты вычислений процедура будет выводить на рабочий лист;
для запуска процедуры будет использоваться специальная последовательность команд.
2x2 - 4x + 5 , х<-1
y= 10sin(x2) + 5cos(x) , -1<=x<=1
ex – 17 , x>1
Решение.
Выполнение пункта 1
Public Function Y(x)
If x <-1 Then
y = 2 * x ^ 2 - 4 * x + 5
ElseIf x <= 1Then
y = 10 * Sin(x ^ 2) + 5 * Cos(x)
Else
y = Exp(x) - 17
End If
End Function
Выполнение пункта 2
Прежде всего обсудим вопрос о том, как можно осуществить ввод данных с рабочего листа и их вывод на рабочий лист. Для этого в Excel есть специальная конструкция:
Cells(i, j).Value
Эта конструкция позволяет получить доступ к ячейке на пересечении строки номер i и столбца номер j на активном рабочем листе (обратите внимание, что номер столбца, как и номер строки, задается числом). С ее помощью можно, как читать данные с листа, так и вывести их на рабочий лист.
Примеры:
Выражение |
Пояснение |
Cells(1,1).Value = 5 |
В ячейку A1 записывается число 5. Эту операция можно рассматривать как операцию вывода на рабочий лист |
Beta=Cells(3,5).Value |
Переменной Beta присваивается значение из ячейки E3. Эту операция можно рассматривать как операцию ввода с рабочего листа |
Cells(1,2).Value=Cells(4,3).Value |
В ячейку B1 копируется значение ячейки C4 |
Обычный (привычный) адрес ячейки можно задавать в другой конструкции Excel:
Range("<адрес ячейки>").Value
Будем считать, что значение Х находится в ячейке B3. Для размещения результата работы программы отведем ячейку D3.
Тогда текст программы может быть таким:
Public Sub Primer()
x = Cells(3, 2).Value Считываем X
If x <-1 Then
y = 2 * x ^ 2 - 4 * x + 5
ElseIf x <= 1Then
y = 10 * Sin(x ^ 2) + 5 * Cos(x)
Else
y = Exp(x) - 17
End If
Cells(3, 4).Value = Y Выводим число Y
End Sub