Φύλλα VBA - Ο απόλυτος οδηγός

Αυτός είναι ο απόλυτος οδηγός για την εργασία με Φύλλα / Φύλλα εργασίας στο Excel.

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

Φύλλα Vs. Φύλλα εργασίας

Υπάρχουν δύο τρόποι αναφοράς Φύλλα χρησιμοποιώντας VBA. Το πρώτο είναι με το αντικείμενο Φύλλα:

1 Φύλλα ("Φύλλο1"). Ενεργοποιήστε

Το άλλο είναι με το αντικείμενο φύλλα εργασίας:

1 Φύλλα εργασίας ("Φύλλο1"). Ενεργοποιήστε

99% των περιπτώσεων, αυτά τα δύο αντικείμενα είναι πανομοιότυπα. Στην πραγματικότητα, αν έχετε αναζητήσει διαδικτυακά παραδείγματα κώδικα VBA, πιθανότατα έχετε δει και τα δύο αντικείμενα να χρησιμοποιούνται. Εδώ είναι η διαφορά:

Η συλλογή φύλλων περιέχει φύλλα εργασίας ΚΑΙ φύλλα γραφήματος.

Χρησιμοποιήστε λοιπόν Φύλλα αν θέλετε να συμπεριλάβετε κανονικά Φύλλα εργασίας ΚΑΙ Φύλλα γραφήματος. Χρησιμοποιήστε φύλλα εργασίας εάν θέλετε να εξαιρέσετε τα φύλλα γραφήματος. Για το υπόλοιπο αυτού του οδηγού θα χρησιμοποιήσουμε τα Φύλλα και τα Φύλλα Εργασίας εναλλακτικά.

Φύλλα αναφοράς

Υπάρχουν διάφοροι τρόποι αναφοράς των Φύλλων:

  • ActiveSheet
  • Όνομα καρτέλας φύλλου
  • Αριθμός ευρετηρίου φύλλων
  • Κωδικός ονόματος φύλλου

ActiveSheet

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

1 MsgBox ActiveSheet.Name

Όνομα φύλλου

Probablyσως είστε πιο εξοικειωμένοι με την αναφορά των Φύλλων με το Όνομα καρτέλας τους:

1 Φύλλα ("Όνομα καρτέλας"). Ενεργοποιήστε

Αριθμός ευρετηρίου φύλλων

Ο αριθμός ευρετηρίου φύλλων είναι η θέση του φύλλου στο βιβλίο εργασίας. 1 είναι το πρώτο φύλλο. 2 είναι το δεύτερο φύλλο κλπ .:

1 Φύλλα (1). Ενεργοποιήστε

Αριθμός ευρετηρίου φύλλων - Τελευταίο φύλλο στο τετράδιο εργασίας

Για να αναφέρετε το τελευταίο φύλλο στο βιβλίο εργασίας, χρησιμοποιήστε τα φύλλα. Υπολογισμός για να λάβετε τον τελευταίο αριθμό ευρετηρίου:

1 Φύλλα (Sheets.Count). Ενεργοποιήστε

Φύλλο "Κωδικό όνομα"

Το όνομα κώδικα φύλλου είναι το όνομα του αντικειμένου στο VBA:

1 Κωδικό όνομα. Ενεργοποιήστε

Αναφορά φύλλων σε άλλα βιβλία εργασίας

Είναι επίσης εύκολο να αναφέρετε τα Φύλλα σε άλλα βιβλία εργασίας. Για να το κάνετε αυτό, πρέπει να χρησιμοποιήσετε το αντικείμενο βιβλίων εργασίας:

1 Βιβλία εργασίας ("VBA_Examples.xlsm"). Φύλλα εργασίας ("Φύλλο1"). Ενεργοποίηση

Σπουδαίος: Το Βιβλίο εργασίας πρέπει να είναι ανοιχτό προτού αναφέρετε τα Φύλλα του.

Ενεργοποίηση έναντι επιλογής φύλλου

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

Όταν ενεργοποιείτε ένα φύλλο γίνεται το ActiveSheet. Αυτό είναι το φύλλο που θα βλέπατε αν κοιτούσατε το πρόγραμμα Excel σας. Μόνο ένα φύλλο μπορεί να ενεργοποιηθεί κάθε φορά.

Ενεργοποιήστε ένα φύλλο

1 Φύλλα ("Φύλλο1"). Ενεργοποιήστε

Όταν επιλέγετε ένα φύλλο, γίνεται επίσης το ActiveSheet. Ωστόσο, μπορείτε να επιλέξετε πολλά φύλλα ταυτόχρονα. Όταν επιλέγονται πολλά φύλλα ταυτόχρονα, το "επάνω" φύλλο είναι το ActiveSheet. Ωστόσο, μπορείτε να αλλάξετε το ActiveSheet μέσα σε επιλεγμένα φύλλα.

Επιλέξτε ένα φύλλο

1 Φύλλα ("Φύλλο1"). Επιλέξτε

Επιλέξτε Πολλαπλά Φύλλα

Χρησιμοποιήστε έναν πίνακα για να επιλέξετε πολλά φύλλα ταυτόχρονα:

1 Φύλλα εργασίας (Array ("Sheet2", "Sheet3")). Επιλέξτε

Μεταβλητή φύλλου εργασίας

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

Για να δηλώσετε μια μεταβλητή φύλλου εργασίας:

1 Dim ws ως φύλλο εργασίας

Εκχωρήστε ένα φύλλο εργασίας σε μια μεταβλητή:

1 Ορισμός ws = Φύλλα ("Φύλλο1")

Τώρα μπορείτε να αναφέρετε τη μεταβλητή φύλλου εργασίας στον κώδικά σας:

1 ws. Ενεργοποιήστε

Κάντε αναζήτηση σε όλα τα φύλλα στο βιβλίο εργασίας

Οι μεταβλητές του φύλλου εργασίας είναι ουσιαστικές όταν θέλετε να περιηγηθείτε σε όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας. Ο ευκολότερος τρόπος για να γίνει αυτό είναι:

12345 Dim ws ως φύλλο εργασίαςΓια κάθε ws σε φύλλα εργασίαςMsgBox ws.nameΕπόμενο ws

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

Προστασία φύλλου εργασίας

Προστασία βιβλίου εργασίας

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

Μπορείτε να ενεργοποιήσετε την προστασία του βιβλίου εργασίας χρησιμοποιώντας το VBA:

1 ActiveWorkbook.Protect Password: = "Password"

ή απενεργοποιήστε την προστασία του βιβλίου εργασίας:

1 ActiveWorkbook.UnProtect Password: = "Κωδικός πρόσβασης"

Σημείωση: Μπορείτε επίσης να προστατεύσετε / καταργήσετε την προστασία χωρίς κωδικό πρόσβασης παραλείποντας το όρισμα Κωδικός πρόσβασης:

1 ActiveWorkbook.Protect

Προστασία φύλλου εργασίας

Η προστασία σε επίπεδο φύλλου εργασίας αποτρέπει αλλαγές σε μεμονωμένα φύλλα εργασίας.

Προστατέψτε το φύλλο εργασίας

1 Φύλλα εργασίας ("Sheet1"). Προστατέψτε το "Password"

Μη προστατευμένο φύλλο εργασίας

1 Φύλλα εργασίας ("Φύλλο1"). Απροστασία "Κωδικός πρόσβασης"

Υπάρχει μια ποικιλία επιλογών κατά την προστασία των φύλλων εργασίας (επιτρέψτε αλλαγές μορφοποίησης, επιτρέψτε στον χρήστη να εισάγει σειρές κ.λπ.) Συνιστούμε να χρησιμοποιήσετε τη συσκευή εγγραφής μακροεντολών για να καταγράψετε τις επιθυμητές ρυθμίσεις.

Συζητάμε λεπτομερέστερα την προστασία του φύλλου εργασίας εδώ.

Φύλλο εργασίας Ορατή ιδιότητα

Alreadyσως γνωρίζετε ήδη ότι τα φύλλα εργασίας μπορούν να κρυφτούν:

Υπάρχουν στην πραγματικότητα τρεις ρυθμίσεις ορατότητας φύλλου εργασίας: Ορατή, Κρυφή και Πολύ Κρυμμένο.Τα κρυμμένα φύλλα μπορούν να αποκρυφτούν από οποιονδήποτε κανονικό χρήστη του Excel - κάνοντας δεξί κλικ στην περιοχή καρτέλας φύλλου εργασίας (φαίνεται παραπάνω). Τα πολύ κρυμμένα φύλλα μπορούν να αποκρυφτούν μόνο με κωδικό VBA ή από τον επεξεργαστή VBA. Χρησιμοποιήστε τα ακόλουθα παραδείγματα κώδικα για να αποκρύψετε / αποκρύψετε φύλλα εργασίας:

Αποκάλυψη φύλλου εργασίας

1 Φύλλα εργασίας ("Φύλλο1"). Ορατό = xlSheetVisible

Απόκρυψη φύλλου εργασίας

1 Φύλλα εργασίας ("Φύλλο1"). Ορατό = xlSheetHidden

Πολύ απόκρυψη φύλλου εργασίας

1 Φύλλα εργασίας ("Sheet1"). Visible = xlSheetVeryHidden

Εκδηλώσεις σε επίπεδο φύλλου εργασίας

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

Οι διαδικασίες εκδήλωσης φύλλου εργασίας πρέπει να τοποθετηθούν σε μια ενότητα φύλλου εργασίας:

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

Φύλλο εργασίας Ενεργοποίηση συμβάντος

Το φύλλο εργασίας ενεργοποιεί γεγονότα κάθε φορά που ανοίγει το φύλλο εργασίας.

123 Private Sub Worksheet_Activate ()Εύρος ("A1"). ΕπιλέξτεΤέλος υπο

Αυτός ο κωδικός θα επιλέξει το κελί A1 (επαναφορά της περιοχής προβολής στο επάνω αριστερό μέρος του φύλλου εργασίας) κάθε φορά που ανοίγει το φύλλο εργασίας.

Συμβάν αλλαγής φύλλου εργασίας

Τα συμβάντα αλλαγής φύλλου εργασίας εκτελούνται κάθε φορά που αλλάζει μια τιμή κελιού στο φύλλο εργασίας. Διαβάστε το σεμινάριό μας σχετικά με τα συμβάντα αλλαγής φύλλου εργασίας για περισσότερες πληροφορίες.

Φύλλο εργασίας Cheat Sheet

Παρακάτω θα βρείτε ένα φύλλο εξαπάτησης που περιέχει κοινά παραδείγματα κώδικα για εργασία με φύλλα σε VBA

Φύλλα εργασίας VBA Cheatsheet

Φύλλα εργασίας VBA Cheatsheet
ΠεριγραφήΠαράδειγμα κώδικα
Παραπομπή και ενεργοποίηση φύλλων
Όνομα καρτέλαςΦύλλα ("Εισαγωγή"). Ενεργοποιήστε
Κωδικό όνομα VBAΦύλλο 1. Ενεργοποιήστε
Θέση ευρετηρίουΦύλλα (1). Ενεργοποιήστε
Επιλέξτε Φύλλο
Επιλέξτε ΦύλλοΦύλλα ("Εισαγωγή"). Επιλέξτε
Ορίστε σε ΜεταβλητήDim ws ως φύλλο εργασίας
Ορισμός ws = ActiveSheet
Όνομα / ΜετονομασίαActiveSheet.Name = "NewName"
Επόμενο φύλλοActiveSheet.Next.Activate
Βρόχος σε όλα τα φύλλαDim ws ως φύλλο εργασίας
Για κάθε ws σε Φύλλα εργασίας
Msgbox ws.name
Επόμενο ws
Κάντε αναζήτηση σε επιλεγμένα φύλλαDim ws Ως φύλλο εργασίας
Για κάθε ws Στο ActiveWindow.SelectedSheets
MsgBox ws.Name
Επόμενο ws
Αποκτήστε το ActiveSheetMsgBox ActiveSheet.Name
Προσθήκη φύλλουΦύλλα. Προσθήκη
Προσθήκη φύλλου και ονόματοςSheets.Add.Name = "NewSheet"
Προσθήκη φύλλου με όνομα από το κελίSheets.Add.Name = range ("a3"). Τιμή
Προσθήκη φύλλου μετά το άλλοSheets.Add After: = Sheets ("Input")
Προσθήκη φύλλου μετά και όνομαSheets.Add (After: = Sheets ("Input")). Name = "NewSheet"
Προσθήκη φύλλου πριν και όνομαSheets.Add (Πριν: = Sheets ("Input")). Name = "NewSheet"
Προσθήκη φύλλου στο τέλος του βιβλίου εργασίαςSheets.Add After: = Sheets (Sheets.Count)
Προσθήκη φύλλου στην αρχή του βιβλίου εργασίαςSheets.Add (Πριν: = Sheets (1)). Name = "FirstSheet"
Προσθήκη φύλλου στη μεταβλητήDim ws Ως φύλλο εργασίας
Ορισμός ws = Φύλλα. Προσθήκη
Αντιγραφή φύλλων εργασίας
Μετακίνηση φύλλου στο τέλος του βιβλίου εργασίαςΦύλλα ("Sheet1"). Μετακίνηση μετά: = Φύλλα (Sheets.Count)
Στο νέο βιβλίο εργασίαςΦύλλα ("Φύλλο1"). Αντιγραφή
Επιλεγμένα φύλλα σε νέο βιβλίο εργασίαςActiveWindow.SelectedSheets.Copy
Πριν από ένα άλλο φύλλοΦύλλα ("Φύλλο1"). Αντιγραφή πριν: = Φύλλα ("Φύλλο2")
Πριν από το πρώτο φύλλοΦύλλα ("Φύλλο1"). Αντιγραφή πριν: = Φύλλα (1)
Μετά το τελευταίο φύλλοΦύλλα ("Sheet1"). Αντιγραφή μετά: = Φύλλα (Sheets.Count)
Αντιγραφή και όνομαΦύλλα ("Sheet1"). Αντιγραφή μετά: = Φύλλα (Sheets.Count)
ActiveSheet.Name = "LastSheet"
Αντιγραφή και όνομα από τιμή κελιούΦύλλα ("Sheet1"). Αντιγραφή μετά: = Φύλλα (Sheets.Count)
ActiveSheet.Name = Εύρος ("A1"). Τιμή
Σε άλλο βιβλίο εργασίαςΦύλλα ("Φύλλο1"). Αντιγραφή πριν: = Βιβλία εργασίας ("Παράδειγμα.xlsm"). Φύλλα (1)
Απόκρυψη / απόκρυψη φύλλων
Απόκρυψη φύλλουΦύλλα ("Φύλλο1"). Ορατό = Λάθος
ή
Φύλλα ("Φύλλο1"). Ορατό = xlSheetHidden
Αποκάλυψη φύλλουΦύλλα ("Φύλλο1"). Ορατό = Αληθινό
ή
Φύλλα ("Sheet1"). Visible = xlSheetVisible
Πολύ απόκρυψη φύλλουΦύλλα ("Sheet1"). Visible = xlSheetVeryHidden
Διαγραφή ή διαγραφή φύλλων
Διαγραφή φύλλουΦύλλα ("Φύλλο1"). Διαγραφή
Διαγραφή φύλλου (Χειρισμός σφαλμάτων)Στο Σφάλμα Συνέχιση Επόμενο
Φύλλα ("Φύλλο1"). Διαγραφή
Σφάλμα GoTo 0
Διαγραφή φύλλου (χωρίς προτροπή)Application.DisplayAlerts = Λάθος
Φύλλα ("Φύλλο1"). Διαγραφή
Application.DisplayAlerts = True
Διαγραφή φύλλουΦύλλα ("Sheet1"). Cells.Clear
Μόνο διαγραφή περιεχομένου φύλλουΦύλλα ("Sheet1"). Cells.ClearContents
Διαγραφή φύλλου UsedRangeΦύλλα ("Sheet1"). UsedRange.Clear
Προστασία ή μη προστασία φύλλων
Απροστασία (Χωρίς κωδικό πρόσβασης)Φύλλα ("Φύλλο1"). Απροστασία
Απροστασία (Κωδικός πρόσβασης)Φύλλα ("Φύλλο1"). Απροστασία "Κωδικός πρόσβασης"
Προστασία (Χωρίς κωδικό πρόσβασης)Φύλλα ("Φύλλο1"). Προστατέψτε
Προστασία (Κωδικός πρόσβασης)Φύλλα ("Sheet1"). Προστατέψτε το "Password"
Προστατέψτε αλλά επιτρέψτε την πρόσβαση VBAΦύλλα ("Sheet1"). Προστατεύστε το UserInterfaceOnly: = True
Κατάργηση προστασίας όλων των φύλλωνDim ws Ως φύλλο εργασίας
Για κάθε ws σε φύλλα εργασίας
ws.Απροστασία "κωδικού πρόσβασης"
Επόμενο ws
wave wave wave wave wave