Создание функций рабочего листа

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

- активизировать редактор Visual Basic;
- если в проекте нет модуля, то создать его выполнив команду меню редактора Вставка / Модуль (Insert / Module);
- выполнить команду меню редактора VB Вставка / Процедура (Insert / Procedure);
- в открывшемся диалоговом окне Добавить процедуру (Add procedure) установить переключатель Функция (Function)
- в поле Имя (Name) ввести имя функции;
- установить соответствующий переключатель Область определения (Scope);
- щелкнуть на ОК. После выполнения этих действий в окне модуля появится заготовка функции (заголовок и окончание), между которыми нужно поместить код тела функции.
- ввести список параметров функции, их типов данных, а также указать тип возвращаемого функцией значения;
- используя команду меню Вид / Просмотр объектов ( View / Object Browser) или нажав клавишу F2 вызвать окно Просмотр объектов (Object Browser);
- раскрыть верхний левый список Список проектов и выбрать из него VBA Project. В окне Классы (Classes) отобразятся элементы текущего проекта;
- выбрать в этом окне модуль, в котором создана функция - в окне Компоненты (Members) отобразятся элементы, которые содержатся в этом модуле.
- выделить в окне Компоненты (Members)  элемент с именем созданной функции и включить контекстное меню;
- выполнить команду контекстного меню Свойства (Properties) – откроется окно Параметры компонента (Member Options).
- в поле Описание (Description) этого окна ввести текст краткого описания функции, если необходимо, то указать файл справки и идентификатор.

Пример 1 : Создание функции пользователя с линейным алгоритмом для вычисления накопленной стоимости
Накопленная стоимость по выданному кредиту (или по вкладу) вычисляется в соответствии с формулой:
S=P(1+iT),
Где S- накопленное значение
P - величина кредита
i - номинальная процентная ставка
T - срок (в месяцах).
Требуется разработать функцию пользователя для расчета накопленной стоимости. Предусмотреть проверку корректности исходных данных.
Разработка.  Прежде всего необходимо определить, какие данные являются исходными для решения задачи и какие типы этих данных.
Для нашей задачи исходными данными являются  величина кредита, номинальная процентная ставка и  срок кредита.  Удобно данные, их идентификаторы и типы представить в виде следующей таблицы:

Для создания функции выполним действия:
- выполним команду меню редактора Visual Basic Вставка / Модуль (Insert / Module), а затем – Вставка / Процедура (Insert / Procedure), раскроется диалоговое окно Добавление процедуры (Add procedure) (рис. ).

Рис.

- в поле имени появившегося окна введем имя функции – НакСумма, установим переключатель Функция. Щелкнем на ОК. В окне редактирования кода появится заготовка функции вида:

Public Function НакСумма()
End Function

- внутри скобок заголовка функции введем описания ее параметров (исходных данных) в соответствии с приведенной таблицей, а за скобками укажем тип значения, возвращаемого функцией:
Public Function НакСумма(ИсходнаяСумма As String, Ставка As String, Срок As String) As Double
- под строкой заголовка функции поместим код инструкций, приведенный в листинге 3.
Листинг 3
'Вычисление накопленного значения исходной суммы по формуле
' S=P(1+iT),
' S- накопленное значение
' P - величина ссуды
' i - номинальная процентная ставка
' T - срок в месяцах
НакСумма = ИсходнаяСумма * (1 + Ставка * Срок / 12)
End If
Создадим описание к разработанной функции, для чего:

- выполним команду меню Вид ® Просмотр объектов ( View ® Object Browser) откроется диалоговое окно Object Browser (рис.);

Рис.

- в списке Список проектов и выберем VBA Project - в поле Классы (Classes) отобразятся элементы текущего проекта;
- выбем в окне Классы модуль, в котором записан код функции - в окне Компоненты (Members) отобразятся элементы, которые содержатся в этом модуле;
- выделим в окне Компоненты (Members)  элемент с именем созданной функции и включим контекстное меню;
- выполним команду контекстного меню Свойства (Properties) – откроется окно Параметры компонента (Member Options) (рис. );

Рис.

- в поле Описание (Description) окна Параметры компонента (Member Options)  введем текст краткого описания функции, если необходимо, то указать файл справки и идентификатор.

Проверим работу функции на примере, вызывая ее с помощью мастера функций Excel.
Правильный результат - 1100 (для P=1000, i =10% и T= 12 месяцев).

Пример 2: Разработка функции пользователя с проверкой корректности исходных данных
Требуется доработать функцию пользователя, разработанную ранее,  таким образом, чтобы она проверяла корректность исходных данных. Данные корректны, если они являются числовыми. Для этой цели используем оператор IF – THEN.
Откроем окно редактирования кода для модуля, содержащего функцию НакСумма.
Дополним ее операторами проверки условий корректности. Поместим инструкции для вывода сообщенийпользователю, если данные некорректны. После доработки функции ее код будет следующий:
Листинг 4
'Вычисление накопленного значения исходной суммы по формуле
' S=P(1+iT),
' S- накопленное значение
' P - величина ссуды
' i - номинальная процентная ставка
' T - срок в месяцах
' Проверка корректности данных
If (IsNumeric(ИсходнаяСумма) And IsNumeric(Ставка) And _
IsNumeric(Срок)) = False Then
MsgBox "Ошибка в исходных данных", vbInformation, _
"Вычисление накопленной суммы"
НакСумма = 0
Exit Function
Else
НакСумма = ИсходнаяСумма * (1 + Ставка * Срок / 12)
End If
Здесь применена встроенная функция IsNumeric, которая возвращает значение True, если ее аргумент является числом, и False, если аргумент не число.
Проверим работу функции для корректных и некорректных данных, вызывая ее с помощью мастера функций Excel.
Пример 3.
Требуется разработать функцию пользователя для расчета накопленной суммы по методу простых процентов при неравномерных непериодических платежах.
Расчет накопленной стоимости при неравномерных непериодических платежах рассчитывается по формуле:

где S – накопленная сумма на счете;
n – количество платежей (периодов)        
ik – номинальная ставка за период;
dtk- продолжительность периода, за который начисляются проценты;
pk – сумма k –того платежа.
Исходными данными для решения задачи являются величины  суммы платежей, даты их выплаты и номинальная процентная ставка. Вычисляемыми данными являются количество платежей и продолжительность периода.
Определим имена переменных и типы данных:

 Замечание.  Если параметр функции передает значения массива ячеек, то он должен иметь тип Variant.
Для создания функции выполним действия:
- создадим модуль;
- поместим в созданный модуль код подпрограммы – функции:
Public Function НАКСУММА(Ставка As Single, _
Платеж As Variant, _
Дата As Variant) As Double
Dim K As Integer
Dim i As Integer
Dim Summa As Double 'Текущая сумма вклада
Dim Дельта As  Integer 'Разность в днях между
‘двумя платежами
Summa = Платеж(1)
K = Платеж.Count 'общее количество платежей
For i = 2 To K
Дельта = Дата(i) - Дата(i - 1)
Summa = Summa + Summa * Ставка * Дельта / 365 + Платеж(i)
Next
НАКСУММА = Summa
End Function
Для проверки работоспособности функции решим задачу, приведенную ниже.

Задача .
Клиент сделал вклад на текущий счет в банке в сумме 1000р под 60 процентов годовых. Через 3, 6 и 9 месяцев он вложил еще по 1000р. В конце учетного года клиент закрыл счет. Какую сумму он получил при закрытии счета?
На рабочем листе Excel подготовьте таблицу с исходными данными и выполните расчет, применив созданную функцию.

Упражнение 8.         

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

Постановка задачи. Накопленная стоимость по выданному кредиту (или по вкладу) вычисляется в соответствии с формулой:

S=P(1+iT),

Где S- накопленное значение

   P - величина кредита

   i - номинальная процентная ставка

 T - срок (в месяцах).

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

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

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

Прступайте к созданию функции, для этого:

1.       Выполните команду меню Вставка, Модуль, а затем – Вставка, Подпрограмма.

2.       В поле имени появившегося окна укажите имя функции – НакСумма. Установите переключатель Функция. Щелкните на ОК. В окне редактирования кода появится заготовка функции вида:

Public Function НакСумма()

End Function

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

Public Function НакСумма(ИсходнаяСумма As String, _

Ставка As String, Срок As String) As Double

Под заголовком функции поместите код функции:

'Вычисление накопленного значения исходной суммы по формуле

' S=P(1+iT),

' S- накопленное значение

' P - величина ссуды

' i - номинальная процентная ставка

' T - срок в месяцах

НакСумма = ИсходнаяСумма * (1 + Ставка * Срок / 12)

End If

4.       Сделайте комментарий к разработанной функции

3.       Проверьте работу функции на примере, вызывая ее с помощью мастера функций Excel.

5.       . При необходимости устраните ошибки.

Правильный результат - 1100 (для P=1000, i =10% и T= 12 месяцев).

Упражнение 9.         

Разработка функции пользователя с проверкой корректности исходных данных

Требуется доработать функцию пользователя, разработанную в упражнении 7,  таким образом, чтобы она проверяла корректность исходных данных. Данные корректны, если они являются числовыми. Для этой цели используем оператор IF – THEN.

1.       Откройте окно редактирования кода для модуля, содержащего функцию НакСумма.

2.       Дополните ее операторами проверки условий корректности. Поместите операторы для вывода сообщений оператору, если данные некорректны. После доработки функции ее код будет следующий:

'Вычисление накопленного значения исходной суммы по формуле

' S=P(1+iT),

' S- накопленное значение

' P - величина ссуды

' i - номинальная процентная ставка

' T - срок в месяцах

' Проверка корректности данных

If (IsNumeric(ИсходнаяСумма) And IsNumeric(Ставка) And _

        IsNumeric(Срок)) = False Then

MsgBox "Ошибка в исходных данных", vbInformation, _

"Вычисление накопленной суммы"

НакСумма = 0

Exit Function

Else

НакСумма = ИсходнаяСумма * (1 + Ставка * Срок / 12)

End If

Здесь применена встроенная функция IsNumeric, которая возвращает значение True, если ее аргумент является числом, и False? Если аргумент не число.

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

Упражнение 10

1.       Создайте в одном из модулей проекта функцию для расчета амортизации с именем АМОРТИЗАЦИЯ.

2.       Поместите в нее следующий код:

Public Function АМОРТИЗАЦИЯ(Стоимость As Double, _

Остаточная_стоимость As Double, Время_эксплуатации As _

Integer,  Период As Integer, Тип As Byte, _

Кратность As Byte) As Double

'Проверка вида расчета. Если Тип <> 0, то кратный метод

' иначе равномерная амортизация

 If Тип <> 0 Then

 АМОРТИЗАЦИЯ = DDB(Стоимость, Остаточная_стоимость, _

                Время_эксплуатации, Период, Кратность)

Else

  АМОРТИЗАЦИЯ = SYD(Стоимость, Остаточная_стоимость, _

                Время_эксплуатации, Период)

End If

End Function

  3.       На рабочем листе поместите исходные данные для проверки функции (рис. 7.3).

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

5.       Отладьте код программы, если необходимо.

6.       Поместите в свойство функции ее краткое описание.

 
Рис. Результаты расчета амортизации

Вопросы для самоконтроля

1.       Какие символы допускается применять в именах переменных?

2.       Можно ли имя переменной начинать с цифры или с символа подчеркивания?

3.       Какие базовые типы данных поддерживает Visual Basic?

4.       Напишите оператор, явно декларирующий строковую переменную для хранения почтового адреса.

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

6.       Напишите оператор, декларирующий одномерный массив из 7 элементов целого типа и имеющий базовый индекс –3.

7.       Что такое динамические массивы и как они декларируются?

8.       Каково различие между переменной и константой. Какие типы констант поддерживает Visual Basic?

9.       Что представляет собой пользовательский тип данных, как он определяется и объявляется?

10.   Каково различие между линейным и блочным оператором If  - Then?

11.   Какой оператор Visual Basic позволяет сделать выбор из нескольких альтернативных вариантов?

12.   Как определяется цикл с известным числом повторений?

13.   Какие циклы с условием поддерживает Visual Basic?

14.   В чем различие циклов с предусловием и постусловием ?

15.   Что такое функция и как она определяется?

16.   Как производится обращение к функции?

17.   Какие типы встроенных функций есть в Visual Basic?

Предыдущая  Следующая   В содержание темы