Πώς να χρησιμοποιήσετε τις λειτουργίες φύλλου εργασίας VBA στο Excel 2016 - ανδρείκελα

Αν και η VBA προσφέρει μια αξιοπρεπή ποικιλία ενσωματωμένων λειτουργιών, μπορεί να μην βρίσκετε πάντα ακριβώς αυτό που χρειάζεστε. Ευτυχώς, μπορείτε επίσης να χρησιμοποιήσετε τις περισσότερες λειτουργίες του φύλλου εργασίας του Excel στις διαδικασίες VBA. Το μόνο φύλλο εργασίας που δεν μπορείτε να χρησιμοποιήσετε είναι αυτά που έχουν μια ισοδύναμη λειτουργία VBA. Για παράδειγμα, δεν μπορείτε να χρησιμοποιήσετε τη λειτουργία RAND του Excel (η οποία παράγει έναν τυχαίο αριθμό) επειδή η VBA έχει ισοδύναμη λειτουργία: Rnd.

Η VBA κάνει τις λειτουργίες του φύλλου εργασίας του Excel διαθέσιμες μέσω του αντικειμένου WorkheetFunction, το οποίο περιέχεται στο αντικείμενο Application. Ακολουθεί ένα παράδειγμα για το πώς μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUM του Excel σε μια δήλωση VBA:

Total = Application. Φύλλο εργασίας. SUM (Εύρος ("A1: A12"))

Μπορείτε να παραλείψετε είτε το τμήμα της εφαρμογής είτε το τμήμα του φύλλου εργασίας της έκφρασης. Και στις δύο περιπτώσεις, η VBA υπολογίζει τι κάνετε. Με άλλα λόγια, αυτές οι τρεις εκφράσεις λειτουργούν ακριβώς το ίδιο:

Σύνολο = Εφαρμογή. Φύλλο εργασίας. SUM (Εύρος ("A1: A12")) Σύνολο = Φύλλο εργασίας. SUM (Εύρος ("A1: A12")) Σύνολο = Εφαρμογή. SUM (Εύρος ("A1: A12"))

Η προσωπική μου προτίμηση είναι να χρησιμοποιήσω το τμήμα WorksheetFunction μόνο για να καταστεί απολύτως σαφές ότι ο κώδικας χρησιμοποιεί μια λειτουργία Excel.

Παραδείγματα λειτουργίας φύλλου εργασίας

Εδώ, ανακαλύπτετε τον τρόπο χρήσης των λειτουργιών φύλλου εργασίας στις εκφράσεις VBA.

Εύρεση της μέγιστης τιμής σε μια περιοχή

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

Χρησιμοποιώντας τη λειτουργία φύλλου εργασίας στον κώδικα VBA.

Sub ShowMax () Dim TheMax Ως διπλό TheMax = Φύλλο εργασίας. MAX (Εύρος ("A: A")) MsgBox TheMax End Sub

Μπορείτε να χρησιμοποιήσετε τη λειτουργία MIN για να έχετε τη μικρότερη τιμή σε ένα εύρος. Και όπως αναμένετε, μπορείτε να χρησιμοποιήσετε άλλες λειτουργίες του φύλλου εργασίας με παρόμοιο τρόπο. Για παράδειγμα, μπορείτε να χρησιμοποιήσετε τη λειτουργία LARGE για να προσδιορίσετε τη μεγαλύτερη τιμή k η μεγαλύτερη σε εύρος τιμών. Η ακόλουθη έκφραση επιδεικνύει το εξής:

SecondHighest = WorkheetFunction. LARGE (Εύρος ("A: A"), 2)

Παρατηρήστε ότι η λειτουργία LARGE χρησιμοποιεί δύο επιχειρήματα. Το δεύτερο επιχείρημα αντιπροσωπεύει το k το δεύτερο μέρος - 2, στην περίπτωση αυτή (η δεύτερη μεγαλύτερη τιμή).

Υπολογισμός πληρωμής υποθηκών

Το επόμενο παράδειγμα χρησιμοποιεί τη λειτουργία του φύλλου εργασίας PMT για τον υπολογισμό της πληρωμής υποθηκών. Χρησιμοποιούνται τρεις μεταβλητές για την αποθήκευση των δεδομένων που μεταβιβάστηκαν στη συνάρτηση Pmt ως επιχειρήματα.Ένα πλαίσιο μηνύματος εμφανίζει την υπολογισμένη πληρωμή.

Sub PmtCalc () Dim IntRate ως Διπλό Δάνειο Δώρου Ως Διπλές Διημίες Διαστήματος Όσο Μακρά IntRate = 0. 0625/12 Περίοδοι = 30 * 12 LoanAmt = 150000 MsgBox WorkheetFunction. PMT (IntRate, Περίοδοι, -LoanAmt) End Sub

Όπως δείχνει η ακόλουθη πρόταση, μπορείτε επίσης να εισαγάγετε τις τιμές απευθείας ως επιχειρήματα λειτουργιών:

MsgBox WorkheetFunction. PMT (0.0625 / 12, 360, -150000)

Ωστόσο, η χρήση μεταβλητών για την αποθήκευση των παραμέτρων καθιστά τον κώδικα ευκολότερο για ανάγνωση και τροποποίηση, εάν είναι απαραίτητο.

Χρησιμοποιώντας μια λειτουργία αναζήτησης

Το ακόλουθο παράδειγμα χρησιμοποιεί τις λειτουργίες InputBox και MsgBox της VBA καθώς και τη λειτουργία VLOOKUP του Excel. Προτρέπει για έναν αριθμό εξαρτήματος και στη συνέχεια παίρνει την τιμή από έναν πίνακα αναζήτησης. Παρακάτω, το εύρος A1: B13 ονομάζεται PriceList.

Η σειρά, με την ονομασία PriceList, περιέχει τιμές για εξαρτήματα.

Sub GetPrice () Dim PartNum ως τιμή εναλλαγής ως Διπλό PartNum = InputBox ("Εισάγετε τον Αριθμό Κωδικού") Φύλλα ("Τιμές"). Ενεργοποίηση τιμής = Λειτουργία φύλλου εργασίας. VCOOK (PartNum, Range ("PriceList"), 2, False) MsgBox PartNum & "κόστος" & End End Sub

Ο αριθμός στοιχείου που εισάγει ο χρήστης αντιστοιχεί στη μεταβλητή PartNum.
Η επόμενη δήλωση ενεργοποιεί το φύλλο εργασίας Τιμές, μόνο σε περίπτωση που δεν είναι ήδη το ενεργό φύλλο.
Ο κώδικας χρησιμοποιεί τη λειτουργία VLOOKUP για να βρει τον αριθμό του εξαρτήματος στον πίνακα.
Παρατηρήστε ότι τα επιχειρήματα που χρησιμοποιείτε σε αυτήν τη δήλωση είναι τα ίδια με αυτά που θα χρησιμοποιούσατε με τη συνάρτηση σε έναν τύπο φύλλου εργασίας. Αυτή η δήλωση αντιστοιχεί το αποτέλεσμα της συνάρτησης στη μεταβλητή Τιμή.
Ο κώδικας εμφανίζει την τιμή για το τμήμα μέσω της λειτουργίας MsgBox.
Αυτή η διαδικασία δεν αντιμετωπίζει σφάλματα και αποτυγχάνει άθλια αν εισάγετε έναν ανύπαρκτο αριθμό εξαρτήματος. (Δοκιμάστε το.) Εάν αυτή ήταν μια πραγματική εφαρμογή που χρησιμοποιείται σε μια πραγματική επιχείρηση, θα θέλατε να προσθέσετε μερικές δηλώσεις που αντιμετωπίζουν τα λάθη πιο χαριτωμένα.

Εισαγωγή λειτουργιών φύλλου εργασίας

Δεν μπορείτε να χρησιμοποιήσετε το παράθυρο διαλόγου Λειτουργία επικόλλησης Excel για να εισαγάγετε μια λειτουργία φύλλου εργασίας σε μια ενότητα VBA. Αντ 'αυτού, εισάγετε τέτοιες λειτουργίες με τον παλιομοδίτικο τρόπο: με το χέρι. Ωστόσο, μπορείτε

να να χρησιμοποιήσετε το παράθυρο διαλόγου Λειτουργία επικόλλησης για να προσδιορίσετε τη λειτουργία που θέλετε να χρησιμοποιήσετε και να μάθετε τα επιχειρήματά της. Μπορείτε επίσης να επωφεληθείτε από την επιλογή Auto Members List της VBE, η οποία εμφανίζει μια αναπτυσσόμενη λίστα όλων των λειτουργιών του φύλλου εργασίας. Απλώς πληκτρολογήστε

Εφαρμογή. Φύλλο εργασίας , ακολουθούμενο από μια περίοδο. Στη συνέχεια, θα δείτε μια λίστα με τις λειτουργίες που μπορείτε να χρησιμοποιήσετε. Εάν αυτή η λειτουργία δεν λειτουργεί, επιλέξτε την εντολή Εργαλεία → Επιλογές του VBE, κάντε κλικ στην καρτέλα Επεξεργαστής και τοποθετήστε έναν έλεγχο δίπλα στα Μέλη αυτόματης λίστας. Λήψη λίστας λειτουργιών φύλλου εργασίας που μπορείτε να χρησιμοποιήσετε στον κώδικα VBA.

Περισσότερες πληροφορίες σχετικά με τη χρήση λειτουργιών φύλλου εργασίας

Οι νεοεισερχόμενοι στο VBA συχνά συγχέουν τις ενσωματωμένες λειτουργίες του VBA και τις λειτουργίες του βιβλίου εργασίας του Excel. Ένας καλός κανόνας που πρέπει να θυμάστε είναι ότι η VBA δεν προσπαθεί να ανακαλύψει τον τροχό.Ως επί το πλείστον, η VBA δεν αναπαράγει τις λειτουργίες του φύλλου εργασίας του Excel.

Για τις περισσότερες λειτουργίες του φύλλου εργασίας που δεν είναι διαθέσιμες ως μέθοδοι του αντικειμένου WorkheetFunction, μπορείτε να χρησιμοποιήσετε έναν ισοδύναμο VBA ενσωματωμένο χειριστή ή λειτουργία. Για παράδειγμα, η λειτουργία φύλλου εργασίας MOD δεν είναι διαθέσιμη στο αντικείμενο WorksheetFunction επειδή το VBA έχει ένα ισοδύναμο: τον ενσωματωμένο χειριστή Mod.

Κάτω γραμμή; Αν πρέπει να χρησιμοποιήσετε μια λειτουργία, πρέπει πρώτα να προσδιορίσετε αν η VBA έχει κάτι που να ανταποκρίνεται στις ανάγκες σας. Εάν όχι, ελέγξτε τις λειτουργίες του φύλλου εργασίας. Εάν όλα τα άλλα αποτύχουν, ίσως μπορείτε να γράψετε μια προσαρμοσμένη λειτουργία χρησιμοποιώντας το VBA.