Σε αυτό το σεμινάριο θα μάθουμε για τη λειτουργία Excel VBA
1) Τι είναι η Visual Basic στο Excel;
2) Πώς να χρησιμοποιήσετε το VBA στο Excel;
3) Πώς να δημιουργήσετε τη συνάρτηση που ορίζεται από το χρήστη;
4) Πώς να γράψετε μακροεντολή;
Πώς να γράψετε κώδικα VBA
Το Excel παρέχει στον χρήστη μια μεγάλη συλλογή έτοιμων λειτουργιών, αρκετή για να ικανοποιήσει τον μέσο χρήστη. Πολλά άλλα μπορούν να προστεθούν εγκαθιστώντας τα διάφορα πρόσθετα που είναι διαθέσιμα. Οι περισσότεροι υπολογισμοί μπορούν να επιτευχθούν με αυτό που παρέχεται, αλλά δεν αργεί να βρεθείτε να επιθυμείτε να υπήρχε μια λειτουργία που έκανε μια συγκεκριμένη δουλειά και δεν μπορείτε να βρείτε κάτι κατάλληλο στη λίστα. Χρειάζεστε ένα UDF. Ένα UDF (User Defined Function) είναι απλά μια συνάρτηση που δημιουργείτε μόνοι σας με VBA. Τα UDF ονομάζονται συχνά "Προσαρμοσμένες Λειτουργίες". Ένα UDF μπορεί να παραμείνει σε μια ενότητα κώδικα που είναι προσαρτημένη σε ένα βιβλίο εργασίας, οπότε θα είναι πάντα διαθέσιμο όταν το βιβλίο εργασίας είναι ανοιχτό. Εναλλακτικά, μπορείτε να δημιουργήσετε το δικό σας πρόσθετο που περιέχει μία ή περισσότερες λειτουργίες που μπορείτε να εγκαταστήσετε στο Excel, ακριβώς όπως ένα εμπορικό πρόσθετο. Μπορείτε επίσης να έχετε πρόσβαση στα UDF με μονάδες κώδικα. Συχνά τα UDF δημιουργούνται από προγραμματιστές για να λειτουργούν αποκλειστικά μέσα στον κώδικα μιας διαδικασίας VBA και ο χρήστης δεν γνωρίζει ποτέ την ύπαρξή τους. Όπως κάθε συνάρτηση, το UDF μπορεί να είναι τόσο απλό ή όσο περίπλοκο θέλετε. Ας ξεκινήσουμε με ένα εύκολο…
Μια συνάρτηση για τον υπολογισμό της επιφάνειας ενός ορθογωνίου
Ναι, ξέρω ότι μπορείτε να το κάνετε αυτό στο μυαλό σας! Η ιδέα είναι πολύ απλή, ώστε να μπορείτε να επικεντρωθείτε στην τεχνική. Ας υποθέσουμε ότι χρειάζεστε μια συνάρτηση για να υπολογίσετε το εμβαδόν ενός ορθογωνίου. Κοιτάζετε τη συλλογή λειτουργιών του Excel, αλλά δεν υπάρχει κατάλληλη. Αυτός είναι ο υπολογισμός που πρέπει να γίνει:
ΠΕΡΙΟΧΗ = ΜΗΚΟΣ x ΠΛΑΤΟΣ
Ανοίξτε ένα νέο βιβλίο εργασίας και, στη συνέχεια, ανοίξτε το Visual Basic Editor (Εργαλεία> Μακροεντολή> Visual Basic Editor ή ALT+F11).
Θα χρειαστείτε μια ενότητα στην οποία θα γράψετε τη λειτουργία σας, οπότε επιλέξτε Εισαγωγή> ΕνότηταΤο Στον τύπο κενής μονάδας: Περιοχή Λειτουργίας και πατήστε ΕΙΣΑΓΩ.Ο Visual Basic Editor συμπληρώνει τη γραμμή για εσάς και προσθέτει μια γραμμή End Function σαν να δημιουργείτε μια υπορουτίνα. Μέχρι στιγμής μοιάζει με αυτό…
Περιοχή συνάρτησης () Λειτουργία λήξης
Τοποθετήστε τον κέρσορα ανάμεσα στις αγκύλες μετά την "Περιοχή". Αν αναρωτηθήκατε ποτέ για τι χρησιμεύουν οι αγκύλες, πρόκειται να το μάθετε! Θα καθορίσουμε τα "ορίσματα" που θα λάβει η λειτουργία μας (ένα διαφωνία είναι μια πληροφορία που απαιτείται για τον υπολογισμό). Τύπος Μήκος διπλό, Πλάτος διπλό και κάντε κλικ στην κενή γραμμή από κάτω. Λάβετε υπόψη ότι καθώς πληκτρολογείτε, εμφανίζεται ένα πλαίσιο κύλισης με όλα τα πράγματα που είναι κατάλληλα για αυτό που πληκτρολογείτε.
Αυτή η δυνατότητα ονομάζεται Αυτόματη λίστα μελώνΤο Εάν δεν εμφανίζεται είτε είναι απενεργοποιημένο (ενεργοποιήστε το στο Εργαλεία> Επιλογές> Επεξεργαστής) ή μπορεί να έχετε κάνει λάθος πληκτρολόγησης νωρίτερα. Είναι ένας πολύ χρήσιμος έλεγχος στη σύνταξή σας. Βρείτε το στοιχείο που χρειάζεστε και κάντε διπλό κλικ για να το εισαγάγετε στον κωδικό σας. Μπορείτε να το αγνοήσετε και να πληκτρολογήσετε αν θέλετε. Ο κωδικός σας μοιάζει τώρα…
Περιοχή συνάρτησης (μήκος ως διπλό, πλάτος ως διπλό) Λειτουργία τέλους
Η δήλωση του τύπου δεδομένων των επιχειρημάτων δεν είναι υποχρεωτική αλλά έχει νόημα. Θα μπορούσατε να πληκτρολογήσετε Μήκος πλάτος και το άφησε έτσι, αλλά η προειδοποίηση του Excel για τον τύπο δεδομένων που πρέπει να περιμένετε βοηθά τον κώδικά σας να τρέχει πιο γρήγορα και λαμβάνει σφάλματα στην εισαγωγή. ο διπλό ο τύπος δεδομένων αναφέρεται στον αριθμό (ο οποίος μπορεί να είναι πολύ μεγάλος) και επιτρέπει κλάσματα. Τώρα για τον ίδιο τον υπολογισμό. Στην κενή γραμμή, πατήστε πρώτα το ΑΥΤΙ κλειδί για εσοχή στον κώδικα (διευκολύνοντας την ανάγνωσή του) και πληκτρολογήστε Περιοχή = Μήκος * Πλάτος. Ακολουθεί ο ολοκληρωμένος κωδικός…
Περιοχή συνάρτησης (μήκος ως διπλό, πλάτος ως διπλό) Περιοχή = Μήκος * Πλάτος Λειτουργία τέλους
Θα παρατηρήσετε μια άλλη από τις δυνατότητες βοήθειας της Visual Basic Editor να εμφανίζεται καθώς πληκτρολογείτε, Αυτόματες γρήγορες πληροφορίες…
Δεν είναι σχετικό εδώ. Ο σκοπός του είναι να σας βοηθήσει να γράψετε λειτουργίες στο VBA, λέγοντάς σας ποια επιχειρήματα απαιτούνται. Μπορείτε να δοκιμάσετε τη λειτουργία σας αμέσως. Μεταβείτε στο παράθυρο του Excel και εισαγάγετε αριθμούς για μήκος και πλάτος σε ξεχωριστά κελιά. Σε ένα τρίτο κελί εισάγετε τη λειτουργία σας σαν να ήταν μία από τις ενσωματωμένες. Σε αυτό το παράδειγμα το κελί Α1 περιέχει το μήκος (17) και το κελί Β1 το πλάτος (6.5). Στο Γ1 πληκτρολόγησα = περιοχή (Α1, Β1) και η νέα συνάρτηση υπολόγισε την περιοχή (110,5)…
Μερικές φορές, τα ορίσματα μιας συνάρτησης μπορεί να είναι προαιρετικά. Σε αυτό το παράδειγμα θα μπορούσαμε να κάνουμε το Πλάτος επιχείρημα προαιρετικό. Ας υποθέσουμε ότι το ορθογώνιο είναι τετράγωνο με μήκος και πλάτος ίσο. Για να αποθηκεύσετε τον χρήστη που πρέπει να εισαγάγει δύο ορίσματα, θα μπορούσαμε να τον αφήσουμε να εισαγάγει μόνο το μήκος και να χρησιμοποιήσουμε τη συνάρτηση αυτή την τιμή δύο φορές (δηλαδή πολλαπλασιάστε το μήκος x μήκος). Έτσι η συνάρτηση γνωρίζει πότε μπορεί να το κάνει αυτό πρέπει να συμπεριλάβουμε ένα IF Δήλωση για να το βοηθήσει να αποφασίσει. Αλλάξτε τον κωδικό έτσι ώστε να μοιάζει με αυτό…
Περιοχή συναρτήσεων (Μήκος ως διπλό, Προαιρετικό πλάτος ως παραλλαγή) Αν χάσετε (Πλάτος) Τότε Περιοχή = Μήκος * Μήκος Άλλη Περιοχή = Μήκος * Πλάτος Τέλος Αν τελική συνάρτηση
Λάβετε υπόψη ότι ο τύπος δεδομένων για το Πλάτος έχει αλλάξει σε Παραλαγή για να επιτρέπονται οι μηδενικές τιμές. Η συνάρτηση επιτρέπει τώρα στο χρήστη να εισάγει μόνο ένα όρισμα π.χ. = περιοχή (Α1).Η δήλωση IF στη συνάρτηση ελέγχει αν έχει παρασχεθεί το όρισμα Πλάτος και υπολογίζει ανάλογα…
Μια λειτουργία για τον υπολογισμό της κατανάλωσης καυσίμου
Μου αρέσει να ελέγχω την κατανάλωση καυσίμου του αυτοκινήτου μου, οπότε όταν αγοράζω καύσιμα σημειώνω τα χιλιόμετρα και πόσο καύσιμο χρειάζεται για να γεμίσει το ρεζερβουάρ. Εδώ στο Ηνωμένο Βασίλειο το καύσιμο πωλείται σε λίτρα. Το χιλιόμετρο του αυτοκινήτου (ΟΚ, άρα είναι χιλιόμετρο) καταγράφει την απόσταση σε μίλια. Και επειδή είμαι πολύ μεγάλος και ηλίθιος για να αλλάξω, καταλαβαίνω μόνο MPG (μίλια ανά γαλόνι). Τώρα αν νομίζετε ότι είναι λίγο λυπηρό, τι θα λέγατε για αυτό. Όταν γυρίσω σπίτι ανοίγω το Excel και εισάγω τα δεδομένα σε ένα φύλλο εργασίας που υπολογίζει το MPG για μένα και γράφει την απόδοση του αυτοκινήτου. Ο υπολογισμός είναι ο αριθμός των χιλιομέτρων που έχει διανύσει το αυτοκίνητο από την τελευταία πλήρωση διαιρούμενο με τον αριθμό των γαλόνων καυσίμου που χρησιμοποιήθηκε…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALLONS OF FUEL
αλλά επειδή το καύσιμο έρχεται σε λίτρα και υπάρχουν 4,546 λίτρα σε ένα γαλόνι…
MPG = (MILES THIS FILL - MILES LAST FILL) / λίτρα καυσίμου x 4.546
Δείτε πώς έγραψα τη λειτουργία…
Λειτουργία MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Liters * 4.546 End Function
και εδώ πώς φαίνεται στο φύλλο εργασίας…
Δεν εκτελούν όλες οι συναρτήσεις μαθηματικούς υπολογισμούς. Εδώ είναι ένα που παρέχει πληροφορίες…
Μια λειτουργία που δίνει το όνομα της ημέρας
Συχνά με ρωτούν αν υπάρχει συνάρτηση ημερομηνίας που δίνει την ημέρα της εβδομάδας ως κείμενο (π.χ. Δευτέρα). Η απάντηση είναι όχι*, αλλά είναι αρκετά εύκολο να το δημιουργήσετε. (*Προσθήκη: Είπα όχι; Ελέγξτε τη σημείωση παρακάτω για να δείτε τη συνάρτηση που ξέχασα!). Το Excel έχει τη συνάρτηση WEEKDAY, η οποία επιστρέφει την ημέρα της εβδομάδας ως αριθμό από το 1 στο 7. Μπορείτε να επιλέξετε ποια ημέρα είναι 1 εάν δεν σας αρέσει η προεπιλογή (Κυριακή). Στο παρακάτω παράδειγμα η συνάρτηση επιστρέφει "5" που τυχαίνει να γνωρίζω σημαίνει "Πέμπτη".
Αλλά δεν θέλω να δω έναν αριθμό, θέλω να δω την «Πέμπτη». Θα μπορούσα να τροποποιήσω τον υπολογισμό προσθέτοντας μια συνάρτηση VLOOKUP που παρέπεμπε σε έναν πίνακα που περιέχει κάπου μια λίστα με αριθμούς και μια αντίστοιχη λίστα με ονόματα ημερών. Or θα μπορούσα να έχω όλο το πράγμα αυτοτελές με πολλαπλές ένθετες προτάσεις IF. Πολύπλοκο! Η απάντηση είναι μια προσαρμοσμένη λειτουργία…
Λειτουργία DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Περίπτωση 5 DayName = "Πέμπτη" Περίπτωση 6 DayName = "Παρασκευή" Περίπτωση 7 DayName = "Τέλος Σαββάτου" Επιλέξτε Λειτουργία Τέλους
Έχω ονομάσει τη συνάρτηση μου "DayName" και χρειάζεται ένα μόνο επιχείρημα, το οποίο ονομάζω "InputDate", το οποίο (φυσικά) πρέπει να είναι ημερομηνία. Ετσι δουλευει…
- Η πρώτη γραμμή της συνάρτησης δηλώνει μια μεταβλητή που έχω ονομάσει "DayNumber", η οποία θα είναι ένας ακέραιος αριθμός (δηλαδή ένας ακέραιος αριθμός).
- Η επόμενη γραμμή της συνάρτησης εκχωρεί μια τιμή σε αυτήν τη μεταβλητή χρησιμοποιώντας τη συνάρτηση WEEKDAY του Excel. Η τιμή θα είναι ένας αριθμός μεταξύ 1 και 7. Αν και η προεπιλογή είναι 1 = Κυριακή, την έχω συμπεριλάβει έτσι κι αλλιώς για λόγους σαφήνειας.
- Τέλος α Δήλωση περίπτωσης εξετάζει την τιμή της μεταβλητής και επιστρέφει το κατάλληλο κομμάτι κειμένου.
Δείτε πώς φαίνεται στο φύλλο εργασίας…
Πρόσβαση στις προσαρμοσμένες λειτουργίες σας
Εάν ένα βιβλίο εργασίας έχει προσαρτημένη μια μονάδα κώδικα VBA που περιέχει προσαρμοσμένες λειτουργίες, αυτές οι λειτουργίες μπορούν εύκολα να αντιμετωπιστούν μέσα στο ίδιο βιβλίο εργασίας όπως παρουσιάζεται στα παραπάνω παραδείγματα. Χρησιμοποιείτε το όνομα της συνάρτησης σαν να ήταν μία από τις ενσωματωμένες συναρτήσεις του Excel.
Μπορείτε επίσης να βρείτε τις λειτουργίες που παρατίθενται στον Οδηγό λειτουργιών (μερικές φορές ονομάζεται εργαλείο επικόλλησης συνάρτησης). Χρησιμοποιήστε τον οδηγό για να εισαγάγετε μια λειτουργία με τον κανονικό τρόπο (Εισαγωγή> Λειτουργία).
Κάντε κύλιση προς τα κάτω στη λίστα των κατηγοριών λειτουργιών για εύρεση Ορισμένο από τον χρήστη και επιλέξτε το για να δείτε μια λίστα με τα διαθέσιμα UDF…
Μπορείτε να δείτε ότι οι λειτουργίες που ορίζονται από τον χρήστη δεν έχουν καμία περιγραφή εκτός από το μη βοηθητικό μήνυμα "Δεν υπάρχει διαθέσιμη βοήθεια", αλλά μπορείτε να προσθέσετε μια σύντομη περιγραφή…
Βεβαιωθείτε ότι βρίσκεστε στο βιβλίο εργασίας που περιέχει τις λειτουργίες. Παω σε Εργαλεία> Μακροεντολή> ΜακροεντολέςΤο Δεν θα δείτε τις λειτουργίες σας να αναφέρονται εδώ, αλλά το Excel τις γνωρίζει! Στο Όνομα μακροεντολής πλαίσιο στο επάνω μέρος του διαλόγου, πληκτρολογήστε το όνομα της συνάρτησης και, στη συνέχεια, κάντε κλικ στο παράθυρο διαλόγου Επιλογές κουμπί. Εάν το κουμπί είναι γκριζαρισμένο είτε έχετε γράψει λάθος το όνομα της συνάρτησης, είτε βρίσκεστε σε λάθος βιβλίο εργασίας, είτε δεν υπάρχει! Αυτό ανοίγει ένα άλλο παράθυρο διαλόγου στο οποίο μπορείτε να εισαγάγετε μια σύντομη περιγραφή της συνάρτησης. Κάντε κλικ Εντάξει για να αποθηκεύσετε την περιγραφή και (εδώ είναι το μπερδεμένο κομμάτι) κάντε κλικ Ματαίωση για να κλείσετε το παράθυρο διαλόγου Μακροεντολή. Θυμηθείτε να αποθηκεύσετε το βιβλίο εργασίας που περιέχει τη συνάρτηση. Την επόμενη φορά που θα μεταβείτε στον Οδηγό λειτουργιών, το UDF θα έχει περιγραφή…
Όπως και οι μακροεντολές, οι συναρτήσεις που ορίζονται από τον χρήστη μπορούν να χρησιμοποιηθούν σε οποιοδήποτε άλλο βιβλίο εργασίας, αρκεί να είναι ανοιχτό το βιβλίο εργασίας που τις περιέχει. Ωστόσο, δεν είναι καλή πρακτική να το κάνουμε αυτό. Η εισαγωγή της συνάρτησης σε διαφορετικό βιβλίο εργασίας δεν είναι απλή. Πρέπει να προσθέσετε το όνομα του βιβλίου εργασίας κεντρικού υπολογιστή στο όνομα της συνάρτησης. Αυτό δεν είναι δύσκολο αν βασίζεστε στον Οδηγό λειτουργίας, αλλά είναι αδέξιο να γράφετε χειροκίνητα. Ο Οδηγός λειτουργίας εμφανίζει τα πλήρη ονόματα τυχόν UDF σε άλλα βιβλία εργασίας…
Εάν ανοίξετε το βιβλίο εργασίας στο οποίο χρησιμοποιήσατε τη συνάρτηση τη στιγμή που το βιβλίο εργασίας που περιέχει τη λειτουργία είναι κλειστό, θα δείτε ένα μήνυμα σφάλματος στο κελί στο οποίο χρησιμοποιήσατε τη συνάρτηση. Το Excel το έχει ξεχάσει! Ανοίξτε το βιβλίο εργασίας του κεντρικού υπολογιστή της λειτουργίας, υπολογίστε εκ νέου και όλα είναι πάλι καλά. Ευτυχώς υπάρχει καλύτερος τρόπος.
Εάν θέλετε να γράψετε λειτουργίες καθορισμένες από το χρήστη για χρήση σε περισσότερα από ένα βιβλία εργασίας, η καλύτερη μέθοδος είναι να δημιουργήσετε ένα Excel ΠροσθέτωΤο Μάθετε πώς να το κάνετε αυτό στο σεμινάριο Δημιουργήστε ένα πρόσθετο Excel.
Προσθήκη
Πραγματικά θα έπρεπε να το ξέρω καλύτερα! Ποτέ, ποτέ, μη λες ποτέ! Αφού σας είπα ότι δεν υπάρχει μια λειτουργία που να παρέχει το όνομα της ημέρας, θυμήθηκα τώρα αυτήν που μπορεί. Δείτε αυτό το παράδειγμα…
Η συνάρτηση TEXT επιστρέφει την τιμή ενός κελιού ως κείμενο σε μια συγκεκριμένη μορφή αριθμού. Έτσι στο παράδειγμα που θα μπορούσα να είχα επιλέξει = TEXT (A1, "ddd") για επιστροφή "Πέμ", = TEXT (A1, "mmmm") για επιστροφή "Σεπτέμβριος" κλπ. Η βοήθεια του Excel έχει μερικά ακόμη παραδείγματα τρόπων χρήσης αυτής της συνάρτησης.
Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook.
Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στο ιστοσελίδα email