Αυτό το άρθρο θα δείξει πώς να δημιουργήσετε ένα Δυναμικό εύρος στο Excel VBA.
Η δήλωση ενός συγκεκριμένου εύρους κελιών ως μεταβλητή στο Excel VBA μας περιορίζει να δουλεύουμε μόνο με αυτά τα συγκεκριμένα κελιά. Δηλώνοντας δυναμικά εύρη στο Excel, αποκτούμε πολύ μεγαλύτερη ευελιξία ως προς τον κώδικά μας και τη λειτουργικότητα που μπορεί να εκτελέσει.
Αναφορά σε εύρη και κελιά
Όταν αναφέρουμε το αντικείμενο Range ή Cell στο Excel, συνήθως αναφερόμαστε σε αυτά με σκληρό κώδικα στη γραμμή και τις στήλες που απαιτούμε.
Range Property
Χρησιμοποιώντας την ιδιότητα εύρους, στα παραδείγματα γραμμών κώδικα παρακάτω, μπορούμε να εκτελέσουμε ενέργειες σε αυτό το εύρος, όπως αλλαγή του χρώματος των κελιών ή καθιστώντας τα κελιά έντονα.
| 12 | Εύρος ("A1: A5"). Font.Color = vbRedΕύρος ("A1: A5"). Font.Bold = True |
Ιδιότητα κελιών
Ομοίως, μπορούμε να χρησιμοποιήσουμε την ιδιότητα κελιών για να αναφερθούμε σε μια περιοχή κελιών αναφέροντας απευθείας τη γραμμή και τη στήλη στην ιδιότητα κελιών. Η σειρά πρέπει πάντα να είναι ένας αριθμός, αλλά η στήλη μπορεί να είναι ένας αριθμός ή ένα γράμμα που περικλείεται σε εισαγωγικά.
Για παράδειγμα, η διεύθυνση κελιού A1 μπορεί να αναφέρεται ως:
| 1 | Κελιά (1,1) |
Ή
| 1 | Κελιά (1, "Α") |
Για να χρησιμοποιήσουμε την ιδιότητα κελιών για να αναφέρουμε μια περιοχή κελιών, πρέπει να υποδείξουμε την αρχή του εύρους και το τέλος του εύρους.
Για παράδειγμα στο εύρος αναφοράς A1: A6 θα μπορούσαμε να χρησιμοποιήσουμε αυτήν τη σύνταξη παρακάτω:
| 1 | Εύρος (Κελιά (1,1), Κελιά (1,6) |
Στη συνέχεια, μπορούμε να χρησιμοποιήσουμε την ιδιότητα Κελιά για να εκτελέσουμε ενέργειες στο εύρος σύμφωνα με τις παρακάτω γραμμές κώδικα παρακάτω:
| 12 | Εύρος (Κελιά (2, 2), Κελιά (6, 2)). Γραμματοσειρά. Χρώμα = vbRedΕύρος (Κελιά (2, 2), Κελιά (6, 2)). Γραμματοσειρά. Τολμηρή = Αληθινή |
Δυναμικά εύρη με μεταβλητές
Καθώς το μέγεθος των δεδομένων μας αλλάζει στο Excel (δηλαδή χρησιμοποιούμε περισσότερες γραμμές και στήλες από τα εύρη που έχουμε κωδικοποιήσει), θα ήταν χρήσιμο να αλλάξουν και τα εύρη στα οποία αναφερόμαστε στον κώδικά μας. Χρησιμοποιώντας το αντικείμενο Range παραπάνω, μπορούμε να δημιουργήσουμε μεταβλητές για να αποθηκεύσουμε τους μέγιστους αριθμούς γραμμών και στηλών της περιοχής του φύλλου εργασίας του Excel που χρησιμοποιούμε και να χρησιμοποιήσουμε αυτές τις μεταβλητές για να προσαρμόσουμε δυναμικά το αντικείμενο εύρους ενώ εκτελείται ο κώδικας.
Για παράδειγμα
| 1234 | Dim lRow ως ακέραιοςDim lCol ως ακέραιοςlRow = Range ("A1048576"). Τέλος (xlUp). ΣειράlCol = Εύρος ("XFD1"). Τέλος (xlToLeft). Στήλη |
Τελευταία σειρά στη στήλη
Δεδομένου ότι υπάρχουν 1048576 σειρές σε ένα φύλλο εργασίας, η μεταβλητή lRow θα πάει στο κάτω μέρος του φύλλου και στη συνέχεια θα χρησιμοποιήσει τον ειδικό συνδυασμό του πλήκτρου Τέλος συν το πλήκτρο επάνω βέλους για να μεταβεί στην τελευταία σειρά που χρησιμοποιείται στο φύλλο εργασίας - αυτό θα μας δώσει τον αριθμό της σειράς που χρειαζόμαστε στην περιοχή μας.
Τελευταία στήλη στη σειρά
Ομοίως, το lCol θα μετακινηθεί στη στήλη XFD που είναι η τελευταία στήλη σε ένα φύλλο εργασίας και στη συνέχεια θα χρησιμοποιήσει τον ειδικό συνδυασμό πλήκτρων του πλήκτρου Τέλος συν το αριστερό πλήκτρο βέλους για να μεταβεί στην τελευταία στήλη που χρησιμοποιείται στο φύλλο εργασίας - αυτό θα μας δώσει αριθμός της στήλης που χρειαζόμαστε στην περιοχή μας.
Επομένως, για να λάβουμε ολόκληρο το εύρος που χρησιμοποιείται στο φύλλο εργασίας, μπορούμε να εκτελέσουμε τον ακόλουθο κώδικα:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol ως ακέραιοςDim rng As RangelRow = Range ("A1048576"). Τέλος (xlUp). Σειρά"χρησιμοποιήστε το lRow για να βρείτε την τελευταία στήλη στο εύροςlCol = Εύρος ("XFD" & lRow). Τέλος (xlToLeft). ΣτήληΡύθμιση rng = Εύρος (Κελιά (1, 1), Κελιά (lRow, lCol))'msgbox για να μας δείξει το εύροςMsgBox "Range is" & rng.AddressΤέλος υπο |
SpecialCells - LastCell
Μπορούμε επίσης να χρησιμοποιήσουμε τη μέθοδο SpecialCells του αντικειμένου εύρους για να χρησιμοποιήσουμε την τελευταία γραμμή και στήλη σε ένα φύλλο εργασίας.
| 123456789101112 | Υποχρήση SpecialCells ()Dim lRow As IntegerDim lCol ως ακέραιοςDim rng As RangeDim rngBegin As RangeΟρισμός rngBegin = Εύρος ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell). ΣειράlCol = rngBegin.SpecialCells (xlCellTypeLastCell). ΣτήληΡύθμιση rng = Εύρος (Κελιά (1, 1), Κελιά (lRow, lCol))'msgbox για να μας δείξει το εύροςMsgBox "Range is" & rng.AddressΤέλος υπο |
UsedRange
Η μέθοδος Used Range περιλαμβάνει όλα τα κελιά που έχουν τιμές στο τρέχον φύλλο εργασίας.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeΡύθμιση rng = ActiveSheet.UsedRange'msgbox για να μας δείξει το εύροςMsgBox "Range is" & rng.AddressΤέλος υπο |
CurrentRegion
Η τρέχουσα περιοχή διαφέρει από το UsedRange στο ότι κοιτάζει τα κελιά που περιβάλλουν ένα κελί που έχουμε δηλώσει ως εύρος έναρξης (δηλαδή τη μεταβλητή rngBegin στο παρακάτω παράδειγμα) και, στη συνέχεια, κοιτάζει όλα τα κελιά που είναι «προσαρτημένα» ή σχετίζονται στο κελί που έχει δηλωθεί. Εάν εμφανιστεί ένα κενό κελί σε μια σειρά ή στήλη, τότε το CurrentRegion θα σταματήσει να ψάχνει για περαιτέρω κελιά.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rngBegin As RangeΟρισμός rngBegin = Εύρος ("A1")Ορίστε rng = rngBegin.CurrentRegion'msgbox για να μας δείξει το εύροςMsgBox "Range is" & rng.AddressΤέλος υπο |
Εάν χρησιμοποιούμε αυτήν τη μέθοδο, πρέπει να βεβαιωθούμε ότι όλα τα κελιά στο εύρος που χρειάζεστε είναι συνδεδεμένα χωρίς κενές γραμμές ή στήλες μεταξύ τους.
Ονομάστηκε Range
Μπορούμε επίσης να αναφέρουμε τις ονομαστικές σειρές στον κωδικό μας. Τα ονόματα εύρους μπορούν να είναι δυναμικά, εφόσον όταν ενημερώνονται ή εισάγονται δεδομένα, το όνομα εύρους μπορεί να αλλάξει ώστε να περιλαμβάνει τα νέα δεδομένα.
Αυτό το παράδειγμα θα αλλάξει τη γραμματοσειρά σε έντονη για το όνομα εύρους "Ιανουάριος"
| 12345 | Sub RangeNameExample ()Dim rng ως RangeΟρισμός rng = Εύρος ("Ιανουάριος")rng.Font.Bold = = TrueΤέλος υπο |
Όπως θα δείτε στην παρακάτω εικόνα, εάν μια γραμμή προστίθεται στο όνομα της περιοχής, τότε το όνομα περιοχής ενημερώνεται αυτόματα για να συμπεριλάβει αυτήν τη σειρά.
Εάν εκτελέσουμε ξανά τον παράδειγμα κώδικα, το εύρος που επηρεάζεται από τον κώδικα θα είναι C5: C9 ενώ στην πρώτη περίπτωση θα ήταν C5: C8.
Πίνακες
Μπορούμε να αναφέρουμε πίνακες (κάντε κλικ για περισσότερες πληροφορίες σχετικά με τη δημιουργία και τον χειρισμό πινάκων στο VBA) στον κώδικά μας. Καθώς τα δεδομένα ενός πίνακα στο Excel ενημερώνονται ή αλλάζουν, ο κώδικας που αναφέρεται στον πίνακα θα αναφέρεται στη συνέχεια στα ενημερωμένα δεδομένα του πίνακα. Αυτό είναι ιδιαίτερα χρήσιμο όταν αναφέρεστε σε Συγκεντρωτικούς πίνακες που είναι συνδεδεμένοι σε εξωτερική πηγή δεδομένων.
Χρησιμοποιώντας αυτόν τον πίνακα στον κωδικό μας, μπορούμε να αναφερθούμε στις στήλες του πίνακα με τις επικεφαλίδες σε κάθε στήλη και να εκτελέσουμε ενέργειες στη στήλη σύμφωνα με το όνομά τους. Καθώς οι σειρές στον πίνακα αυξάνονται ή μειώνονται σύμφωνα με τα δεδομένα, το εύρος του πίνακα θα προσαρμοστεί ανάλογα και ο κωδικός μας θα εξακολουθεί να λειτουργεί για ολόκληρη τη στήλη του πίνακα.
Για παράδειγμα:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). DeleteΤέλος υπο |
