Για να αξιοποιήσετε στο έπακρο το Excel και το VBA, πρέπει να γνωρίζετε πώς να χρησιμοποιείτε αποτελεσματικά τους βρόχους.
Στο VBA, οι βρόχοι σάς επιτρέπουν να περάσετε από ένα σύνολο αντικειμένων/τιμών και να το αναλύσετε ένα προς ένα. Μπορείτε επίσης να εκτελέσετε συγκεκριμένες εργασίες για κάθε βρόχο.
Ακολουθεί ένα απλό παράδειγμα χρήσης βρόχων VBA στο Excel.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων και θέλετε να επισημάνετε όλα τα κελιά σε ομοιόμορφες σειρές. Μπορείτε να χρησιμοποιήσετε ένα βρόχο VBA για να περάσετε το εύρος και να αναλύσετε κάθε αριθμό σειράς κελιού. Αν αποδειχθεί ομοιόμορφο, του δίνεις χρώμα, αλλιώς το αφήνεις ως έχει.
Τώρα, αυτό, φυσικά, είναι πολύ απλό να κάνετε loop στο Excel VBA (και μπορείτε επίσης να το κάνετε αυτό χρησιμοποιώντας μορφοποίηση υπό όρους).
Στην πραγματική ζωή, μπορείτε να κάνετε πολλά περισσότερα με βρόχους VBA στο Excel που μπορούν να σας βοηθήσουν να αυτοματοποιήσετε εργασίες.
Ακολουθούν μερικά πιο πρακτικά παραδείγματα όπου οι βρόχοι VBA μπορούν να είναι χρήσιμοι:
- Κάντε αναζήτηση σε μια σειρά κελιών και αναλύστε κάθε κελί (επισημάνετε κελιά με ένα συγκεκριμένο κείμενο σε αυτό).
- Περιηγηθείτε σε όλα τα φύλλα εργασίας και κάντε κάτι με το καθένα (όπως προστατέψτε το/προστατέψτε το).
- Βολέρετε όλα τα ανοιχτά βιβλία εργασίας (και αποθηκεύστε κάθε βιβλίο εργασίας ή κλείστε όλα εκτός από το ενεργό βιβλίο εργασίας).
- Κάντε βρόχο σε όλους τους χαρακτήρες σε ένα κελί (και εξαγάγετε το αριθμητικό μέρος από μια συμβολοσειρά).
- Βοηθήστε όλες τις τιμές ενός πίνακα.
- Κάντε βρόχο σε όλα τα γραφήματα/αντικείμενα (και δώστε ένα περίγραμμα ή αλλάξτε το χρώμα του φόντου).
Τώρα για να χρησιμοποιήσετε καλύτερα βρόχους στο Excel VBA, πρέπει να γνωρίζετε για τα διάφορα είδη που υπάρχουν και τη σωστή σύνταξη του καθενός.
Σε αυτό το σεμινάριο, θα παρουσιάσω διαφορετικούς τύπους βρόχων Excel VBA και θα καλύψω μερικά παραδείγματα για κάθε βρόχο
Σημείωση: Αυτό θα είναι ένα τεράστιο σεμινάριο, όπου θα προσπαθήσω να καλύψω κάθε βρόχο VBA με λεπτομέρεια. Σας συνιστώ να προσθέσετε σελιδοδείκτη σε αυτήν τη σελίδα για μελλοντική αναφορά.Εάν ενδιαφέρεστε να μάθετε VBA με τον εύκολο τρόπο, ελέγξτε το δικό μου Online Εκπαίδευση Excel VBA.
Για Επόμενο Βρόχο
Ο βρόχος «Για επόμενο» σας επιτρέπει να περάσετε από ένα μπλοκ κώδικα για τον καθορισμένο αριθμό φορών.
Για παράδειγμα, αν σας ζητήσω να προσθέσετε τους ακέραιους αριθμούς από το 1 στο 10 με μη αυτόματο τρόπο, προσθέτετε τους δύο πρώτους αριθμούς, στη συνέχεια προσθέτετε τον τρίτο αριθμό στο αποτέλεσμα, στη συνέχεια προσθέτετε τον τέταρτο αριθμό στο αποτέλεσμα, ούτω καθεξής…
Έτσι δεν είναι;
Η ίδια λογική χρησιμοποιείται στον βρόχο For Next στο VBA.
Καθορίζετε πόσες φορές θέλετε να εκτελείται ο βρόχος και καθορίζετε επίσης τι θέλετε να κάνει ο κώδικας κάθε φορά που εκτελείται ο βρόχος.
Παρακάτω είναι η σύνταξη του βρόχου Για το επόμενο:
Για μετρητή = Έναρξη έως τέλος [Τιμή βήματος] [Αποκλεισμός κώδικα για εκτέλεση] Επόμενο [μετρητής]
Στο βρόχο For Next, μπορείτε να χρησιμοποιήσετε έναν μετρητή (ή οποιαδήποτε μεταβλητή) που θα χρησιμοποιηθεί για την εκτέλεση του βρόχου. Αυτός ο μετρητής σας επιτρέπει να εκτελέσετε αυτόν τον βρόχο για έναν απαιτούμενο αριθμό φορών.
Για παράδειγμα, εάν θέλω να προσθέσω τους πρώτους 10 θετικούς ακέραιους αριθμούς, τότε η τιμή του μετρητή μου θα είναι από 1 έως 10.
Ας ρίξουμε μια ματιά σε μερικά παραδείγματα για να καταλάβουμε καλύτερα πώς λειτουργεί ο βρόχος For Next.
Παράδειγμα 1 - Προσθήκη των πρώτων 10 θετικών ακεραίων
Παρακάτω είναι ο κώδικας που θα προσθέσει τους πρώτους 10 θετικούς ακέραιους αριθμούς χρησιμοποιώντας έναν βρόχο For Next.
Στη συνέχεια, θα εμφανίσει ένα πλαίσιο μηνυμάτων που δείχνει το άθροισμα αυτών των αριθμών.
Sub AddNumbers () Dim Total As Integer Dim Count As Integer Total = 0 Για Count = 1 To 10 Total = Σύνολο + Count Επόμενο πλήθος MsgBox Total End Sub
Σε αυτόν τον κώδικα, η τιμή του Σύνολο έχει οριστεί σε 0 προτού μπείτε στον βρόχο For Next.
Μόλις μπει στον βρόχο, διατηρεί τη συνολική τιμή μετά από κάθε βρόχο. Έτσι, μετά τον πρώτο βρόχο, όταν ο μετρητής είναι 1, η τιμή «Σύνολο» γίνεται 1 και μετά τον δεύτερο βρόχο γίνεται 3 (1+2) κ.ο.κ.
Και τέλος, όταν τελειώσει ο βρόχος, η μεταβλητή «Σύνολο» έχει το άθροισμα των πρώτων 10 θετικών ακεραίων.
Ένα MsgBox εμφανίζει απλώς το αποτέλεσμα σε ένα πλαίσιο μηνυμάτων.
Παράδειγμα 2 - Προσθήκη των πρώτων 5 ακόμη και θετικών ακεραίων
Για να αθροίσουμε τους πέντε πρώτους άρτιους ακέραιους αριθμούς (δηλ., 2,4,6,8 και 10), χρειάζεστε έναν παρόμοιο κωδικό με προϋπόθεση για να λάβετε υπόψη μόνο τους ζυγούς αριθμούς και να αγνοήσετε τους περιττούς αριθμούς.
Εδώ είναι ένας κώδικας που θα το κάνει:
Sub AddEvenNumbers () Dim Total As Integer Dim Count As Integer Total = 0 Για Count = 2 To 10 Step 2 Total = Σύνολο + Count Επόμενο πλήθος MsgBox Total End Sub
Σημειώστε ότι ξεκινήσαμε την τιμή Count από 2 και χρησιμοποιήσαμε επίσης το 'Βήμα 2‘.
Όταν χρησιμοποιείτε 'Βήμα 2', λέει στον κώδικα να αυξάνει την τιμή "Count" κατά 2 κάθε φορά που εκτελείται ο βρόχος.
Έτσι, η τιμή Count ξεκινά από 2 και στη συνέχεια γίνεται 4, 6, 8 και 10 καθώς εμφανίζεται ο κύκλος.
ΣΗΜΕΙΩΣΗ: Ένας άλλος τρόπος για να γίνει αυτό θα μπορούσε να είναι η εκτέλεση του βρόχου από το 1 έως το 10 και μέσα στον βρόχο να ελέγξετε αν ο αριθμός είναι ζυγός ή περιττός. Ωστόσο, η χρήση του Step, σε αυτή την περίπτωση, είναι ένας πιο αποτελεσματικός τρόπος, καθώς δεν απαιτεί τον βρόχο να τρέξει 10 φορές, αλλά μόνο 5 φορές.
Η τιμή Step μπορεί επίσης να είναι αρνητική. Σε αυτήν την περίπτωση, ο μετρητής ξεκινά σε υψηλότερη τιμή και συνεχίζει να μειώνεται κατά την καθορισμένη τιμή βήματος.
Παράδειγμα 3 - Εισαγωγή σειριακού αριθμού στα επιλεγμένα κελιά
Μπορείτε επίσης να χρησιμοποιήσετε το βρόχο For Next για να περάσετε από μια συλλογή αντικειμένων (όπως κελιά ή φύλλα εργασίας ή βιβλία εργασίας),
Ακολουθεί ένα παράδειγμα που εισάγει γρήγορα σειριακούς αριθμούς σε όλα τα επιλεγμένα κελιά.
Sub EnterSerialNumber () Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0). Value = Counter Next Counter End Υπο
Ο παραπάνω κώδικας μετρά πρώτα τον αριθμό των επιλεγμένων σειρών και στη συνέχεια εκχωρεί αυτήν την τιμή στη μεταβλητή RowCount. Στη συνέχεια, τρέχουμε τον βρόχο από το "1 στο RowCount".
Σημειώστε επίσης ότι δεδομένου ότι η επιλογή μπορεί να είναι οποιοσδήποτε αριθμός γραμμών, έχουμε Ρυθμίστε τη μεταβλητή Rng σε Επιλογή (με τη γραμμή "Ορισμός Rng = Επιλογή"). Τώρα μπορούμε να χρησιμοποιήσουμε τη μεταβλητή ‘Rng’ για να αναφερθούμε στην επιλογή στον κώδικά μας.
Παράδειγμα 4 - Προστατέψτε όλα τα φύλλα εργασίας στο ενεργό βιβλίο εργασίας
Μπορείτε να χρησιμοποιήσετε τον βρόχο «Για επόμενο» για να περάσετε από όλα τα φύλλα εργασίας στο ενεργό βιβλίο εργασίας και να προστατέψετε (ή να προστατέψετε) κάθε ένα από τα φύλλα εργασίας.
Παρακάτω είναι ο κώδικας που θα το κάνει αυτό:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets (i) .Προστασία επόμενου i End Sub
Ο παραπάνω κώδικας μετρά τον αριθμό των φύλλων χρησιμοποιώντας το ActiveWorkbook.Worksheets.Count. Αυτό λέει στον VBA πόσες φορές πρέπει να εκτελεστεί ο βρόχος.
Σε κάθε περίπτωση, αναφέρεται στο βιβλίο εργασίας Ith (χρησιμοποιώντας φύλλα εργασίας (i)) και το προστατεύει.
Μπορείτε επίσης να χρησιμοποιήσετε αυτόν τον ίδιο κώδικα για να προστατέψετε τα φύλλα εργασίας. Απλά άλλαξε γραμμή Φύλλα εργασίας (i). Προστασία προς το Φύλλα εργασίας (i) .UnProtect.
Ένθετα βρόχους "For Next"
Μπορείτε να χρησιμοποιήσετε ένθετους βρόχους "Για επόμενο" για να κάνετε πιο περίπλοκους αυτοματισμούς στο Excel. Ένας ένθετος βρόχος «Για το επόμενο» θα σήμαινε ότι υπάρχει ένας βρόχος «Για το επόμενο» μέσα σε έναν βρόχο «Για το επόμενο».
Επιτρέψτε μου να σας δείξω πώς να το χρησιμοποιήσετε χρησιμοποιώντας ένα παράδειγμα.
Ας υποθέσουμε ότι έχω 5 βιβλία εργασίας ανοιχτά στο σύστημά μου και θέλω να προστατεύσω όλα τα φύλλα εργασίας σε όλα αυτά τα βιβλία εργασίας.
Παρακάτω είναι ο κώδικας που θα το κάνει αυτό:
Sub ProtectWorksheets () Dim i As Integer Dim j Ως Integer For i = 1 To Workbooks.Count For j = 1 To Workbooks (i). Worksheets.Count Workbooks (i) .Worksheets (j) .Προστασία Επόμενου j Επόμενο i End Sub
Τα παραπάνω είναι ένας ένθετος βρόχος For Next καθώς έχουμε χρησιμοποιήσει έναν βρόχο For Next μέσα σε έναν άλλο.
Δηλώσεις «EXIT For» στο For Next Loops
Η δήλωση «Έξοδος για» σας επιτρέπει να βγείτε εντελώς από τον βρόχο «Για επόμενο».
Μπορείτε να το χρησιμοποιήσετε σε περιπτώσεις όπου θέλετε ο βρόχος For Next να τελειώσει όταν πληρείται μια συγκεκριμένη συνθήκη.
Ας πάρουμε ένα παράδειγμα όπου έχετε ένα σύνολο αριθμών στη στήλη Α και θέλετε να επισημάνετε όλους τους αρνητικούς αριθμούς με κόκκινη γραμματοσειρά. Σε αυτήν την περίπτωση, πρέπει να αναλύσουμε κάθε κελί για την τιμή του και στη συνέχεια να αλλάξουμε ανάλογα το χρώμα της γραμματοσειράς.
Αλλά για να κάνουμε τον κώδικα πιο αποτελεσματικό, μπορούμε πρώτα να ελέγξουμε αν υπάρχουν αρνητικές τιμές στη λίστα ή όχι. Εάν δεν υπάρχουν αρνητικές τιμές, μπορούμε να χρησιμοποιήσουμε τη δήλωση Exit For για να βγει απλώς από τον κώδικα.
Παρακάτω είναι ο κώδικας που το κάνει αυτό:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng. Count for i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Έπειτα Έξοδος για Αν Rng (i). Τιμή <0 Στη συνέχεια Rng (i). Γραμματοσειρά. Χρώμα = vb Κόκκινο Επόμενο i Τέλος Υπο
Όταν χρησιμοποιείτε τη δήλωση «Έξοδος για» εντός ενός ένθετου βρόχου «Για επόμενο», θα βγει από τον βρόχο στον οποίο εκτελείται και θα συνεχίσει να εκτελεί την επόμενη γραμμή στον κώδικα μετά τον βρόχο Για επόμενο.
Για παράδειγμα, στον παρακάτω κώδικα, η δήλωση «Έξοδος για» θα σας βγάλει από τον εσωτερικό βρόχο, αλλά ο εξωτερικός βρόχος θα συνεχίσει να λειτουργεί.
Sub SampleCode () Για i = 1 έως 10 Για j = 1 έως 10 Έξοδος για επόμενο J Next i End sub
Do while Loop
Ένας βρόχος ‘Do while’ σας επιτρέπει να ελέγξετε για μια συνθήκη και να εκτελέσετε τον βρόχο ενώ πληρείται αυτή η συνθήκη (ή είναι ΑΛΗΘΙΝΗ).
Υπάρχουν δύο τύποι σύνταξης στο Do while Loop.
Κάντε [Ενώ συνθήκη] [Αποκλεισμός κώδικα για εκτέλεση] Βρόχος
και
Κάντε [Αποκλεισμός κώδικα για εκτέλεση] Βρόχος [Κατάσταση]
Η διαφορά μεταξύ αυτών των δύο είναι ότι στην πρώτη, η συνθήκη Ελέγχεται πρώτα πριν εκτελεστεί οποιοδήποτε μπλοκ κώδικα, και στη δεύτερη περίπτωση, το μπλοκ κώδικα εκτελείται πρώτα και στη συνέχεια ελέγχεται η συνθήκη Ενώ.
Αυτό σημαίνει ότι εάν η συνθήκη Ενώ είναι ψευδής είναι και οι δύο περιπτώσεις, ο κώδικας θα εξακολουθεί να εκτελείται τουλάχιστον μία φορά στη δεύτερη περίπτωση (καθώς η συνθήκη «Ενώ» ελέγχεται μετά την εκτέλεση του κώδικα μία φορά).
Τώρα ας δούμε μερικά παραδείγματα χρήσης των βρόχων Do while στο VBA.
Παράδειγμα 1 - Προσθέστε τους πρώτους 10 θετικούς ακέραιους αριθμούς χρησιμοποιώντας VBA
Ας υποθέσουμε ότι θέλετε να προσθέσετε τους πρώτους δέκα θετικούς ακέραιους αριθμούς χρησιμοποιώντας τον βρόχο Do while στο VBA.
Για να το κάνετε αυτό, μπορείτε να χρησιμοποιήσετε το βρόχο Do while έως ότου ο επόμενος αριθμός είναι μικρότερος ή ίσος με 10. Μόλις ο αριθμός είναι μεγαλύτερος από 1o, ο βρόχος σας θα σταματήσει.
Εδώ είναι ο κώδικας VBA που θα εκτελέσει αυτόν τον βρόχο Do while και εμφανίζει το αποτέλεσμα σε ένα πλαίσιο μηνυμάτων.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do while i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End End
Ο παραπάνω βρόχος συνεχίζει να λειτουργεί έως ότου η τιμή του «i» γίνει 11. Μόλις γίνει 11, ο βρόχος τελειώνει (καθώς η συνθήκη Ενώ γίνεται λάθος).
Μέσα στον βρόχο, χρησιμοποιήσαμε μια μεταβλητή Αποτέλεσμα που διατηρεί την τελική τιμή Μόλις ολοκληρωθεί ο βρόχος, ένα πλαίσιο μηνύματος εμφανίζει την τιμή της μεταβλητής «Αποτέλεσμα».
Παράδειγμα 2 - Εισαγάγετε ημερομηνίες για τον τρέχοντα μήνα
Ας υποθέσουμε ότι θέλετε να εισαγάγετε όλες τις ημερομηνίες του τρέχοντος μήνα σε μια στήλη φύλλου εργασίας.
Μπορείτε να το κάνετε αυτό χρησιμοποιώντας τον ακόλουθο κώδικα βρόχου Do while:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do while Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Ο παραπάνω κώδικας θα εισάγει όλες τις ημερομηνίες στην πρώτη στήλη του φύλλου εργασίας (ξεκινώντας από το Α1). Οι βρόχοι συνεχίζονται έως ότου η τιμή μήνας της μεταβλητής «CMDate» ταιριάζει με αυτήν του τρέχοντος μήνα.
Έξοδος από τη δήλωση
Μπορείτε να χρησιμοποιήσετε τη δήλωση Exit Do για να βγείτε από τον βρόχο. Μόλις ο κώδικας εκτελέσει τη γραμμή ‘Exit Do’, βγαίνει από τον βρόχο Do while και περνά το στοιχείο ελέγχου στην επόμενη γραμμή αμέσως μετά τον βρόχο.
Για παράδειγμα, εάν θέλετε να εισαγάγετε τις πρώτες 10 ημερομηνίες μόνο, τότε μπορείτε να βγείτε από τον βρόχο μόλις εισαχθούν οι πρώτες 10 ημερομηνίες.
Ο παρακάτω κώδικας θα το κάνει αυτό:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do while Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 Εάν i> = 10 Τότε Έξοδος Do CMDate = CMDate + 1 Loop End Sub
Στον παραπάνω κώδικα, η δήλωση IF χρησιμοποιείται για να ελέγξει εάν η τιμή του i είναι μεγαλύτερη από 10 ή όχι. Μόλις η τιμή του «i» γίνει 10, εκτελείται η δήλωση Exit Do και ο βρόχος τελειώνει.
Κάνε μέχρι βρόχου
Οι βρόχοι "Do Until" μοιάζουν πολύ με τους βρόχους "Do while".
Στο «Κάνε Ενώ», ο βρόχος εκτελείται έως ότου ικανοποιηθεί η δεδομένη συνθήκη, ενώ στο «Εκτελέστε έως», κάνει βρόχο έως ότου ικανοποιηθεί η καθορισμένη συνθήκη.
Υπάρχουν δύο τύποι σύνταξης στο Do Before Loop.
Κάντε [Μέχρι την κατάσταση] [Αποκλεισμός κώδικα για εκτέλεση] Βρόχος
και
Κάντε [Αποκλεισμός κώδικα για εκτέλεση] Βρόχος [Μέχρι συνθήκη]
Η διαφορά μεταξύ αυτών των δύο είναι ότι στην πρώτη, η συνθήκη Μέχρι να ελεγχθεί πρώτα πριν εκτελεστεί οποιοδήποτε μπλοκ κώδικα, και στη δεύτερη περίπτωση, το μπλοκ κώδικα εκτελείται πρώτα και στη συνέχεια η συνθήκη Έως.
Αυτό σημαίνει ότι εάν η συνθήκη Μέχρι το TRUE είναι και οι δύο περιπτώσεις, ο κώδικας εξακολουθεί να εκτελείται τουλάχιστον μία φορά στη δεύτερη περίπτωση (καθώς η συνθήκη «Μέχρι» ελέγχεται αφού εκτελεστεί ο κώδικας μία φορά).
Τώρα ας δούμε μερικά παραδείγματα χρήσης των βρόχων Do Until στο VBA.
Σημείωση: Όλα τα παραδείγματα για το Do Until είναι τα ίδια με αυτά του Do while. Αυτά έχουν τροποποιηθεί για να σας δείξουν πώς λειτουργεί ο βρόχος Do Until.
Παράδειγμα 1 - Προσθέστε τους πρώτους 10 θετικούς ακέραιους αριθμούς χρησιμοποιώντας VBA
Ας υποθέσουμε ότι θέλετε να προσθέσετε τους πρώτους δέκα θετικούς ακέραιους αριθμούς χρησιμοποιώντας τον βρόχο Do Until στο VBA.
Για να γίνει αυτό, πρέπει να τρέξετε τον βρόχο μέχρι ο επόμενος αριθμός να είναι μικρότερος ή ίσος με 10. Μόλις ο αριθμός είναι μεγαλύτερος από 1o, ο βρόχος σας θα σταματήσει.
Εδώ είναι ο κώδικας VBA που θα εκτελέσει αυτόν τον βρόχο και θα εμφανίσει το αποτέλεσμα σε ένα πλαίσιο μηνυμάτων.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do Until i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End End
Ο παραπάνω βρόχος συνεχίζει να λειτουργεί έως ότου η τιμή του «i» γίνει 11. Μόλις γίνει 11, ο βρόχος τελειώνει (καθώς η συνθήκη «Μέχρι» γίνεται True).
Παράδειγμα 2 - Εισαγάγετε ημερομηνίες για τον τρέχοντα μήνα
Ας υποθέσουμε ότι θέλετε να εισαγάγετε όλες τις ημερομηνίες του τρέχοντος μήνα σε μια στήλη φύλλου εργασίας.
Μπορείτε να το κάνετε αυτό χρησιμοποιώντας τον ακόλουθο κώδικα βρόχου Do Until:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Έτος (Ημερομηνία), Μήνας (Ημερομηνία), 1) Εύρος έως τον μήνα (CMDate) Μήνας (Ημερομηνία) ("A1"). Offset ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Ο παραπάνω κώδικας θα εισάγει όλες τις ημερομηνίες στην πρώτη στήλη του φύλλου εργασίας (ξεκινώντας από το Α1). Ο κύκλος συνεχίζεται έως ότου ο μήνας της μεταβλητής CMDate δεν είναι ίσος με αυτόν του τρέχοντος μήνα.
Έξοδος από τη δήλωση
Μπορείτε να χρησιμοποιήσετε τη δήλωση "Έξοδος Do" για να βγείτε από τον βρόχο.
Μόλις ο κώδικας εκτελέσει τη γραμμή ‘Exit Do’, βγαίνει από τον βρόχο Do Until και περνά το στοιχείο ελέγχου στην επόμενη γραμμή αμέσως μετά τον βρόχο.
Για παράδειγμα, εάν θέλετε να εισαγάγετε τις πρώτες 10 ημερομηνίες μόνο, τότε μπορείτε να βγείτε από τον βρόχο μόλις εισαχθούν οι πρώτες 10 ημερομηνίες.
Ο παρακάτω κώδικας θα το κάνει αυτό:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Έτος (Ημερομηνία), Μήνας (Ημερομηνία), 1) Εύρος έως τον μήνα (CMDate) Μήνας (Ημερομηνία) ("A1"). Offset ( i, 0) = CMDate i = i + 1 If i> = 10 Τότε Έξοδος Do CMDate = CMDate + 1 Loop End Sub
Στον παραπάνω κώδικα, μόλις η τιμή του «i» γίνει 10, εκτελείται η κατάσταση Exit Do και ο βρόχος τελειώνει.
Για κάθε
Στο VBA, μπορείτε να περιηγηθείτε σε ένα σύνολο συλλογών χρησιμοποιώντας τον βρόχο «Για κάθε».
Ακολουθούν ορισμένα παραδείγματα συλλογών στο Excel VBA:
- Μια συλλογή από όλα τα ανοιχτά βιβλία εργασίας.
- Μια συλλογή από όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας.
- Μια συλλογή όλων των κελιών σε μια περιοχή επιλεγμένων κελιών.
- Μια συλλογή από όλα τα γραφήματα ή τα σχήματα στο βιβλίο εργασίας.
Χρησιμοποιώντας το βρόχο «Για κάθε», μπορείτε να περάσετε από κάθε ένα από τα αντικείμενα μιας συλλογής και να εκτελέσετε κάποια ενέργεια σε αυτό.
Για παράδειγμα, μπορείτε να περάσετε από όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας και να τα προστατέψετε, ή μπορείτε να περάσετε από όλα τα κελιά της επιλογής και να αλλάξετε τη μορφοποίηση.
Με το βρόχο «Για κάθε» (αναφέρεται επίσης ως βρόχος «Για κάθε-επόμενο»), δεν χρειάζεται να γνωρίζετε πόσα αντικείμενα υπάρχουν σε μια συλλογή.
Ο βρόχος «Για κάθε» θα περνούσε αυτόματα από κάθε αντικείμενο και θα εκτελούσε την καθορισμένη ενέργεια. Για παράδειγμα, εάν θέλετε να προστατεύσετε όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας, ο κωδικός θα είναι ο ίδιος είτε έχετε ένα βιβλίο εργασίας με 3 φύλλα εργασίας είτε 30 φύλλα εργασίας.
Εδώ είναι η σύνταξη του βρόχου Για κάθε-επόμενο στο Excel VBA.
Για κάθε στοιχείο της συλλογής [Αποκλεισμός κώδικα για εκτέλεση] Επόμενο [στοιχείο]
Τώρα ας δούμε μερικά παραδείγματα χρήσης του Για κάθε βρόχο στο Excel.
Παράδειγμα 1 - Μεταβείτε σε όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας (και προστατέψτε το)
Ας υποθέσουμε ότι έχετε ένα βιβλίο εργασίας όπου θέλετε να προστατεύσετε όλα τα φύλλα εργασίας.
Παρακάτω για κάθε επόμενο βρόχο μπορείτε να το κάνετε αυτό εύκολα:
Sub ProtectSheets () Dim ws As Worksheet For Every ws In ActiveWorkbook. Worksheets ws.Προστασία Επόμενο ws Τέλος Sub
Στον παραπάνω κώδικα, ορίσαμε τη μεταβλητή "ws" ως αντικείμενο φύλλου εργασίας. Αυτό λέει στη VBA ότι το "ws" πρέπει να ερμηνευτεί ως αντικείμενο φύλλου εργασίας στον κώδικα.
Τώρα χρησιμοποιούμε τη δήλωση «Για καθένα» για να περάσουμε από κάθε «ws» (που είναι αντικείμενο φύλλου εργασίας) στη συλλογή όλων των φύλλων εργασίας στο ενεργό βιβλίο εργασίας (δίνεται από το ActiveWorkbook.Worksheets).
Σημειώστε ότι σε αντίθεση με άλλους βρόχους όπου προσπαθήσαμε να προστατεύσουμε όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας, εδώ δεν χρειάζεται να ανησυχούμε για το πόσα φύλλα εργασίας υπάρχουν στο βιβλίο εργασίας.
Δεν χρειάζεται να τα μετρήσουμε για να τρέξουμε τον βρόχο. Για κάθε βρόχο εξασφαλίζει ότι όλα τα αντικείμενα αναλύονται ένα προς ένα.
Παράδειγμα 2 - Μεταβείτε σε όλα τα ανοιχτά βιβλία εργασίας (και αποθηκεύστε όλα)
Εάν εργάζεστε με πολλά βιβλία εργασίας ταυτόχρονα, μπορεί να σας βοηθήσει να αποθηκεύσετε όλα αυτά τα βιβλία εργασίας ταυτόχρονα.
Ο παρακάτω κώδικας VBA μπορεί να το κάνει αυτό για εμάς:
Sub SaveAllWorkbooks () Dim wb As Workbook For Every wb In Workbooks wb.Save Next wb End Sub
Λάβετε υπόψη ότι σε αυτόν τον κώδικα, δεν λαμβάνετε ένα μήνυμα που σας ζητά να αποθηκεύσετε το βιβλίο εργασίας σε μια συγκεκριμένη τοποθεσία (αν το αποθηκεύσετε για πρώτη φορά).
Το αποθηκεύει στον προεπιλεγμένο φάκελο (ήταν ο φάκελος "Έγγραφα" στην περίπτωσή μου). Αυτός ο κώδικας λειτουργεί καλύτερα όταν αυτά τα αρχεία είναι ήδη αποθηκευμένα και κάνετε αλλαγές και θέλετε να αποθηκεύσετε γρήγορα όλα τα βιβλία εργασίας.
Παράδειγμα 3 - Περάστε από όλα τα κελιά σε μια επιλογή (Επισημάνετε αρνητικές τιμές)
Χρησιμοποιώντας τον βρόχο "Για κάθε", μπορείτε να κάνετε βρόχο σε όλα τα κελιά σε ένα συγκεκριμένο εύρος ή στην επιλεγμένη περιοχή.
Αυτό μπορεί να είναι χρήσιμο όταν θέλετε να αναλύσετε κάθε κελί και να εκτελέσετε μια ενέργεια με βάση αυτό.
Για παράδειγμα, παρακάτω είναι ο κώδικας που θα περάσει από όλα τα κελιά στην επιλογή και θα αλλάξει το χρώμα κελιού των κελιών με αρνητικές τιμές σε κόκκινο.
Sub HighlightNegativeCells () Dim Cll As Range For Every Cll In Selection If Cll.Value <0 Then Cll.Interior.Color = vbΚόκκινο Τέλος Αν Επόμενο Cll End Sub
(Σημείωση έχω χρησιμοποιήσει το Cll ως σύντομο όνομα μεταβλητής για το κελί. Συνιστάται να μην χρησιμοποιείτε ονόματα αντικειμένων όπως φύλλα ή εύρος ως ονόματα μεταβλητών)
Στον παραπάνω κώδικα, ο βρόχος Για κάθε-επόμενο περνά από τη συλλογή κελιών στην επιλογή. Η δήλωση IF χρησιμοποιείται για να προσδιορίσει εάν η τιμή του κελιού είναι αρνητική ή όχι. Σε περίπτωση που είναι, στο κελί δίνεται κόκκινο εσωτερικό χρώμα, αλλιώς πηγαίνει στο επόμενο κελί.
Σε περίπτωση που δεν έχετε επιλογή και, αντίθετα, θέλετε η VBA να επιλέξει όλα τα συμπληρωμένα κελιά σε μια στήλη, ξεκινώντας από ένα συγκεκριμένο κελί (όπως χρησιμοποιούμε το πλήκτρο Control + Shift + κάτω βέλος για να επιλέξουμε όλα τα γεμισμένα κελιά), μπορείτε χρησιμοποιήστε τον παρακάτω κωδικό:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) For Every Cll In Rng If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Στο παραπάνω παράδειγμα, δεν έχει σημασία πόσα γεμάτα κελιά υπάρχουν. Θα ξεκινήσει από το κελί Α1 και θα αναλύσει όλα τα συνεχόμενα γεμάτα κελιά στη στήλη.
Επίσης, δεν χρειάζεται να έχετε επιλέξει το κελί A1. Μπορείτε να επιλέξετε οποιοδήποτε κελί μακριά και όταν εκτελείται ο κώδικας, θα εξακολουθεί να λαμβάνει υπόψη όλα τα κελιά της στήλης Α (ξεκινώντας από το Α1) και θα χρωματίζει τα αρνητικά κελιά.
Δήλωση «Έξοδος για»
Μπορείτε να χρησιμοποιήσετε τη δήλωση «Έξοδος για» στον βρόχο Για κάθε-επόμενο για να βγει από τον βρόχο. Αυτό γίνεται συνήθως σε περίπτωση που πληρούται μια συγκεκριμένη προϋπόθεση.
Για παράδειγμα, στο Παράδειγμα 3, καθώς περνάμε από ένα σύνολο κελιών, μπορεί να είναι πιο αποτελεσματικό να ελέγξουμε αν υπάρχουν αρνητικές τιμές ή όχι. Σε περίπτωση που δεν υπάρχουν αρνητικές τιμές, μπορούμε απλά να βγούμε από το βρόχο και να εξοικονομήσουμε χρόνο επεξεργασίας VBA.
Παρακάτω είναι ο κώδικας VBA που θα το κάνει αυτό:
Sub HighlightNegativeCells () Dim Cll As Range For Every Cll In Selection If WorksheetFunction.Min (Selection)> = 0 Στη συνέχεια Exit For If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Πού να τοποθετήσετε τον κωδικό VBA
Αναρωτιέστε πού πηγαίνει ο κώδικας VBA στο βιβλίο εργασίας του Excel;
Το Excel έχει ένα backend VBA που ονομάζεται VBA editor. Πρέπει να αντιγράψετε και να επικολλήσετε τον κώδικα στο παράθυρο κωδικού της λειτουργικής μονάδας VB Editor.
Ακολουθούν τα βήματα για να το κάνετε αυτό:
- Μεταβείτε στην καρτέλα Προγραμματιστής.
- Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
- Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα. Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
- Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
- Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.
