Questa pagina vi insegna come creare un semplice calcolatore di prestito in Excel VBA. Il foglio di lavoro contiene i seguenti controlli ActiveX: due barre di scorrimento e due pulsanti di opzione.

Loan Calculator in Excel VBA

Nota: le istruzioni riportate di seguito non insegnano come formattare il foglio di lavoro. Partiamo dal presupposto che si sa come cambiare tipi di font, inserire righe e colonne, bordi aggiungere, modificare i colori di sfondo, ecc

Eseguire le seguenti operazioni per creare il calcolatore di prestito:

  1. Aggiungere i due controlli barra di scorrimento. Fare clic su Inserisci dalla scheda Developer e quindi fare clic sulla barra di scorrimento nella sezione Controlli ActiveX.

Create Scroll Bars

  1. Aggiungere i due pulsanti di opzione. Fare clic su Inserisci dalla scheda Developer e quindi fare clic sul pulsante Opzioni nella sezione Controlli ActiveX.

Create Option Buttons

Modificare le seguenti proprietà dei controlli barra di scorrimento (assicurarsi Design Mode è selezionato).

  1. Fare clic destro del mouse sul primo controllo barra di scorrimento, e quindi fare clic su Proprietà. Set Min a 0, massima a 20, SmallChange a 0 e LargeChange a 2.

  2. Fare clic destro del mouse sul secondo controllo barra di scorrimento, e quindi fare clic su Proprietà. Set Min a 5, massima a 30, SmallChange a 1, LargeChange a 5, e LinkedCell a F8.

Spiegazione: quando si fa clic sulla freccia, il valore barra di scorrimento sale o scende dal SmallChange. Quando si sceglie tra il cursore e la freccia, il valore barra di scorrimento sale o scende dal LargeChange.

Creare un evento di modifica del foglio di lavoro. Codice aggiunto all’evento Change foglio di lavoro sarà eseguito da Excel VBA quando si modifica una cella di un foglio di lavoro.

  1. Aprire il Visual Basic Editor.

  2. Fare doppio clic su Sheet1 (Foglio1) in Esplora progetti.

  3. Scegliere foglio di lavoro dall’elenco a discesa sinistra e scegli Modifica dall’elenco a discesa a destra.

Worksheet Change Event in Excel VBA

  1. L’evento di modifica ascolta tutte le modifiche di Sheet1. Noi vogliamo solo Excel VBA per eseguire il Calcola sub se qualcosa cambia nella cella D4.

Per ottenere questo, aggiungere la seguente riga di codice per l’evento Change foglio di lavoro (più il sub Calcola in seguito).

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

  1. Ottenere la percentuale nel F6 cellulare (cambiare il formato dei F6 cella percentuale). Tasto destro del mouse sul primo controllo barra di scorrimento, e quindi fare clic su Visualizza codice. Aggiungere le seguenti righe di codice:

Private Sub ScrollBar1_Change()

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

Application.Run "Calculate"

End Sub

  1. destro del mouse cliccare sul secondo controllo barra di scorrimento, e quindi fare clic su Visualizza codice. Aggiungere la seguente riga di codice:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

  1. tasto destro del mouse sul primo controllo pulsante di opzione e quindi fare clic su Visualizza codice. Aggiungere la seguente riga di codice:

Private Sub OptionButton1_Click()

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

Application.Run "Calculate"

End Sub

  1. tasto destro del mouse sul secondo controllo pulsante di opzione e quindi fare clic su Visualizza codice. Aggiungere la seguente riga di codice:

Private Sub OptionButton2_Click()

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

Application.Run "Calculate"

End Sub

  1. Tempo per creare il sub. Si può passare attraverso la nostra funzione e sub capitolo per saperne di più su Subs. Se siete di fretta, è sufficiente posizionare il sub di nome Calcola in un modulo (in Visual Basic Editor, fare clic su Inserisci, 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

Spiegazione: il sub ottiene i giusti parametri per la funzione del foglio di lavoro Pmt. La funzione di PMT in Excel calcola i pagamenti per un prestito sulla base di pagamenti costanti e un tasso di interesse costante. Se si effettua i pagamenti mensili (Sheet1.OptionButton1.Value = true), Excel VBA utilizza tasso / 12 per votare e nper * 12 per NUM.RATE (numero totale di pagamenti). Il risultato è un numero negativo, perché i pagamenti sono considerati un debito. Moltiplicando il risultato per -1 dà esito positivo.

Loan Calculator Result