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

Продолжая выполнение лабораторной работы, создадим функции пользователя, реализующие вычисления функций F1V(x,a,…), F2V(x,a,…), F3V(x,a,…), а также функцию, реализующую вычисление F(x), средствами VBA. Рассмотрим последовательность действий, выполняемых при создании пользовательских функций и включения их в библиотеку функций программы Excel.

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

1. В меню "Сервис" выбираем команду "Макрос ►" и далее выполняем команду "Редактор Visual Basic". Открывающееся при этом окно программы Microsoft Visual Basic показано на рис. 3.

Рис. 3. Окно программы Microsoft Visual Basic

2. В окне программы Microsoft Visual Basic выбираем меню "Insert" (Вставка). В этом меню выбирается и выполняется команда Module (Модуль). В результате выполнения этой команды в проект рабочей книги включается папка "Modules" (Модули), в которой создается первый модуль - "Module1". Рабочее поле модуля открывается под полем проекта (Project - VBAProject) и является полем редактора, в котором будут создаваться пользовательские функции и процедуры.

3. В меню "Insert" (Вставка) окна Microsoft Visual Basic выбираем команду "Procedure…" (Процедура). Открывается окно "Add Procedure" (Вставить процедуру).

Рис. 4

В поле Name (Имя) вводим имя создаваемой функции. Например, F1V.

Очень важно, чтобы имя функции не совпадало с именем какой-либо ячейки на листе книги. Имена ячеек принимают значения от А1 до IV65536. Нарушение этого условия приводит к появлению ошибок при выполнении процедур и функций.

4. В группе Type (Тип) выбираем переключатель, определяющий тип создаваемой процедуры. Тип Sub (Подпрограмма) может быть использован для создания процедур, выполняющих какие-либо действия и не передающих в основную программу числовые значения. Тип процедуры Function (Функция) используется для определения подпрограмм, результатом выполнения которых является одно числовое или логическое значение, передаваемое (возвращаемое) в основную программу через имя функции.

В рассматриваемом примере выбираем переключатель типа Function.

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

6. После нажатия кнопки ОК в поле редактора вносятся две строки заготовки функции:

Public Function F1V()

End Function

7. Используя эту заготовку, допишем функцию пользователя для вычислений на первой ветке разветвляющейся функции.

Public Function F1V(x, a, b)

F1V = a + Exp(b * x)

End Function

8. Для проверки работоспособности созданной функции F1V, используя "Мастер функций", запишем обращение к ней в ячейку С12. Для этого в окне первого шага "Мастера функций" в поле "Категория:" выбираем категорию "Определенные пользователем". Выбор функции F1V и нажатие кнопки ОК открывает ДО для определения параметров обращения. На рис. 4 показан результат определения ссылок в полях окна второго шага "Мастера функций".

Следует обратить внимание на то, что ссылки на параметры А и В должны быть абсолютными. Этим обеспечивается возможность"протягивания" формулы для вычисления значений функции F1V в ячейках от С12 до С32.

Рис. 5

9. Повторяя действия пунктов от 2 до 8, создаем функции F2V и F3V, реализующие соответственно функции для вычисления второй, третьей ветвей функции средствами алгоритмического языка Visual Basic. Ниже приведены тексты этих функций.

Public Function F2V(x, a, b)

F2V=(a+b*x)/(1+x)

End Function

Public Function F3V(x, a, b)

F3V = a / (b * x)

End Function

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

Рассмотрим порядок выполнения операций по записи оператора для вычисления функции F2V

Первой выполняется операция умножения b на х. Второй выполняется операция сложения , в результате выполнения которой вычисляется значение числителя. Третьей операцией выполняется вычисление знаменателя (1 + х) и лишь после этого выполняется операция деления значения () на (1 + х).

Неправильная расстановка скобок в записи выражений является наиболее часто встречающейся ошибкой.

10. Реализовав в модуле функции F1V, F2V, F3V и заполнив соответствующие столбцы таблицы, выполним проверку правильности вычислений. Для этого сравним значения F1V, F2V, F3V, записанные в столбцах таблицы седьмой лабораторной работы, со значениями, записанными в аналогичных столбцах лабораторной работы №6.

    11. Следующим шагом в выполнении лабораторной работы будет реализация разветвляющейся функции F(x) средствами программы Excel. В ячейку Е12 введём формулу, реализующую алгоритм вычисления разветвляющейся функции (рис.1). Нажмём кнопку "=" в строке формул и вызовем окно мастера функций. В списке функций или в окне "Мастер функций - шаг 1 из 2" в категории "Логические" выбираем функцию "ЕСЛИ".

Рис. 6. ДО "Мастер функций", обеспечивающее выбор функции

12. Открывается ДО функции "ЕСЛИ", для реализации 1-го, 2-го и 3-го блоков алгоритма блок-схемы

Рис. 7. ДО логической функции ЕСЛИ

12. В строку ввода "Логическое выражение", вводим выражение "A12<$B$10", соответствующую условию "Х < α". Для этого последовательно выполняем следующие действия:

• активизируем поле ввода строки "Логическое выражение";

• УМ в поле ячейки А12, ЩЛК. В поле ввода формируется ссылка на ячейку А12, в которую записано значение Хнач;

• вводим с клавиатуры символ "<";

• УМ в поле ячейки В10, в которой записано значение параметра α, ЩЛК и F4. Формируется абсолютная ссылка на ячейку: $B$10.

• При вычислении этого выражения для значения Х равного Хнач. Если Хнач < α, то справа от поля ввода появляется значение "ИСТИНА";

• активизируем поле ввода строки "Значение_если_истина" и вводим в это поле выражение, обеспечивающее вычисление первой ветви функции: $B$9+exp($D$9*A12), соответствующее функции

F1V = a + Exp(b * x);

• в результате в ДО "ЕСЛИ" получаем оформленные поля ввода "Логическое выражение" и "Значение_если_истина".

Рис.8. Окно формирования полей ввода функции ЕСЛИ. Определение условия и функции первой ветви.

14. Закончив набор формулы в поле "Значение_если_истина" переводим текстовый курсор в поле ввода "Значение_если_ложь". Щелчком ЛКМ по слову ЕСЛИ в поле "Имя" строки формул открываем новое окно мастера формул для функции ЕСЛИ. Это окно используется для определения функций второй и третьей ветвей.

15. В поле "Логическое выражение" второго окна ЕСЛИ (в соответствии с алгоритмом рис. 1 блок 3) вводим условие "Х<=β", которому соответствует выражение

A12<=$D$10

• активизируем поле ввода строки "Значение_если_истина" и вводим в это поле выражение, обеспечивающее вычисление второй ветви функции: ($B$9+$D$9*A12)/(1+A12), соответствующее функции

F2V=(a+b*x)/(1+x)

• активизируем поле ввода строки "Значение_если_истина" и вводим в это поле выражение, обеспечивающее вычисление третьей ветви функции: $B$9/($D$9*A12), соответствующее функции

F3V = a / (b * x)

Окончательный вид формул, записываемых во втором окне мастера формул функции ЕСЛИ приведён на рис. 9.

15. Протягиваем формулу, записанную в ячейку Е12 по столбцу. Определяем по значениям аргумента Х точки перехода от одной верви функции к другой и сравниваем значения функции F(x) со значениями в столбцах функций F1V, F2V, F3V.

16. Если результаты сравнения положительны, переходим к записи функций QRF и QRF1 для вычисления значений разветвляющейся функции. Эти функции представляют два варианта реализации алгоритма, блок-схема которого приведена на рис. 1.

Рис. 9. Вид окна функции ЕСЛИ, реализация второй и третьей ветвей

15. Если результаты сравнения положительны, переходим к записи функций QRF и QRF1 для вычисления значений разветвляющейся функции. Эти функции представляют два варианта реализации алгоритма, блок-схема которого приведена на рис. 1.