10 τρόποι να επιταχύνετε τις μακροεντολές σας - ανδρείκελα

Καθώς οι μακροεντολές του Excel γίνονται ολοένα και πιο εύρωστες και πολύπλοκες, ίσως διαπιστώσετε ότι χάνουν απόδοση. Όταν συζητάμε μακροεντολές, η απόδοση είναι συνήθως συνώνυμη με την ταχύτητα . Η ταχύτητα είναι πόσο γρήγορα οι διαδικασίες σας VBA εκτελούν τις προβλεπόμενες εργασίες τους. Ακολουθούν δέκα τρόποι για να διατηρήσετε τις μακροεντολές του Excel σε λειτουργία στο βέλτιστο επίπεδο απόδοσής τους.

Υπολογισμοί παύσης φύλλου

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

Μπορείτε να χρησιμοποιήσετε την εφαρμογή. Ιδιότητα υπολογισμού για να ενημερώσετε το Excel για να μεταβείτε στη λειτουργία χειροκίνητου υπολογισμού. Όταν ένα βιβλίο εργασίας βρίσκεται σε λειτουργία χειροκίνητου υπολογισμού, το βιβλίο εργασίας δεν θα υπολογίζεται εκ νέου μέχρι να ενεργοποιηθεί ρητά ένας υπολογισμός πατώντας το πλήκτρο F9.

Τοποθετήστε το Excel σε λειτουργία χειροκίνητου υπολογισμού, εκτελέστε τον κωδικό σας και, στη συνέχεια, μεταβείτε στη λειτουργία αυτόματου υπολογισμού.

Εφαρμογή Sub Macro1 (). Υπολογισμός = xlCalculationManual 'Τοποθετήστε τον μακροεντολή σας εδώ Εφαρμογή. Υπολογισμός = xlΑπολογισμός Αυτόματη End Sub

Η ρύθμιση του τρόπου υπολογισμού ξανά στο xlCalculationAutomatic θα ενεργοποιήσει αυτόματα έναν επανυπολογισμό του φύλλου εργασίας, οπότε δεν χρειάζεται να πατήσετε το πλήκτρο F9 μετά τη λειτουργία της μακροεντολής σας.

Απενεργοποίηση ενημέρωσης οθόνης φύλλων

Ίσως παρατηρήσετε ότι όταν εκτελούνται οι μακροεντολές σας, η οθόνη σας τρεμοπαίζει αρκετά. Αυτό το τρεμόπαιγμα είναι το Excel που προσπαθεί να επανασχεδιάσει την οθόνη για να δείξει την τρέχουσα κατάσταση του φύλλου εργασίας. Δυστυχώς, κάθε φορά που το Excel αναδιπλώνει την οθόνη, καταλαμβάνει τους πόρους μνήμης.

Μπορείτε να χρησιμοποιήσετε την εφαρμογή. ScreenUpdating property για να απενεργοποιήσετε τις ενημερώσεις οθόνης μέχρι να ολοκληρωθεί η μακροεντολή σας. Η απενεργοποίηση της ενημέρωσης οθόνης εξοικονομεί χρόνο και πόρους, επιτρέποντας στη μακροεντολή σας να εκτελείται λίγο πιο γρήγορα. Αφού ολοκληρωθεί η εκτέλεση του κώδικα μακροεντολών, μπορείτε να ενεργοποιήσετε ξανά την ενημέρωση της οθόνης.

Εφαρμογή Sub Macro1 (). Υπολογισμός = xlΑπολογισμόςΜηνιαία Εφαρμογή. ScreenUpdating = False 'Τοποθετήστε εδώ το macro κώδικα Application. Υπολογισμός = xlΑπολογισμόςΑυτόματη Εφαρμογή. ScreenUpdating = True End Sub

Αφού ορίσετε την ιδιότητα ScreenUpdating πίσω στην τιμή True, το Excel θα ενεργοποιήσει αυτόματα μια αναδιαμόρφωση της οθόνης.

Απενεργοποίηση ενημερώσεων γραμμής κατάστασης

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

Είναι σημαντικό να σημειωθεί ότι η απενεργοποίηση της ενημέρωσης οθόνης είναι ξεχωριστή από την απενεργοποίηση της οθόνης κατάστασης κατάστασης. Η γραμμή κατάστασης θα συνεχίσει να ενημερώνεται ακόμη και αν απενεργοποιήσετε την ενημέρωση της οθόνης. Μπορείτε να χρησιμοποιήσετε την εφαρμογή. Ιδιότητα DisplayStatusBar για να απενεργοποιήσετε προσωρινά τις ενημερώσεις της γραμμής κατάστασης, βελτιώνοντας περαιτέρω την απόδοση της μακροεντολής σας:

Εφαρμογή Sub Macro1 (). Υπολογισμός = xlΑπολογισμόςΜηνιαία Εφαρμογή. ScreenUpdating = False Application. DisplayStatusBar = False 'Τοποθετήστε τον μακροεντολή σας εδώ Εφαρμογή. Υπολογισμός = xlΑπολογισμόςΑυτόματη Εφαρμογή. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Λέγοντας το Excel να αγνοεί τα συμβάντα

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

Μερικές φορές, οι τυπικές μακροεντολές πραγματοποιούν αλλαγές που θα ενεργοποιήσουν μια διαδικασία συμβάντος. Για παράδειγμα, αν έχετε μια τυπική μακροεντολή που χειρίζεται πολλά κελιά στο Φύλλο1, κάθε φορά που αλλάζει ένα κελί σε αυτό το φύλλο, η μακροεντολή σας πρέπει να τεθεί σε παύση ενώ εκτελείται το συμβάν Worksheet_Change.

Μπορείτε να προσθέσετε ένα άλλο επίπεδο αύξησης της απόδοσης χρησιμοποιώντας την ιδιότητα EnableEvents για να ενημερώσετε το Excel για να αγνοήσετε τα συμβάντα ενώ εκτελείται η μακροεντολή σας.

Ρυθμίστε την ιδιότητα EnableEvents σε False πριν εκτελέσετε τη μακροεντολή σας. Αφού ολοκληρωθεί η εκτέλεση του κώδικα μακροεντολών, μπορείτε να ορίσετε την ιδιότητα EnableEvents στην επιλογή TRUE.

Εφαρμογή Sub Macro1 (). Υπολογισμός = xlΑπολογισμόςΜηνιαία Εφαρμογή. ScreenUpdating = False Application. DisplayStatusBar = ψευδή εφαρμογή. EnableEvents = False 'Τοποθετήστε τον κωδικό μακροεντολών εδώ Εφαρμογή. Υπολογισμός = xlΑπολογισμόςΑυτόματη Εφαρμογή. ScreenUpdating = True Application. DisplayStatusBar = Αληθινή Εφαρμογή. EnableEvents = True End Sub

Απόκρυψη σελίδων διαλείμματα

Κάθε φορά που η μακροεντολή τροποποιεί τον αριθμό των γραμμών, τροποποιεί τον αριθμό των στηλών ή αλλάζει τη ρύθμιση της σελίδας ενός φύλλου εργασίας, το Excel αναγκάζεται να πάρει χρόνο επανυπολογίζοντας τα διαλείμματα σελίδας στο φύλλο.

Μπορείτε να αποφύγετε αυτήν τη συμπεριφορά απλά κρύβοντας τα σπασίματα σελίδας πριν ξεκινήσετε τη μακροεντολή σας.

Ρυθμίστε την ιδιότητα φύλλου DisplayPageBreaks σε False για να αποκρύψετε τις διαλείψεις σελίδας. Εάν θέλετε να συνεχίσετε να εμφανίζετε διαλείμματα σελίδας μετά τη λειτουργία της μακροεντολής, ορίστε την ιδιότητα φύλλου DisplayPageBreaks πίσω στην επιλογή TRUE.

Εφαρμογή Sub Macro1 (). Υπολογισμός = xlΑπολογισμόςΜηνιαία Εφαρμογή. ScreenUpdating = False Application. DisplayStatusBar = ψευδή εφαρμογή. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Τοποθετήστε τον μακροεντολή σας εδώ Εφαρμογή. Υπολογισμός = xlΑπολογισμόςΑυτόματη Εφαρμογή. ScreenUpdating = True Application. DisplayStatusBar = Αληθινή Εφαρμογή. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Ανανέωση ενημερώσεων πίνακα περιστροφής

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

Μπορείτε να βελτιώσετε την απόδοση της μακροεντολής σας, αναστέλλοντας τον επανυπολογισμό του περιστρεφόμενου πίνακα μέχρι να γίνουν όλες οι αλλαγές πεδίου περιστροφής. Απλά ορίστε τον Συγκεντρωτικό Πίνακα. Μη αυτόματη ενημέρωση της ιδιότητας True για να αναβάλλετε τον επανυπολογισμό, να εκτελέσετε τον μακροεντολή σας και, στη συνέχεια, να ορίσετε τον Συγκεντρωτικό Πίνακα. Μη αυτόματηαναπροσαρμογή της ιδιότητας πίσω στο False για να ενεργοποιήσετε τον επανυπολογισμό.

Sub Macro1 () ActiveSheet. Συγκεντρωτικοί πίνακες ("Συγκεντρωτικός πίνακας1"). ManualUpdate = True "Τοποθετήστε τον κωδικό μακροεντολών σας στο ActiveSheet. Συγκεντρωτικοί πίνακες ("Συγκεντρωτικός πίνακας1"). ManualUpdate = False End Sub

Είναι σημαντικό να θυμάστε ότι αν και ο Macro Recorder εξοικονομεί χρόνο γράφοντας κώδικα VBA για εσάς, δεν γράφει πάντοτε τον πιο αποτελεσματικό κώδικα. Ένα πρωταρχικό παράδειγμα είναι το πώς η εγγραφή Macro καταγράφει οποιαδήποτε ενέργεια αντιγραφής και επικόλλησης που πραγματοποιείτε κατά την εγγραφή.

Μπορείτε να δώσετε στις μακροεντολές σας μια μικρή ώθηση κόβοντας τον μεσάζοντα και πραγματοποιώντας ένα άμεσο αντίγραφο από ένα κελί σε ένα κελί προορισμού. Αυτός ο εναλλακτικός κώδικας χρησιμοποιεί το παράθυρο προορισμού για να παρακάμψει το πρόχειρο και να αντιγράψει τα περιεχόμενα του κελιού Α1 απευθείας στο κελί Β1.

Εύρος ("A1"). Προορισμός αντιγραφής: = Περιοχή ("B1")

Αν θέλετε να αντιγράψετε μόνο τιμές (όχι μορφοποίηση ή τύπους), μπορείτε να βελτιώσετε ακόμη περισσότερο την απόδοση αποφεύγοντας τη μέθοδο Αντιγραφής όλα μαζί. Απλά ορίστε την τιμή του κελιού προορισμού στην ίδια τιμή που βρίσκεται στο κελί πηγής. Αυτή η μέθοδος είναι περίπου 25 φορές πιο γρήγορη από τη χρήση της μεθόδου Αντιγραφή:

Εύρος ("B1"). Τιμή = Εύρος ("A1"). Εάν θέλετε να αντιγράψετε μόνο τύπους από ένα κελί σε άλλο (όχι τιμές ή μορφοποίηση), μπορείτε να ορίσετε τον τύπο του κελί προορισμού στον ίδιο τύπο που περιέχεται στο κελί πηγής:

Εύρος ("B1"). Τύπος = Περιοχή ("Α1"). Formula

Χρήση της δήλωσης

Κατά την εγγραφή μακροεντολών, θα χειρίζεστε συχνά το ίδιο αντικείμενο περισσότερες από μία φορές. Μπορείτε να εξοικονομήσετε χρόνο και να βελτιώσετε την απόδοση χρησιμοποιώντας τη δήλωση "With" για να εκτελέσετε διάφορες ενέργειες σε ένα δεδομένο αντικείμενο σε μία λήψη.

Η εντολή With με το ακόλουθο παράδειγμα λέει στο Excel να εφαρμόζει όλες τις αλλαγές μορφοποίησης ταυτόχρονα:

Με εύρος ("A1"). Γραμματοσειρά. Bold = True. Πλαίσιο = Αληθινό. Underline = xlUnderlineStyleSingle End Με

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

Η αποφυγή της μεθόδου Επιλογή

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

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

Φύλλα ("Φύλλο1"). Εύρος ("A1"). FormulaR1C1 = Φύλλα "1000" ("Φύλλο2"). Εύρος ("A1"). FormulaR1C1 = Φύλλα "1000" ("Φύλλο3"). Εύρος ("A1"). FormulaR1C1 = "1000"

Σημειώστε ότι δεν επιλέγεται τίποτα.Ο κώδικας χρησιμοποιεί απλώς την ιεραρχία αντικειμένων για να εφαρμόσει τις απαιτούμενες ενέργειες.

Περιορισμός των μετακινήσεων στο φύλλο εργασίας

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

Για παράδειγμα, οι παρακάτω απλοί κώδικες αναγκάζουν το VBA να επιστρέψει συνεχώς στα φύλλα ("Φύλλο1"). Εύρος ("A1") για να πάρετε τον αριθμό που απαιτείται για τη σύγκριση που εκτελείται στη δήλωση If:

Για ReportMonth = 1 έως 12 Εάν εύρος ("A1"). Value = ReportMonth Στη συνέχεια, MsgBox 1000000 / ReportMonth End If Next ReportMonth

Μια πολύ πιο αποτελεσματική μέθοδος είναι να αποθηκεύσετε την τιμή στα Sheets ("Sheet1"). ("A1") σε μια μεταβλητή που ονομάζεται MyMonth. Με αυτόν τον τρόπο, ο κώδικας αναφέρει τη μεταβλητή MyMonth αντί του φύλλου εργασίας:

Dim MyMonth ως ακέραιο MyMonth = Εύρος ("A1"). Αξία για ReportMonth = 1 έως 12 Εάν MyMonth = ReportMonth Στη συνέχεια MsgBox 1000000 / ReportMonth Τέλος αν Next ReportMonth

Εξετάστε το μοχλό μεταβλητές για να εργαστείτε με τα δεδομένα στη μνήμη, σε αντίθεση με τα άμεσα φύλλα εργασίας αναφοράς.