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

Υπάρχουν πολλοί τρόποι χρήσης των λειτουργιών στο VBA. Το VBA έρχεται φορτωμένο με πολλές ενσωματωμένες λειτουργίες. Μπορείτε ακόμη να δημιουργήσετε τις δικές σας λειτουργίες (UDF). Ωστόσο, μπορείτε επίσης να χρησιμοποιήσετε πολλές από τις λειτουργίες του Excel στο VBA χρησιμοποιώντας το Application.WorksheetFunction.

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

Για πρόσβαση σε μια συνάρτηση Excel στο VBA προσθέστε Application.WorksheetFunction μπροστά από τη λειτουργία που θέλετε να καλέσετε. Στο παρακάτω παράδειγμα, θα καλέσουμε τη μέγιστη συνάρτηση του Excel:

12	Χαμηλή μέγιστη τιμή όσο καιρόmaxvalue = Application.WorksheetFunction.Max (Εύρος ("a1"). Τιμή, Εύρος ("a2"). Τιμή)

Η σύνταξη των συναρτήσεων είναι η ίδια, ωστόσο θα εισαγάγετε τα ορίσματα της συνάρτησης όπως θα κάνατε με οποιαδήποτε άλλη συνάρτηση VBA.

Παρατηρήστε ότι η σύνταξη της λειτουργίας Max εμφανίζεται όταν πληκτρολογείτε (παρόμοια με τις λειτουργίες VBA):

Φύλλο εργασίας Μέθοδος λειτουργίας

Το WorksheetFunction είναι μια μέθοδος αντικειμένου εφαρμογής. Σας επιτρέπει να έχετε πρόσβαση σε πολλές (όχι όλες) τις τυπικές λειτουργίες του φύλλου εργασίας του Excel. Γενικά, δεν θα έχετε πρόσβαση σε οποιεσδήποτε λειτουργίες φύλλου εργασίας που έχουν αντίστοιχη έκδοση VBA.

Μπορείτε να δείτε μια λίστα με πολλές από τις πιο κοινές λειτουργίες φύλλου εργασίας παρακάτω.

Application.WorksheetFunction vs Application

Υπάρχουν δύο τρόποι πρόσβασης σε αυτές τις λειτουργίες:

Application.WorksheetFunction (όπως φαίνεται παραπάνω):

1	maxvalue = Application.WorksheetFunction.Max (Εύρος ("a1"). Τιμή, Εύρος ("a2"). Τιμή)

ή μπορείτε να παραλείψετε τη Λειτουργία φύλλου εργασίας

1	maxvalue = Application.Max (Range ("a1"). Value, Range ("a2"). Value)

Δυστυχώς, η παράλειψη του WorksheetFunction θα εξαλείψει το Intellisense που εμφανίζει τη σύνταξη (δείτε την παραπάνω εικόνα). Ωστόσο, έχει ένα μεγάλο πιθανό πλεονέκτημα: Χειρισμός σφαλμάτων.

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

Ας δούμε ένα παράδειγμα για να δούμε τη διαφορά:

Χειρισμός σφαλμάτων συνάρτησης Vlookup WorksheetFunction

Θα προσπαθήσουμε να εκτελέσουμε ένα Vlookup που δεν θα έχει ως αποτέλεσμα έναν αγώνα. Έτσι, η συνάρτηση Vlookup θα επιστρέψει ένα σφάλμα.

Αρχικά, θα χρησιμοποιήσουμε τη μέθοδο WorksheetFunction. Παρατηρήστε πώς το VBA ρίχνει ένα σφάλμα:

Στη συνέχεια παραλείπουμε τη Λειτουργία φύλλου εργασίας. Παρατηρήστε πώς το

Στη συνέχεια θα παραλείψουμε το WorksheetFunction. Παρατηρήστε πώς δεν ρίχνεται κανένα σφάλμα και αντ 'αυτού η συνάρτηση "τιμή" περιέχει την τιμή σφάλματος από το Vlookup.

Λίστα λειτουργιών φύλλου εργασίας VBA

Παρακάτω θα βρείτε μια λίστα με τις περισσότερες κοινές λειτουργίες φύλλου εργασίας VBA.

Λειτουργία	Περιγραφή
Λογικός
ΚΑΙ	Ελέγχει εάν πληρούνται όλες οι προϋποθέσεις. ΣΩΣΤΟ ΛΑΘΟΣ
ΑΝ	Εάν πληρούνται οι προϋποθέσεις, κάντε κάτι, αν όχι, κάντε κάτι άλλο.
IFERROR	Εάν το αποτέλεσμα είναι σφάλμα, κάντε κάτι άλλο.
Ή	Ελέγχει εάν πληρούνται οι όροι. ΣΩΣΤΟ ΛΑΘΟΣ
Αναζήτηση και αναφορά
ΕΠΙΛΕΓΩ	Επιλέγει μια τιμή από μια λίστα με βάση τον αριθμό θέσης της.
HLOOKUP	Αναζητήστε μια τιμή στην πρώτη σειρά και επιστρέψτε μια τιμή.
ΔΕΙΚΤΗΣ	Επιστρέφει μια τιμή με βάση τους αριθμούς στηλών και σειρών.
ΨΑΧΝΩ	Αναζητά τιμές είτε οριζόντια είτε κάθετα.
ΑΓΩΝΑΣ	Αναζητά μια τιμή σε μια λίστα και επιστρέφει τη θέση της.
ΜΕΤΑΘΕΤΩ	Αναστρέφει τον προσανατολισμό μιας σειράς κελιών.
VLOOKUP	Αναζητήστε μια τιμή στην πρώτη στήλη και επιστρέψτε μια τιμή.
Ημερομηνία ώρα
ΗΜΕΡΟΜΗΝΙΑ	Επιστρέφει μια ημερομηνία από έτος, μήνα και ημέρα.
DATEVALUE	Μετατρέπει μια ημερομηνία που είναι αποθηκευμένη ως κείμενο σε έγκυρη ημερομηνία
ΗΜΕΡΑ	Επιστρέφει την ημέρα ως αριθμό (1-31).
DAYS360	Επιστρέφει ημέρες μεταξύ 2 ημερομηνιών σε έτος 360 ημερών.
ΕΚΠΑΙΔΕΥΣΗ	Επιστρέφει μια ημερομηνία, n μήνες μακριά από την ημερομηνία έναρξης.
ΕΟΜΟΝΘΟΣ	Επιστρέφει την τελευταία ημέρα του μήνα, n μήνες πριν από την ημερομηνία.
ΩΡΑ	Επιστρέφει την ώρα ως αριθμό (0-23).
ΛΕΠΤΟ	Επιστρέφει το λεπτό ως αριθμός (0-59).
ΜΗΝΑΣ	Επιστρέφει τον μήνα ως αριθμό (1-12).
ΔΙΚΤΥΕΣ ΗΜΕΡΕΣ	Αριθμός εργάσιμων ημερών μεταξύ 2 ημερομηνιών.
NETWORKDAYS.INTL	Εργάσιμες ημέρες μεταξύ 2 ημερομηνιών, προσαρμοσμένα Σαββατοκύριακα.
ΤΩΡΑ	Επιστρέφει την τρέχουσα ημερομηνία και ώρα.
ΔΕΥΤΕΡΟΣ	Επιστρέφει το δεύτερο ως αριθμό (0-59)
ΧΡΟΝΟΣ	Επιστρέφει το χρόνο από μία ώρα, λεπτό και δευτερόλεπτο.
ΧΡΟΝΟΣ	Μετατρέπει τον χρόνο που είναι αποθηκευμένος ως κείμενο σε έγκυρο χρόνο.
ΚΑΘΗΜΕΡΙΝΗ	Επιστρέφει την ημέρα της εβδομάδας ως αριθμό (1-7).
ΕΒΔΟΜΑΔΙΑ	Επιστρέφει τον αριθμό της εβδομάδας σε ένα έτος (1-52).
ΗΜΕΡΑ ΕΡΓΑΣΙΑΣ	Η ημερομηνία n εργάσιμες ημέρες από μια ημερομηνία.
ΕΤΟΣ	Επιστρέφει το έτος.
YEARFRAC	Επιστρέφει το κλάσμα του έτους μεταξύ 2 ημερομηνιών.
Μηχανική
ΜΕΤΑΤΡΕΠΩ	Μετατρέψτε τον αριθμό από τη μία μονάδα στην άλλη.
Χρηματοοικονομική
FV	Υπολογίζει τη μελλοντική τιμή.
ΦΒ	Υπολογίζει την παρούσα τιμή.
NPER	Υπολογίζει το συνολικό αριθμό των περιόδων πληρωμής.
PMT	Υπολογίζει το ποσό πληρωμής.
ΤΙΜΗ	Υπολογίζει το Επιτόκιο.
NPV	Υπολογίζει την καθαρή παρούσα αξία.
IRR	Ο εσωτερικός ρυθμός απόδοσης για ένα σύνολο περιοδικών CF.
XIRR	Ο εσωτερικός ρυθμός απόδοσης για ένα σύνολο μη περιοδικών CF.
ΤΙΜΗ	Υπολογίζει την τιμή ενός ομολόγου.
ΕΙΣΑΓΩΓΙΚΟ	Το επιτόκιο ενός πλήρως επενδυμένου τίτλου.
Πληροφορίες
ISERR	Ελέγξτε εάν η τιμή κελιού είναι σφάλμα, αγνοεί το #N/A. ΣΩΣΤΟ ΛΑΘΟΣ
ISERROR	Ελέγξτε εάν η τιμή κελιού είναι σφάλμα. ΣΩΣΤΟ ΛΑΘΟΣ
ISEVEN	Δοκιμάστε αν η τιμή κελιού είναι άρτια. ΣΩΣΤΟ ΛΑΘΟΣ
ΙΣΛΟΓΙΚΟ	Δοκιμάστε αν το κελί είναι λογικό (ΑΛΗΘΙΝΟ ή ΛΑΘΟΣ). ΣΩΣΤΟ ΛΑΘΟΣ
ISNA	Ελέγξτε αν η τιμή κελιού είναι #N/A. ΣΩΣΤΟ ΛΑΘΟΣ
ISNONTEXT	Δοκιμάστε αν το κελί δεν είναι κείμενο (τα κενά κελιά δεν είναι κείμενο). ΣΩΣΤΟ ΛΑΘΟΣ
ΑΡΙΘΜΟΣ	Δοκιμάστε αν το κελί είναι ένας αριθμός. ΣΩΣΤΟ ΛΑΘΟΣ
ISODD	Ελέγξτε αν η τιμή κελιού είναι περιττή. ΣΩΣΤΟ ΛΑΘΟΣ
ISTEXT	Δοκιμάστε αν το κελί είναι κείμενο. ΣΩΣΤΟ ΛΑΘΟΣ
ΤΥΠΟΣ	Επιστρέφει τον τύπο της τιμής σε ένα κελί.
Μαθηματικά
ABS	Υπολογίζει την απόλυτη τιμή ενός αριθμού.
ΣΥΝΟΛΟ	Ορίστε και εκτελέστε υπολογισμούς για μια βάση δεδομένων ή μια λίστα.
ΟΡΟΦΗ	Στρογγυλοποιεί έναν αριθμό προς τα πάνω, στο πλησιέστερο καθορισμένο πολλαπλάσιο.
COS	Επιστρέφει το συνημίτονο μιας γωνίας.
ΠΤΥΧΙΑ	Μετατρέπει ακτίνια σε μοίρες.
DSUM	Συγκεντρώνει εγγραφές βάσης δεδομένων που πληρούν ορισμένα κριτήρια.
ΑΚΟΜΗ ΚΑΙ	Στρογγυλοποιείται στον πλησιέστερο ακέραιο.
ΛΗΞΗ	Υπολογίζει την εκθετική τιμή για έναν δεδομένο αριθμό.
ΓΕΓΟΝΟΣ	Επιστρέφει το παραγοντικό.
ΠΑΤΩΜΑ	Στρογγυλοποιεί έναν αριθμό προς τα κάτω, στο πλησιέστερο καθορισμένο πολλαπλάσιο.
GCD	Επιστρέφει τον μεγαλύτερο κοινό διαιρέτη.
INT	Στρογγυλοποιεί έναν αριθμό στον πλησιέστερο ακέραιο.
LCM	Επιστρέφει το λιγότερο κοινό πολλαπλάσιο.
LN	Επιστρέφει τον φυσικό λογάριθμο ενός αριθμού.
ΚΟΥΤΣΟΥΡΟ	Επιστρέφει τον λογάριθμο ενός αριθμού σε μια καθορισμένη βάση.
LOG10	Επιστρέφει τον λογάριθμο βάσης-10 ενός αριθμού.
MROUND	Στρογγυλοποιεί έναν αριθμό σε ένα καθορισμένο πολλαπλάσιο.
ΠΕΡΙΤΤΟΣ	Στρογγυλοποιείται στον πλησιέστερο περιττό ακέραιο.
πι	Η τιμή του PI.
ΕΞΟΥΣΙΑ	Υπολογίζει έναν αριθμό που αυξάνεται σε μια ισχύ.
ΠΡΟΪΟΝ	Πολλαπλασιάζει έναν πίνακα αριθμών.
ΠΗΛΙΚΟ	Επιστρέφει το ακέραιο αποτέλεσμα της διαίρεσης.
ΡΑΔΙΑΝΤΕΣ	Μετατρέπει μια γωνία σε ακτίνια.
RANDBETWEEN	Υπολογίζει έναν τυχαίο αριθμό μεταξύ δύο αριθμών.
ΓΥΡΟΣ	Στρογγυλοποιεί έναν αριθμό σε έναν καθορισμένο αριθμό ψηφίων.
ΣΤΡΟΓΓΥΛΟΠΟΙΗΣΗ ΠΡΟΣ ΤΑ ΚΑΤΩ	Στρογγυλοποιεί έναν αριθμό προς τα κάτω (προς το μηδέν).
ΜΑΝΔΡΙΣΜΑ ΖΩΩΝ	Στρογγυλοποιεί έναν αριθμό προς τα πάνω (μακριά από το μηδέν).
ΑΜΑΡΤΙΑ	Επιστρέφει το ημίτονο μιας γωνίας.
ΜΕΡΙΚΟ ΣΥΝΟΛΟ	Επιστρέφει μια συνοπτική στατιστική για μια σειρά δεδομένων.
ΑΘΡΟΙΣΜΑ	Προσθέτει αριθμούς μαζί.
SUMIF	Αθροίζει αριθμούς που πληρούν κριτήρια.
ΑΡΙΣΤΟΙ	Αθροίζει αριθμούς που πληρούν πολλαπλά κριτήρια.
SUMPRODUCT	Πολλαπλασιάζει πίνακες αριθμών και αθροίζει τον πίνακα που προκύπτει.
ΗΛΙΟΚΑΜΕΝΟΣ	Επιστρέφει την εφαπτομένη μιας γωνίας.
Στατιστικά
ΜΕΣΗ ΤΙΜΗ	Μέσους αριθμούς.
AVERAGEIF	Μέσος όρος αριθμών που πληρούν κριτήρια.
ΜΕΣΟΤΕΡΑ	Μέσος όρος αριθμών που πληρούν πολλαπλά κριτήρια.
ΚΟΡΕΛ	Υπολογίζει το συσχετισμό δύο σειρών.
ΜΕΤΡΩ	Μετράει κελιά που περιέχουν έναν αριθμό.
ΚΟΥΝΤΑ	Μετρήστε τα κελιά που δεν είναι κενά.
ΛΟΓΙΣΜΙΚΟΣ ΤΡΑΠΕΖΟΣ	Μετράει τα κελιά που είναι κενά.
COUNTIF	Μετράει κελιά που πληρούν κριτήρια.
COUNTIFS	Μετράει κελιά που πληρούν πολλαπλά κριτήρια.
ΠΡΟΒΛΕΨΗ	Προβλέψτε μελλοντικές τιμές y από γραμμική γραμμή τάσης.
ΣΥΧΝΟΤΗΤΑ	Μετράει τιμές που εμπίπτουν σε καθορισμένα εύρη.
ΑΝΑΠΤΥΞΗ	Υπολογίζει τις τιμές Υ με βάση την εκθετική ανάπτυξη.
ΑΝΑΧΑΙΤΙΖΩ	Υπολογίζει την ανάσχεση Υ για μια γραμμή που ταιριάζει καλύτερα.
ΜΕΓΑΛΟ	Επιστρέφει τη μεγαλύτερη τιμή k.
LINEST	Επιστρέφει στατιστικά στοιχεία για μια γραμμή τάσης.
ΜΕΓΙΣΤΗ	Επιστρέφει τον μεγαλύτερο αριθμό.
ΔΙΑΜΕΣΟΣ	Επιστρέφει τον διάμεσο αριθμό.
ΜΙΝ	Επιστρέφει τον μικρότερο αριθμό.
ΤΡΟΠΟΣ	Επιστρέφει τον πιο κοινό αριθμό.
ΠΕΡΙΣΤΕΡΙΛΙΚΟ	Επιστρέφει το εκατοστημόριο kth.
PERCENTILE.INC	Επιστρέφει το εκατοστημόριο kth. Όπου το k περιλαμβάνει.
PERCENTILE.EXC	Επιστρέφει το εκατοστημόριο kth. Όπου το k είναι αποκλειστικό.
ΤΕΤΑΡΤΙΚΟ	Επιστρέφει την καθορισμένη τεταρτημοριακή τιμή.
QUARTILE.INC	Επιστρέφει την καθορισμένη τεταρτημοριακή τιμή. Περιεκτικός.
QUARTILE.EXC	Επιστρέφει την καθορισμένη τεταρτημοριακή τιμή. Αποκλειστικός.
ΤΑΞΗ	Κατάταξη ενός αριθμού μέσα σε μια σειρά.
RANK.AVG	Κατάταξη ενός αριθμού μέσα σε μια σειρά. Μέσοι όροι
RANK.EQ	Κατάταξη ενός αριθμού μέσα σε μια σειρά. Κορυφαία κατάταξη.
ΚΛΙΣΗ	Υπολογίζει την κλίση από γραμμική παλινδρόμηση.
ΜΙΚΡΟ	Επιστρέφει τη μικρότερη τιμή kth.
STDEV	Υπολογίζει την τυπική απόκλιση.
STDEV.P	Υπολογίζει το SD ενός ολόκληρου πληθυσμού.
STDEV.S	Υπολογίζει το SD ενός δείγματος.
STDEVP	Υπολογίζει το SD ενός ολόκληρου πληθυσμού
ΤΑΣΗ	Υπολογίζει τις τιμές Υ βάσει γραμμής τάσης.
Κείμενο
ΚΑΘΑΡΗ	Αφαιρεί όλους τους μη εκτυπώσιμους χαρακτήρες.
ΔΟΛΑΡΙΟ	Μετατρέπει έναν αριθμό σε κείμενο σε μορφή νομίσματος.
ΕΥΡΗΜΑ	Εντοπίζει τη θέση του κειμένου μέσα σε ένα κελί. Με διάκριση πεζών-κεφαλαίων.
ΑΡΙΣΤΕΡΑ	Περικοπεί κείμενο σε αριθμό χαρακτήρων από τα αριστερά.
LEN	Μετράει αριθμό χαρακτήρων στο κείμενο.
ΣΤΑ ΜΕΣΑ	Εξάγει κείμενο από τη μέση ενός κελιού.
ΚΑΤΑΛΛΗΛΟΣ	Μετατρέπει το κείμενο σε κατάλληλη περίπτωση.
ΑΝΤΙΚΑΘΙΣΤΩ	Αντικαθιστά το κείμενο με βάση την τοποθεσία του.
REPT	Επαναλαμβάνει το κείμενο αρκετές φορές.
ΣΩΣΤΑ	Περικοπεί κείμενο σε αριθμό χαρακτήρων από τα δεξιά.
ΑΝΑΖΗΤΗΣΗ	Εντοπίζει τη θέση του κειμένου μέσα σε ένα κελί. Δεν έχει διάκριση πεζών-κεφαλαίων.
ΥΠΟΚΑΤΑΣΤΑΤΟ	Βρίσκει και αντικαθιστά κείμενο. Με διάκριση πεζών-κεφαλαίων.
ΚΕΙΜΕΝΟ	Μετατρέπει μια τιμή σε κείμενο με συγκεκριμένη μορφή αριθμού.
ΤΑΚΤΟΠΟΙΗΣΗ	Αφαιρεί όλα τα επιπλέον κενά από το κείμενο.