Εργασία με φύλλα εργασίας χρησιμοποιώντας Excel VBA (εξηγείται με παραδείγματα)

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

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

Σε αυτό το σεμινάριο, θα καλύψω λεπτομερώς τα «Φύλλα εργασίας» και θα σας δείξω επίσης μερικά πρακτικά παραδείγματα.

Ας ξεκινήσουμε λοιπόν.

Όλοι οι κωδικοί που αναφέρω σε αυτό το σεμινάριο πρέπει να τοποθετηθούν στο VB Editor. Μεταβείτε στην ενότητα "Πού να βάλετε τον κώδικα VBA" για να μάθετε πώς λειτουργεί.

Εάν ενδιαφέρεστε να μάθετε VBA με τον εύκολο τρόπο, ελέγξτε το δικό μου Online Εκπαίδευση Excel VBA.

Διαφορά μεταξύ φύλλων εργασίας και φύλλων στο VBA

Στο VBA, έχετε δύο συλλογές που μπορεί μερικές φορές να είναι λίγο συγκεχυμένες.

Σε ένα βιβλίο εργασίας, μπορείτε να έχετε φύλλα εργασίας και καθώς και φύλλα γραφημάτων. Το παρακάτω παράδειγμα έχει τρία φύλλα εργασίας και ένα φύλλο γραφήματος.

Στο Excel VBA:

• Η συλλογή «Φύλλα εργασίας» θα αναφέρεται στη συλλογή όλων των αντικειμένων φύλλου εργασίας σε ένα βιβλίο εργασίας. Στο παραπάνω παράδειγμα, η συλλογή φύλλων εργασίας θα αποτελείται από τρία φύλλα εργασίας.
• Η συλλογή "Φύλλα" θα αναφέρεται σε όλα τα φύλλα εργασίας καθώς και σε φύλλα γραφημάτων στο βιβλίο εργασίας. Στο παραπάνω παράδειγμα, θα είχε τέσσερα στοιχεία - 3 φύλλα εργασίας + 1 φύλλο γραφήματος.

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

Αλλά όταν έχετε ένα ή περισσότερα φύλλα γραφημάτων, η συλλογή "Φύλλα" θα είναι μεγαλύτερη από τη συλλογή "Φύλλα εργασίας"

Φύλλα = Φύλλα εργασίας + Φύλλα γραφήματος

Τώρα με αυτήν τη διάκριση, συνιστώ να είστε όσο το δυνατόν πιο συγκεκριμένοι κατά τη σύνταξη ενός κώδικα VBA.

Έτσι, εάν πρέπει να ανατρέξετε μόνο σε φύλλα εργασίας, χρησιμοποιήστε τη συλλογή "Φύλλα εργασίας" και εάν πρέπει να ανατρέξετε σε όλα τα φύλλα (συμπεριλαμβανομένων των φύλλων γραφημάτων), χρησιμοποιήστε τη συλλογή "Φύλλα".

Σε αυτό το σεμινάριο, θα χρησιμοποιήσω μόνο τη συλλογή "Φύλλα εργασίας".

Αναφορά σε ένα φύλλο εργασίας στο VBA

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

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

Χρήση του ονόματος του φύλλου εργασίας

Ο ευκολότερος τρόπος αναφοράς σε ένα φύλλο εργασίας είναι να χρησιμοποιήσετε το όνομά του.

Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα βιβλίο εργασίας με τρία φύλλα εργασίας - Φύλλο 1, Φύλλο 2, Φύλλο 3.

Και θέλετε να ενεργοποιήσετε το Φύλλο 2.

Μπορείτε να το κάνετε χρησιμοποιώντας τον ακόλουθο κώδικα: Sub ActivateSheet () Worksheets ("Sheet2"). Activate End Sub

Ο παραπάνω κώδικας ζητά από τη VBA να ανατρέξει στο Sheet2 στη συλλογή φύλλων εργασίας και να το ενεργοποιήσει.

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

Υπο φύλλα ActivateSheet () ("Sheet2"). Ενεργοποιήστε το End Sub

Χρησιμοποιώντας τον αριθμό ευρετηρίου

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

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

Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε τον αριθμό ευρετηρίου των φύλλων εργασίας.

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

Ο παρακάτω κώδικας θα ενεργοποιήσει το Sheet2:

Sub ActivateSheet () Φύλλα εργασίας (2). Ενεργοποιήστε το End Sub

Σημειώστε ότι χρησιμοποιήσαμε τον αριθμό ευρετηρίου 2 σε Φύλλα εργασίας (2). Αυτό θα αναφέρεται στο δεύτερο αντικείμενο στη συλλογή των φύλλων εργασίας.

Τώρα, τι συμβαίνει όταν χρησιμοποιείτε το 3 ως αριθμό ευρετηρίου;

Θα επιλέξει το Sheet3.

Αν αναρωτιέστε γιατί επέλεξε το Sheet3, καθώς είναι σαφώς το τέταρτο αντικείμενο.

Αυτό συμβαίνει επειδή ένα φύλλο γραφήματος δεν αποτελεί μέρος της συλλογής φύλλων εργασίας.

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

Αντίθετα, εάν χρησιμοποιείτε Φύλλα, τα Φύλλα (1) θα αναφέρονται στα Φύλλα1, τα Φύλλα (2) θα αναφέρονται στο Φύλλο2, τα Φύλλα (3) θα αναφέρονται στο Διάγραμμα1 και τα Φύλλα (4) θα αναφέρονται στο Φύλλο3.

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

Σημείωση: Ο αριθμός ευρετηρίου πηγαίνει από αριστερά προς τα δεξιά. Έτσι, εάν μετατοπίσετε το Φύλλο2 στα αριστερά του Φύλλου1, τότε τα Φύλλα εργασίας (1) θα αναφέρονται στο Φύλλο2.

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

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

Και αν το όνομα του φύλλου έχει αλλάξει, ο κωδικός σας δεν θα λειτουργήσει μέχρι να αλλάξετε το όνομα του φύλλου εργασίας και στον κωδικό VBA.

Για να αντιμετωπίσετε αυτό το πρόβλημα, μπορείτε να χρησιμοποιήσετε το κωδικό όνομα του φύλλου εργασίας (αντί για το κανονικό όνομα που χρησιμοποιούσαμε μέχρι τώρα). Ένα κωδικό όνομα μπορεί να εκχωρηθεί στο VB Editor και δεν αλλάζει όταν αλλάζετε το όνομα του φύλλου από την περιοχή του φύλλου εργασίας.

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

1. Κάντε κλικ στην καρτέλα Προγραμματιστής.
2. Κάντε κλικ στο κουμπί Visual Basic. Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας VB.
3. Κάντε κλικ στην επιλογή Προβολή στο μενού και κάντε κλικ στο Παράθυρο έργου. Αυτό θα κάνει το παράθυρο Ιδιότητες ορατό. Εάν το παράθυρο Ιδιότητες είναι ήδη ορατό, παραλείψτε αυτό το βήμα.
4. Κάντε κλικ στο όνομα του φύλλου στην εξερεύνηση έργου που θέλετε να μετονομάσετε.
5. Στο παράθυρο Ιδιότητες, αλλάξτε το όνομα στο πεδίο μπροστά (Όνομα). Λάβετε υπόψη ότι δεν μπορείτε να έχετε κενά στο όνομα.

Τα παραπάνω βήματα θα αλλάξουν το όνομα του φύλλου εργασίας σας στο backend VBA. Στην προβολή φύλλου εργασίας του Excel, μπορείτε να ονομάσετε το φύλλο εργασίας όπως θέλετε, αλλά στο πίσω μέρος, θα απαντήσει και στα δύο ονόματα - το όνομα του φύλλου και το κωδικό όνομα.

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

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

Για παράδειγμα, τόσο η γραμμή θα ενεργοποιήσει το φύλλο εργασίας.

Φύλλα εργασίας ("Όνομα φύλλου"). Ενεργοποιήστε τον κωδικό όνομα. Ενεργοποιήστε

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

Αναφορά σε φύλλο εργασίας σε διαφορετικό τετράδιο εργασίας

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

Για παράδειγμα, εάν έχετε ένα βιβλίο εργασίας με το όνομα Παραδείγματα και θέλετε να ενεργοποιήσετε το Sheet1 στο Παράδειγμα βιβλίου εργασίας, πρέπει να χρησιμοποιήσετε τον παρακάτω κώδικα:

Sub SheetActivate () Workbooks ("Examples.xlsx"). Worksheets ("Sheet1"). Activate End Sub

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

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

Προσθήκη φύλλου εργασίας

Ο παρακάτω κώδικας θα προσθέσει ένα φύλλο εργασίας (ως το πρώτο φύλλο εργασίας - δηλαδή, ως το αριστερότερο φύλλο στην καρτέλα φύλλου).

Sub AddSheet () Φύλλα εργασίας. Προσθήκη τέλους υπο

Παίρνει το προεπιλεγμένο όνομα Sheet2 (ή οποιοδήποτε άλλο αριθμό με βάση πόσα φύλλα υπάρχουν ήδη).

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

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

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

Ομοίως, μπορείτε επίσης να προσθέσετε ένα φύλλο μετά από ένα φύλλο εργασίας (ας πούμε Sheet2), χρησιμοποιώντας τον παρακάτω κώδικα:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

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

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Διαγραφή φύλλου εργασίας

Ο παρακάτω κώδικας θα διαγράψει το ενεργό φύλλο από το βιβλίο εργασίας.

Sub DeleteSheet () ActiveSheet.Delete End Sub

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

Εάν δεν θέλετε να δείτε το μήνυμα προειδοποίησης, χρησιμοποιήστε τον παρακάτω κώδικα:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Όταν το Application.DisplayAlerts έχει οριστεί σε False, δεν θα σας εμφανίσει το μήνυμα προειδοποίησης. Εάν το χρησιμοποιείτε, θυμηθείτε να το επαναφέρετε στο True στο τέλος του κώδικα.

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

Εάν θέλετε να διαγράψετε ένα συγκεκριμένο φύλλο, μπορείτε να το κάνετε χρησιμοποιώντας τον ακόλουθο κώδικα:

Sub DeleteSheet () Φύλλα εργασίας ("Sheet2"). Delete End Sub

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

Υποδιαγραφή DeetSheet () Sheet5.Delete End Sub

Μετονομασία των φύλλων εργασίας

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

Ο ακόλουθος κώδικας θα αλλάξει το όνομα του Φύλλου1 σε "Περίληψη".

Sub RenameSheet () Φύλλα εργασίας ("Sheet1"). Name = "Summary" End Sub

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

Για παράδειγμα, εάν θέλετε να εισαγάγετε τέσσερα φύλλα με το όνομα2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 και 2021-2022 Q4, μπορείτε να χρησιμοποιήσετε τον παρακάτω κωδικό.

Sub RenameSheet () Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Add after: = Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i) .Name = "2018 Q" & i Next i Τέλος υπο

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

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

Όταν εργάζεστε με φύλλα εργασίας, μπορείτε να αντιστοιχίσετε ένα φύλλο εργασίας σε μια μεταβλητή αντικειμένου και, στη συνέχεια, να χρησιμοποιήσετε τη μεταβλητή αντί για τις αναφορές φύλλου εργασίας.

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

Εδώ είναι ο κώδικας που θα προσθέσει το 2021-2022 ως πρόθεμα σε όλα τα ονόματα του φύλλου εργασίας.

Sub RenameSheet () Dim Ws As Worksheet For Every Ws In Worksheets Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Ο παραπάνω κώδικας δηλώνει μια μεταβλητή Ws ως τύπο φύλλου εργασίας (χρησιμοποιώντας τη γραμμή ‘Dim Ws As Worksheet’).

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

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

Στον παρακάτω κώδικα, εκχωρούμε τη μεταβλητή Ws στο Sheet2 και τη χρησιμοποιούμε για πρόσβαση σε όλες τις ιδιότητες του Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

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

Σημειώστε ότι ο κώδικας δηλώνει το αντικείμενο Ws ως μεταβλητή τύπου φύλλου εργασίας (χρησιμοποιώντας τη γραμμή Dim Ws ως φύλλο εργασίας).

Απόκρυψη φύλλων εργασίας χρησιμοποιώντας VBA (Κρυφό + Πολύ κρυφό)

Η απόκρυψη και απόκρυψη φύλλων εργασίας στο Excel είναι μια απλή εργασία.

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

Αλλά τι γίνεται αν δεν θέλετε να μπορούν να αποκρύψουν το (τα) φύλλο (τα) εργασίας.

Μπορείτε να το κάνετε αυτό χρησιμοποιώντας το VBA.

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

Sub HideAllExcetActiveSheet () Dim Ws As Worksheet For Every Ws In ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Στον παραπάνω κώδικα, η ιδιότητα Ws.Visible αλλάζει σε xlSheetVeryHidden.

• Όταν η ιδιότητα Ορατό έχει οριστεί σε xlSheetVisible, το φύλλο είναι ορατό στην περιοχή του φύλλου εργασίας (ως καρτέλες φύλλου εργασίας).
• Όταν η ιδιότητα Ορατή έχει οριστεί σε xlSheetHidden, το φύλλο είναι κρυφό, αλλά ο χρήστης μπορεί να το αποκρύψει κάνοντας δεξί κλικ σε οποιαδήποτε καρτέλα φύλλου.
• Όταν η ιδιότητα Ορατή έχει οριστεί σε xlSheetVeryHidden, το φύλλο είναι κρυφό και δεν μπορεί να αποκρυφτεί από την περιοχή του φύλλου εργασίας. Πρέπει να χρησιμοποιήσετε έναν κωδικό VBA ή το παράθυρο ιδιοτήτων για να τον αποκρύψετε.

Εάν θέλετε να αποκρύψετε απλά φύλλα, τα οποία μπορούν εύκολα να κρυφτούν, χρησιμοποιήστε τον παρακάτω κώδικα:

Sub HideAllExceptActiveSheet () Dim Ws As Worksheet For Every Ws In ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Ο παρακάτω κώδικας θα εμφανίσει όλα τα φύλλα εργασίας (κρυφά και πολύ κρυφά).

Sub UnhideAllWoksheets () Dim Ws As Worksheet For Every Ws In ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Σχετικό άρθρο: Αποκάλυψη όλων των φύλλων στο Excel (με μια κίνηση)

Απόκρυψη φύλλων με βάση το κείμενο σε αυτό

Ας υποθέσουμε ότι έχετε πολλά φύλλα με το όνομα διαφορετικών τμημάτων ή ετών και θέλετε να αποκρύψετε όλα τα φύλλα εκτός από αυτά που έχουν το έτος 2021-2022.

Μπορείτε να το κάνετε αυτό χρησιμοποιώντας μια λειτουργία VBA INSTR.

Ο παρακάτω κώδικας θα αποκρύψει όλα τα φύλλα εκτός από αυτά με το κείμενο2021-2022.

Sub HideWithMatchingText () Dim Ws As Worksheet For Every Ws In Worksheets If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

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

Ο παραπάνω κώδικας ελέγχει εάν το όνομα περιέχει το κείμενο2021-2022. Εάν συμβεί, τίποτα δεν συμβαίνει, αλλιώς το φύλλο εργασίας είναι κρυφό.

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

Ταξινόμηση των φύλλων εργασίας με αλφαβητική σειρά

Χρησιμοποιώντας το VBA, μπορείτε να ταξινομήσετε γρήγορα τα φύλλα εργασίας με βάση τα ονόματά τους.

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

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets. Count for i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets (j). Όνομα < Φύλλα (i). Όνομα Στη συνέχεια Φύλλα (j). Μετακίνηση πριν: = Φύλλα (i) Τέλος Αν Επόμενο j Επόμενο i Application.ScreenUpdating = True End Sub

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

Προστασία/Κατάργηση προστασίας όλων των φύλλων με τη μία

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

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

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'αντικαταστήστε το Test123 με τον κωδικό πρόσβασης που θέλετε για κάθε ws Στα φύλλα εργασίας ws.Προστασία κωδικού πρόσβασης: = κωδικός πρόσβασης Επόμενο ws Τέλος υπο

Ο παρακάτω κώδικας θα προστατεύσει όλα τα φύλλα με τη μία.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'αντικαταστήστε το Test123 με τον κωδικό πρόσβασης που χρησιμοποιήσατε προστατεύοντας Για κάθε ws Στα φύλλα εργασίας ws.Προστασία κωδικού πρόσβασης: = κωδικός πρόσβασης Επόμενο ws Τέλος υπο

Δημιουργία πίνακα περιεχομένων όλων των φύλλων εργασίας (με υπερσυνδέσμους)

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

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Name & "! A1", _ TextToDisplay: = Φύλλα εργασίας (i) .Name Next i End Sub

Ο παραπάνω κώδικας εισάγει ένα νέο φύλλο εργασίας και το ονομάζει Ευρετήριο.

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

Πού να τοποθετήσετε τον κωδικό VBA

Αναρωτιέστε πού πηγαίνει ο κώδικας VBA στο βιβλίο εργασίας του Excel;

Το Excel έχει ένα backend VBA που ονομάζεται VBA editor. Πρέπει να αντιγράψετε και να επικολλήσετε τον κώδικα στο παράθυρο κωδικού της λειτουργικής μονάδας VB Editor.

Ακολουθούν τα βήματα για να το κάνετε αυτό:

1. Μεταβείτε στην καρτέλα Προγραμματιστής.
2. Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
3. Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα. Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
4. Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
5. Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.

Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα Excel VBA:

• Εργασία με βιβλία εργασίας χρησιμοποιώντας VBA.
• Χρήση IF then Else Statements στο VBA.
• Για επόμενο βρόχο στο VBA.
• Δημιουργία συνάρτησης που καθορίζεται από το χρήστη στο Excel.
• Πώς να καταγράψετε μια μακροεντολή στο Excel.
• Πώς να εκτελέσετε μια μακροεντολή στο Excel.
• Excel VBA Events - Ένας εύκολος (και πλήρης) οδηγός.
• Πώς να δημιουργήσετε ένα πρόσθετο στο Excel.
• Πώς να αποθηκεύσετε και να επαναχρησιμοποιήσετε τη μακροεντολή χρησιμοποιώντας το Excel Personal Macro Workbook.