Проектирование в среде VBA
20
Министерство науки и образования Украины
Харьковский национальный экономический университет
Кафедра информатики и компьютерных технологий
Индивидуальное научно-исследовательское заданиеПо курсу:
«Экономическая информатика»На тему
: «Проектирование в среде VBA»Руководитель: Выполнила:Давыдов Д.Д. студентка 1 курса 8 группыфакультета ФЭФБондаренко В.К.Харьков, 2007
Тема: Проектирование приложения в интегрированной среде редактора VBA для решения задачи - определение суммы возврата кредита с уплатой процентов банку по истечении определенного срока.Цель: выработать умения и навыки работы с компонентами интегрированной среды VBA при разработке приложений для решения задач по банковскому делу.Постановка задачиСоставить программу, реализующего вычислительный процесс по определению суммы возврата кредита с уплатой процентов банку по истечению срока действия кредитного договора (в рассматриваемом примере принимается срок кредитования, равным 124 месяцам). Предусматривается выплата по процентам в конце действия кредитного договора.Математическая модельЕжемесячная процентная ставка, исходя из общей суммы процентов, которую рассчитывает получить банк по данному кредиту, рассчитывается по формуле:
P% = P / 100 / 12где P% - месячная процентная ставкаР - годовая процентная ставкаСумма средств, подлежащая возврату банку по истечению срока действия кредитного договора, увеличится на сумму начисленных процентов за пользование кредита:
S1% = K1 * P% - за первый месяцЗа последующие месяцы сумма начисленных процентов рассчитывается с помощью цикла по формуле
S%2= У (S%2 + (K1 - K1/ N * (i - 1)) * Р%)Общая сумма начисленных процентов:
S% = S%1 + S%2где
S%1
- сумма начисленных процентов за пользование кредитом за первый месяц;S%2 - сумма начисленных процентов за пользование кредитом за последующие месяцыK1 - сумма кредитаN - срок кредитаP% - месячная процентная ставкаi - целое число меняется от 2 до NПо истечению срока действия кредитного договора (124 месяца для рассматриваемого примера) сумма средств, подлежащая возврату клиентом банка, с начисленной суммой процентов определяется по формуле:
K2 = K1 + S%,где
К2 - сумма средств, подлежащая возврату банку по истечении срока.Используя математическую модель, требуется разработать алгоритм решения задачи, в котором необходимо предусмотреть четыре этапа обработки данных:1. Ввод исходных данных для решения задачи - сумма полученного кредита и годовая процентная ставка за пользование кредитом;2. Вычисление суммы начисленных процентов за пользование кредитом;3. Расчет суммы средств, которая возвращается клиентом банку;4. Вывод результатов вычислений.
Алгоритм решения задачиВ соответствии с приведенной выше математической моделью необходимо разработать алгоритм решения задачи. Пример разработанного алгоритма представлен на рис. 1.
Структура данныхВ разрабатываемом приложении используются различные элементы управления, состав которых и их основные свойства представлены в таблице 1.Таблица. Основные свойства элементов управления
|
Имя элемента управления | Name | Caption | Text | Назначение | |
Label | lblКредитор | Кредитор: (введите наименование банка) | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblЗаемщик | Заемщик (введите Ф. И. О.) | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblКредит | Сумма кредита | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblСрокКредита | Срок кредита | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblСтавка | Процентная ставка | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblСуммаПлатежей | Сумма платежей по процентам | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblВозврат | Сумма возврата кредита с уплатой процентов | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblДата1 | Дата выдачи кредита | Отсутствует | Текст свойства Caption выводится на форме | |
Label | lblДата2 | Дата возврата кредита | Отсутствует | Текст свойства Caption выводится на форме | |
TextBox | txtКредитор | Отсутствует | Пусто | Используется для ввода наименования банка | |
TextBox | txtЗаемщик | Отсутствует | Пусто | Используется для ввода Ф. И. О. клиента | |
TextBox | txtКредит | Отсутствует | Пусто | Используется для ввода исходных данных (сумма кредита) | |
TextBox | txtСрокКредита | Отсутствует | Пусто | Используется для ввода исходных данных (срок кредита) | |
TextBox | txtСтавка | Отсутствует | Пусто | Используется для ввода исходных данных (процентная ставка) | |
TextBox | txtСуммаПлатежей | Отсутствует | Пусто | Используется для вывода суммы платежей по процентам | |
TextBox | txtВозврат | Отсутствует | Пусто | Используется для вывода суммы возврата кредита с уплатой процентов | |
TextBox | txtДата1 | Отсутствует | Пусто | Используется для вывода даты выдачи кредита (вычисляется на момент выполнения программы) | |
TextBox | txtДата2 | Отсутствует | Пусто | Используется для вывода даты возврата кредита | |
CommandButton | cmdПечать | Печать формы | Отсутствует | Печать формы с результатами вычислений | |
CommandButton | cmdВычислить | Вычислить | Отсутствует | Вычисляется результат | |
CommandButton | cmdВыход | Выход | Отсутствует | Завершается работа приложения | |
|
Элементы управления
Label используются для вывода текстовой информации на форме, позволяющей пользователю сориентироваться, как происходит в программе процесс ввода данных и где размещены результаты вычислений. Текст, введенный в свойстве
Caption элемента управления
Label, выводится на форме и в процессе выполнения программы не может быть изменен. Данные
Сумма кредита, Срок кредита, Процентная ставка являются исходными и вводятся с клавиатуры в текстовые поля
txtКредит, txtСрокКердита и
txtСтавка. Поэтому для соответствующих элементов управления
TextBox в свойстве
Name указаны префиксы
txt. Результаты вычислений
Сумма возврата кредита с уплатой процентов, Сумма платежей по процентам выводятся в форме также в текстовое поле -
txtВозврат,
txtСуммаПлатежей. В коде программы при выполнении вычислений используются 10 переменных:
curSumKreditintSroksngProcentcurSumPlatcurSumPlatVsegocurSumVozvratcurPlatZa1mesdtmData1dtmData2iВ таблице 2 представлены имена переменных, которые используются в коде программы, и их назначение при обработке банковской информации при кредитовании.Таблица 2Структура данных
|
Реквизит | Имя | Тип переменной | Назначение | |
Платежи за месяц | curSumKredit | Currency | Сумма предоставляемого банком кредита | |
Процентная ставка | sngProcent | Single | Месячная процентная ставка | |
Платеж по процентам | curSumPlatVsego | Currency | Общая сумма начисленных процентов | |
Платеж по процентам | curSumPlat | Currency | Сумма платежей по процентам за последующие месяцы | |
Платежи по процентам | curPlatZa1mes | Currency | Платежи по процентам за первый месяц | |
Сумма возврата кредита | curSumVozvrat | Currency | Сумма средств, возвращаемая клиентом | |
Срок кредита | intSrok | Integer | Количество месяцев , на которые выдан кредит | |
Дата выдачи кредита | dtmData1 | Date | Дата выдачи кредита | |
Дата возврата кредита | dtmData2 | Date | Дата возврата кредита | |
Счетчик | i | integer | Переменная цикла | |
|
Интерфейс с пользователемЗадача решается с помощью формы, структура которой представлена на рис. 2. Проект формы и внешний ее вид после выполнения программы представлены на рис. 3.
6Рис. 2. Расположение объектов на формеОбъекты -
Кредитор, Заёмщик, Сумма кредита, Срок кредита, Процентная ставка, Сумма возврата кредита с уплатой процентов, Сумма платежей по процентам, Дата выдачи кредита, Дата возврата кредита размещаются на форме с помощью элемента управления
Label и имеют префикс
lbl.
в эти два поля (
txt Кредитор и
txt Заемщик соответственно) вводится текстовая информация с клавиатуры
-- txtКредит --
txtСрокКредита--
txtСтавка -- txtДата1 -- txtДата2--
txtСуммаПлатежей--
txtВозврат--
cmdПечать --cmdВычислить --
cmdВыходПроект формыГрафический образ формы после выполнения приложения Рис.3. Проект формы и внешний ее вид после выполнения программы
Код программыКод программы, который обеспечивает выполнение указанного выше проекта, представлен на рис. 4.Рис.4. Окно кода с процедурами обработки событийРасчет суммы средств, подлежащей возврату банку, выполняется по щелчку на кнопке
Вычислить определяется начисляемая за пользование кредитом сумма по процентам, которая суммируется с величиной кредита, полученной клиентом банка по кредитному договору.В текстовые поля
txtДата1 (Дата выдачи кредита)
и
txtДата2 (Дата возврата кредита)
информация вводится с помощью функции дат:
Now и
Dateadd соответственно. Дата выдачи кредита - это текущая дата и время, полученная с помощью функции
Now. Допускается, что оформление кредитного договора и выдача клиенту кредита осуществляется в тот день, когда данная программа запускается на выполнение.
Дата возврата кредита определяется с помощью функции
DateAdd, которая добавляет к заданной дате (дате выдачи кредита) количество временных интервалов. В рассматриваемом примере предусматривается выдача кредита на год с выплатой суммы по процентам в конце действия кредитного договора. Срок действия кредитного договора вводится в коде программы с помощью константы, значение которой и добавляется функцией
DateAdd к дате выдачи кредита.Результаты работы программы выдаются на печать с помощью команды
PrintForm, которая выводит на принтер графический образ окна формы. Завершение работы программы осуществляется с помощью командной кнопки
Выход.На рис. 4 представлены процедуры, которые выполняют указанные выше действия.
Проектирование приложения1. Если не открыто приложение
Microsoft Excel, то откройте его командой
Пуск -- Программы -- Microsoft Office -- Microsoft Word или другим способом.2. Выполните команду
Сервис -- Макрос -- Безопасность и установите следующие параметры безопасности:2.1. Во вкладке
Безопасность выберите переключатель
Средняя.2.2. Во вкладке
Надежные изделия установите флажок
Доверять доступ к Visual Basic Project (для
версии 2003 и выше).3. Закройте окно приложения
Microsoft Word, повторите п. 1 и проверьте, сохранились ли параметры безопасности, которые были установлены в п. 2.4. Находясь в среде
Word или другого приложения из
Microsoft Office (
Excel или др.) откройте окно интегрированной среды разработки приложений редактора
Visual Basic командой
Сервис -- Макрос -- Редактор Visual Basic или нажатием комбинации клавиш
<Alt>+<F11>.5. На панели инструментов щелкните кнопку
Insert UserForm - . Появляется окно с формой
UserForm1.6. Задайте имя и заголовок формы. Для этого перейдите в окно свойств
Properties и введите значения следующих свойств:
Name: FrmКредитCaption: КредитованиеПримечание. В свойстве Caption студент указывает название работы.7. Поместите на форму надписи
Кредитор: (введите наименование банка), Заёмщик (введите Ф. И. О.), Сумма кредита, Срок кредита, Процентная ставка, Сумма платежей по процентам, Сумма возврата кредита с уплатой процентов, Дата выдачи кредита, Дата возврата кредита в соответствующие поля (см. рис. 3). · Выберите на панели элементов управления элемент
Label, а затем щелкните на форме в том месте, где будет начинаться надпись, и выделите область для надписи.· Выделите на форме элемент
Label1 щелчком левой кнопкой мыши, а затем в свойстве
Name окна
Properties введите текст
lblКредитор, а в свойстве
Caption, укажите «
Кредитор: (введите наименование банка)».· Справа от созданной надписи (см. рис. 2 и 3), поместите текстовое поле (
TextBox). Выберите на панели элементов управления элемент
TextBox, а затем щелкните на форме и выделите область для нового объекта. Выделите на форме элемент
TextBox1, а затем в окне
Properties введите значение свойства:
Name -
txtКредит.8. Аналогично создайте поля для размещения на форме надписей
Заемщик (введите Ф. И. О.),
Сумма кредита, Срок кредита, Процентная ставка, Сумма платежей по процентам, Сумма возврата кредита с уплатой процентов, Дата выдачи кредита, Дата возврата кредита и соответствующие им текстовые поля (имена полей см. в табл. 1).9. Создайте командную кнопку
Печать формы.· Выберите на панели элементов управления элемент
CommandButton, а затем щелкните на форме в том месте, где будет начинаться кнопка - слева внизу формы, и удерживая нажатой левую кнопку мыши выделите для нее область (см. рис. 3). · Перейдите в окно
Properties и введите значения следующих свойств:
Caption:
Печать формыName:
cmdПечать10. Аналогично создайте командную кнопки
Выход и Вычислить (в
Caption укажите
Выход, а в
Name -
cmdВыход), (в
Caption укажите
Вычислить, а в
Name -
cmdВычислить). Размещение кнопки на форме см. на рис. 2 и 3.11. Вызовите окно
Code (кода) командой
View - Code. Указанное окно может быть вызвано и другими способами, например с помощью контекстного меню.12. Создайте код процедуры, которая обрабатывает событие
щелчок на кнопке «
cmdВычислить».· Дважды щелкните на кнопке «
cmdВычислить». В результате открывается окно кода с заголовком и концом процедуры. В тело процедуры введите оператор
Dim для
явного объявления семи переменных (См. табл. 2 и рис. 4).Переменным
curSumKredit и
intSrok необходимо присвоить значения текстовых полей, которые были введены в форме. Для этих целей введите в тело процедуры два оператора
curSumKredit = CCur(TxtКредит)intSrok = CInt(TxtСрокКредита)Для выполнения вычислений в тело процедуры введите следующие операторы
sngProcent = CSng(TxtСтавка) / intSrok / 100curPlatZa1mes = curSumKredit * sngProcentВ первом операторе определяется процентная ставка за один месяц, во втором вычисляется сумма, начисленная банком по процентам за первый месяц
curSumPlat = 0Далее в переменную для подсчета начислений по процентам за последующие месяцы засылается 0 для начала выполнения цикла.
For i = 2 To intSrokcurSumPlat = curSumPlat + (curSumKredit - curSumKredit / intSrok * (i - 1)) * sngProcentNext iДалее происходит выполнение цикла.
curSumPlatVsego = curSumPlat + curPlatZa1mescurSumVozvrat = curSumPlat + curSumKreditСледующие два оператора вычисляют сумму выплат по процентам и общие выплаты клиента по кредиту.· Следующий оператор обеспечивают вывод полученного результата в текстовое поле формы
TxtСуммаПлатежей = Format(curSumPlatVsego, "0.00")TxtВозврат = Format(curSumVozvrat, "0.00")Для вычисления даты возврата кредита также в тело процедуры введите оператор:
dtmData2 = DateAdd("m", intSrok, dtmData1)Примечание. Возврат кредита осуществляется через год после заключения кредитного договора.13. Создайте код процедуры, которая обрабатывает засылание в поле
TxtДата1 текущую дату при активации формы. Дважды щелкните на форме
TxtДата1. поменяйте название процедуры в верхнем правом окне на
Activate. В тело процедуры введите оператор
TxtДата1 = NowПримечание. Дата выдачи кредита вычисляется на момент выполнения программы.14. Создайте код процедуры, которая обрабатывает событие «
Щелчок на командной кнопке Печать формы»Дважды щелкните на кнопке
Печать формы. В тело процедуры введите оператор, который выведет на печать графический образ окна формы с полученными результатами:
Me.PrintForm15. Создайте код процедуры, которая обрабатывает событие «
Щелчок на командной кнопке Выход».Дважды щелкните на кнопке
Выход. В тело процедуры введите операторы, которые закроют форму.
Unload MeSet FrmКредит = Nothing16. Сохраните документ
Word вместе с программой в своей папке (имя файла, например, может быть -
Кредитование).17. Запустите программу на выполнение и проверьте ее работу с несколькими комплектами исходных данных.18. Завершите работу в приложении
Microsoft Excel. Закройте окно
Excel.19. Двойным щелчком на имени только что созданного файла откройте его. При открытии ответьте на запрос - отключать ли макросы, нажав кнопку
Не отключать макросы, а затем перейдите в окно интегрированной среды разработки приложений редактора
Visual Basic и убедитесь, что в нем имеется форма
UserForm и у вас имеется возможность снова запустить созданную вами программу
Visual Basic.
Литература1.
С Браун Visual Basic 6.0. Учебный курс. - СПб: Питер, 2005.2. Верб Дж. Программирование в Excel 2003:тетрадь разработчика. Пер. с англ. - М.: КУДИЦ-ОБРАЗ, 2006 - 304 с.3. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. - СПб. БХВ-Петербург, 2001. - 816 с.4. Джексон, Мзри, Стонтон, Майк. Финансовое моделирование в ЕхсеІ и VBA: углубленный курс.: Пер. с англ. - М.: Издательский дом "Вильямс", 2006. - 352 с.5. Додж М., Стинсон К. Ефективна робота з Microsoft Excel 2003. - Спб: Питер, 2005. - 1056 с.6. Долженков В. А., Колесников Ю. В.Самоучитель Microsoft Excel 2003. - СПб.: БХВ-Петербург, 2005. - 432 с.7. Лабораторний практикум по информатике и компьютерным технологиям. /Под ред. проф. А.И. Пушкаря. - X.: Издат. Дом "ИНЖЗК", 2004. - 468 с.8. Олійник А.В., Шацька В.М. Інформаційні системи і технології у фінансових установах: Навчальний посібник. - Львів: "Новий Світ-2000", 2006. - 436 с.9. Современные компьютерные технологии. /Под ред. проф. А.И. Пушкаря. - X.: Издат. дом "ИНЖЗК", 2004. - 464 с.10. Райтингер М., Муч Г. Visual Basic 6.0: Пер. с нем. - К.: Издательская группа BHV, 1999, - 720 с.11. Основи алгоритмізації та програмування/ Федько В. В., Плоткін В. І. - Харків: Веста: Видавництво «Ранок», 2003. - 192 с.12. Федько В.В. Лабораторный практикум по VBA. Электронный учебник. Фонды ХНЭУ, 2006 - 568 кб.