Όταν εργάζεστε με το Excel, ο περισσότερος χρόνος σας περνά στην περιοχή του φύλλου εργασίας - ασχολείται με κελιά και εύρη.
Και αν θέλετε να αυτοματοποιήσετε την εργασία σας στο Excel χρησιμοποιώντας VBA, πρέπει να ξέρετε πώς να εργάζεστε με κελιά και εύρη χρησιμοποιώντας VBA.
Υπάρχουν πολλά διαφορετικά πράγματα που μπορείτε να κάνετε με τα εύρη στο VBA (όπως επιλογή, αντιγραφή, μετακίνηση, επεξεργασία κ.λπ.).
Έτσι, για να καλύψω αυτό το θέμα, θα χωρίσω αυτό το σεμινάριο σε ενότητες και θα σας δείξω πώς να εργάζεστε με κελιά και εύρη στο Excel VBA χρησιμοποιώντας παραδείγματα.
Ας αρχίσουμε.
Όλοι οι κωδικοί που αναφέρω σε αυτό το σεμινάριο πρέπει να τοποθετηθούν στο VB Editor. Μεταβείτε στην ενότητα "Πού να βάλετε τον κώδικα VBA" για να μάθετε πώς λειτουργεί.Εάν ενδιαφέρεστε να μάθετε VBA με τον εύκολο τρόπο, ελέγξτε το δικό μου Online Εκπαίδευση Excel VBA.
Επιλογή κελιού / εύρους στο Excel χρησιμοποιώντας VBA
Για να εργαστείτε με κελιά και εύρη στο Excel χρησιμοποιώντας VBA, δεν χρειάζεται να το επιλέξετε.
Στις περισσότερες περιπτώσεις, είναι καλύτερο να μην επιλέξετε κελιά ή εύρη (όπως θα δούμε).
Παρ 'όλα αυτά, είναι σημαντικό να περάσετε από αυτήν την ενότητα και να καταλάβετε πώς λειτουργεί. Αυτό θα είναι ζωτικής σημασίας για τη μάθηση VBA και πολλές έννοιες που καλύπτονται εδώ θα χρησιμοποιηθούν σε όλο αυτό το σεμινάριο.
Ας ξεκινήσουμε λοιπόν με ένα πολύ απλό παράδειγμα.
Επιλογή ενός κελιού χρησιμοποιώντας VBA
Εάν θέλετε να επιλέξετε ένα κελί στο ενεργό φύλλο (π. Α1), τότε μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:
Sub SelectCell () Range ("A1"). Επιλέξτε End Sub
Ο παραπάνω κώδικας έχει τα υποχρεωτικά τμήματα "Sub" και "End Sub" και μια γραμμή κώδικα που επιλέγει το κελί A1.
Το εύρος ("A1") λέει στο VBA τη διεύθυνση του κελιού στο οποίο θέλουμε να αναφερθούμε.
Επιλέγω είναι μια μέθοδος του αντικειμένου Range και επιλέγει τα κελιά/το εύρος που καθορίζονται στο αντικείμενο Range. Οι αναφορές κελιών πρέπει να περικλείονται σε διπλά εισαγωγικά.
Αυτός ο κωδικός θα εμφανίσει σφάλμα σε περίπτωση που ένα φύλλο γραφήματος είναι ενεργό φύλλο. Ένα φύλλο γραφημάτων περιέχει γραφήματα και δεν χρησιμοποιείται ευρέως. Δεδομένου ότι δεν έχει κελιά/περιοχές, ο παραπάνω κώδικας δεν μπορεί να τον επιλέξει και θα καταλήξει να εμφανίζει σφάλμα.
Λάβετε υπόψη ότι επειδή θέλετε να επιλέξετε το κελί στο ενεργό φύλλο, πρέπει απλώς να καθορίσετε τη διεύθυνση κελιού.
Αλλά εάν θέλετε να επιλέξετε το κελί σε ένα άλλο φύλλο (ας πούμε Sheet2), πρέπει πρώτα να ενεργοποιήσετε το Sheet2 και στη συνέχεια να επιλέξετε το κελί σε αυτό.
Sub SelectCell () Φύλλα εργασίας ("Sheet2"). Ενεργοποίηση εύρους ("A1"). Επιλέξτε End Sub
Ομοίως, μπορείτε επίσης να ενεργοποιήσετε ένα βιβλίο εργασίας, στη συνέχεια να ενεργοποιήσετε ένα συγκεκριμένο φύλλο εργασίας σε αυτό και, στη συνέχεια, να επιλέξετε ένα κελί.
Sub SelectCell () Τετράδια εργασίας ("Book2.xlsx"). Φύλλα εργασίας ("Sheet2"). Ενεργοποιήστε το εύρος ("A1"). Επιλέξτε End Sub
Σημειώστε ότι όταν αναφέρεστε σε βιβλία εργασίας, πρέπει να χρησιμοποιήσετε το πλήρες όνομα μαζί με την επέκταση αρχείου (.xlsx στον παραπάνω κώδικα). Σε περίπτωση που το βιβλίο εργασίας δεν έχει αποθηκευτεί ποτέ, δεν χρειάζεται να χρησιμοποιήσετε την επέκταση αρχείου.
Τώρα, αυτά τα παραδείγματα δεν είναι πολύ χρήσιμα, αλλά θα δείτε αργότερα σε αυτό το σεμινάριο πώς μπορούμε να χρησιμοποιήσουμε τις ίδιες έννοιες για να αντιγράψουμε και να επικολλήσουμε κελιά στο Excel (χρησιμοποιώντας VBA).
Όπως επιλέγουμε ένα κελί, μπορούμε επίσης να επιλέξουμε ένα εύρος.
Σε περίπτωση εύρους, μπορεί να είναι ένα εύρος σταθερού μεγέθους ή ένα εύρος μεταβλητού μεγέθους.
Σε ένα εύρος σταθερού μεγέθους, θα γνωρίζετε πόσο μεγάλο είναι το εύρος και μπορείτε να χρησιμοποιήσετε το ακριβές μέγεθος στον κωδικό VBA. Αλλά με ένα εύρος μεταβλητού μεγέθους, δεν έχετε ιδέα πόσο μεγάλο είναι το εύρος και πρέπει να χρησιμοποιήσετε λίγο μαγικό VBA.
Ας δούμε πώς να το κάνουμε αυτό.
Επιλογή εύρους καθορισμού μεγέθους
Εδώ είναι ο κωδικός που θα επιλέξει το εύρος A1: D20.
Sub SelectRange () Range ("A1: D20"). Επιλέξτε End Sub
Ένας άλλος τρόπος για να γίνει αυτό είναι η χρήση του παρακάτω κώδικα:
Sub SelectRange () Range ("A1", "D20"). Επιλέξτε End Sub
Ο παραπάνω κώδικας παίρνει την επάνω αριστερή διεύθυνση κελιού (A1) και την κάτω δεξιά διεύθυνση κελιού (D20) και επιλέγει ολόκληρο το εύρος. Αυτή η τεχνική γίνεται χρήσιμη όταν εργάζεστε με εύρος μεταβλητού μεγέθους (όπως θα δούμε όταν η ιδιότητα Τέλος καλύπτεται αργότερα σε αυτό το σεμινάριο).
Εάν θέλετε η επιλογή να γίνει σε διαφορετικό βιβλίο εργασίας ή διαφορετικό φύλλο εργασίας, τότε πρέπει να πείτε στη VBA τα ακριβή ονόματα αυτών των αντικειμένων.
Για παράδειγμα, ο παρακάτω κώδικας θα επέλεγε το εύρος A1: D20 στο φύλλο εργασίας του Sheet2 στο βιβλίο εργασίας του Book2.
Sub SelectRange () Τετράδια εργασίας ("Book2.xlsx"). Φύλλα εργασίας ("Sheet1"). Ενεργοποιήστε το εύρος ("A1: D20"). Επιλέξτε End Sub
Τώρα, τι γίνεται αν δεν γνωρίζετε πόσες σειρές υπάρχουν. Τι γίνεται αν θέλετε να επιλέξετε όλα τα κελιά που έχουν μια τιμή σε αυτό.
Σε αυτές τις περιπτώσεις, πρέπει να χρησιμοποιήσετε τις μεθόδους που εμφανίζονται στην επόμενη ενότητα (σχετικά με την επιλογή εύρους μεταβλητού μεγέθους).
Επιλογή εύρους μεταβλητού μεγέθους
Υπάρχουν διάφοροι τρόποι με τους οποίους μπορείτε να επιλέξετε μια σειρά κελιών. Η μέθοδος που θα επιλέξετε θα εξαρτηθεί από τον τρόπο δομής των δεδομένων.
Σε αυτήν την ενότητα, θα καλύψω μερικές χρήσιμες τεχνικές που είναι πραγματικά χρήσιμες όταν εργάζεστε με σειρές στο VBA.
Επιλέξτε Χρήση της ιδιότητας CurrentRange
Σε περιπτώσεις όπου δεν γνωρίζετε πόσες γραμμές/στήλες έχουν τα δεδομένα, μπορείτε να χρησιμοποιήσετε την ιδιότητα CurrentRange του αντικειμένου Εύρος.
Η ιδιότητα CurrentRange καλύπτει όλα τα συνεχόμενα γεμάτα κελιά σε μια περιοχή δεδομένων.
Παρακάτω είναι ο κώδικας που θα επιλέξει την τρέχουσα περιοχή που περιέχει το κελί A1.
Sub SelectCurrentRegion () Range () A ("A1"). CurrentRegion.Select End Sub
Η παραπάνω μέθοδος είναι καλή όταν έχετε όλα τα δεδομένα ως πίνακα χωρίς κενές γραμμές/στήλες.
Σε περίπτωση όμως που έχετε κενές γραμμές/στήλες στα δεδομένα σας, δεν θα επιλέξει αυτές μετά τις κενές γραμμές/στήλες. Στην παρακάτω εικόνα, ο κώδικας CurrentRegion επιλέγει δεδομένα έως τη σειρά 10 καθώς η γραμμή 11 είναι κενή.
Σε τέτοιες περιπτώσεις, μπορεί να θέλετε να χρησιμοποιήσετε την ιδιότητα UsedRange του αντικειμένου φύλλου εργασίας.
Επιλέξτε Χρησιμοποιώντας την ιδιότητα UsedRange
Το UsedRange σάς επιτρέπει να παραπέμπετε σε τυχόν κελιά που έχουν αλλάξει.
Έτσι, ο παρακάτω κώδικας θα επέλεγε όλα τα χρησιμοποιημένα κελιά στο ενεργό φύλλο.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Σημειώστε ότι σε περίπτωση που έχετε χρησιμοποιήσει ένα μακρινό κελί, θα ληφθεί υπόψη από τον παραπάνω κώδικα και θα επιλεγούν όλα τα κελιά μέχρι να χρησιμοποιηθεί αυτό το κελί.
Επιλέξτε Χρήση της ιδιότητας Τέλους
Τώρα, αυτό το μέρος είναι πραγματικά χρήσιμο.
Η ιδιότητα Τέλος σάς επιτρέπει να επιλέξετε το τελευταίο γεμάτο κελί. Αυτό σας επιτρέπει να μιμηθείτε την επίδραση του πλήκτρου ελέγχου κάτω/επάνω βέλους ή ελέγχου δεξιού/αριστερού πλήκτρου.
Ας προσπαθήσουμε να το καταλάβουμε χρησιμοποιώντας ένα παράδειγμα.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να επιλέξετε γρήγορα τα τελευταία συμπληρωμένα κελιά στη στήλη Α.
Το πρόβλημα εδώ είναι ότι τα δεδομένα μπορούν να αλλάξουν και δεν γνωρίζετε πόσα κελιά έχουν γεμίσει. Εάν πρέπει να το κάνετε αυτό χρησιμοποιώντας πληκτρολόγιο, μπορείτε να επιλέξετε το κελί Α1 και, στη συνέχεια, να χρησιμοποιήσετε το πλήκτρο Control + κάτω βέλος και θα επιλέξει το τελευταίο κελί που έχει συμπληρωθεί στη στήλη.
Τώρα ας δούμε πώς να το κάνουμε αυτό χρησιμοποιώντας το VBA. Αυτή η τεχνική είναι χρήσιμη όταν θέλετε να μεταβείτε γρήγορα στο τελευταίο γεμάτο κελί σε μια στήλη μεταβλητού μεγέθους
Sub GoToLastFilledCell () Range () A ("A1"). End (xlDown). Επιλέξτε End Sub
Ο παραπάνω κώδικας θα μεταπηδήσει στο τελευταίο κελί της στήλης Α.
Ομοίως, μπορείτε να χρησιμοποιήσετε το Τέλος (xlToRight) για να μεταβείτε στο τελευταίο πλήρες κελί στη σειρά.
Sub GoToLastFilledCell () Range ("A1"). End (xlToRight). Επιλέξτε End Sub
Τώρα, τι γίνεται αν θέλετε να επιλέξετε ολόκληρη τη στήλη αντί να μεταβείτε στο τελευταίο γεμάτο κελί.
Μπορείτε να το κάνετε χρησιμοποιώντας τον παρακάτω κώδικα:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Επιλέξτε End Sub
Στον παραπάνω κώδικα, χρησιμοποιήσαμε την πρώτη και την τελευταία αναφορά του κελιού που πρέπει να επιλέξουμε. Ανεξάρτητα από το πόσα γεμάτα κελιά υπάρχουν, ο παραπάνω κώδικας θα επιλέξει όλα.
Θυμηθείτε το παραπάνω παράδειγμα όπου επιλέξαμε το εύρος A1: D20 χρησιμοποιώντας την ακόλουθη γραμμή κώδικα:
Εύρος ("A1 ″," D20 ")
Εδώ το A1 ήταν το επάνω αριστερό κελί και το D20 ήταν το κάτω δεξιά κελί της περιοχής. Μπορούμε να χρησιμοποιήσουμε την ίδια λογική στην επιλογή εύρους μεταβλητού μεγέθους. Αλλά επειδή δεν γνωρίζουμε την ακριβή διεύθυνση του κάτω δεξιού κελιού, χρησιμοποιήσαμε την ιδιότητα Τέλος για να την αποκτήσουμε.
Στο εύρος ("A1", Range ("A1"). End (xlDown)), "A1" αναφέρεται στο πρώτο κελί και Range ("A1"). Το τέλος (xlDown) αναφέρεται στο τελευταίο κελί. Δεδομένου ότι έχουμε παράσχει και τις δύο αναφορές, η μέθοδος Select επιλέγει όλα τα κελιά μεταξύ αυτών των δύο αναφορών.
Ομοίως, μπορείτε επίσης να επιλέξετε ένα ολόκληρο σύνολο δεδομένων που έχει πολλές σειρές και στήλες.
Ο παρακάτω κώδικας θα επιλέξει όλες τις συμπληρωμένες γραμμές/στήλες ξεκινώντας από το κελί Α1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Επιλέξτε End Sub
Στον παραπάνω κώδικα, χρησιμοποιήσαμε το εύρος ("A1"). Τέλος (xlDown). Τέλος (xlToRight) για να λάβετε την αναφορά του κάτω δεξιά κελιού που έχει συμπληρωθεί από το σύνολο δεδομένων.
Διαφορά μεταξύ της χρήσης CurrentRegion και End
Αν αναρωτιέστε γιατί να χρησιμοποιήσετε την ιδιότητα Τέλος για να επιλέξετε το πλήρες εύρος όταν έχουμε την ιδιότητα CurrentRegion, επιτρέψτε μου να σας πω τη διαφορά.
Με την ιδιότητα Τέλος, μπορείτε να καθορίσετε το κελί έναρξης. Για παράδειγμα, εάν έχετε τα δεδομένα σας στο A1: D20, αλλά η πρώτη σειρά είναι κεφαλίδες, μπορείτε να χρησιμοποιήσετε την ιδιότητα Τέλος για να επιλέξετε τα δεδομένα χωρίς τις κεφαλίδες (χρησιμοποιώντας τον παρακάτω κώδικα).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown). End (xlToRight)). Επιλέξτε End Sub
Αλλά το CurrentRegion θα επιλέξει αυτόματα ολόκληρο το σύνολο δεδομένων, συμπεριλαμβανομένων των κεφαλίδων.
Μέχρι τώρα σε αυτό το σεμινάριο, έχουμε δει πώς να παραπέμπουμε σε μια σειρά κελιών χρησιμοποιώντας διαφορετικούς τρόπους.
Τώρα ας δούμε μερικούς τρόπους με τους οποίους μπορούμε πραγματικά να χρησιμοποιήσουμε αυτές τις τεχνικές για να κάνουμε κάποια δουλειά.
Αντιγράψτε κελιά / εύρη χρησιμοποιώντας VBA
Όπως ανέφερα στην αρχή αυτού του σεμιναρίου, η επιλογή ενός κελιού δεν είναι απαραίτητη για την εκτέλεση ενεργειών σε αυτό. Σε αυτήν την ενότητα θα δείτε πώς να αντιγράφετε κελιά και εύρη χωρίς καν να τα επιλέξετε.
Ας ξεκινήσουμε με ένα απλό παράδειγμα.
Αντιγραφή ενιαίου κελιού
Εάν θέλετε να αντιγράψετε το κελί A1 και να το επικολλήσετε στο κελί D1, θα το έκανε ο παρακάτω κώδικας.
Sub CopyCell () Range ("A1"). Copy Range ("D1") End Sub
Σημειώστε ότι η μέθοδος αντιγραφής του αντικειμένου περιοχής αντιγράφει το κελί (ακριβώς όπως το Control +C) και το επικολλά στον καθορισμένο προορισμό.
Στο παραπάνω παράδειγμα κώδικα, ο προορισμός καθορίζεται στην ίδια γραμμή όπου χρησιμοποιείτε τη μέθοδο Αντιγραφή. Εάν θέλετε να κάνετε τον κωδικό σας ακόμη πιο ευανάγνωστο, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:
Sub CopyCell () Range ("A1"). Copy Destination: = Range ("D1") End Sub
Οι παραπάνω κωδικοί θα αντιγράψουν και θα επικολλήσουν την τιμή καθώς και τη μορφοποίηση/τύπους σε αυτήν.
Όπως ίσως έχετε ήδη παρατηρήσει, ο παραπάνω κώδικας αντιγράφει το κελί χωρίς να το επιλέξετε. Ανεξάρτητα από το πού βρίσκεστε στο φύλλο εργασίας, ο κώδικας θα αντιγράψει το κελί A1 και θα το επικολλήσει στο D1.
Επίσης, σημειώστε ότι ο παραπάνω κώδικας θα αντικαταστήσει κάθε υπάρχοντα κώδικα στο κελί D2. Εάν θέλετε το Excel να σας ενημερώσει εάν υπάρχει ήδη κάτι στο κελί D1 χωρίς να το αντικαταστήσετε, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα.
Sub CopyCell () If Range ("D1") "" Τότε Response = MsgBox ("Θέλετε να αντικαταστήσετε τα υπάρχοντα δεδομένα", vbYesNo) Τερματισμός Αν Αν Response = vbYes Τότε Εύρος ("A1"). Αντιγραφή Range ("D1 ") End If End Sub
Αντιγραφή εύρους μεγέθους διόρθωσης
Εάν θέλετε να αντιγράψετε το A1: D20 σε J1: M20, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:
Sub CopyRange () Range ("A1: D20"). Copy Range ("J1") End Sub
Στο κελί προορισμού, πρέπει απλώς να καθορίσετε τη διεύθυνση του επάνω αριστερού κελιού. Ο κωδικός θα αντιγράψει αυτόματα το ακριβές εύρος αντιγραφής στον προορισμό.
Μπορείτε να χρησιμοποιήσετε την ίδια κατασκευή για να αντιγράψετε δεδομένα από το ένα φύλλο στο άλλο.
Ο παρακάτω κώδικας θα αντιγράψει το A1: D20 από το ενεργό φύλλο στο Sheet2.
Sub CopyRange () Range ("A1: D20"). Copy φύλλα εργασίας ("Sheet2"). Range ("A1") End Sub
Τα παραπάνω αντιγράφουν τα δεδομένα από το ενεργό φύλλο. Βεβαιωθείτε λοιπόν ότι το φύλλο που περιέχει τα δεδομένα είναι το ενεργό φύλλο πριν εκτελέσετε τον κώδικα. Για να είστε ασφαλείς, μπορείτε επίσης να καθορίσετε το όνομα του φύλλου εργασίας κατά την αντιγραφή των δεδομένων.
Sub CopyRange () Φύλλα εργασίας ("Sheet1"). Range ("A1: D20"). Αντιγραφή φύλλων εργασίας ("Sheet2"). Range ("A1") End Sub
Το καλό με τον παραπάνω κώδικα είναι ότι ανεξάρτητα από το ποιο φύλλο είναι ενεργό, θα αντιγράφει πάντα τα δεδομένα από το Sheet1 και θα τα επικολλά στο Sheet2.
Μπορείτε επίσης να αντιγράψετε μια ονομαστική περιοχή χρησιμοποιώντας το όνομά της αντί για την αναφορά.
Για παράδειγμα, εάν έχετε ένα εύρος με όνομα "SalesData", μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα για να αντιγράψετε αυτά τα δεδομένα στο Sheet2.
Sub CopyRange () Range ("SalesData"). Αντιγραφή φύλλων εργασίας ("Sheet2"). Range ("A1") End Sub
Εάν το εύρος της ονομαζόμενης περιοχής είναι ολόκληρο το βιβλίο εργασίας, δεν χρειάζεται να είστε στο φύλλο που έχει το εύρος ονόματος για να εκτελέσετε αυτόν τον κώδικα. Δεδομένου ότι το εύρος που ονομάζεται είναι περιορισμένο για το βιβλίο εργασίας, μπορείτε να έχετε πρόσβαση σε αυτό από οποιοδήποτε φύλλο χρησιμοποιώντας αυτόν τον κώδικα.
Εάν έχετε έναν πίνακα με το όνομα Table1, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα για να τον αντιγράψετε στο Sheet2.
Sub CopyTable () Range ("Table1 [#All]"). Copy worksheets ("Sheet2"). Range ("A1") End Sub
Μπορείτε επίσης να αντιγράψετε ένα εύρος σε άλλο βιβλίο εργασίας.
Στο ακόλουθο παράδειγμα, αντιγράφω τον πίνακα Excel (Πίνακας 1), στο βιβλίο εργασίας Book2.
Sub CopyCurrentRegion () Range ("Table1 [#All]"). Copy Workbooks ("Book2.xlsx"). Worksheets ("Sheet1"). Range ("A1") End Sub
Αυτός ο κωδικός θα λειτουργούσε μόνο εάν το βιβλίο εργασίας είναι ήδη ανοιχτό.
Αντιγραφή εύρους μεταβλητού μεγέθους
Ένας τρόπος αντιγραφής εύρους μεταβλητού μεγέθους είναι η μετατροπή αυτών σε ονομαζόμενες περιοχές ή πίνακα Excel και η χρήση των κωδικών όπως φαίνεται στην προηγούμενη ενότητα.
Αλλά αν δεν μπορείτε να το κάνετε αυτό, μπορείτε να χρησιμοποιήσετε την τρέχουσα περιοχή ή την ιδιότητα Τέλος του αντικειμένου εύρους.
Ο παρακάτω κώδικας θα αντιγράψει την τρέχουσα περιοχή στο ενεργό φύλλο και θα την επικολλήσει στο Φύλλο2.
Sub CopyCurrentRegion () Range ("A1"). CurrentRegion.Copy Worksheets ("Sheet2"). Range ("A1") End Sub
Αν θέλετε να αντιγράψετε την πρώτη στήλη του συνόλου δεδομένων σας μέχρι το τελευταίο κελί και να την επικολλήσετε στο Sheet2, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Αντιγραφή φύλλων εργασίας ("Sheet2"). Range ("A1") End Sub
Εάν θέλετε να αντιγράψετε τις γραμμές καθώς και τις στήλες, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα:
Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Αντιγραφή φύλλων εργασίας ("Sheet2"). Range ("A1") End Sub
Σημειώστε ότι όλοι αυτοί οι κωδικοί δεν επιλέγουν τα κελιά κατά την εκτέλεσή τους. Σε γενικές γραμμές, θα βρείτε λίγες μόνο περιπτώσεις όπου πρέπει πραγματικά να επιλέξετε ένα κελί/εύρος πριν εργαστείτε σε αυτό.
Εκχώρηση εύρους σε μεταβλητές αντικειμένων
Μέχρι τώρα, χρησιμοποιούσαμε την πλήρη διεύθυνση των κελιών (όπως βιβλία εργασίας ("Book2.xlsx"). Φύλλα εργασίας ("Sheet1"). Εύρος ("A1")).
Για να κάνετε τον κωδικό σας πιο διαχειρίσιμο, μπορείτε να αντιστοιχίσετε αυτά τα εύρη σε μεταβλητές αντικειμένων και στη συνέχεια να χρησιμοποιήσετε αυτές τις μεταβλητές.
Για παράδειγμα, στον παρακάτω κώδικα, έχω εκχωρήσει το εύρος προέλευσης και προορισμού σε μεταβλητές αντικειμένων και στη συνέχεια χρησιμοποίησα αυτές τις μεταβλητές για να αντιγράψω δεδομένα από το ένα εύρος στο άλλο.
Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Φύλλα εργασίας ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Τέλος υπο
Ξεκινάμε δηλώνοντας τις μεταβλητές ως αντικείμενα εύρους. Στη συνέχεια, εκχωρούμε το εύρος σε αυτές τις μεταβλητές χρησιμοποιώντας την εντολή Set. Μόλις εκχωρηθεί το εύρος στη μεταβλητή, μπορείτε απλά να χρησιμοποιήσετε τη μεταβλητή.
Εισαγάγετε δεδομένα στο επόμενο κενό κελί (χρησιμοποιώντας πλαίσιο εισαγωγής)
Μπορείτε να χρησιμοποιήσετε τα πλαίσια εισαγωγής για να επιτρέψετε στο χρήστη να εισάγει τα δεδομένα.
Για παράδειγμα, ας υποθέσουμε ότι έχετε το σύνολο δεδομένων παρακάτω και θέλετε να εισαγάγετε το αρχείο πωλήσεων, μπορείτε να χρησιμοποιήσετε το πλαίσιο εισαγωγής στο VBA. Χρησιμοποιώντας έναν κωδικό, μπορούμε να βεβαιωθούμε ότι συμπληρώνει τα δεδομένα στην επόμενη κενή γραμμή.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown). Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Ορισμός ποσού = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Κατηγορία προϊόντος") Ποσότητα.Αξία = InboxBox ("Ποσότητα") Amount.Value = InputBox ("Ποσό") End Sub
Ο παραπάνω κώδικας χρησιμοποιεί το πλαίσιο εισαγωγής VBA για να λάβει τις εισόδους από το χρήστη και, στη συνέχεια, εισάγει τις εισόδους στα καθορισμένα κελιά.
Λάβετε υπόψη ότι δεν χρησιμοποιήσαμε ακριβείς αναφορές κελιών. Αντ 'αυτού, χρησιμοποιήσαμε την ιδιότητα Τέλος και Μετατόπιση για να βρούμε το τελευταίο κενό κελί και να συμπληρώσουμε τα δεδομένα σε αυτό.
Αυτός ο κωδικός δεν είναι καθόλου χρήσιμος. Για παράδειγμα, εάν εισαγάγετε μια συμβολοσειρά κειμένου όταν το πλαίσιο εισαγωγής ζητά ποσότητα ή ποσό, θα παρατηρήσετε ότι το Excel το επιτρέπει. Μπορείτε να χρησιμοποιήσετε μια συνθήκη If για να ελέγξετε εάν η τιμή είναι αριθμητική ή όχι και, στη συνέχεια, να την επιτρέψετε ανάλογα.
Looping Through Cells / Ranges
Μέχρι τώρα έχουμε δει πώς να επιλέγουμε, να αντιγράφουμε και να εισάγουμε τα δεδομένα σε κελιά και εύρη.
Σε αυτήν την ενότητα, θα δούμε πώς να κάνουμε βρόχο σε ένα σύνολο κελιών/γραμμών/στηλών σε μια περιοχή. Αυτό θα μπορούσε να είναι χρήσιμο όταν θέλετε να αναλύσετε κάθε κελί και να εκτελέσετε κάποια ενέργεια με βάση αυτό.
Για παράδειγμα, εάν θέλετε να επισημάνετε κάθε τρίτη σειρά στην επιλογή, τότε πρέπει να κάνετε βρόχο και να ελέγξετε τον αριθμό γραμμής. Ομοίως, εάν θέλετε να επισημάνετε όλα τα αρνητικά κελιά αλλάζοντας το χρώμα της γραμματοσειράς σε κόκκινο, πρέπει να κάνετε μια αναζήτηση και να αναλύσετε την τιμή κάθε κελιού.
Ακολουθεί ο κώδικας που θα βολέψει τις γραμμές στα επιλεγμένα κελιά και θα επισημάνει εναλλακτικές σειρές.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Επιλογή για κάθε Myrow στο Myrange.Rows If Myrow.Row Mod 2 = 0 Στη συνέχεια Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Ο παραπάνω κώδικας χρησιμοποιεί τη λειτουργία MOD για να ελέγξει τον αριθμό σειράς στην επιλογή. Εάν ο αριθμός σειράς είναι ζυγός, τονίζεται με κυανό χρώμα.
Ακολουθεί ένα άλλο παράδειγμα όπου ο κώδικας περνά από κάθε κελί και επισημαίνει τα κελιά που έχουν αρνητική τιμή σε αυτό.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Επιλογή για κάθε Mycell In Myrange If Mycell <0 Τότε Mycell.Interior.Color = vbRed Τέλος Αν Επόμενο Mycell End Sub
Σημειώστε ότι μπορείτε να κάνετε το ίδιο πράγμα χρησιμοποιώντας τη μορφοποίηση υπό όρους (η οποία είναι δυναμική και είναι καλύτερος τρόπος για να το κάνετε αυτό). Αυτό το παράδειγμα προορίζεται μόνο για να σας δείξει πώς λειτουργεί ο κύκλος με κελιά και εύρη στο VBA.
Πού να τοποθετήσετε τον κωδικό VBA
Αναρωτιέστε πού πηγαίνει ο κώδικας VBA στο βιβλίο εργασίας του Excel;
Το Excel έχει ένα backend VBA που ονομάζεται VBA editor. Πρέπει να αντιγράψετε και να επικολλήσετε τον κώδικα στο παράθυρο κωδικού της λειτουργικής μονάδας VB Editor.
Ακολουθούν τα βήματα για να το κάνετε αυτό:
- Μεταβείτε στην καρτέλα Προγραμματιστής.
- Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
- Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα.Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
- Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
- Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.
