Εργασία με μακροεντολές Excel VBA
Οι μακροεντολές στο Excel αποθηκεύονται ως κώδικας VBA και μερικές φορές θέλετε να επεξεργαστείτε αυτόν τον κώδικα απευθείας. Αυτό το σεμινάριο θα καλύψει τον τρόπο προβολής και επεξεργασίας μακροεντολών, θα περιγράψει ορισμένες τεχνικές εντοπισμού σφαλμάτων μακροεντολών και θα δώσει μερικά κοινά παραδείγματα επεξεργασίας.
Προβολή μακροεντολών
Μια λίστα μακροεντολών μπορεί να εμφανιστεί στο παράθυρο διαλόγου Μακροεντολές. Για να προβάλετε αυτό το παράθυρο διαλόγου, επιλέξτε την καρτέλα Προγραμματιστής στην κορδέλα και κάντε κλικ στο κουμπί Μακροεντολές.
Εάν είναι ανοιχτά πολλά βιβλία εργασίας, οι μακροεντολές από όλα τα βιβλία εργασίας θα εμφανιστούν στη λίστα. Οι μακροεντολές στο ενεργό βιβλίο εργασίας θα εμφανίζονται μόνο με το όνομά τους, ενώ οι μακροεντολές σε άλλα βιβλία εργασίας θα προτίθενται με το όνομα του βιβλίου εργασίας και ένα θαυμαστικό (δηλαδή "Book2! OtherMacro").
Ανοίξτε μια μακροεντολή για επεξεργασία
Μπορείτε να χρησιμοποιήσετε το παράθυρο διαλόγου Μακροεντολή για να ανοίξετε τον κώδικα για μια μακροεντολή επιλέγοντας το όνομα της μακροεντολής και κάνοντας κλικ στο κουμπί Επεξεργασία. Αυτό θα ανοίξει τη μακροεντολή στο πρόγραμμα επεξεργασίας VB.
Εναλλακτικά, μπορείτε να ανοίξετε το VB Editor απευθείας κάνοντας κλικ στο κουμπί Visual Basic στην καρτέλα Developer ή πατώντας τη συντόμευση πληκτρολογίου ALT+F11.
Χρησιμοποιώντας αυτήν τη μέθοδο, θα χρειαστεί να μεταβείτε στην επιθυμητή μακροεντολή (που ονομάζεται επίσης «διαδικασία»). Θα εξετάσουμε τη διάταξη του VBA Editor:
Επισκόπηση του VB Editor
Το VB Editor έχει πολλά παράθυρα. σε αυτό το σεμινάριο θα καλύψουμε το παράθυρο έργου, το παράθυρο Ιδιότητες και το παράθυρο κώδικα.
Παράθυρο έργου
Το παράθυρο έργου δείχνει κάθε αρχείο Excel ως δικό του έργο, με όλα τα αντικείμενα σε αυτό το έργο να κατηγοριοποιούνται ανά τύπο. Οι καταγεγραμμένες μακροεντολές θα εμφανιστούν στην κατηγορία "Ενότητες", συνήθως στο αντικείμενο "Module1". (Εάν το έργο σας διαθέτει πολλαπλές ενότητες και δεν είστε σίγουροι για το πού είναι αποθηκευμένη η μακροεντολή σας, απλώς ανοίξτε το από το προαναφερθέν παράθυρο διαλόγου Μακροεντολές.)
Παράθυρο ιδιοτήτων
Το παράθυρο ιδιοτήτων εμφανίζει τις ιδιότητες και τις σχετικές τιμές ενός αντικειμένου - για παράδειγμα, κάνοντας κλικ σε ένα αντικείμενο φύλλου εργασίας στο παράθυρο έργου θα εμφανιστεί μια λίστα ιδιοτήτων για το φύλλο εργασίας. Τα ονόματα ιδιοτήτων βρίσκονται στα αριστερά και οι τιμές ιδιοκτησίας στα δεξιά.
Επιλέγοντας μια ενότητα στο παράθυρο Έργου θα εμφανιστεί ότι έχει μόνο μία ιδιότητα, "(Όνομα)". Μπορείτε να αλλάξετε το όνομα μιας ενότητας κάνοντας διπλό κλικ στην τιμή της ιδιότητας, πληκτρολογώντας ένα νέο όνομα και πατώντας Enter. Η αλλαγή του ονόματος μιας ενότητας θα την μετονομάσει στο Παράθυρο έργου, κάτι που είναι χρήσιμο εάν έχετε πολλές ενότητες.
Κωδικός παραθύρων
Τα παράθυρα κώδικα είναι ειδικοί επεξεργαστές κειμένου στους οποίους μπορείτε να επεξεργαστείτε τον κώδικα VBA της μακροεντολής σας. Εάν θέλετε να δείτε τον κώδικα για μια μακροεντολή που βρίσκεται στο Module1, κάντε διπλό κλικ στο 'Module1' στο Παράθυρο έργου.
Εκτέλεση μακροεντολών στο πρόγραμμα επεξεργασίας VB
Οι μακροεντολές μπορούν να εκτελεστούν απευθείας από το πρόγραμμα επεξεργασίας VB, το οποίο είναι χρήσιμο για δοκιμές και εντοπισμό σφαλμάτων.
Εκτέλεση μακροεντολής
- Στο παράθυρο έργου, κάντε διπλό κλικ στη μονάδα που περιέχει τη μακροεντολή που θέλετε να δοκιμάσετε (για να ανοίξετε το παράθυρο κώδικα)
- Στο παράθυρο Κωδικός, τοποθετήστε τον κέρσορα οπουδήποτε στον κώδικα της μακροεντολής μεταξύ "Sub" και "End Sub"
- Κάντε κλικ στο Τρέξιμο κουμπί στη γραμμή εργαλείων ή πατήστε τη συντόμευση πληκτρολογίου F5
"Step-Through" μια μακροεντολή
Αντί να εκτελέσετε τη μακροεντολή ταυτόχρονα, μπορείτε να εκτελέσετε τη μακροεντολή μία γραμμή τη φορά, χρησιμοποιώντας μια συντόμευση πληκτρολογίου για να "περάσετε" τον κώδικα. Η μακροεντολή θα σταματήσει σε κάθε γραμμή, επιτρέποντάς σας να διασφαλίσετε ότι κάθε γραμμή κώδικα κάνει αυτό που περιμένετε στο Excel. Μπορείτε επίσης να διακόψετε τη συνέχιση μιας μακροεντολής ανά πάσα στιγμή χρησιμοποιώντας αυτήν τη μέθοδο.
Για να "περάσετε" μια μακροεντολή:
- Στο παράθυρο έργου, κάντε διπλό κλικ στη μονάδα που περιέχει τη μακροεντολή που θέλετε να δοκιμάσετε (για να ανοίξετε το παράθυρο κώδικα)
- Στο παράθυρο Κωδικός, τοποθετήστε τον κέρσορα οπουδήποτε στον κώδικα της μακροεντολής
- Πατήστε τη συντόμευση πληκτρολογίου F8 για να ξεκινήσετε τη διαδικασία "βήμα προς βήμα"
- Πατήστε επανειλημμένα το πλήκτρο F8 για να προωθήσετε την εκτέλεση του κώδικα, υποδεικνύεται από την κίτρινη επισήμανση στο παράθυρο Κωδικός
- Για να σταματήσετε τη συνέχιση μιας μακροεντολής, πατήστε το Επαναφορά κουμπί
Γιατί να επεξεργαστείτε μακροεντολές VBA;
Η συσκευή εγγραφής μακροεντολών - ενώ είναι αποτελεσματική - είναι επίσης πολύ περιορισμένη. Σε ορισμένες περιπτώσεις παράγει αργές μακροεντολές, καταγράφει ενέργειες που δεν σκοπεύατε να επαναλάβετε ή καταγράφει πράγματα που δεν νομίζατε ότι κάνατε. Η εκμάθηση της επεξεργασίας των μακροεντολών σας θα τους βοηθήσει να λειτουργούν γρηγορότερα, πιο αποτελεσματικά και πιο προβλέψιμα.
Εκτός από τη διόρθωση αυτών των ζητημάτων, θα κερδίσετε επίσης μια τεράστια αύξηση της παραγωγικότητας όταν αξιοποιήσετε την πλήρη ισχύ των μακροεντολών. Οι μακροεντολές δεν πρέπει απλώς να είναι ηχογραφήσεις εργασιών - οι μακροεντολές μπορούν να περιλαμβάνουν λογική, ώστε να εκτελούν εργασίες μόνο υπό συγκεκριμένες συνθήκες. Σε λίγα μόνο λεπτά μπορείτε να κωδικοποιήσετε βρόχους που επαναλαμβάνουν μια εργασία εκατοντάδες ή χιλιάδες φορές με τη μία!
Παρακάτω, θα βρείτε μερικές εύχρηστες συμβουλές που θα σας βοηθήσουν να βελτιστοποιήσετε τον κώδικα μακροεντολής σας, καθώς και εργαλεία που θα κάνουν τις μακροεντολές σας να δουλεύουν πιο σκληρά και πιο έξυπνα.
Κοινά παραδείγματα επεξεργασίας μακροεντολών
Επιταχύνετε τις μακροεντολές
Εάν έχετε μια μακροεντολή που χρειάζεται πολύ χρόνο για να τρέξει, μπορεί να υπάρχουν δύο λόγοι για τους οποίους λειτουργεί αργά.
Για ένα: όταν εκτελείται μια μακροεντολή, το Excel θα δείχνει τα πάντα όπως συμβαίνει σε πραγματικό χρόνο - όσο μπορεί Κοίτα γρήγορα σε σας, στην πραγματικότηταεπίδειξη το έργο είναι μια σημαντική επιτυχία επιτυχίας. Ένας τρόπος για να κάνετε το Excel να τρέχει σημαντικά πιο γρήγορα είναι να το πείτε σταματήστε να ενημερώνετε την οθόνη:
'Απενεργοποίηση εφαρμογής ενημέρωσης οθόνης. ScreenUpdating = Λάθος' Ενεργοποίηση εφαρμογής ενημέρωσης οθόνης.ScreenUpdating = True
Η γραμμή "Application.ScreenUpdating = False" σημαίνει ότι δεν θα δείτε τη μακροεντολή να λειτουργεί, αλλά θα τρέξει πολύ πιο γρήγορα. Σημειώστε ότι πρέπει πάντα να ορίζετε την Ενημέρωση οθόνης σε Αληθινή στο τέλος της μακροεντολής σας, διαφορετικά το Excel ενδέχεται να μην λειτουργεί όπως περιμένετε αργότερα!
Ένας άλλος τρόπος για να επιταχύνετε τις μακροεντολές:απενεργοποιήστε τον αυτόματο υπολογισμό στη μακροεντολήΤο Εάν έχετε εργαστεί με πολύπλοκα υπολογιστικά φύλλα, θα ξέρετε ότι μικρές αλλαγές μπορούν να προκαλέσουν χιλιάδες υπολογισμούς που χρειάζονται χρόνο για να ολοκληρωθούν, γι 'αυτό και πολλοί άνθρωποι απενεργοποιούν τον αυτόματο υπολογισμό στις επιλογές του Excel. Μπορείτε επίσης να το αλλάξετε με κώδικα VBA, έτσι ώστε η μακροεντολή σας να εξακολουθεί να λειτουργεί γρήγορα σε άλλους υπολογιστές. Αυτό βοηθά στις περιπτώσεις που αντιγράφετε-επικολλάτε πολλά κελιά τύπου ή προκαλείτε πολλούς υπολογισμούς που ενεργοποιούν καθώς επικολλάτε δεδομένα σε μια περιοχή:
'Απενεργοποίηση εφαρμογής αυτόματου υπολογισμού. Υπολογισμός = xlCalculationManual' Ενεργοποίηση εφαρμογής αυτόματου υπολογισμού. Υπολογισμός = xlCalculationAutomatic
Προσθήκη βρόχων και λογικής (αν δηλώσεις)
Η συσκευή εγγραφής μακροεντολών αποθηκεύει όλες τις ενέργειές σας ως κώδικα σε μια γλώσσα που ονομάζεται VBA. Το VBA είναι κάτι περισσότερο από έναν τρόπο καταγραφής ενεργειών στο Excel - είναι μια γλώσσα προγραμματισμού, πράγμα που σημαίνει ότι μπορεί να περιέχει κώδικα για τη λήψη αποφάσεων σχετικά με τις ενέργειες που πρέπει να εκτελεστούν ή για την επανάληψη ενεργειών έως ότου ικανοποιηθεί μια συνθήκη.
Looping
Πείτε ότι θέλετε να δημιουργήσετε μια μακροεντολή που ετοίμασε μια αναφορά και ως μέρος αυτής της μακροεντολής έπρεπε να προσθέσετε δεκαεννέα φύλλα στο βιβλίο εργασίας, για συνολικά είκοσι. Μπορείτε να καταγράψετε τον εαυτό σας κάνοντας κλικ στο κουμπί (+) ξανά και ξανά, ή μπορείτε να γράψετε έναν βρόχο που επαναλαμβάνει τη δράση για εσάς, όπως αυτό:
Sub ReportPrep () Dim i As Long For i = 1 Σε 19 Φύλλα. Προσθήκη Επόμενο i Τέλος Υπο
Σε αυτό το παράδειγμα, χρησιμοποιούμε α Για βρόχο, το οποίο είναι ένα είδος βρόχου που επαναλαμβάνεται μέσα από μια σειρά στοιχείων. Εδώ, το εύρος μας είναι οι αριθμοί 1 έως 19, χρησιμοποιώντας μια μεταβλητή που ονομάζεται «i», ώστε ο βρόχος να μπορεί να παρακολουθεί. Μέσα στο βρόχο μας, υπάρχει μόνο μία ενέργεια που επαναλαμβάνεται μεταξύ του Για καιΕπόμενο γραμμές (το φύλλο προστίθεται), αλλά μπορείτε να προσθέσετε όσο κώδικα μέσα στον βρόχο θέλετε να κάνετε πράγματα όπως μορφοποίηση του φύλλου ή αντιγραφή και επικόλληση δεδομένων σε κάθε φύλλο - ό, τι θέλετε να επαναλάβετε.
Αν Δηλώσεις
Ενα Αν δήλωση χρησιμοποιείται για να αποφασίσει εάν κάποιος κώδικας εκτελείται ή όχι, χρησιμοποιώντας μια λογική δοκιμή για να λάβει την απόφαση. Εδώ είναι ένα απλό παράδειγμα:
Sub ClearIfSmall () If Selection.Value <100 Στη συνέχεια Selection.Clear End If End Sub
Αυτό το απλό παράδειγμα δείχνει πώς λειτουργεί η δήλωση If - δοκιμάζετε κάποια συνθήκη που είναι είτε True είτε False (είναι η τιμή του επιλεγμένου κελιού μικρότερη από 100;), και αν το τεστ επιστρέψει True, ο κώδικας μέσα εκτελείται.
Ένα μειονέκτημα αυτού του κώδικα είναι ότι δοκιμάζει μόνο ένα κελί κάθε φορά (και θα αποτύχει εάν επιλέξετε πολλά κελιά). Αυτό θα ήταν πιο χρήσιμο αν μπορούσατε … να κάνετε βρόχο σε κάθε επιλεγμένο κελί και να δοκιμάσετε το καθένα…
Sub ClearIfSmall () Dim c As Range For Every c In Selection.Cells If c.Value <100 Στη συνέχεια c.Clear End If Next c End Sub
Σε αυτό το παράδειγμα, υπάρχει ένας ελαφρώς διαφορετικός βρόχος For - αυτός δεν περνά μέσα από μια σειρά αριθμών, αλλά αντλείται από όλα τα κελιά της επιλογής, χρησιμοποιώντας μια μεταβλητή που ονομάζεται «c» για να παρακολουθεί. Μέσα στον βρόχο, η τιμή του «c» χρησιμοποιείται για να καθοριστεί εάν το κελί πρέπει να καθαριστεί ή όχι.
Οι βρόχοι και οι δηλώσεις If μπορούν να συνδυαστούν με όποιον τρόπο θέλετε - μπορείτε να βάλετε βρόχους μέσα σε βρόχους, ή έναν Αν μέσα σε άλλον, ή να χρησιμοποιήσετε ένα If για να αποφασίσετε εάν ένας βρόχος πρέπει να εκτελείται καθόλου.
<<>>
Κατάργηση εφέ κύλισης
Ένας κοινός λόγος για την επεξεργασία κώδικα μακροεντολής είναι η κατάργηση της κύλισης της οθόνης. Κατά την εγγραφή μιας μακροεντολής, μπορεί να χρειαστεί να προσεγγίσετε άλλες περιοχές ενός φύλλου εργασίας με κύλιση, αλλά οι μακροεντολές δεν χρειάζονται κύλιση για πρόσβαση στα δεδομένα.
Η κύλιση μπορεί να γεμίσει τον κώδικα σας με εκατοντάδες ή ακόμη και χιλιάδες γραμμές περιττού κώδικα. Ακολουθεί ένα παράδειγμα του κώδικα που καταγράφεται όταν κάνετε κλικ και σύρετε στη γραμμή κύλισης:
Αυτό το είδος κώδικα είναι εντελώς περιττό και θα μπορούσε να διαγραφεί χωρίς να επηρεαστεί οποιαδήποτε άλλη λειτουργία. Ακόμα κι αν θέλετε να διατηρήσετε την κύλιση, αυτός ο κώδικας θα μπορούσε να συμπυκνωθεί σε έναν βρόχο.
Κατάργηση περιττού κωδικού
Οι καταγεγραμμένες μακροεντολές τείνουν να προσθέτουν πολλούς περιττούς κώδικες που δεν αντικατοπτρίζουν απαραίτητα αυτό που θέλετε να κάνει η μακροεντολή. Πάρτε για παράδειγμα τον ακόλουθο καταγεγραμμένο κώδικα, ο οποίος καταγράφει την αλλαγή ονόματος γραμματοσειράς σε ένα κελί:
Παρόλο που άλλαξε μόνο το όνομα της γραμματοσειράς, καταγράφηκαν έντεκα (11) αλλαγές γραμματοσειράς, όπως το μέγεθος της γραμματοσειράς, τα εφέ κειμένου κ.λπ. Εάν η πρόθεση της μακροεντολής ήταν να αλλάξει μόνο το όνομα της γραμματοσειράς (αφήνοντας όλες τις άλλες ιδιότητες μόνο) η καταγεγραμμένη μακροεντολή δεν θα λειτουργήσει!
Είναι δυνατό να αλλάξετε αυτήν τη μακροεντολή έτσι ώστε να αλλάζει μόνο το όνομα της γραμματοσειράς:
Όχι μόνο αυτή η μακροεντολή θα λειτουργεί όπως προβλέπεται τώρα, αλλά είναι επίσης πολύ πιο εύκολο να διαβαστεί.
Κατάργηση κινήσεων δρομέα
Ένα άλλο πράγμα που καταγράφεται σε μακροεντολές είναι το φύλλο εργασίας και οι επιλογές κελιών. Αυτό είναι ένα πρόβλημα επειδή ένας χρήστης μπορεί εύκολα να χάσει την παρακολούθηση αυτού που δούλευε μόλις ο δρομέας μετακινηθεί σε διαφορετική θέση μετά από μια μακροεντολή.
Όπως και με την κύλιση, εσείς μπορεί να χρειαστεί να μετακινήσετε τον κέρσορα και να επιλέξετε διαφορετικά κελιά για να εκτελέσετε μια εργασία, αλλά οι μακροεντολές δεν χρειάζεται να χρησιμοποιούν τον κέρσορα για πρόσβαση στα δεδομένα. Εξετάστε τον ακόλουθο κώδικα, ο οποίος αντιγράφει μια περιοχή και στη συνέχεια την επικολλά σε τρία άλλα φύλλα:
Υπάρχουν μερικά προβλήματα με αυτόν τον κωδικό:
- Ο χρήστης θα χάσει την προηγούμενη θέση του στο βιβλίο εργασίας
- Η μακροεντολή δεν καθορίζει ποιο φύλλο αντιγράφουμεαπό - αυτό θα μπορούσε να είναι πρόβλημα εάν η μακροεντολή εκτελέστηκε σε λάθος φύλλο
Επιπλέον, ο κώδικας είναι δύσκολο να διαβαστεί και είναι σπάταλος. Αυτά τα ζητήματα μπορούν να επιλυθούν αρκετά εύκολα:
Σε αυτόν τον κώδικα, είναι σαφές ότι αντιγράφουμε από το Sheet1 και ούτε το ενεργό φύλλο εργασίας ούτε το επιλεγμένο εύρος δεν χρειάζεται να αλλάξουν για να επικολλήσουν τα δεδομένα. (Μια σημαντική αλλαγή είναι η χρήση του "PasteSpecial" αντί του "Paste" - Τα αντικείμενα εύρους, όπως το "Range (" C4 ″ "), έχουν πρόσβαση μόνο στην εντολή PasteSpecial.)
Κάθε φορά που ο κώδικας γεμίζει με αναφορές στο.
