Αυτό το σεμινάριο θα συζητήσει πώς να επιταχύνετε τις μακροεντολές VBA και άλλες βέλτιστες πρακτικές VBA.
Ρυθμίσεις για επιτάχυνση του κώδικα VBA
Παρακάτω θα βρείτε πολλές συμβουλές για να επιταχύνετε τον κώδικα VBA. Οι συμβουλές είναι χαλαρά οργανωμένες κατά σημασία.
Ο ευκολότερος τρόπος για να βελτιώσετε την ταχύτητα του κώδικα VBA είναι απενεργοποιώντας την ενημέρωση οθόνης και απενεργοποιώντας τους αυτόματους υπολογισμούς. Αυτές οι ρυθμίσεις πρέπει να απενεργοποιηθούν σε όλες τις μεγάλες διαδικασίες.
Απενεργοποιήστε την ενημέρωση οθόνης
Από προεπιλογή, το Excel θα εμφανίζει αλλαγές στα βιβλία εργασίας σε πραγματικό χρόνο καθώς εκτελείται ο κώδικας VBA. Αυτό προκαλεί τεράστια επιβράδυνση στην ταχύτητα επεξεργασίας καθώς το Excel ερμηνεύει και εμφανίζει τις περισσότερες αλλαγές για κάθε γραμμή κώδικα.
Για να απενεργοποιήσετε την Ενημέρωση οθόνης:
| 1 | Application.ScreenUpdating = Λάθος |
Στο τέλος της μακροεντολής σας, θα πρέπει να ενεργοποιήσετε ξανά την Ενημέρωση οθόνης:
| 1 | Application.ScreenUpdating = True |
Ενώ ο κωδικός σας εκτελείται, ίσως χρειαστεί να "ανανεώσετε" την οθόνη. Δεν υπάρχει εντολή "ανανέωσης". Αντ 'αυτού, θα χρειαστεί να ενεργοποιήσετε ξανά την Ενημέρωση οθόνης και να την απενεργοποιήσετε ξανά.
Ορίστε τους υπολογισμούς σε μη αυτόματο
Κάθε φορά που αλλάζει μια τιμή κελιού, το Excel πρέπει να ακολουθεί το "δέντρο υπολογισμού" για να υπολογίσει εκ νέου όλα τα εξαρτημένα κελιά. Επιπλέον, κάθε φορά που αλλάζει ένας τύπος, το Excel θα πρέπει να ενημερώνει το "δέντρο υπολογισμού", εκτός από τον εκ νέου υπολογισμό όλων των εξαρτημένων κελιών. Ανάλογα με το μέγεθος του βιβλίου εργασίας σας, αυτοί οι επανυπολογισμοί μπορούν να προκαλέσουν την αδικαιολόγητη αργή εκτέλεση των μακροεντολών σας.
Για να ορίσετε τους υπολογισμούς σε χειροκίνητα:
| 1 | Application.Calculation = xlManual |
Για να υπολογίσετε ξανά με μη αυτόματο τρόπο ολόκληρο το βιβλίο εργασίας:
| 1 | Υπολογίζω |
Σημειώστε ότι μπορείτε επίσης να υπολογίσετε μόνο ένα φύλλο, εύρος ή μεμονωμένο κελί, εάν είναι απαραίτητο για βελτιωμένη ταχύτητα.
Για να επαναφέρετε τους αυτόματους υπολογισμούς (στο τέλος της διαδικασίας σας):
| 1 | Εφαρμογή. Υπολογισμός = xlΑυτόματο |
Σπουδαίος! Αυτή είναι μια ρύθμιση Excel. Εάν δεν επαναρυθμίσετε τους υπολογισμούς στο αυτόματο, το βιβλίο εργασίας σας δεν θα υπολογίσει εκ νέου μέχρι να του το πείτε.
Θα δείτε τις μεγαλύτερες βελτιώσεις από τις παραπάνω ρυθμίσεις, αλλά υπάρχουν αρκετές άλλες ρυθμίσεις που μπορούν να κάνουν τη διαφορά:
Απενεργοποίηση συμβάντων
Τα γεγονότα είναι "ενεργοποιητές" που προκαλούν ιδιαίτερη διαδικασίες εκδηλώσεων τρέχω. Παραδείγματα περιλαμβάνουν: όταν αλλάζει οποιοδήποτε κελί σε φύλλο εργασίας, όταν ενεργοποιείται ένα φύλλο εργασίας, όταν ανοίγει ένα βιβλίο εργασίας, πριν αποθηκευτεί ένα βιβλίο εργασίας κ.λπ.
Η απενεργοποίηση συμβάντων μπορεί να προκαλέσει μικρές βελτιώσεις ταχύτητας όταν εκτελούνται οποιεσδήποτε μακροεντολές, αλλά η βελτίωση της ταχύτητας μπορεί να είναι πολύ μεγαλύτερη εάν το βιβλίο εργασίας σας χρησιμοποιεί συμβάντα. Και σε ορισμένες περιπτώσεις η απενεργοποίηση συμβάντων είναι απαραίτητη για να αποφευχθεί η δημιουργία ατελείωτων βρόχων.
Για να απενεργοποιήσετε τα συμβάντα:
| 1 | Application.EnableEvents = Λάθος |
Για να ενεργοποιήσετε ξανά τα συμβάντα:
| 1 | Application.EnableEvents = True |
Απενεργοποίηση PageBreaks
Η απενεργοποίηση των PageBreaks μπορεί να βοηθήσει σε ορισμένες περιπτώσεις:
- Έχετε ορίσει προηγουμένως μια ιδιότητα PageSetup για το σχετικό φύλλο εργασίας και Η διαδικασία VBA τροποποιεί τις ιδιότητες πολλών γραμμών ή στηλών
- Ή Η διαδικασία VBA σας αναγκάζει το Excel να υπολογίζει αλλαγές σελίδας (εμφανίζει προεπισκόπηση εκτύπωσης ή τροποποιεί τυχόν ιδιότητες του PageSetup).
Για να απενεργοποιήσετε τις PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = Λάθος |
Για να ενεργοποιήσετε ξανά το PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Βέλτιστες πρακτικές για τη βελτίωση της ταχύτητας VBA
Αποφύγετε την ενεργοποίηση και την επιλογή
Όταν εγγράφετε μια μακροεντολή, θα δείτε πολλές μεθόδους ενεργοποίησης και επιλογής:
| 12345678 | Sub Slow_Example ()Φύλλα ("Φύλλο2"). ΕπιλέξτεΕύρος ("D9"). ΕπιλέξτεActiveCell.FormulaR1C1 = "παράδειγμα"Εύρος ("D12"). ΕπιλέξτεActiveCell.FormulaR1C1 = "επίδειξη"Εύρος ("D13"). ΕπιλέξτεΤέλος υπο |
Η ενεργοποίηση και η επιλογή αντικειμένων είναι συνήθως περιττή, προσθέτουν ακαταστασία στον κώδικά σας και είναι πολύ χρονοβόρα. Θα πρέπει να αποφεύγετε αυτές τις μεθόδους όταν είναι δυνατόν.
Βελτιωμένο Παράδειγμα:
| 1234 | Υπο Fast_Example ()Φύλλα ("Φύλλο2"). Εύρος ("D9"). FormulaR1C1 = "παράδειγμα"Φύλλα ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Τέλος υπο |
Αποφύγετε την αντιγραφή και επικόλληση
Η αντιγραφή απαιτεί σημαντική μνήμη. Δυστυχώς, δεν μπορείτε να πείτε στο VBA να καθαρίσει την εσωτερική μνήμη. Αντ 'αυτού, το Excel θα καθαρίσει την εσωτερική του μνήμη σε (φαινομενικά) συγκεκριμένα διαστήματα. Έτσι, εάν εκτελείτε πολλές λειτουργίες αντιγραφής και επικόλλησης, διατρέχετε τον κίνδυνο να σπρώξετε πολύ μνήμη, γεγονός που μπορεί να επιβραδύνει δραστικά τον κώδικά σας ή ακόμα και να καταστρέψει το Excel.
Αντί για αντιγραφή και επικόλληση, σκεφτείτε να ορίσετε τις ιδιότητες τιμής των κελιών.
| 123456789 | Sub CopyPaste ()'ΒραδύτερηΕύρος ("a1: a1000"). Εύρος αντιγραφής ("b1: b1000")'ΓρηγορότεραΕύρος ("b1: b1000"). Τιμή = Εύρος ("a1: a1000"). ΤιμήΤέλος υπο |
Χρησιμοποιήστε τους βρόχους Για κάθε αντί για Βρόχους
Κατά την περιήγηση σε αντικείμενα, ο βρόχος For Every είναι ταχύτερος από τον βρόχο For. Παράδειγμα:
Αυτό για βρόχο:
| 123456 | Υποβρόχος 1 ()dim i as RangeΓια i = 1 έως 100Κελιά (i, 1). Αξία = 1Επόμενο iΤέλος υπο |
| 123456 | Υποβρόχος 2 ()Dim cell As RangeΓια κάθε κελί εντός εμβέλειας ("a1: a100")κελί. Αξία = 1Επόμενο κελίΤέλος υπο |
Δήλωση μεταβλητών / Χρήση επιλογής ρητή
Το VBA δεν απαιτεί να δηλώσετε τις μεταβλητές σας, εκτός εάν προσθέσετε Option Explicit στο επάνω μέρος της μονάδας σας:| 1 | Επιλογή ρητή |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlΤέλος υπο |
Χρήση με - Τέλος με δηλώσεις
Εάν αναφέρετε τα ίδια αντικείμενα πολλές φορές (π.χ. εύρος, φύλλα εργασίας, βιβλία εργασίας), σκεφτείτε να χρησιμοποιήσετε τη δήλωση με. Είναι πιο γρήγορη στην επεξεργασία, μπορεί να κάνει τον κώδικα σας ευκολότερο στην ανάγνωση και απλοποιεί τον κώδικά σας.Με παράδειγμα δήλωσης:| 12345678 | Υπο Faster_Example ()Με φύλλα ("Φύλλο2").Range ("D9"). FormulaR1C1 = "παράδειγμα".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueΤέλος μεΤέλος υπο |
| 123456 | Sub Slow_Example ()Φύλλα ("Φύλλο2"). Εύρος ("D9"). FormulaR1C1 = "παράδειγμα"Φύλλα ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Φύλλα ("Φύλλο2"). Εύρος ("D9"). Font.Bold = TrueΦύλλα ("Sheet2"). Εύρος ("D12"). Font.Bold = TrueΤέλος υπο |
Συμβουλές για βέλτιστες πρακτικές για προχωρημένους
Προστατεύστε το UserInterfaceOnly
Είναι καλή πρακτική να προστατεύετε τα φύλλα εργασίας σας από την επεξεργασία απροστάτευτων κελιών για να αποτρέψετε τον τελικό χρήστη (ή εσάς!) Από τυχαία καταστροφή του βιβλίου εργασίας. Ωστόσο, αυτό θα προστατεύσει επίσης το φύλλο εργασίας από τα VBA να κάνουν αλλαγές. Επομένως, πρέπει να προστατεύσετε και να προστατεύσετε εκ νέου τα φύλλα εργασίας, κάτι που είναι πολύ χρονοβόρο όταν γίνεται σε πολλά φύλλα.
| 12345 | Υπο UnProtectSheet ()Φύλλα ("sheet1"). Μη προστατευμένος "κωδικός πρόσβασης"«Επεξεργασία φύλλου 1Φύλλα ("sheet1"). Προστασία "κωδικού πρόσβασης"Τέλος υπο |
Αντ 'αυτού, μπορείτε να προστατεύσετε τα φύλλα με τη ρύθμιση UserInterfaceOnly: = True. Αυτό επιτρέπει στο VBA να κάνει αλλαγές σε φύλλα, ενώ παράλληλα τα προστατεύει από τον χρήστη.
| 1 | Φύλλα ("sheet1"). Προστασία κωδικού πρόσβασης: = "password", UserInterFaceOnly: = True |
Σπουδαίος! Το UserInterFaceOn επαναφέρει μόνο στο False κάθε φορά που ανοίγει το βιβλίο εργασίας. Έτσι, για να χρησιμοποιήσετε αυτήν την εκπληκτική λειτουργία, θα χρειαστεί να χρησιμοποιήσετε τα συμβάντα Workbook_Open ή Auto_Open για να ορίσετε τη ρύθμιση κάθε φορά που ανοίγει το βιβλίο εργασίας.
Τοποθετήστε αυτόν τον κωδικό στη μονάδα Thisbookbook:
| 123456 | Private Sub Workbook_Open ()Dim ws Ως φύλλο εργασίαςΓια κάθε ws σε φύλλα εργασίαςws.Protect Password: = "password", UserInterFaceOnly: = TrueΕπόμενο wsΤέλος υπο |
ή αυτόν τον κωδικό σε οποιαδήποτε κανονική ενότητα:
| 123456 | Private Sub Auto_Open ()Dim ws Ως φύλλο εργασίαςΓια κάθε ws σε φύλλα εργασίαςws.Protect Password: = "password", UserInterFaceOnly: = TrueΕπόμενο wsΤέλος υπο |
Χρησιμοποιήστε πίνακες για να επεξεργαστείτε μεγάλα εύρη
Μπορεί να είναι πολύ χρονοβόρος ο χειρισμός μεγάλου εύρους κελιών (Παράδειγμα 100.000+). Αντί να περιηγείστε σε εύρη κελιών, να χειρίζεστε κάθε κελί, μπορείτε να φορτώσετε τα κελιά σε έναν πίνακα, να επεξεργαστείτε κάθε στοιχείο στον πίνακα και στη συνέχεια να εξάγετε τον πίνακα στα αρχικά του κελιά. Η φόρτωση των κελιών σε πίνακες για χειρισμό μπορεί να είναι πολύ πιο γρήγορη.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Dim cell As RangeDim tStart As DoubletStart = ΧρονοδιακόπτηςΓια κάθε κελί εντός εμβέλειας ("A1: A100000")cell.Value = cell.Value * 100Επόμενο κελίDebug.Print (Timer - tStart) & "seconds"Τέλος υποSub LoopArray ()Dim arr As VariantDim στοιχείο ως παραλλαγήDim tStart As DoubletStart = Χρονοδιακόπτηςarr = Εύρος ("A1: A100000"). ΤιμήΓια κάθε στοιχείο Σε βέλοςστοιχείο = στοιχείο * 100Επόμενο στοιχείοΕύρος ("A1: A100000"). Τιμή = arrDebug.Print (Timer - tStart) & "seconds"Τέλος υπο |
