Cette page vous apprend comment créer une simple calculatrice 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.

Loan Calculator in Excel VBA

Note: les instructions ci-dessous ne vous apprend pas comment formater la feuille de calcul. Nous partons du principe que vous savez comment changer les types de polices, insérer des lignes et des colonnes, bordures ajouter, modifier les couleurs de fond, etc.

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

  1. Ajoutez les deux commandes de la barre de défilement. Cliquez sur Insérer dans l’onglet Developer, puis cliquez sur la barre de défilement dans la section Contrôles ActiveX.

Create Scroll Bars

  1. Ajoutez les deux boutons d’option. Cliquez sur Insérer dans l’onglet Developer, puis cliquez sur l’option Bouton dans la section Contrôles ActiveX.

Create Option Buttons

Modifiez les propriétés suivantes des contrôles scrollbar (assurez-vous que le mode Création est sélectionné).

  1. Cliquez droit de la souris sur la première commande de la barre de défilement, puis cliquez sur Propriétés. Ens à 0, Max à 20, SmallChange à 0 et LargeChange à 2.

  2. Cliquez droit de la souris sur le deuxième contrôle de la barre de défilement, puis cliquez sur Propriétés. Ens à 5, Max à 30, SmallChange à 1, LargeChange à 5 et LinkedCell à F8.

Explication: lorsque vous cliquez sur la flèche, la valeur de la barre de défilement monte ou vers le bas par SmallChange. Lorsque vous cliquez entre le curseur et la flèche, la valeur de la barre de défilement monte ou vers le bas par LargeChange.

Créer une feuille de calcul Modifier l’événement. Code ajouté à la feuille de changement d’événement sera exécuté par Excel VBA lorsque vous modifiez une cellule sur une feuille de calcul.

  1. Ouvrez Visual Basic Editor.

  2. Double-cliquez sur Sheet1 (Sheet1) dans le projet Explorer.

  3. Choisissez la feuille de travail dans la liste déroulante de gauche et choisissez Modifier de la droite liste déroulante.

Worksheet Change Event in Excel VBA

  1. La feuille de changement d’événement écoute tous les changements sur Sheet1. Nous voulons seulement Excel VBA pour exécuter le sous Calculer si quelque chose change dans la cellule D4.

Pour ce faire, ajoutez la ligne de code suivant à la feuille de travail Modifier l’événement (plus sur la sous Calculer plus tard).

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

  1. Obtenez le pourcentage droit à F6 cellulaire (changer le format de cellule F6 à pourcentage). clic droit 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

  1. Clic droit sur la deuxième commande de la barre de défilement, puis cliquez sur Afficher le code. Ajoutez la ligne de code suivant:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

  1. Clic droit sur le premier contrôle de bouton d’option, puis cliquez sur Afficher le code. Ajoutez la ligne de code suivant:

Private Sub OptionButton1_Click()

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

Application.Run "Calculate"

End Sub

  1. Clic droit sur la deuxième commande de bouton d’option, puis cliquez sur Afficher le code. Ajoutez la ligne de code suivant:

Private Sub OptionButton2_Click()

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

Application.Run "Calculate"

End Sub

  1. Il est temps de créer le sous. Vous pouvez passer par notre fonction et sous chapitre pour en savoir plus sur les sous-marins. Si vous êtes pressé, il suffit de placer le sous le nom Calculer dans un module (Dans l’éditeur Visual Basic, 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 VPM dans Excel calcule les paiements pour un prêt basé sur des paiements constants et un taux d’intérêt constant. Si vous effectuez des paiements mensuels (Sheet1.OptionButton1.Value = true), Excel VBA utilise le taux / 12 pour le taux et NPM * 12 pour NPM (nombre total de paiements). Le résultat est un nombre négatif, car les paiements sont considérés comme un débit. Multipliant le résultat par -1 donne un résultat positif.

Loan Calculator Result