/ / Калькулятор позики в Excel VBA

Калькулятор позики в Excel VBA

Ця сторінка вчить вас, як створити простий калькулятор кредиту в Excel VBA. Робочий аркуш містить наступні елементи керування ActiveX: дві смуги прокрутки та дві кнопки вибору.

Калькулятор позики в Excel VBA

Примітка. Наведені нижче інструкції не навчають вас, як форматувати робочий аркуш. Ми припускаємо, що ви знаєте, як змінювати типи шрифтів, вставляти рядки та стовпці, додавати кордони, змінювати фонові кольори тощо.

Виконайте наступні кроки для створення кредитного калькулятора:

1. Додайте два елементи керування прокруткою. Натисніть вкладку "Вставити" на вкладці "Розробник", а потім клацніть на панелі прокрутки в розділі "Елементи керування ActiveX".

Створіть смуги прокрутки

2. Додайте дві кнопки вибору. Натисніть вкладку "Вставити" на вкладці "Розробник", а потім натисніть кнопку "Параметри" у розділі "Елементи керування ActiveX".

Створити кнопки вибору

Змініть наступні властивості елементів керування прокруткою (переконайтеся, що вибрано режим проектування).

3. Клацніть правою кнопкою миші на першому елементі керування прокруткою, а потім клацніть на властивостях. Встановіть мінімум 0, максимум 20, SmallChange до 0 і LargeChange до 2.

4. Клацніть правою кнопкою миші на другому елементі керування прокруткою, а потім клацніть на властивостях. Встановіть мінімальне значення для 5, максимум до 30, SmallChange до 1, LargeChange до 5 і LinkedCell для F8.

Пояснення: коли ви натискаєте стрілку, значення прокрутки збільшується або зменшується за допомогою SmallChange. Коли ви натискаєте між повзунком та стрілкою, величина смуги прокручування збільшується або зменшується за допомогою LargeChange.

Створіть подію робочого листа. Код, доданий до події Зміни робочого аркуша, буде виконаний Excel VBA, коли ви зміните клітинку на робочому аркуші.

5. Відкрийте редактор Visual Basic.

6. Двічі клацніть на Sheet1 (Sheet1) в Project Explorer.

7. Виберіть Worksheet у спадному списку ліворуч та оберіть Change у спадному списку праворуч.

Робочий лист змінити події в Excel VBA

8 Подія "Змінити таблицю" слухає всі зміни на Sheet1. Ми хочемо, щоб програма Excel VBA запускала підрахунок "Розрахувати", якщо щось змінюється в комірці D4. Для цього додати наступну кодову рядок до події зміни робочого аркуша (докладніше про параметр «Обчислити підрахунок пізніше»).

If Target.Address = "$D$4" Then Application.Run "Calculate"

9. Отримати правильний відсоток у комірці F6 (змінити формат комірки F6 у відсотках). Клацніть правою кнопкою миші на першому елементі керування прокруткою, а потім натисніть кнопку Перегляд коду. Додайте наступні кодові рядки:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100
Application.Run "Calculate"

End Sub

10. Клацніть правою кнопкою миші на другому елементі керування прокруткою, а потім натисніть кнопку Переглянути код. Додайте наступну кодову лінію:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Клацніть правою кнопкою миші на елементі керування кнопкою першого вибору та натисніть кнопку Переглянути код. Додайте наступну кодову лінію:

Private Sub OptionButton1_Click()

If OptionButton1.Value = True Then Range("C12").Value = "Monthly Payment"
Application.Run "Calculate"

End Sub

12. Правою кнопкою миші клацніть елемент керування другим вибором, а потім натисніть кнопку Перегляд коду. Додайте наступну кодову лінію:

Private Sub OptionButton2_Click()

If OptionButton2.Value = True Then Range("C12").Value = "Yearly Payment"
Application.Run "Calculate"

End Sub

13. Час створення підрозділу. Ви можете прочитати розділ "Функція" та "Підрозділ", щоб дізнатись більше про підпис. Якщо ви поспішаєте, просто помістіть підменю Calculate в модуль (у редакторі Visual Basic натисніть на Insert, Module).

Sub Calculate()

Dim loan As Long, rate As Double, nper As Integer

loan = Range("D4").Value
rate = Range("F6").Value
nper = Range("F8").Value

If Sheet1.OptionButton1.Value = True Then
    rate = rate / 12
    nper = nper * 12
End If

Range("D12").Value = -1 * WorksheetFunction.Pmt(rate, nper, loan)

End Sub

Пояснення: суб отримує правильні параметри для робочого аркуша функції Pmt. Функція Pmt в Excel розраховує платежі за кредитом на основі постійних платежів та постійної процентної ставки. Якщо Ви здійснюєте щомісячні платежі (Sheet1.OptionButton1.Value = True), Excel VBA використовує курс / 12 для курсу та nper * 12 для nper (загальна кількість платежів). Результат - негативне число, оскільки платежі вважаються дебетовими. Помноження результату на -1 дає позитивний результат.

Результат калькулятора позик

Також читайте: