Эта страница научит вас, как создать простой калькулятор кредита в Excel VBA. Рабочий лист содержит следующие элементы управления ActiveX: две полосы прокрутки и две дополнительные кнопки.

Loan Calculator in Excel VBA

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

Выполните следующие шаги, чтобы создать калькулятор кредита:

  1. Добавьте два элемента управления ScrollBar. Нажмите на Insert на вкладке Developer, а затем нажмите на Scroll Bar в разделе Элементы управления ActiveX.

Create Scroll Bars

  1. Добавьте две дополнительные кнопки. Нажмите на Insert на вкладке Developer, а затем нажмите на кнопку Option в разделе Элементы управления ActiveX.

Create Option Buttons

Измените следующие свойства элементов управления ScrollBar (убедитесь, что выбран режим Design).

  1. Щелкните правой кнопкой мыши на первый элемент управления полосы прокрутки, а затем нажмите кнопку Свойства. Set Min 0, Max 20, SmallChange до 0 и LargeChange до 2.

  2. Щелкните правой кнопкой мыши на второй полосы прокрутки, а затем нажмите кнопку Свойства. Set Min 5, Max 30, SmallChange 1, LargeChange до 5, и LinkedCell до F8.

Объяснение: при нажатии на стрелку значение полосы прокрутки идет вверх или вниз по SmallChange. При нажатии кнопки между ползунком и стрелкой, значение полосы прокрутки идет вверх или вниз по LargeChange.

Создание рабочего листа Изменить событие. Код добавлен в Worksheet Change Event будет выполняться Excel VBA при изменении ячейки на листе.

  1. Откройте редактор Visual Basic.

  2. Дважды щелкните на Sheet1 (Лист1) в окне Project Explorer.

  3. Выберите рабочий лист из левого выпадающего списка и выберите Изменить справа в раскрывающемся списке.

Worksheet Change Event in Excel VBA

  1. Рабочий лист Change Event прослушивает все изменения на Лист1. Мы хотим только Excel VBA для запуска суб Calculate, если что-то изменится в ячейке D4.

Для этого добавьте следующую строку кода в рабочий лист Change Event (больше о суб Calculate позже).

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

  1. Получить правильный процент в ячейке F6 (изменить формат ячейки F6 в процентах). Щелкните правой кнопкой мыши на первый элемент управления полосы прокрутки, а затем нажмите View Code. Добавьте следующие строки кода:

Private Sub ScrollBar1_Change()

Range("F6").Value = ScrollBar1.Value / 100

Application.Run "Calculate"

End Sub

  1. Правая кнопка мыши нажмите на второй полосе прокрутки, а затем нажмите View Code. Добавьте следующую строку кода:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

  1. Правой кнопкой мыши щелкните на первой кнопке управления опцией, а затем нажмите View Code. Добавьте следующую строку кода:

Private Sub OptionButton1_Click()

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

Application.Run "Calculate"

End Sub

  1. Правой кнопкой мыши нажмите на второй кнопки управления опции, а затем нажмите View Code. Добавьте следующую строку кода:

Private Sub OptionButton2_Click()

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

Application.Run "Calculate"

End Sub

  1. Время создания суб. Вы можете пройти через нашу функцию и к югу от главы более узнать о подлодке. Если вы спешите, просто поместите подлодку с именем Calculate в модуль (в редакторе Visual Basic, выберите Вставка, модуль).

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 в Excel вычисляет платежи по ссуде на основе постоянных выплат и постоянной процентной ставки. Если вы делаете ежемесячные платежи (Sheet1.OptionButton1.Value = True), Excel VBA использует скорость / 12 для скорости и кпер * 12 для КПЕРОВ (общее количество платежей). В результате отрицательное число, потому что платежи считаются дебетом. Умножив результат на -1 дает положительный результат.

Loan Calculator Result