Αυτές είναι οι σημειώσεις μου από την εμφάνισή μου στο επεισόδιο 33 του Call for Help στο TechTV Canada.
Εγγραφή μακροεντολών VBA
Κάθε αντίγραφο του Excel που πουλήθηκε από το 1995 περιλαμβάνει την απίστευτα ισχυρή Visual Basic for Applications (VBA) που κρύβεται πίσω από τα κελιά. Εάν έχετε Excel, έχετε VBA.
Το VBA σάς επιτρέπει να αυτοματοποιήσετε οτιδήποτε μπορείτε να κάνετε στο Excel, καθώς και να κάνετε περισσότερα πράγματα που δεν είναι γενικά δυνατά στο Excel.
Ευτυχώς για εμάς, η Microsoft συμπεριέλαβε μια συσκευή εγγραφής μακροεντολών με το Excel. Αυτό σας επιτρέπει να γράψετε κώδικα VBA χωρίς να είστε προγραμματιστής. Δυστυχώς, το Macro Recorder είναι ελαττωματικό. Δεν θα παράγει κώδικα που θα λειτουργεί κάθε φορά. Η μακροεντολή εγγραφής θα σας φέρει κοντά, αλλά σε πολλές περιπτώσεις πρέπει να διορθώσετε λίγο τον κώδικα για να λειτουργήσει αξιόπιστα. Σε άλλες περιπτώσεις, πρέπει να γνωρίζετε πότε να χρησιμοποιήσετε τη σχετική εγγραφή για να κάνετε τη μακροεντολή να λειτουργεί.
Ο Tracy Syrstad και εγώ γράψαμε VBA και Macros Microsoft Excel 2016 προκειμένου να σας βοηθήσουμε να κατανοήσετε τους περιορισμούς του εγγεγραμμένου κώδικα και να σας διδάξουμε πώς να διορθώσετε τον εγγεγραμμένο κώδικα σε κάτι που θα λειτουργεί κάθε φορά.
Στην εκπομπή, έδειξα τη διαδικασία εγγραφής μιας μακροεντολής που θα λειτουργήσει τέλεια αν ξέρετε πώς να χρησιμοποιήσετε το κουμπί Σχετική αναφορά. Κάποιος μου είχε δώσει ένα φύλλο εργασίας του Excel με εκατοντάδες ονόματα και διευθύνσεις. Ήθελαν να στέλνουν ετικέτες. Εάν έχετε χρησιμοποιήσει ποτέ τη δυνατότητα συγχώνευσης αλληλογραφίας Microsoft Word, γνωρίζετε ότι χρειάζεστε κάθε πεδίο σε μια νέα στήλη στο φύλλο εργασίας. Αντ 'αυτού, το συγκεκριμένο φύλλο εργασίας είχε τα δεδομένα να κατεβαίνουν στη στήλη Α.
Η διαδικασία για τη διόρθωση μιας μόνο ετικέτας είναι αρκετά κουραστική.
- Ξεκινήστε με το δείκτη κελιού στο όνομα στη στήλη A.
- Πατήστε το κάτω βέλος για να μετακινηθείτε στη διεύθυνση
- Ctrl + x για κοπή
- Πάνω βέλος, δεξί βέλος για μετακίνηση στη στήλη Β δίπλα στο όνομα
- Ctrl + v για επικόλληση
- Κάτω βέλος δύο φορές, αριστερό βέλος μία φορά για να μετακινηθείτε στην πόλη
- Ctrl + x για κοπή
- Πάνω βέλος δύο φορές, δεξί βέλος τρις
- Ctrl + v για επικόλληση
- Αριστερό βέλος δύο φορές, κάτω βέλος μία φορά για μετακίνηση στην κενή γραμμή τώρα.
- Κρατήστε πατημένο το πλήκτρο shift ενώ πατάτε το κάτω βέλος δύο φορές
- Alt + edr για να διαγράψετε τις κενές σειρές
- Πάνω και κάτω βέλος για να επιλέξετε ξανά το όνομα.
Εδώ είναι το κινούμενο σχέδιο που δείχνει αυτά τα βήματα:
Ρύθμιση του Excel για να επιτρέπονται μακροεντολές
Αν και κάθε αντίγραφο του Excel περιέχει VBA, από προεπιλογή, η Microsoft απενεργοποιεί τη δυνατότητα εκτέλεσης μακροεντολών. Πρέπει να κάνετε μια εφάπαξ προσαρμογή για να επιτρέψετε την εκτέλεση μακροεντολών. Ανοίξτε το Excel. Από το μενού, επιλέξτε Εργαλεία> Μακροεντολή> Ασφάλεια. Εάν το επίπεδο ασφάλειας μακροεντολών έχει οριστεί σε υψηλό, αλλάξτε το σε Μεσαίο.
Προετοιμασία για εγγραφή της μακροεντολής
Σκεφτείτε τα απαραίτητα βήματα για την επίτευξη της εργασίας. Θέλετε να βεβαιωθείτε ότι ξεκινάτε με το δείκτη κελιού σε ένα όνομα και τελειώνετε με το επόμενο όνομα. Αυτό θα σας επιτρέψει να εκτελείτε πολλές φορές τη μακροεντολή πολλές φορές. Όταν έχετε έτοιμα τα βήματα, ενεργοποιήστε τη συσκευή εγγραφής μακροεντολών. Από το μενού, επιλέξτε Εργαλεία> Μακροεντολή> Εγγραφή νέας μακροεντολής.
Εάν εκτελείτε αυτήν τη μακροεντολή για πολλές ημέρες, θα πρέπει να δώσετε στη μακροεντολή ένα χρήσιμο όνομα. Εάν είναι μια εφάπαξ μακροεντολή για την αυτοματοποίηση μιας μεμονωμένης εργασίας, τότε αφήνοντας το όνομα της μακροεντολής ως Macro1 είναι πιθανώς μια χαρά. Το σημαντικό πεδίο εδώ είναι το πλήκτρο συντόμευσης. Εάν δημιουργείτε μια μακροεντολή για εφάπαξ χρήση, αντιστοιχίστε τη μακροεντολή σε ένα πλήκτρο συντόμευσης, όπως το Ctrl + a - είναι πολύ εύκολο να πατήσετε Ctrl + επανειλημμένα, καθώς τα πλήκτρα είναι κοντά το ένα στο άλλο. Εάν πρόκειται να δημιουργήσετε μια μακροεντολή που θα χρησιμοποιείτε καθημερινά, τότε θέλετε να αντιστοιχίσετε τη μακροεντολή σε ένα κλειδί που δεν είναι ήδη σημαντικός συνδυασμός πλήκτρων συντόμευσης. Ctrl + j ή Ctrl + k είναι καλές επιλογές.
Οι μακροεντολές μίας χρήσης πρέπει να αποθηκεύονται στο ThisWorkbook. Εάν πρέπει να χρησιμοποιήσετε επανειλημμένα τη μακροεντολή σε πολλά διαφορετικά βιβλία εργασίας, τότε μπορείτε να αποθηκεύσετε τη μακροεντολή στο βιβλίο εργασίας προσωπικής μακροεντολής.
Τώρα σας παρουσιάζεται η πιο μικρή γραμμή εργαλείων σε όλο το Excel. τη γραμμή εργαλείων διακοπής εγγραφής. Έχει μόνο δύο εικονίδια και μοιάζει με αυτό:
Εάν αυτή η γραμμή εργαλείων σας εμποδίσει, μην πατήσετε το X για να την κλείσετε. Αντ 'αυτού, πιάστε την μπλε γραμμή και σύρετε τη γραμμή εργαλείων σε μια νέα θέση. Η πράξη μετακίνησης της γραμμής εργαλείων δεν καταγράφεται στη μακροεντολή. Εάν κλείσετε κατά λάθος τη γραμμή εργαλείων, επιστρέψτε την με Προβολή> Γραμμές εργαλείων> Διακοπή εγγραφής. Αυτή η ενέργεια, ωστόσο, θα καταγραφεί.
Το πρώτο κουμπί στη γραμμή εργαλείων είναι το κουμπί "Διακοπή εγγραφής". Αυτό είναι αυτονόητο. Όταν ολοκληρώσετε την εγγραφή της μακροεντολής, πατήστε τη γραμμή εργαλείων διακοπής εγγραφής.
Το πιο σημαντικό (και σπάνια κατανοητό) κουμπί είναι το κουμπί "Σχετική αναφορά".
Στο τρέχον παράδειγμα μας, πρέπει να πατήσετε το κουμπί Σχετική αναφορά πριν ξεκινήσετε. Εάν εγγράψετε μια μακροεντολή με ενεργοποιημένες τις σχετικές αναφορές, το Excel θα καταγράψει βήματα ως εξής:
- Μετακινήστε ένα κελί προς τα κάτω
- Κόψτε το τρέχον κελί
- Μετακινήστε ένα κελί προς τα πάνω
- Μετακινήστε ένα κελί δεξιά
- Επικόλληση
- και τα λοιπά.
Αν αντ 'αυτού καταγράψατε τη μακροεντολή χωρίς σχετικές αναφορές, η μακροεντολή θα καταγράφει τα εξής βήματα:
- Μετακίνηση στο κελί A4
- Κόψτε το κελί A4
- Μετακίνηση στο κελί A3
- Μετακίνηση στο κελί B3
- Επικόλληση στο κελί B3
- και τα λοιπά.
Αυτό θα ήταν τρομερά λάθος - χρειαζόμαστε τη μακροεντολή για να εργαζόμαστε σε κελιά που είναι 1 και 2 κελιά από το σημείο εκκίνησης της μακροεντολής. Καθώς εκτελείτε τη μακροεντολή ξανά και ξανά, το σημείο εκκίνησης θα είναι η σειρά 4, η σειρά 5, η σειρά 6 κ.λπ. Η εγγραφή της μακροεντολής χωρίς σχετικές αναφορές θα ενεργοποιήσει τη μακροεντολή να εκτελείται πάντα στη γραμμή 3 ως σημείο εκκίνησης.
Ακολουθήστε αυτά τα βήματα:
- Επιλέξτε το σχετικό κουμπί αναφοράς στη γραμμή εργαλείων διακοπής εγγραφής
- Ο δείκτης κελιού πρέπει να βρίσκεται ήδη σε ένα όνομα στη στήλη A.
- Πατήστε το κάτω βέλος για να μετακινηθείτε στη διεύθυνση
- Ctrl + x για κοπή
- Πάνω βέλος, δεξί βέλος για μετακίνηση στη στήλη Β δίπλα στο όνομα
- Ctrl + v για επικόλληση
- Κάτω βέλος δύο φορές, αριστερό βέλος μία φορά για να μετακινηθείτε στην πόλη
- Ctrl + x για κοπή
- Πάνω βέλος δύο φορές, δεξί βέλος τρις
- Ctrl + v για επικόλληση
- Αριστερό βέλος δύο φορές, κάτω βέλος μία φορά για μετακίνηση στην κενή γραμμή τώρα.
- Κρατήστε πατημένο το πλήκτρο shift ενώ πατάτε το κάτω βέλος δύο φορές
- Alt + edr για να διαγράψετε τις κενές σειρές
- Πάνω και κάτω βέλος για να επιλέξετε το επόμενο όνομα στη λίστα.
- Πατήστε τη γραμμή εργαλείων Διακοπή εγγραφής
- Αποθηκεύστε το βιβλίο εργασίας
- Δοκιμάστε τη μακροεντολή πατώντας το πλήκτρο συντόμευσης που εκχωρήθηκε παραπάνω. Θα πρέπει να διορθώσει τέλεια το επόμενο όνομα στη λίστα
Μόλις δείτε ότι η μακροεντολή λειτουργεί όπως επιθυμείτε, μπορείτε να κρατήσετε πατημένο το Ctrl + a για να διορθώσετε γρήγορα μερικά ονόματα ανά δευτερόλεπτο. Ολόκληρη η λίστα των 500 ονομάτων μπορεί να διορθωθεί σε ένα ή δύο λεπτά.
Συμβουλή μπόνους - Όχι στην εκπομπή
Μπορείτε εύκολα να τυλίξετε τη μακροεντολή σε έναν απλό βρόχο για να το διορθώσετε και τα 500 ονόματα χωρίς να χρειάζεται να πατήσετε Ctrl + μερικές εκατοντάδες φορές.
Πατήστε το πλήκτρο Alt + F11 για να ανοίξετε το πρόγραμμα επεξεργασίας μακροεντολών.
Εάν δεν βλέπετε το παράθυρο Project - VBAProject, πατήστε Ctrl + r.
Κάντε δεξί κλικ στο Module1 και επιλέξτε Προβολή κώδικα. Θα δείτε τον κωδικό που μοιάζει με αυτό:
Τώρα, δεν χρειάζεται να καταλάβετε τι κάνει αυτός ο κώδικας, αλλά γνωρίζετε ότι θέλουμε να εκτελέσουμε τα πάντα σε αυτήν τη μακροεντολή μία φορά για κάθε όνομα που έχουμε. Αν γνωρίζετε ότι υπάρχουν 462 ονόματα, τότε μπορείτε να προσθέσετε 2 γραμμές για να εκτελέσετε τον κωδικό 462 φορές. Στην κορυφή της μακροεντολής, εισαγάγετε μια νέα γραμμή με τις λέξεις " For i = 1 to 462". Στο κάτω μέρος της μακροεντολής, εισαγάγετε μια γραμμή με την ένδειξη " Next i". Αυτό λέει στο VBA να εκτελέσει τον κώδικα μέσα σε 462 φορές.
συμπέρασμα
Μετά από αυτήν την απλή μακροεντολή, έδειξα ένα παράδειγμα στην εμφάνιση μιας πολύ περίπλοκης μακροεντολής. Αυτή η μακροεντολή δημιούργησε συγκεντρωτικούς πίνακες και γραφήματα για 46 τμήματα μιας εταιρείας. Αυτή η αναφορά διαρκεί 40 ώρες κάθε μήνα. Η μακροεντολή VBA εκτελείται και δημιουργεί και τις 46 αναφορές σε λιγότερο από 2 λεπτά.
Το βιβλίο VBA και μακροεντολές Microsoft Excel 2016 θα σας καθοδηγήσει στην καμπύλη εκμάθησης, ώστε να μπορείτε να ξεκινήσετε από την εγγραφή απλών μακροεντολών όπως αυτή έως την εκτέλεση πολύπλοκων αναφορών που μπορούν να σας εξοικονομήσουν εκατοντάδες ώρες ετησίως. Φυσικά, εάν δεν θέλετε να μάθετε VBA, προσλάβετε έναν Σύμβουλο Excel για να σχεδιάσετε μια αναφορά για εσάς.
