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


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



Рисунок У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 включен, осталось решить данную задачу вручную на рабочем листе и посмотреть на записанный макрос.

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

  1. Выберите команду Сервис, Подбор параметра (Tools, Goal Seek).
  2. В появившемся диалоговом окне Подбор параметра (Goal Seek) в поле Установить в ячейке (Set cell) укажите ссылку на ячейку B6, где содержится формула с левой частью решаемого уравнения. В поле Значение (То value) введите число 10 000, значение правой части решаемого уравнения. В поле Изменяя значение ячейки (By changing cell) укажите ссылку на ячейку B7, которая отведена под неизвестную в решаемом уравнении.
  3. Нажмите на кнопку ОК. Появится диалоговое окно Результат подбора параметра (Goal Seek Status) с информацией о найденном решении.
  4. Нажмите на кнопку ОК диалогового окна Результат подбора параметра (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 по следующему алгоритму:

  1. Выделите ячейку вз. Выберите команду Формат, Ячейки (Format, Cells). В появившемся диалоговом окне Формат ячеек (Format Cells), на вкладке Число (Number) в списке Числовые форматы (Category) выберите Денежный (Currency). В поле Число десятичных знаков (Decimal places) введите 0, а в раскрывающемся списке Обозначения (Symbol) выберите р. Нажмите кнопку ОК.
  2. Выделите ячейку В4 и установите в ней числовой формат, как описано в выше.
  3. Выделите ячейку В5. Выберите команду Формат, Ячейки (Format, Cells). В появившемся диалоговом окне Формат ячеек (Format Cells) на вкладке Число (Number) в списке Числовые форматы (Category) выберите Процентный (Percentage). В поле Число десятичных знаков (Decimal places) введите 2. Нажмите кнопку ОК.
  4. Выделите ячейку 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









Начало  Назад  Вперед


Книжный магазин