Проектирование в среде 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.




Начало

Конец

Ввод

K1, P, N











P% = P / 100 / 12





S% = S%1 + S%2

     S1% = K1 * P%



S%2 = 0

S%2 = (S%2 + (K1 – K1/ N * (i - 1)) * Р%)



i = i +1

   i <= N

     Вывод

     K2 , S



K2 = K1 + S%

Рис1.

i = 2


























































Структура данных



В разрабатываемом приложении используются различные элементы управления, состав которых и их основные свойства представлены в таблице 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 переменных: 

curSumKredit

intSrok

sngProcent

curSumPlat

curSumPlatVsego

curSumVozvrat

curPlatZa1mes

dtmData1

dtmData2

i

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



Таблица 2

Структура данных

Реквизит

Имя

Тип переменной

Назначение

Платежи за месяц

curSumKredit

Currency

Сумма предоставляемого банком кредита

Процентная ставка

sngProcent

Single

Месячная процентная ставка 

Платеж по процентам

curSumPlatVsego

Currency

Общая сумма начисленных процентов 

Платеж по процентам

curSumPlat

Currency

Сумма платежей по процентам за последующие месяцы

Платежи по процентам

curPlatZa1mes

Currency

Платежи по процентам за первый месяц

Сумма возврата кредита

curSumVozvrat

Currency

Сумма средств, возвращаемая клиентом 

Срок кредита 

intSrok

Integer

Количество месяцев , на которые выдан кредит

Дата выдачи кредита

dtmData1

Date

Дата выдачи кредита

Дата возврата кредита

dtmData2

Date

Дата возврата кредита

Счетчик

i

integer

Переменная цикла


Интерфейс с пользователем



Задача решается с помощью формы, структура которой представлена на рис. 2. Проект формы и внешний ее вид после выполнения программы представлены на рис. 3.





1



Кредитор



2



Заемщик



Процентная ставка

Сумма кредита

Срок кредита





5







4









3







Сумма возврата кредита с уплатой процентов, грн.

Сумма  платежей по процентам, грн.

7

Дата возврата кредита



6



Дата выдачи кредита

6



9







8













10

Выход

Вычислить

Печать формы



12

11



Рис. 2. Расположение объектов на форме



Объекты - Кредитор, Заёмщик, Сумма кредита, Срок кредита, Процентная ставка, Сумма возврата кредита с уплатой процентов, Сумма платежей по процентам,  Дата выдачи кредита, Дата возврата кредита размещаются на форме с помощью элемента управления Label и имеют префикс lbl.







2

1          



в эти два поля (txt Кредитор и txt Заемщик соответственно) вводится текстовая информация с клавиатуры





3



 — txtКредит  



4—txtСрокКредита



5—txtСтавка



6 — txtДата1



7 — txtДата2



8— txtСуммаПлатежей



9— txtВозврат



10—cmdПечать



11 —cmdВычислить

 

12—cmdВыход





Проект формы







Графический образ формы после выполнения приложения 





Рис.3. Проект формы и внешний ее вид после выполнения программы


Код программы



Код программы, который обеспечивает выполнение указанного выше проекта, представлен на рис. 4.





Рис.4. Окно кода с процедурами обработки событий



Расчет суммы средств, подлежащей возврату банку, выполняется по щелчку на кнопке Вычислить определяется начисляемая за пользование кредитом сумма по процентам, которая суммируется  с величиной кредита, полученной клиентом банка по кредитному договору.

В текстовые поля txtДата1 (Дата выдачи кредита) и txtДата2 (Дата возврата кредита) информация вводится с помощью функции дат: Now и Dateadd соответственно. 

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

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

Результаты работы программы выдаются на печать с помощью команды PrintForm, которая выводит на принтер графический образ окна формы. 

Завершение работы программы осуществляется с помощью командной кнопки Выход.

На рис. 4 представлены процедуры, которые выполняют указанные выше действия. 



Проектирование приложения



		Если не открыто приложение Microsoft Excel, то откройте его командой Пуск — Программы — Microsoft Office — Microsoft Word или другим способом.

		Выполните команду Сервис — Макрос — Безопасность и установите следующие параметры безопасности:

			 Во вкладке Безопасность выберите переключатель Средняя.

			 Во вкладке Надежные изделия установите флажок Доверять доступ к Visual Basic Project (для версии 2003 и выше).

		Закройте окно приложения Microsoft Word, повторите п. 1 и проверьте, сохранились ли параметры безопасности, которые были установлены в п. 2.

		Находясь в среде Word или другого приложения из Microsoft Office (Excel или др.) откройте окно интегрированной среды разработки приложений редактора Visual Basic командой Сервис — Макрос — Редактор Visual Basic или нажатием комбинации клавиш <Alt>+<F11>.

	На панели инструментов щелкните кнопку Insert UserForm - . Появляется окно с формой UserForm1.

	Задайте имя и заголовок формы. Для этого перейдите в окно свойств Properties и введите значения следующих свойств:

	

	Name: FrmКредит

	Caption: Кредитование

	

	Примечание. В свойстве Caption студент указывает название работы.

	Поместите на форму надписи Кредитор: (введите наименование банка), Заёмщик (введите Ф. И. О.), Сумма кредита, Срок кредита, Процентная ставка, Сумма платежей по процентам,  Сумма возврата кредита с уплатой процентов,  Дата выдачи кредита, Дата возврата кредита в соответствующие поля (см. рис. 3). 

			Выберите на панели элементов управления элемент Label, а затем щелкните на форме в том месте, где будет начинаться надпись, и выделите область для надписи.

			Выделите на форме элемент Label1 щелчком левой кнопкой мыши, а затем в свойстве Name окна Properties введите текст lblКредитор, а в свойстве Caption, укажите «Кредитор: (введите наименование банка)».

			Справа от созданной надписи (см. рис. 2 и 3), поместите текстовое поле (TextBox). Выберите на панели элементов управления элемент TextBox, а затем щелкните на форме и выделите область для нового объекта. Выделите на форме элемент TextBox1, а затем в окне Properties введите значение свойства: Name - txtКредит.

	Аналогично создайте поля для размещения на форме надписей Заемщик (введите Ф. И. О.), Сумма кредита, Срок кредита, Процентная ставка, Сумма платежей по процентам, Сумма возврата кредита с уплатой процентов, Дата выдачи кредита, Дата возврата кредита и соответствующие им текстовые поля (имена полей см. в табл. 1).

	Создайте командную кнопку Печать формы.

			Выберите на панели элементов управления элемент CommandButton, а затем щелкните на форме в том месте, где будет начинаться кнопка – слева внизу формы, и удерживая нажатой левую кнопку мыши выделите для нее область (см. рис. 3). 

			Перейдите в окно Properties и введите значения  следующих свойств:

	Caption: 	 Печать формы

	Name:     	cmdПечать

	Аналогично создайте командную кнопки Выход и Вычислить (в Caption укажите Выход, а в Name – cmdВыход),  (в Caption укажите Вычислить, а в Name – cmdВычислить). Размещение кнопки на форме см. на рис. 2 и 3.

	Вызовите окно Code (кода) командой View – Code. Указанное окно может быть вызвано и другими способами, например с помощью контекстного меню.

	Создайте код процедуры, которая обрабатывает событие щелчок на кнопке  «cmdВычислить».

			Дважды щелкните на кнопке «cmdВычислить». В результате открывается окно кода с заголовком и концом процедуры. В тело процедуры введите оператор Dim для явного объявления семи переменных (См. табл. 2 и рис. 4).

Переменным curSumKredit и intSrok необходимо присвоить значения текстовых полей, которые были введены в форме. Для этих целей введите в тело процедуры два оператора

	

	curSumKredit = CCur(TxtКредит)

	intSrok = CInt(TxtСрокКредита)

	

	Для выполнения вычислений в тело процедуры введите следующие операторы 

		

		sngProcent = CSng(TxtСтавка) / intSrok / 100

		curPlatZa1mes = curSumKredit * sngProcent

		

		В первом операторе определяется процентная ставка за один месяц, во втором вычисляется сумма, начисленная банком по процентам за первый месяц

		

		curSumPlat = 0

		

		Далее в переменную для подсчета начислений по процентам за последующие месяцы засылается 0 для начала выполнения цикла.

		

		For i = 2 To intSrok

		curSumPlat = curSumPlat + (curSumKredit - curSumKredit / intSrok * (i - 1)) * sngProcent

		Next i

		

		Далее происходит выполнение цикла. 

		

		curSumPlatVsego = curSumPlat + curPlatZa1mes

		curSumVozvrat = curSumPlat + curSumKredit

		

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

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



		TxtСуммаПлатежей = Format(curSumPlatVsego, "0.00")

		TxtВозврат = Format(curSumVozvrat, "0.00")

		

	Для вычисления даты возврата кредита также в тело процедуры введите оператор:

	

	dtmData2 = DateAdd("m", intSrok, dtmData1)

	

	Примечание. Возврат кредита осуществляется через год после заключения кредитного договора.

	Создайте код процедуры, которая обрабатывает засылание в поле TxtДата1 текущую дату при активации формы. Дважды щелкните на форме TxtДата1. поменяйте название процедуры в верхнем правом окне на Activate. В тело процедуры введите оператор

	

	TxtДата1 = Now

	

	Примечание. Дата выдачи кредита вычисляется на момент выполнения программы.

	Создайте код процедуры, которая обрабатывает событие «Щелчок на командной кнопке Печать формы»

	Дважды щелкните на кнопке Печать формы. В тело процедуры введите оператор, который выведет на печать графический образ окна формы с полученными результатами:

	

	Me.PrintForm

	

	Создайте код процедуры, которая обрабатывает событие «Щелчок на командной кнопке Выход».

	Дважды щелкните на кнопке Выход. В тело процедуры введите операторы, которые закроют форму.

	

	Unload Me

	Set FrmКредит = Nothing

	

	Сохраните документ Word вместе с программой в своей папке (имя файла, например, может быть – Кредитование).

	Запустите программу на выполнение и проверьте ее работу с несколькими комплектами исходных данных.

	Завершите работу в приложении Microsoft Excel. Закройте окно Excel.

	Двойным щелчком на имени только что созданного файла откройте его. При открытии ответьте на запрос - отключать ли макросы, нажав кнопку Не отключать макросы, а затем перейдите в окно интегрированной среды разработки приложений редактора Visual Basic и убедитесь, что в нем имеется форма UserForm и у вас имеется возможность снова запустить созданную вами программу Visual Basic.

	
Литература



		С Браун Visual Basic 6.0. Учебный курс. -  СПб: Питер, 2005.

		Верб Дж. Программирование в Excel 2003:тетрадь разработчика. Пер. с англ. – М.: КУДИЦ-ОБРАЗ, 2006 – 304 с.

		Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб. БХВ-Петербург, 2001. – 816 с.

		Джексон, Мзри, Стонтон, Майк. Финансовое моделирование в ЕхсеІ и VBA: углубленный курс.: Пер. с англ. – М.: Издательский дом "Вильямс", 2006. – 352 с.

		Додж М., Стинсон К. Ефективна робота з Microsoft Excel 2003. – Спб: Питер, 2005. – 1056 с.

		Долженков В. А., Колесников Ю. В.Самоучитель Microsoft Excel 2003. – СПб.: БХВ-Петербург, 2005. – 432 с.

		Лабораторний практикум по информатике и компьютерным технологиям. /Под ред. проф. А.И. Пушкаря. - X.: Издат. Дом "ИНЖЗК", 2004. - 468 с.

		Олійник А.В., Шацька В.М. Інформаційні системи і технології у фінансових установах: Навчальний посібник. – Львів: "Новий Світ-2000", 2006. – 436 с.

		Современные компьютерные технологии. /Под ред. проф. А.И. Пушкаря. - X.: Издат. дом "ИНЖЗК", 2004. – 464 с.

		Райтингер М., Муч Г. Visual Basic 6.0: Пер. с нем. – К.: Издательская группа BHV, 1999, - 720 с.

		Основи алгоритмізації та програмування/ Федько В. В., Плоткін В. І. – Харків: Веста: Видавництво «Ранок», 2003. – 192 с.

		Федько В.В. Лабораторный практикум по VBA. Электронный учебник. Фонды ХНЭУ, 2006 – 568 кб.