Excel VBA Loops - Για καθένα, για επόμενο, Do while, Nested & More

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

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

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

Γρήγορα παραδείγματα βρόχου VBA

Για κάθε βρόχους

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

Βρόχος σε όλα τα φύλλα εργασίας στο τετράδιο εργασίας

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

12345678

Sub LoopThroughSheets ()Dim ws Ως φύλλο εργασίαςΓια κάθε ws σε φύλλα εργασίαςws.Visible = TrueΕπόμενοΤέλος υπο

Loop Through All Cells in Range

Αυτός ο κώδικας θα περιηγηθεί σε μια σειρά κελιών, δοκιμάζοντας εάν η τιμή κελιού είναι αρνητική, θετική ή μηδενική:

1234567891011121314

Sub If_Loop ()Dim Cell ως RangeΓια κάθε κελί σε εμβέλεια ("A2: A6")Αν Cell.Value> 0 ΤότεCell.Offset (0, 1) .Value = "Θετικό"ElseIf Cell.Value <0 ΤότεCell.Offset (0, 1) .Value = "Αρνητικό"ΑλλούCell.Offset (0, 1) .Value = "Zero"Τέλος εανΕπόμενο κελίΤέλος υπο

Για τους επόμενους βρόχους

Ένας άλλος τύπος "For" Loop είναι ο For Next Loop. Το For Next Loop σάς επιτρέπει να κάνετε βρόχο μέσω ακεραίων.

Αυτός ο κώδικας θα μετακινηθεί σε ακέραιους αριθμούς 1 έως 10, εμφανίζοντας τον καθένα με ένα πλαίσιο μηνυμάτων:

123456

Sub ForLoop ()Dim i As IntegerΓια i = 1 έως 10MsgBox iΕπόμενο iΤέλος υπο

Do while Loops

Το Do When Loops θα κάνει βρόχο ενώ πληρείται μια προϋπόθεση. Αυτός ο κώδικας θα κάνει επίσης βρόχο στους ακέραιους 1 έως 10, εμφανίζοντας τον καθένα με ένα πλαίσιο μηνυμάτων.

12345678

Sub DoWhileLoop ()Dim n ως ακέραιοςn = 1Do while n <11MsgBox nn = n + 1ΒρόχοςΤέλος υπο

Κάνετε μέχρι βρόχους

Αντιστρόφως, το Do Until Loops θα κάνει βρόχο έως ότου ικανοποιηθεί μια προϋπόθεση. Αυτός ο κώδικας κάνει το ίδιο με τα δύο προηγούμενα παραδείγματα.

12345678

Sub DoUntilLoop ()Dim n ως ακέραιοςn = 1Κάντε μέχρι n> = 10MsgBox nn = n + 1ΒρόχοςΤέλος υπο

Θα το συζητήσουμε παρακάτω, αλλά πρέπει να είστε εξαιρετικά προσεκτικοί όταν δημιουργείτε βρόχους Do while ή Do Until, ώστε να μην δημιουργείτε βρόχο που δεν τελειώνει.

VBA Loop Builder

Αυτό είναι ένα στιγμιότυπο οθόνης του "Loop Builder" από το πρόσθετο Premium VBA: AutoMacro. Το Loop Builder σάς επιτρέπει να χτίζετε γρήγορα και εύκολα βρόχους για να περιτυλίγετε διαφορετικά αντικείμενα ή αριθμούς. Μπορείτε να εκτελέσετε ενέργειες σε κάθε αντικείμενο ή/και να επιλέξετε μόνο αντικείμενα που πληρούν ορισμένα κριτήρια.

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

Τώρα θα καλύψουμε σε βάθος τους διαφορετικούς τύπους βρόχων.

VBA για επόμενο βρόχο

Για σύνταξη βρόχου

Το For Next Loop σάς επιτρέπει να επαναλάβετε ένα μπλοκ κώδικα καθορισμένο αριθμό φορών. Η σύνταξη είναι:

12345

[Μετρητής φωτισμού ως ακέραιος]Για μετρητή = Έναρξη έως τέλος [Τιμή βήματος][Κάνε κάτι]Επόμενο [Μετρητής]

Όπου τα στοιχεία σε αγκύλες είναι προαιρετικά.

• [Dim Counter τόσο μακρύ] - Δηλώνει τη μεταβλητή του μετρητή. Απαιτείται εάν η επιλογή ρητή δηλώνεται στο επάνω μέρος της μονάδας σας.
• Μετρητής - Μια ακέραιη μεταβλητή που χρησιμοποιείται για την καταμέτρηση
• Αρχή - Η τιμή εκκίνησης (Παράδειγμα 1)
• Τέλος - Η τελική τιμή (Παράδειγμα 10)
• [Τιμή βήματος] - Σας επιτρέπει να μετράτε κάθε n ακέραιους αριθμούς αντί για κάθε 1 ακέραιο. Μπορείτε επίσης να κάνετε αντίστροφα με αρνητική τιμή (π.χ. Βήμα -1)
• [Κάνε κάτι] - Ο κωδικός που θα επαναληφθεί
• Επόμενο [Μετρητής] - Δήλωση κλεισίματος για τον επόμενο βρόχο. Μπορείτε να συμπεριλάβετε τον μετρητή ή όχι. Ωστόσο, συνιστώ ανεπιφύλακτα να συμπεριλάβετε τον μετρητή, καθώς διευκολύνει την ανάγνωση του κώδικα.

Αν αυτό είναι μπερδεμένο, μην ανησυχείτε. Θα εξετάσουμε μερικά παραδείγματα:

Μετρήστε έως το 10

Αυτός ο κωδικός θα μετρήσει έως 10 χρησιμοποιώντας έναν βρόχο για επόμενο:

12345678

Υπο ForEach_CountTo10 ()Dim n ως ακέραιοςΓια n = 1 έως 10MsgBox nΕπόμενο nΤέλος υπο

Για Βήμα Βρόχου

Μέτρηση έως 10 - Μόνο άρτιοι αριθμοί

Αυτός ο κωδικός θα μετρήσει έως 10 μόνο μετρώντας ζυγούς αριθμούς:

12345678

Υπο ForEach_CountTo10_Even ()Dim n ως ακέραιοςΓια n = 2 έως 10 Βήμα 2MsgBox nΕπόμενο nΤέλος υπο

Σημειώστε ότι προσθέσαμε το "Βήμα 2". Αυτό λέει στον βρόχο Για να "περπατήσει" στον μετρητή κατά 2. Μπορούμε επίσης να χρησιμοποιήσουμε μια αρνητική τιμή βημάτων για να κάνουμε αντίστροφα:

Για Βήμα Βρόχου - Αντίστροφο

Αντίστροφη μέτρηση από 10

Αυτός ο κωδικός θα μετρήσει αντίστροφα από 10:

123456789

Υπο ForEach_Countdown_Inverse ()Dim n ως ακέραιοςΓια n = 10 έως 1 Βήμα -1MsgBox nΕπόμενο nMsgBox "Lift Off"Τέλος υπο

Διαγραφή γραμμών εάν το κελί είναι κενό

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

Αυτό το παράδειγμα θα διαγράψει γραμμές με κενά κελιά (ξεκινώντας από την κάτω σειρά):

12345678910

Υπο ForEach_DeleteRows_BlankCells ()Dim n ως ακέραιοςΓια n = 10 έως 1 Βήμα -1If Range ("a" & n) .Value = "" ΤότεΕύρος ("a" & n) .EntireRow.DeleteΤέλος εανΕπόμενο nΤέλος υπο

Nested For Loop

Μπορείτε να "φωλιάσετε" ένα For Loop μέσα σε ένα άλλο For Loop. Θα χρησιμοποιήσουμε το Nested For Loops για να δημιουργήσουμε έναν πίνακα πολλαπλασιασμού:

1234567891011

Υπο Nested_ForEach_MultiplicationTable ()Χαμηλή σειρά ως ακέραιος, κολ ως ακέραιοςΓια τη σειρά = 1 έως 9Για col = 1 έως 9Κελιά (γραμμή + 1, στήλη + 1). Αξία = σειρά * κολΕπόμενο κολΕπόμενη σειράΤέλος υπο

Έξοδος για

Η δήλωση Exit For σάς επιτρέπει να βγείτε αμέσως από έναν βρόχο For Next.

Συνήθως θα χρησιμοποιούσατε το Exit For μαζί με μια δήλωση If, εξερχόμενη από το For Next Loop εάν πληρούται μια συγκεκριμένη προϋπόθεση.

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

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

12345678910111213

Sub ExitFor_Loop ()Dim i As IntegerΓια i = 1 έως 1000If Range ("A" & i) .Value = "error" ΤότεΕύρος ("A" & i). ΕπιλέξτεMsgBox "Βρέθηκε σφάλμα"Έξοδος γιαΤέλος εανΕπόμενο iΤέλος υπο

Σημαντικό: Στην περίπτωση του Nested For Loops, Exit For εξέρχεται μόνο από το τρέχον For Loop, όχι από όλους τους ενεργούς βρόχους.

Συνέχεια για

Το VBA δεν έχει την εντολή "Συνέχεια" που βρίσκεται στη Visual Basic. Αντ 'αυτού, θα χρειαστεί να χρησιμοποιήσετε το "Exit".

VBA για κάθε βρόχο

Το VBA για κάθε βρόχο θα κάνει βρόχο σε όλα τα αντικείμενα μιας συλλογής:

• Όλα τα κελιά σε μια περιοχή
• Όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας
• Όλα τα σχήματα σε ένα φύλλο εργασίας
• Όλα τα ανοιχτά βιβλία εργασίας

Μπορείτε επίσης να χρησιμοποιήσετε το Nested For Every Loops για:

• Όλα τα κελιά σε μια περιοχή σε όλα τα φύλλα εργασίας
• Όλα τα σχήματα σε όλα τα φύλλα εργασίας
• Όλα τα φύλλα σε όλα τα ανοιχτά βιβλία εργασίας
• και ούτω καθεξής…

Η σύνταξη είναι:

123	Για κάθε αντικείμενο στη συλλογή[Κάνε κάτι]Επόμενο [Αντικείμενο]

Οπου:

• Αντικείμενο - Μεταβλητή που αντιπροσωπεύει μια περιοχή, ένα φύλλο εργασίας, ένα βιβλίο εργασίας, ένα σχήμα κ.λπ. (π.χ. rng)
• Συλλογή - Συλλογή αντικειμένων (π.χ. Εύρος («a1: a10»)
• [Κάνε κάτι] - Μπλοκ κώδικα για εκτέλεση σε κάθε αντικείμενο
• Επόμενο [Αντικείμενο] - Καταληκτική δήλωση. Το [Object] είναι προαιρετικό, ωστόσο συνιστάται ανεπιφύλακτα.

Για κάθε κελί σε εμβέλεια

Αυτός ο κώδικας θα κάνει βρόχο σε κάθε κελί σε μια περιοχή:

123456789

Υπο ForEachCell_inRange ()Dim cell As RangeΓια κάθε κελί εντός εμβέλειας ("a1: a10")cell.Value = cell.Offset (0,1) .ValueΕπόμενο κελίΤέλος υπο

Για κάθε φύλλο εργασίας στο τετράδιο εργασίας

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

123456789

Υπο ForEachSheet_inWorkbook ()Dim ws Ως φύλλο εργασίαςΓια κάθε ws σε φύλλα εργασίαςws.Απροστασία "κωδικού πρόσβασης"Επόμενο wsΤέλος υπο

Για κάθε ανοιχτό βιβλίο εργασίας

Αυτός ο κωδικός θα αποθηκεύσει και θα κλείσει όλα τα ανοιχτά βιβλία εργασίας:

123456789

Υπο ForEachWB_inWorkbooks ()Dim wb Ως βιβλίο εργασίαςΓια κάθε wb Σε βιβλία εργασίαςwb. Κλείσιμο SaveChanges: = TrueΕπόμενο wbΤέλος υπο

Για κάθε σχήμα σε φύλλο εργασίας

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

123456789

Sub ForEachShape ()Dim shp As ShapeΓια κάθε shp Στο ActiveSheet.Shapesshp. ΔιαγραφήΕπόμενο shpΤέλος υπο

Για κάθε σχήμα σε κάθε φύλλο εργασίας στο τετράδιο εργασίας

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

1234567891011

Υπο ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetΓια κάθε ws σε φύλλα εργασίαςΓια κάθε shp Σε ws.Shapesshp. ΔιαγραφήΕπόμενο shpΕπόμενο wsΤέλος υπο

Για καθένα - IF Loop

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

Αυτός ο κώδικας θα αποκρύψει όλες τις κενές σειρές σε ένα εύρος:

12345678910

Υπο ForEachCell_inRange ()Dim cell As RangeΓια κάθε κελί εντός εμβέλειας ("a1: a10")If cell.Value = "" Τότε _cell.EntireRow.Hidden = TrueΕπόμενο κελίΤέλος υπο

VBA Do while Loop

Τα VBA Do While και Do Until (δείτε την επόμενη ενότητα) είναι πολύ παρόμοια. Θα επαναλάβουν ένα βρόχο ενώ (ή έως ότου) πληρούνται οι προϋποθέσεις.

Ο βρόχος Do while θα επαναλάβει ένα βρόχο ενώ πληρείται μια προϋπόθεση.

Ακολουθεί η σύνταξη Do while:

123	Do while Condition[Κάνε κάτι]Βρόχος

Οπου:

• Κατάσταση - Η συνθήκη για δοκιμή
• [Κάνε κάτι] - Το μπλοκ κωδικών για επανάληψη

Μπορείτε επίσης να ρυθμίσετε έναν βρόχο Do while με την κατάσταση στο τέλος του βρόχου:

123	Κάνω[Κάνε κάτι]Loop while Condition

Θα παρουσιάσουμε το καθένα και θα δείξουμε πώς διαφέρουν:

Κάνε Ενώ

Εδώ είναι το παράδειγμα του βρόχου Do While που δείξαμε προηγουμένως:

12345678

Sub DoWhileLoop ()Dim n ως ακέραιοςn = 1Do while n <11MsgBox nn = n + 1ΒρόχοςΤέλος υπο

Βρόχος Ενώ

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

12345678

Sub DoLoopWhile ()Dim n ως ακέραιοςn = 1ΚάνωMsgBox nn = n + 1Βρόχος Ενώ n <11Τέλος υπο

VBA Do Until Loop

Εκτελέστε έως ότου οι βρόχοι επαναλάβουν έναν βρόχο έως ότου ικανοποιηθεί μια συγκεκριμένη προϋπόθεση. Η σύνταξη είναι ουσιαστικά η ίδια με τους βρόχους Do while:

123	Εκτελέστε μέχρι την κατάσταση[Κάνε κάτι]Βρόχος

και ομοίως η συνθήκη μπορεί να πάει στην αρχή ή στο τέλος του βρόχου:

123	Κάνω[Κάνε κάτι]Βρόχος μέχρι την κατάσταση

Κάνε Μέχρι

Αυτός ο βρόχος do Until θα μετρήσει έως 10, όπως τα προηγούμενα παραδείγματά μας

12345678

Sub DoUntilLoop ()Dim n ως ακέραιοςn = 1Κάντε μέχρι n> 10MsgBox nn = n + 1ΒρόχοςΤέλος υπο

Βρόχος Μέχρι

Αυτός ο βρόχος μέχρι το βρόχο θα μετρήσει έως 10:

12345678

Sub DoLoopUntil ()Dim n ως ακέραιοςn = 1ΚάνωMsgBox nn = n + 1Βρόχος Μέχρι n> 10Τέλος υπο

Έξοδος Do Loop

Παρόμοια με τη χρήση του Exit For για έξοδο από έναν βρόχο, χρησιμοποιείτε την εντολή Exit Do για έξοδο αμέσως από έναν βρόχο Do

1

Έξοδος Do

Ακολουθεί ένα παράδειγμα Exit Do:

123456789101112131415

Sub ExitDo_Loop ()Dim i As Integeri = 1Κάντε έως ότου>> 1000If Range ("A" & i) .Value = "error" ΤότεΕύρος ("A" & i). ΕπιλέξτεMsgBox "Βρέθηκε σφάλμα"Έξοδος DoΤέλος εανi = i + 1ΒρόχοςΤέλος υπο

Τερματισμός ή διακοπή βρόχου

Όπως αναφέραμε παραπάνω, μπορείτε να χρησιμοποιήσετε το Exit For ή Exit Do για έξοδο από τους βρόχους:

1	Έξοδος για

1

Έξοδος Do

Ωστόσο, αυτές οι εντολές πρέπει να προστεθούν στον κωδικό σας προτού εκτελέσετε τον βρόχο σας.

Εάν προσπαθείτε να "σπάσετε" έναν βρόχο που τρέχει αυτήν τη στιγμή, μπορείτε να δοκιμάσετε να πατήσετε ESC ή CTRL + Πάυση για διάλειμμα στο πληκτρολόγιο. Ωστόσο, αυτό μπορεί να μην λειτουργήσει. Εάν δεν λειτουργεί, θα πρέπει να περιμένετε να τελειώσει ο βρόχος σας ή, στην περίπτωση ενός ατελείωτου βρόχου, να χρησιμοποιήσετε CTRL + ALT + Διαγράφω να αναγκάσει να κλείσει το Excel.

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

Περισσότερα Παραδείγματα Βρόχου

Loop Through Rows

Αυτό θα κάνει βρόχο σε όλες τις γραμμές σε μια στήλη:

123456789

Public Sub LoopThroughRows ()Dim cell As RangeΓια κάθε κελί σε εύρος ("A: A")Ff cell.value "" στη συνέχεια MsgBox cell.address & ":" & cell.valueΕπόμενο κελίΤέλος υπο

Loop Through Colonns

Αυτό θα κάνει βρόχο σε όλες τις στήλες σε μια σειρά:

123456789

Public Sub LoopThroughColumns ()Dim cell As RangeΓια κάθε κελί σε εύρος ("1: 1")If cell.Value "" Στη συνέχεια MsgBox cell.Address & ":" & cell.ValueΕπόμενο κελίΤέλος υπο

Βρόχος μέσα από αρχεία σε ένα φάκελο

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

12345678910111213141516171819

Sub LoopThroughFiles ()Dim oFSO Ως αντικείμενοDim oFolder As ObjectDim oFile ως αντικείμενοDim i As IntegerΟρισμός oFSO = CreateObject ("Scripting.FileSystemObject")Ρύθμιση oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Για κάθε oFile In oFolder.FilesΕύρος ("A" & i). Αξία = oFile.Namei = i + 1Επόμενο αρχείοΤέλος υπο

Loop Through Array

Αυτός ο κώδικας θα περάσει από τον πίνακα "arrList":

123	Για i = LBound (arrList) Προς UBound (arrList)MsgBox arrList (i)Επόμενο i

Η συνάρτηση LBound παίρνει το "κάτω όριο" του πίνακα και το UBound παίρνει το "ανώτερο όριο".

Βρόχοι στην Access VBA

Τα περισσότερα από τα παραπάνω παραδείγματα θα λειτουργούν επίσης στο Access VBA. Ωστόσο, στην Access, κάνουμε βρόχο στο αντικείμενο Recordset και όχι στο αντικείμενο εύρους.

123456789101112131415161718

Sub LoopThroughRecords ()Στο Σφάλμα Συνέχιση ΕπόμενοDim dbs As DatabaseDim rst As RecordsetΟρίστε dbs = CurrentDbΡύθμιση rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Με πρώτη.MoveLast.MoveFirstΕκτελέστε μέχρι .EOF = TrueMsgBox (rst.Fields ("Όνομα πελάτη")).MoveNextΒρόχοςΤέλος μεπρώτα. ΚλείσιμοΡύθμιση rst = ΤίποταΟρισμός dbs = ΤίποταΤέλος υπο