Самоучитель VBA
       

Практика



Для решения задачи нахождения текущего объема вклада и нахождения маргинальной процентной ставки с помощью редактора пользовательских форм создадим Диалоговое окно Маргинальная процентная ставка (рис. У2.1).

Рис. У2.1. Диалоговое окно Маргинальная процентная ставка

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



UserForm Initialize

  1. Активизирует диалоговое окно.

  2. Запрещает ввод данных пользователем в поля Текущий объем ссуды И Маргинальная процентная ставка.

  3. Назначает клавише <Esc> функцию кнопки отмена, а клавише <Enter> - вычислить.

  4. Связывает с кнопками Вычислить и отмена всплывающие подсказки.
Нажатие кнопки вычислить запускает на выполнение процедуру

CommandButton1_Click

  1. Проверяет, являются ли вводимые данные числами. Если хотя бы одно из них не является числом, то на экране отображается соответствующее информационное сообщение с ; установкой фокуса на поле, где произведен некорректный ввод данных.

  2. Проверяет, будет ли ссуда меньше, чем возвращаемая сумма денег. Если ссуда больше этой суммы, то отображается сообщение с указанием, на сколько ссуда меньше возвращаемой суммы денег (рис. У2.2).

  3. Используя финансовую функцию рабочего листа, пз (PV) вычисляет чистый текущий объем ссуды при введенной процентной ставке.

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

  5. Подготавливает рабочий лист для вывода результатов вычислений. Выводит полученные данные на рабочий лист и в диалоговое окно (рис. У2.3).
Нажатие кнопку отмена запускает на выполнение процедуру

CommandButton2_Click

 Закрывает диалоговое окно.

Рис. У2.2. Сообщение о некорректном вводе данных

Рис. У2.3. Отчет, выводимый на рабочем листе программой расчета маргинальной процентной ставки

Private Sub CommandButtonl_Click() '

' Процедура расчета маргинальной процентной ставки


'

Dim i As Double

Dim p As Double

Dim A As Double

Dim iMarg As Double

Dim pPure As Double

Dim n As Integer

' n - число выплат

' p - размер ссуды

' a - размер одной выплаты

' i - процентная ставка

' pPure - текущий объем ссуды,

' на рабочем листе вычисляется функцией ПЗ

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

' Проверка того, чтобы введенные

' в диалоговое окно данные являются числами

'

If IsNumeric(TextBoxl.Text) = False Then MsgBox "Ошибка в числе выплат",

vblnformation, "Маргинальная ставка"

TextBoxl.SetFocus

Exit Sub

End If

'

If IsNumeric(TextBox2.Text) = False Then MsgBox "Ошибка в размере ссуды",

vblnformation, "Маргинальная ставка" TextBox2.SetFocus

Exit Sub

End If

If IsNumeric(UserForml.TextBox3.Text) = False Then MsgBox "Ошибка в размере одной выплаты",

vblnformation, "Маргинальная ставка"

TextBox3.SetFocus

Exit Sub

End If

'

If IsNumeric(TextBox4.Text) = False Then

MsgBox "Ошибка в процентной ставке",

vblnformation, "Маргинальная ставка"

TextBox2.SetFocus

Exit Sub

End If

'

' Ввод данных в переменные из диалогового окна

'

n = CInt(TextBoxl.Text)

р = CDbl(TextBox2.Text)

А = CInt(TextBqx3.Text)

i = CInt(TextBox4.Text) / 100

'

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

'

If n * А < р Then

MsgBox "Возвращается на " & CStr(Format(р - n * A, "Fixed")) & " меньше размера ссуды", vbExclamation, "Маргинальная ставка"

TextBoxl.SetFocus

Exit Sub

End If

'

' Изменение ширины столбцов и задание режима ввода

' текста с переносом

'

ActiveSheet.Columns("A:A").Select

With Selection

.ColumnWidth =20 .WrapText = True

End With

ActiveSheet.Columns("B:B").Select

Selection.ColumnWidth = 12

' Выбор ячейки В2 для того, чтобы снять выделение со столбца В

ActiveSheet.Range("В2").Select

'

' Ввод названий записей на рабочем листе



With ActiveSheet

.Range("A2").Value = "Число выплат"

.Range("A3").Value = "Размер ссуды"

.Range("A4").Value = "Размер одной выплаты"

.Range("A5").Value = "Процентная ставка"

.Range("A6").Value = "Текущий объем ссуды"

.Range("А7").Value = "Маргинальная процентная ставка"

.Range("A8").Value = "Маргинальный чистый текущий объем ссуды"

.Range("B8").Activate End With

'

' Расчет чистого текущего объема ссуды

'

pPure = Application.PV(i, n, -A)

'

' Нахождение маргинальной процентной ставки

' с помощью команды Подбор параметра.

' Ввод данных в ячейки активного рабочего листа

' и задание процентного и денежного форматов в ячейках

With ActiveSheet

.Range("B2").Value = n

.Range("B3").NumberFormat = "#,##0$"

.Range("B3").Value = p

.Range("B4").NumberFormat = "#,##0$"

.Range("B4").Value = A

.Range("B5").NumberFormat = "0.00%"

.Range("B5").Value = i

.Range("B7").NumberFormat = "0.00%"

'

' Ввод начального приближения для маргинальной процентной ставки

'

.Range("B7").Value = i

'

' Ввод формулы расчета '

.Range("B8").FormulaLocal = "=ПЗ(B7;B2;-B4)"

.Range("B6").Value = .Range("B8").Value

'

' Выполнение команды Подбор параметра

'

.Range("B8").GoalSeek Goal:=p, ChangingCell:=.Range("B7")

' Присвоение найденного значения маргинальной процентной ставки

' переменной iMarg

iMarg = .Range("B7").Value

End With

'

' Переформатирование найденных значений ' и вывод их в диалоговом окне

TextBoxS.Text = CStr(Format(pPure, "Fixed"))

TextBox6.Text = CStr(Format(iMarg * 100, "Fixed"))

End Sub

Private Sub CommandButton2_Click()

'

' Процедура закрытия диалогового окна



UserForml.Hide

'

End Sub

Private Sub UserForm_Initialize()

' Процедура вызова диалогового окна.

' Поля

' Чистый текущий объем ссуды

' и Маргинальная процентная ставка

' доступны для вывода информации, но не для ее ввода

TextBox5.Enabled = False

TextBox6.Enabled = False

'

' Клавише <Enter> назначена функция кнопки Вычислить

' Кнопке Вычислить назначен текст всплывающей подсказки

'

With CommandButtonl .Default = True

.ControlTipText = "Расчет и составление отчета на рабочем листе"

End With

'

' Клавише <Esc> назначена функция кнопки Отмена

' Кнопке Отмена назначен текст всплывающей подсказки

With CommandButton2 .Cancel = True

.ControlTipText = "Кнопка отмены"

End With '

UserForml.Show End Sub

При написании программ, в которых используется те или иные возможности рабочего листа, а не только VBA, часто бывает удобным вариантом воспользоваться средством MacroRecorder. Он создаст макрос, описывающий на языке VBA осуществляемые вами действия. MacroRecorder является одним из интеллектуальных средств Microsoft Office, которое облегчает работу разработчиков приложений. Конечно, создаваемый им макрос будет во многих отношениях не оптимальным, но тем не менее он даст хорошую подсказку о том, какие инструкции должны быть добавлены в программу, чтобы добиться требуемого эффекта.

Итак, для активизации MacroRecorder выберите команду Сервис, Макрос. Начать запись (Tools, Macro, Record New Macro) и запустите MacroRecorder на запись. После задания всех параметров в появившемся диалоговом окне Запись макроса (Record Macro) и нажатия кнопки ОК, появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка.

Сперва решите уравнение, вычисляющее маргинальную процентную ставку. В ячейку В2 введено число выплат, равное б, ячейка B7 отведена под процентную ставку, а в ячейку вб введена формула =пз{В7;в2;-B4), вычисляющая текущий объем ссуды.


Требуется найти процентную ставку, при которой текущий объем ссуды равен 10 000.

Итак, MacroRecorder включен, осталось решить данную задачу вручную на рабочем листе и посмотреть на записанный макрос.

Решите задачу в соответствии со следующим алгоритмом:

  • Выберите команду Сервис, Подбор параметра (Tools, Goal Seek).

  • В появившемся диалоговом окне Подбор параметра (Goal Seek) в поле Установить в ячейке (Set cell) укажите ссылку на ячейку B6, где содержится формула с левой частью решаемого уравнения. В поле Значение (То value) введите число 10 000, значение правой части решаемого уравнения. В поле Изменяя значение ячейки (By changing cell) укажите ссылку на ячейку B7, которая отведена под неизвестную в решаемом уравнении.

  • Нажмите на кнопку ОК. Появится диалоговое окно Результат подбора параметра (Goal Seek Status) с информацией о найденном решении.

  • Нажмите на кнопку ОК диалогового окна Результат подбора параметра (Goal Seek Status).

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

    Sub Макрос1()

    ' Макрос1 Макрос

    ' Макрос записан 06.05.99 (Ксенофон Заковыркин)

    '

    Range ("B8") .GoalSeek Goal:=10000,

    ChangingCell:=Range ("-B7")

    End Sub

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



    При написании программы данного приложения MacroRecorder может также пригодиться для задания числовых форматов в ячейках вз, В4, B5 и B7.

    Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro) и запустите MacroRecorder на запись.

    Задавайте числовые форматы в ячейках вз, В4, В5 и BV по следующему алгоритму:

  • Выделите ячейку вз. Выберите команду Формат, Ячейки (Format, Cells). В появившемся диалоговом окне Формат ячеек (Format Cells), на вкладке Число (Number) в списке Числовые форматы (Category) выберите Денежный (Currency). В поле Число десятичных знаков (Decimal places) введите 0, а в раскрывающемся списке Обозначения (Symbol) выберите р. Нажмите кнопку ОК.

  • Выделите ячейку В4 и установите в ней числовой формат, как описано в выше.

  • Выделите ячейку В5. Выберите команду Формат, Ячейки (Format, Cells). В появившемся диалоговом окне Формат ячеек (Format Cells) на вкладке Число (Number) в списке Числовые форматы (Category) выберите Процентный (Percentage). В поле Число десятичных знаков (Decimal places) введите 2. Нажмите кнопку ОК.

  • Выделите ячейку B7 и установите в ней числовой формат, как описано в предыдущем пункте.

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

    Sub Макрос3()

    '

    ' МакросЗ Макрос

    ' Макрос записан 06.05.99 (Ксенофон Заковыркин)

    '

    '

    '

    Range("ВЗ").Select

    Selection.NumberFormat = "#,##0$"

    Range("B4").Select

    Selection.NumberFormat = "#,##0$"

    Range("B5").Select

    Selection.NumberFormat = "0.00%"

    Range("B7").Select

    Selection.NumberFormat = "0.00%"

    End Sub



    Рис. У2.4. Диалоговое окно Формат ячеек

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




    Содержание раздела