/ / Calcolatore di prestiti in VBA di Excel

Calcolatore di prestiti in VBA di Excel

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

Calcolatore di prestiti in VBA di Excel

Nota: le istruzioni seguenti non ti insegnano come formattare il foglio di lavoro. Supponiamo che tu sappia come cambiare tipo di carattere, inserire righe e colonne, aggiungere bordi, cambiare i colori di sfondo, ecc.

Esegui i seguenti passaggi per creare il calcolatore del prestito:

1. Aggiungi i due controlli della barra di scorrimento. Fare clic su Inserisci dalla scheda Sviluppatore e quindi fare clic su Barra di scorrimento nella sezione Controlli ActiveX.

Crea barre di scorrimento

2. Aggiungi i due pulsanti di opzione. Fare clic su Inserisci dalla scheda Sviluppatore e quindi fare clic sul pulsante Opzione nella sezione Controlli ActiveX.

Crea pulsanti di opzione

Modificare le seguenti proprietà dei controlli della barra di scorrimento (assicurarsi che la Modalità progettazione sia selezionata).

3. Fare clic con il pulsante destro del mouse sul primo controllo della barra di scorrimento, quindi fare clic su Proprietà. Impostare Min su 0, Max su 20, SmallChange su 0 e LargeChange su 2.

4. Fare clic con il pulsante destro del mouse sul secondo controllo della barra di scorrimento, quindi fare clic su Proprietà. Impostare Min a 5, Max a 30, SmallChange a 1, LargeChange a 5 e LinkedCell a F8.

Spiegazione: quando si fa clic sulla freccia, il valore della barra di scorrimento aumenta o diminuisce di SmallChange. Quando fai clic tra il cursore e la freccia, il valore della barra di scorrimento aumenta o diminuisce di LargeChange.

Creare un evento di modifica del foglio di lavoro. Il codice aggiunto all'Evento di modifica del foglio di lavoro verrà eseguito da VBA di Excel quando si modifica una cella in un foglio di lavoro.

5. Aprire il Visual Basic Editor.

6. Fare doppio clic su Sheet1 (Sheet1) in Project Explorer.

7. Seleziona Foglio di lavoro dall'elenco a discesa a sinistra e scegli Cambia dall'elenco a discesa a destra.

Evento di modifica del foglio di lavoro in VBA di Excel

8. L'evento di modifica del foglio di lavoro ascolta tutte le modifiche su Sheet1. VBA di Excel viene utilizzato solo per eseguire il sub Calcolo se qualcosa cambia nella cella D4. Per ottenere ciò, aggiungere la seguente riga di codice all'Evento di modifica del foglio di lavoro (ulteriori informazioni sul sottoprogramma Calcola in seguito).

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

9. Ottieni la giusta percentuale nella cella F6 (modifica il formato della cella F6 in percentuale). Fai clic con il pulsante destro del mouse sul primo controllo della barra di scorrimento, quindi fai clic su Visualizza codice. Aggiungi le seguenti linee di codice:

Private Sub ScrollBar1_Change()

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

End Sub

10. Fare clic con il pulsante destro del mouse sul secondo controllo della barra di scorrimento, quindi fare clic su Visualizza codice. Aggiungi la seguente riga di codice:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Fare clic con il pulsante destro del mouse sul primo controllo pulsante di opzione, quindi fare clic su Visualizza codice. Aggiungi la seguente riga di codice:

Private Sub OptionButton1_Click()

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

End Sub

12. Fare clic con il pulsante destro del mouse sul secondo pulsante di controllo opzione, quindi fare clic su Visualizza codice. Aggiungi la seguente riga di codice:

Private Sub OptionButton2_Click()

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

End Sub

13. Tempo per creare il sub. È possibile consultare il capitolo Funzione e Sottotitolo per ulteriori informazioni sui sottotitoli. Se si ha fretta, è sufficiente posizionare il sottotitolo denominato Calculate in un modulo (nel Visual Basic Editor, fare clic su Inserisci, Modulo).

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 parametri corretti per la funzione di foglio di lavoro Pmt. La funzione Pmt in Excel calcola i pagamenti per un prestito basato su pagamenti costanti e un tasso di interesse costante. Se si effettuano pagamenti mensili (Sheet1.OptionButton1.Value = True), VBA di Excel utilizza rate / 12 per rate e nper * 12 per nper (numero totale di pagamenti). Il risultato è un numero negativo, perché i pagamenti sono considerati un debito. Moltiplicando il risultato per -1 si ottiene un risultato positivo.

Risultato del Calcolo del prestito

Leggi anche: