Υπολογισμός δανείου στο Excel VBA

Αυτή η σελίδα σας διδάσκει πώς να δημιουργήσετε ένα απλό υπολογιστής δανείου σε Excel VBA. Το φύλλο εργασίας περιέχει τα ακόλουθα στοιχεία ελέγχου ActiveX: δύο κύλισης και δύο κουμπιά επιλογής.

Σημείωση: Οι παρακάτω οδηγίες δεν σας διδάσκουν πώς να μορφοποιήσετε το φύλλο εργασίας. Υποθέτουμε ότι γνωρίζετε πώς να αλλάξετε τύπους γραμματοσειρών, να εισαγάγετε γραμμές και στήλες, να προσθέτετε σύνορα, να αλλάζετε χρώματα φόντου κ.λπ.

Εκτελέστε τα παρακάτω βήματα για να δημιουργήσετε τον υπολογισμό δανείου:

1. Προσθέστε τα δύο στοιχεία ελέγχου της γραμμής κύλισης. Κάντε κλικ στην επιλογή Εισαγωγή από την καρτέλα Προγραμματιστής και, στη συνέχεια, κάντε κλικ στην κύλιση στη γραμμή ελέγχου ActiveX.

2. Προσθέστε τα δύο κουμπιά επιλογών. Κάντε κλικ στο "Εισαγωγή" από την καρτέλα "Προγραμματιστής" και στη συνέχεια κάντε κλικ στο κουμπί "Επιλογή" στην ενότητα "Στοιχεία ελέγχου ActiveX".

Αλλάξτε τις ακόλουθες ιδιότητες των στοιχείων ελέγχου της γραμμής κύλισης (βεβαιωθείτε ότι έχετε επιλέξει τη λειτουργία σχεδίασης).

3. Κάντε δεξί κλικ με το ποντίκι στον πρώτο πίνακα ελέγχου κύλισης και, στη συνέχεια, κάντε κλικ στην επιλογή Ιδιότητες. Ρυθμίστε τα Min στο 0, το Max στο 20, το SmallChange στο 0 και το LargeChange στο 2.

4. Κάντε δεξιό κλικ με το ποντίκι στον δεύτερο πίνακα ελέγχου κύλισης και, στη συνέχεια, κάντε κλικ στην επιλογή Ιδιότητες. Ορίστε το Min στο 5, το Μέγιστο έως το 30, το SmallChange στο 1, το ΜεγάλοChange στο 5 και το LinkedCell στο F8.

Επεξήγηση: όταν κάνετε κλικ στο βέλος, η τιμή της γραμμής κύλισης ανεβαίνει προς τα πάνω ή προς τα κάτω από το SmallChange. Όταν κάνετε κλικ μεταξύ του ρυθμιστικού και του βέλους, η τιμή της γραμμής κύλισης ανεβαίνει προς τα επάνω ή προς τα κάτω από το LargeChange.

Δημιουργία συμβάντος αλλαγής φύλλου εργασίας. Ο κώδικας που προστέθηκε στο συμβάν αλλαγής φύλλου εργασίας θα εκτελεστεί από το Excel VBA όταν αλλάζετε ένα κελί σε ένα φύλλο εργασίας.

5. Ανοίξτε τον επεξεργαστή της Visual Basic.

6. Κάντε διπλό κλικ στο Sheet1 (Sheet1) στο Explorer του Έργου.

7. Επιλέξτε το φύλλο εργασίας από την αριστερή αναπτυσσόμενη λίστα και επιλέξτε Αλλαγή από τη δεξιά αναπτυσσόμενη λίστα.

8. Το συμβάν αλλαγής φύλλου εργασίας ακούει όλες τις αλλαγές στο Φύλλο1. Θέλουμε μόνο το Excel VBA να εκτελέσει το Calculate sub εάν κάτι αλλάζει στο κελί D4. Για να το επιτύχετε, προσθέστε την ακόλουθη γραμμή κώδικα στο συμβάν αλλαγής φύλλου εργασίας (περισσότερες πληροφορίες σχετικά με το υποσύνολο υπολογισμού αργότερα).

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

9. Αποκτήστε το σωστό ποσοστό στο κελί F6 (αλλάξτε τη μορφή του στοιχείου F6 σε ποσοστό). Κάντε δεξί κλικ με το ποντίκι στον πρώτο πίνακα ελέγχου κύλισης και, στη συνέχεια, κάντε κλικ στην επιλογή Προβολή κώδικα. Προσθέστε τις ακόλουθες γραμμές κώδικα:

Private Sub ScrollBar1_Change()

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

End Sub

10. Κάντε δεξί κλικ με το ποντίκι στη δεύτερη μπάρα κύλισης και, στη συνέχεια, κάντε κλικ στην επιλογή Προβολή κώδικα. Προσθέστε την ακόλουθη γραμμή κώδικα:

Private Sub ScrollBar2_Change()

Application.Run "Calculate"

End Sub

11. Κάντε δεξί κλικ στο κουμπί επιλογής του πρώτου κουμπιού επιλογής και, στη συνέχεια, κάντε κλικ στην επιλογή Προβολή κωδικού. Προσθέστε την ακόλουθη γραμμή κώδικα:

Private Sub OptionButton1_Click()

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

End Sub

12. Κάντε δεξιό κλικ με το ποντίκι στο δεύτερο κουμπί ελέγχου επιλογής και, στη συνέχεια, κάντε κλικ στην επιλογή Προβολή κώδικα. Προσθέστε την ακόλουθη γραμμή κώδικα:

Private Sub OptionButton2_Click()

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

End Sub

13. Ώρα για να δημιουργήσετε το δευτερεύον. Μπορείτε να περάσετε από το κεφάλαιο "Λειτουργία" και "Υπο-κεφάλαιο" για να μάθετε περισσότερα σχετικά με τα υποσυστήματα Εάν βιάζεστε, απλώς τοποθετήστε το υπομενού Υπολογισμός σε μια ενότητα (Στο πρόγραμμα επεξεργασίας της 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

Εξήγηση: το sub λαμβάνει τις σωστές παραμέτρους για τη λειτουργία φύλλου εργασίας Pmt. Η συνάρτηση Pmt στο Excel υπολογίζει τις πληρωμές για ένα δάνειο βάσει σταθερών πληρωμών και σταθερού επιτοκίου. Εάν κάνετε μηνιαίες πληρωμές (Sheet1.OptionButton1.Value = True), το Excel VBA χρησιμοποιεί ποσοστό / 12 για το ποσοστό και nper * 12 για nper (συνολικός αριθμός πληρωμών). Το αποτέλεσμα είναι ένας αρνητικός αριθμός, επειδή οι πληρωμές θεωρούνται χρέωση. Ο πολλαπλασιασμός του αποτελέσματος κατά -1 δίνει ένα θετικό αποτέλεσμα.