/ / Calculateur de prêt dans Excel VBA

Calculateur de prêt dans Excel VBA

Cette page vous apprend à créer un simple calculateur de prêt dans Excel VBA. La feuille de calcul contient les contrôles ActiveX suivants: deux barres de défilement et deux boutons d'option.

Calculateur de prêt dans Excel VBA

Remarque: les instructions ci-dessous ne vous expliquent pas comment formater la feuille de calcul. Nous supposons que vous savez comment changer les types de police, insérer des lignes et des colonnes, ajouter des bordures, changer les couleurs d’arrière-plan, etc.

Exécutez les étapes suivantes pour créer la calculatrice de prêt:

1. Ajoutez les deux contrôles de la barre de défilement. Cliquez sur Insérer dans l'onglet Développeur, puis sur Barre de défilement dans la section Contrôles ActiveX.

Créer des barres de défilement

2. Ajoutez les deux boutons d'option. Cliquez sur Insérer dans l'onglet Développeur, puis sur Bouton d'option dans la section Contrôles ActiveX.

Créer des boutons d'option

Modifiez les propriétés suivantes des contrôles de la barre de défilement (assurez-vous que le mode Conception est sélectionné).

3. Cliquez avec le bouton droit de la souris sur le premier contrôle de la barre de défilement, puis sur Propriétés. Définissez Min sur 0, Max sur 20, SmallChange sur 0 et LargeChange sur 2.

4. Cliquez avec le bouton droit de la souris sur le deuxième contrôle de la barre de défilement, puis sur Propriétés. Définissez Min sur 5, Max sur 30, SmallChange sur 1, LargeChange sur 5 et LinkedCell sur F8.

Explication: lorsque vous cliquez sur la flèche, la valeur de la barre de défilement augmente ou diminue de SmallChange. Lorsque vous cliquez entre le curseur et la flèche, la valeur de la barre de défilement augmente ou diminue de GrandChange.

Créez un événement de changement de feuille de calcul. Le code ajouté à l'événement Modification de la feuille de calcul sera exécuté par Excel VBA lorsque vous modifiez une cellule dans une feuille de calcul.

5. Ouvrez Visual Basic Editor.

6. Double-cliquez sur Sheet1 (Sheet1) dans l'Explorateur de projets.

7. Choisissez Feuille de calcul dans la liste déroulante de gauche, puis choisissez Modifier dans la liste déroulante de droite.

Événement de changement de feuille de calcul dans Excel VBA

8 L'événement de changement de feuille de calcul écoute toutes les modifications de la feuille Sheet1. Nous voulons uniquement qu'Excel VBA exécute le calculateur secondaire si quelque chose change dans la cellule D4. Pour ce faire, ajoutez la ligne de code suivante à l'événement de changement de feuille de calcul (pour en savoir plus sur le sous-calcul Calculer plus tard).

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

9. Obtenez le bon pourcentage dans la cellule F6 (modifiez le format de la cellule F6 en pourcentage). Cliquez avec le bouton droit de la souris sur le premier contrôle de la barre de défilement, puis cliquez sur Afficher le code. Ajoutez les lignes de code suivantes:

Private Sub ScrollBar1_Change()

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

End Sub

10. Cliquez avec le bouton droit de la souris sur le deuxième contrôle de la barre de défilement, puis sur Afficher le code. Ajoutez la ligne de code suivante:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Cliquez avec le bouton droit de la souris sur le premier bouton de commande, puis sur Afficher le code. Ajoutez la ligne de code suivante:

Private Sub OptionButton1_Click()

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

End Sub

12. Cliquez avec le bouton droit de la souris sur le deuxième bouton de contrôle, puis sur Afficher le code. Ajoutez la ligne de code suivante:

Private Sub OptionButton2_Click()

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

End Sub

13. Il est temps de créer le sous-marin. Vous pouvez consulter notre chapitre Fonction et Sous-chapitre pour en savoir plus sur les sous-programmes. Si vous êtes pressé, placez simplement le sous-nom Calculer dans un module (dans Visual Basic Editor, cliquez sur Insérer, 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

Explication: le sous obtient les bons paramètres pour la fonction de feuille de calcul Pmt. La fonction Pmt dans Excel calcule les paiements d'un prêt sur la base de paiements constants et d'un taux d'intérêt constant. Si vous effectuez des paiements mensuels (Sheet1.OptionButton1.Value = True), Excel VBA utilise rate / 12 pour rate et nper * 12 pour nper (nombre total de paiements). Le résultat est un nombre négatif, car les paiements sont considérés comme un débit. Multiplier le résultat par -1 donne un résultat positif.

Résultat de la calculatrice de prêt

Lisez aussi: