Η χρήση μακροεντολών Excel μπορεί να επιταχύνει την εργασία και να σας εξοικονομήσει πολύ χρόνο.
Ένας τρόπος για να λάβετε τον κώδικα VBA είναι να καταγράψετε τη μακροεντολή και να λάβετε τον κώδικα που δημιουργεί. Ωστόσο, αυτός ο κωδικός ανά συσκευή εγγραφής μακροεντολών είναι συχνά γεμάτος με κώδικα που δεν χρειάζεται πραγματικά. Επίσης το macro recorder έχει κάποιους περιορισμούς.
Έτσι, αξίζει να έχετε μια συλλογή από χρήσιμους κώδικες μακροεντολών VBA που μπορείτε να έχετε στην πίσω τσέπη σας και να τις χρησιμοποιείτε όταν χρειάζεται.
Ενώ η συγγραφή ενός κώδικα μακροεντολής Excel VBA ενδέχεται να διαρκέσει λίγο χρόνο, μόλις ολοκληρωθεί, μπορείτε να τον διατηρήσετε διαθέσιμο ως αναφορά και να τον χρησιμοποιήσετε όποτε τον χρειαστείτε στη συνέχεια.
Σε αυτό το τεράστιο άρθρο, θα παραθέσω μερικά χρήσιμα παραδείγματα μακροεντολών του Excel που χρειάζομαι συχνά και τα κρατάω μακριά στο ιδιωτικό μου θησαυροφυλάκιο.
Θα συνεχίσω να ενημερώνω αυτό το σεμινάριο με περισσότερα παραδείγματα μακροεντολών. Αν πιστεύετε ότι κάτι πρέπει να υπάρχει στη λίστα, αφήστε ένα σχόλιο.
Μπορείτε να προσθέσετε σελιδοδείκτη σε αυτήν τη σελίδα για μελλοντική αναφορά.
Τώρα πριν μπω στο Παράδειγμα μακροεντολών και σας δώσω τον κωδικό VBA, επιτρέψτε μου πρώτα να σας δείξω πώς να χρησιμοποιήσετε αυτούς τους κωδικούς παραδείγματος.
Χρήση του κώδικα από παραδείγματα μακροεντολών του Excel
Ακολουθούν τα βήματα που πρέπει να ακολουθήσετε για να χρησιμοποιήσετε τον κώδικα από οποιοδήποτε από τα παραδείγματα:
- Ανοίξτε το βιβλίο εργασίας στο οποίο θέλετε να χρησιμοποιήσετε τη μακροεντολή.
- Κρατήστε πατημένο το πλήκτρο ALT και πατήστε F11. Αυτό ανοίγει το πρόγραμμα επεξεργασίας VB.
- Κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα στην εξερεύνηση έργου.
- Μεταβείτε στο Εισαγωγή -> Ενότητα.
- Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο κωδικού μονάδας.
Σε περίπτωση που το παράδειγμα λέει ότι πρέπει να επικολλήσετε τον κώδικα στο παράθυρο κώδικα φύλλου εργασίας, κάντε διπλό κλικ στο αντικείμενο φύλλου εργασίας και αντιγράψτε τον κώδικα στο παράθυρο κώδικα.
Αφού εισαγάγετε τον κώδικα σε ένα βιβλίο εργασίας, πρέπει να τον αποθηκεύσετε με επέκταση .XLSM ή .XLS.
Πώς να εκτελέσετε τη μακροεντολή
Αφού αντιγράψετε τον κώδικα στο VB Editor, ακολουθούν τα βήματα για την εκτέλεση της μακροεντολής:
- Μεταβείτε στην καρτέλα Προγραμματιστής.
- Κάντε κλικ στις μακροεντολές.
- Στο παράθυρο διαλόγου Μακροεντολή, επιλέξτε τη μακροεντολή που θέλετε να εκτελεστεί.
- Κάντε κλικ στο κουμπί Εκτέλεση.
Σε περίπτωση που δεν μπορείτε να βρείτε την καρτέλα προγραμματιστή στην κορδέλα, διαβάστε αυτό το σεμινάριο για να μάθετε πώς να το αποκτήσετε.
Σχετικό σεμινάριο: Διαφορετικοί τρόποι εκτέλεσης μιας μακροεντολής στο Excel.
Σε περίπτωση που ο κώδικας έχει επικολληθεί στο παράθυρο κωδικού φύλλου εργασίας, δεν χρειάζεται να ανησυχείτε για την εκτέλεση του κώδικα. Θα εκτελεστεί αυτόματα όταν πραγματοποιηθεί η καθορισμένη ενέργεια.
Τώρα, ας μπούμε στα χρήσιμα παραδείγματα μακροεντολών που μπορούν να σας βοηθήσουν να αυτοματοποιήσετε την εργασία και να εξοικονομήσετε χρόνο.
Σημείωση: Θα βρείτε πολλές περιπτώσεις απόστροφης (‘) ακολουθούμενες από μια ή δύο γραμμές. Αυτά είναι σχόλια που αγνοούνται κατά την εκτέλεση του κώδικα και τοποθετούνται ως σημειώσεις για τον εαυτό/αναγνώστη.
Σε περίπτωση που βρείτε κάποιο σφάλμα στο άρθρο ή τον κώδικα, παρακαλώ να είστε φοβεροί και ενημερώστε με.
Παραδείγματα μακροεντολών Excel
Παρακάτω παραδείγματα μακροεντολών καλύπτονται σε αυτό το άρθρο:
Αποκάλυψη όλων των φύλλων εργασίας με μια κίνηση
Εάν εργάζεστε σε ένα βιβλίο εργασίας που έχει πολλά κρυμμένα φύλλα, πρέπει να αποκρύψετε αυτά τα φύλλα ένα προς ένα. Αυτό μπορεί να πάρει λίγο χρόνο σε περίπτωση που υπάρχουν πολλά κρυμμένα φύλλα.
Εδώ είναι ο κώδικας που θα εμφανίσει όλα τα φύλλα εργασίας στο βιβλίο εργασίας.
'Αυτός ο κώδικας θα αποκρύψει όλα τα φύλλα στο βιβλίο εργασίας Sub UnhideAllWoksheets () Dim ws As Worksheet For Every ws In ActiveWorkbook. Worksheets ws.Visible = xlSheetVisible Next ws End sub
Ο παραπάνω κώδικας χρησιμοποιεί έναν βρόχο VBA (Για καθένα) για να περάσει από κάθε φύλλο εργασίας στο βιβλίο εργασίας. Στη συνέχεια, αλλάζει την ορατή ιδιότητα του φύλλου εργασίας σε ορατή.
Ακολουθεί ένα λεπτομερές σεμινάριο για το πώς να χρησιμοποιήσετε διάφορες μεθόδους για να αποκρύψετε φύλλα στο Excel.
Απόκρυψη όλων των φύλλων εργασίας εκτός από το ενεργό φύλλο
Εάν εργάζεστε σε μια αναφορά ή έναν πίνακα ελέγχου και θέλετε να αποκρύψετε όλο το φύλλο εργασίας εκτός από αυτό που περιέχει την αναφορά/τον πίνακα ελέγχου, μπορείτε να χρησιμοποιήσετε αυτόν τον κώδικα μακροεντολής.
'Αυτή η μακροεντολή θα αποκρύψει όλο το φύλλο εργασίας εκτός από το ενεργό φύλλο 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
Ταξινόμηση φύλλων εργασίας αλφαβητικά χρησιμοποιώντας 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 Αν Φύλλα (j). Όνομα <Φύλλα (i). Όνομα Στη συνέχεια Φύλλα (j). Μετακίνηση πριν: = Φύλλα (i) Τέλος Αν Επόμενο j Επόμενο i Application.ScreenUpdating = True End Sub
Προστατέψτε όλα τα φύλλα εργασίας με τη μία
Εάν έχετε πολλά φύλλα εργασίας σε ένα βιβλίο εργασίας και θέλετε να προστατεύσετε όλα τα φύλλα, μπορείτε να χρησιμοποιήσετε αυτόν τον κώδικα μακροεντολής.
Σας επιτρέπει να καθορίσετε τον κωδικό πρόσβασης μέσα στον κώδικα. Θα χρειαστείτε αυτόν τον κωδικό πρόσβασης για να προστατέψετε το φύλλο εργασίας.
"Αυτός ο κώδικας θα προστατεύσει όλα τα φύλλα με τη μία" Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'αντικαταστήστε το Test123 με τον κωδικό πρόσβασης που θέλετε για κάθε ws στα φύλλα εργασίας ws.Προστασία κωδικού πρόσβασης: = κωδικός πρόσβασης Τέλος υπο
Καταργήστε την προστασία όλων των φύλλων εργασίας με τη μία
Εάν έχετε κάποια ή όλα τα φύλλα εργασίας προστατευμένα, μπορείτε απλώς να χρησιμοποιήσετε μια μικρή τροποποίηση του κώδικα που χρησιμοποιείται για την προστασία των φύλλων για να τον προστατέψετε.
"Αυτός ο κώδικας θα προστατεύσει όλα τα φύλλα με τη μία" Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" "αντικαταστήστε το Test123 με τον κωδικό πρόσβασης που θέλετε για κάθε ws στα φύλλα εργασίας ws. Προστασία κωδικού πρόσβασης: = κωδικός πρόσβασης Τέλος υπο
Λάβετε υπόψη ότι ο κωδικός πρόσβασης πρέπει να είναι ο ίδιος που χρησιμοποιήθηκε για το κλείδωμα των φύλλων εργασίας. Εάν δεν είναι, θα δείτε ένα σφάλμα.
Αποκάλυψη όλων των γραμμών και στηλών
Αυτός ο κώδικας μακροεντολής θα εμφανίσει όλες τις κρυφές γραμμές και στήλες.
Αυτό θα μπορούσε να είναι πραγματικά χρήσιμο εάν λάβετε ένα αρχείο από κάποιον άλλο και θέλετε να είστε σίγουροι ότι δεν υπάρχουν κρυφές γραμμές/στήλες.
'Αυτός ο κώδικας θα αποκρύψει όλες τις γραμμές και τις στήλες στο φύλλο εργασίας Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Ξεμπλέξτε όλα τα συγχωνευμένα κελιά
Είναι συνηθισμένη πρακτική η συγχώνευση κυττάρων για να γίνει ένα. Ενώ λειτουργεί, όταν συγχωνεύονται κελιά δεν θα μπορείτε να ταξινομήσετε τα δεδομένα.
Σε περίπτωση που εργάζεστε με ένα φύλλο εργασίας με συγχωνευμένα κελιά, χρησιμοποιήστε τον παρακάτω κώδικα για να ξεμπερδέψετε όλα τα συγχωνευμένα κελιά με τη μία.
'Αυτός ο κώδικας θα ξεμπερδέψει όλα τα συγχωνευμένα κελιά Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Σημειώστε ότι αντί για Συγχώνευση και Κέντρο, συνιστώ να χρησιμοποιήσετε την επιλογή Κέντρο σε όλη την επιλογή.
Αποθηκεύστε το βιβλίο εργασίας με το TimeStamp στο όνομά του
Πολύς χρόνος, ίσως χρειαστεί να δημιουργήσετε εκδόσεις της εργασίας σας. Αυτά είναι αρκετά χρήσιμα σε μεγάλα έργα όπου εργάζεστε με ένα αρχείο με την πάροδο του χρόνου.
Μια καλή πρακτική είναι να αποθηκεύσετε το αρχείο με χρονικές σημάνσεις.
Η χρήση χρονικών σημάνσεων θα σας επιτρέψει να επιστρέψετε σε ένα συγκεκριμένο αρχείο για να δείτε ποιες αλλαγές έγιναν ή ποια δεδομένα χρησιμοποιήθηκαν.
Εδώ είναι ο κώδικας που θα αποθηκεύσει αυτόματα το βιβλίο εργασίας στον καθορισμένο φάκελο και θα προσθέσει μια χρονική σήμανση όποτε αποθηκεύεται.
'Αυτός ο κωδικός θα αποθηκεύσει το αρχείο με μια χρονική σήμανση στο όνομά του Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Πρέπει να καθορίσετε τη θέση του φακέλου και το όνομα του αρχείου.
Στον παραπάνω κώδικα, "C: UsersUsernameDesktop είναι η θέση του φακέλου που έχω χρησιμοποιήσει. Πρέπει να καθορίσετε τη θέση του φακέλου όπου θέλετε να αποθηκεύσετε το αρχείο. Επίσης, έχω χρησιμοποιήσει ένα γενικό όνομα "WorkbookName" ως πρόθεμα ονόματος αρχείου. Μπορείτε να καθορίσετε κάτι που σχετίζεται με το έργο ή την εταιρεία σας.
Αποθηκεύστε κάθε φύλλο εργασίας ως ξεχωριστό PDF
Εάν εργάζεστε με δεδομένα για διαφορετικά έτη ή τμήματα ή προϊόντα, ενδέχεται να χρειαστεί να αποθηκεύσετε διαφορετικά φύλλα εργασίας ως αρχεία PDF.
Αν και θα μπορούσε να είναι μια χρονοβόρα διαδικασία αν γίνει χειροκίνητα, το VBA μπορεί πραγματικά να το επιταχύνει.
Εδώ είναι ένας κώδικας VBA που θα αποθηκεύσει κάθε φύλλο εργασίας ως ξεχωριστό PDF.
'Αυτός ο κωδικός θα αποθηκεύσει κάθε φύλλο ως ξεχωριστό PDF Sub SaveWorkshetAsPDF () Dim ws As Worksheet For Every ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
Στον παραπάνω κώδικα, έχω καθορίσει τη διεύθυνση της θέσης του φακέλου στην οποία θέλω να αποθηκεύσω τα αρχεία PDF. Επίσης, κάθε PDF θα έχει το ίδιο όνομα με αυτό του φύλλου εργασίας. Θα πρέπει να τροποποιήσετε αυτήν τη θέση φακέλου (εκτός αν το όνομά σας είναι επίσης Sumit και το αποθηκεύετε σε δοκιμαστικό φάκελο στην επιφάνεια εργασίας).
Λάβετε υπόψη ότι αυτός ο κώδικας λειτουργεί μόνο για φύλλα εργασίας (και όχι για φύλλα γραφήματος).
Αποθηκεύστε κάθε φύλλο εργασίας ως ξεχωριστό PDF
Εδώ είναι ο κώδικας που θα αποθηκεύσει ολόκληρο το βιβλίο εργασίας σας ως PDF στον καθορισμένο φάκελο.
'Αυτός ο κωδικός θα αποθηκεύσει ολόκληρο το βιβλίο εργασίας ως PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Θα πρέπει να αλλάξετε τη θέση του φακέλου για να χρησιμοποιήσετε αυτόν τον κωδικό.
Μετατρέψτε όλους τους τύπους σε τιμές
Χρησιμοποιήστε αυτόν τον κώδικα όταν έχετε ένα φύλλο εργασίας που περιέχει πολλούς τύπους και θέλετε να μετατρέψετε αυτούς τους τύπους σε τιμές.
'Αυτός ο κώδικας θα μετατρέψει όλους τους τύπους σε τιμές Sub ConvertToValues () With ActiveSheet.UsedRange .Value = .Value End With End Sub
Αυτός ο κώδικας προσδιορίζει αυτόματα τα κελιά που χρησιμοποιούνται και το μετατρέπουν σε τιμές.
Προστατέψτε/κλειδώστε τα κύτταρα με τύπους
Μπορεί να θέλετε να κλειδώσετε κελιά με τύπους όταν έχετε πολλούς υπολογισμούς και δεν θέλετε να το διαγράψετε κατά λάθος ή να το αλλάξετε.
Εδώ είναι ο κώδικας που θα κλειδώσει όλα τα κελιά που έχουν τύπους, ενώ όλα τα άλλα κελιά δεν είναι κλειδωμένα.
'Αυτός ο κώδικας μακροεντολής θα κλειδώσει όλα τα κελιά με τύπους Sub LockCellsWithFormulas () With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub
Σχετικό σεμινάριο: Πώς να κλειδώσετε κελιά στο Excel.
Προστατέψτε όλα τα φύλλα εργασίας στο τετράδιο εργασίας
Χρησιμοποιήστε τον παρακάτω κώδικα για να προστατέψετε όλα τα φύλλα εργασίας σε ένα βιβλίο εργασίας με τη μία.
'Αυτός ο κώδικας θα προστατεύσει όλα τα φύλλα στο βιβλίο εργασίας Sub ProtectAllSheets () Dim ws As Worksheet For Every ws In Worksheets ws.Προστασία Επόμενο ws Τέλος Sub
Αυτός ο κώδικας θα περάσει από όλα τα φύλλα εργασίας ένα προς ένα και θα τον προστατεύσει.
Σε περίπτωση που θέλετε να καταργήσετε την προστασία όλων των φύλλων εργασίας, χρησιμοποιήστε το ws.Unprotect αντί του ws.Protect στον κώδικα.
Εισαγάγετε μια σειρά μετά από κάθε άλλη σειρά στην επιλογή
Χρησιμοποιήστε αυτόν τον κωδικό όταν θέλετε να εισαγάγετε μια κενή σειρά μετά από κάθε σειρά στο επιλεγμένο εύρος.
'Αυτός ο κωδικός θα εισαγάγει μια σειρά μετά από κάθε σειρά στην επιλογή Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Εισαγωγή ActiveCell.Offset (2, 0). Επιλέξτε Επόμενο i Τέλος υπο
Ομοίως, μπορείτε να τροποποιήσετε αυτόν τον κώδικα για να εισαγάγετε μια κενή στήλη μετά από κάθε στήλη στο επιλεγμένο εύρος.
Αυτόματη εισαγωγή ημερομηνίας & χρονικής σήμανσης στο παρακείμενο κελί
Η χρονική σήμανση είναι κάτι που χρησιμοποιείτε όταν θέλετε να παρακολουθείτε δραστηριότητες.
Για παράδειγμα, μπορεί να θέλετε να παρακολουθείτε δραστηριότητες όπως πότε πραγματοποιήθηκε μια συγκεκριμένη δαπάνη, πότε δημιουργήθηκε το τιμολόγιο πώλησης, πότε έγινε η καταχώριση δεδομένων σε ένα κελί, πότε ενημερώθηκε τελευταία η αναφορά κ.λπ.
Χρησιμοποιήστε αυτόν τον κωδικό για να εισαγάγετε μια σφραγίδα ημερομηνίας και ώρας στο παρακείμενο κελί όταν πραγματοποιείται μια καταχώριση ή όταν επεξεργαστείτε το υπάρχον περιεχόμενο.
'Αυτός ο κώδικας θα εισαγάγει μια χρονική σήμανση στο διπλανό κελί Ιδιωτικό υποσελίδιο εργασίας_Αλλαγή (ByVal Target As Range) Στο Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Μορφή (Τώρα (), "ημ-μμ-εεεε ω: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Σημειώστε ότι πρέπει να εισαγάγετε αυτόν τον κώδικα στο παράθυρο κωδικού φύλλου εργασίας (και όχι το παράθυρο κωδικού ενότητας όπως κάναμε σε άλλα παραδείγματα μακροεντολών του Excel μέχρι τώρα). Για να το κάνετε αυτό, στο VB Editor, κάντε διπλό κλικ στο όνομα του φύλλου στο οποίο θέλετε αυτήν τη λειτουργία. Στη συνέχεια, αντιγράψτε και επικολλήστε αυτόν τον κώδικα στο παράθυρο κώδικα αυτού του φύλλου.
Επίσης, αυτός ο κώδικας λειτουργεί όταν η εισαγωγή δεδομένων γίνεται στη στήλη Α (σημειώστε ότι ο κώδικας έχει τη γραμμή Target.Column = 1). Μπορείτε να το αλλάξετε ανάλογα.
Επισημάνετε εναλλακτικές σειρές στην επιλογή
Η επισήμανση εναλλακτικών σειρών μπορεί να αυξήσει την αναγνωσιμότητα των δεδομένων σας εξαιρετικά. Αυτό μπορεί να είναι χρήσιμο όταν πρέπει να βγάλεις μια εκτύπωση και να ανατρέξεις στα δεδομένα.
Ακολουθεί ένας κώδικας που θα επισημάνει αμέσως εναλλακτικές σειρές στην επιλογή.
'Αυτός ο κώδικας θα επισημάνει εναλλακτικές σειρές στην επιλογή Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Επιλογή για κάθε Myrow In Myrange. Σειρές Εάν Myrow.Row Mod 2 = 1 Στη συνέχεια Myrow.Interior.Color = vbCyan End Αν Επόμενο Myrow End Sub
Σημειώστε ότι έχω καθορίσει το χρώμα ως vbCyan στον κώδικα. Μπορείτε επίσης να καθορίσετε άλλα χρώματα (όπως vbRed, vbGreen, vbBlue).
Επισημάνετε κελιά με ανορθόγραφες λέξεις
Το Excel δεν έχει ορθογραφικό έλεγχο όπως στο Word ή στο PowerPoint. Ενώ μπορείτε να εκτελέσετε τον ορθογραφικό έλεγχο πατώντας το πλήκτρο F7, δεν υπάρχει οπτική ένδειξη όταν υπάρχει ορθογραφικό λάθος.
Χρησιμοποιήστε αυτόν τον κώδικα για να επισημάνετε αμέσως όλα τα κελιά που έχουν ορθογραφικό λάθος.
'Αυτός ο κώδικας θα επισημάνει τα κελιά που έχουν ανορθόγραφες λέξεις Sub HighlightMisspelledCells () Dim cl As Range For Every cl In ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Στη συνέχεια cl.Interior.Color = vbRed End If Next cl End Sub
Λάβετε υπόψη ότι τα κελιά που επισημαίνονται είναι εκείνα που έχουν κείμενο που το Excel θεωρεί ως ορθογραφικό λάθος. Σε πολλές περιπτώσεις, θα επισημάνει επίσης ονόματα ή όρους επωνυμίας που δεν καταλαβαίνει.
Ανανέωση όλων των περιστροφικών πινάκων στο βιβλίο εργασίας
Εάν έχετε περισσότερους από έναν Συγκεντρωτικούς πίνακες στο βιβλίο εργασίας, μπορείτε να χρησιμοποιήσετε αυτόν τον κωδικό για να ανανεώσετε όλους αυτούς τους Συγκεντρωτικούς πίνακες ταυτόχρονα.
'Αυτός ο κώδικας θα ανανεώσει όλο τον Συγκεντρωτικό πίνακα στο Υποβιβλίο εργασίας RefreshAllPivotTables () Dim PT As PivotTable For every PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Μπορείτε να διαβάσετε περισσότερα για την ανανέωση των Συγκεντρωτικών Πινάκων εδώ.
Αλλάξτε την πεζά γράμματα των επιλεγμένων κελιών σε κεφαλαία
Ενώ το Excel έχει τους τύπους για να αλλάξει την κεφαλαία του κειμένου, σας κάνει να το κάνετε σε ένα άλλο σύνολο κελιών.
Χρησιμοποιήστε αυτόν τον κωδικό για να αλλάξετε αμέσως την κεφαλαία γραμματοσειρά του κειμένου στο επιλεγμένο κείμενο.
"Αυτός ο κωδικός θα αλλάξει την επιλογή σε Upper Case Sub ChangeCase () Dim Rng As Range For Every Rng In Selection.Cells If Rng.HasFormula = False then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Σημειώστε ότι σε αυτήν την περίπτωση, έχω χρησιμοποιήσει το UCase για να κάνω την περίπτωση κειμένου επάνω. Μπορείτε να χρησιμοποιήσετε το LCase για πεζά.
Επισημάνετε όλα τα κελιά με σχόλια
Χρησιμοποιήστε τον παρακάτω κώδικα για να επισημάνετε όλα τα κελιά που έχουν σχόλια.
'Αυτός ο κώδικας θα επισημάνει κελιά που έχουν σχόλια' Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Σε αυτήν την περίπτωση, έχω χρησιμοποιήσει το vbBlue για να δώσω ένα μπλε χρώμα στα κελιά. Μπορείτε να το αλλάξετε σε άλλα χρώματα εάν θέλετε.
Επισημάνετε τα κενά κελιά με VBA
Ενώ μπορείτε να επισημάνετε κενό κελί με μορφοποίηση υπό όρους ή χρησιμοποιώντας το παράθυρο διαλόγου Μετάβαση στο Ειδικό, αν πρέπει να το κάνετε αρκετά συχνά, είναι καλύτερο να χρησιμοποιήσετε μια μακροεντολή.
Μόλις δημιουργηθεί, μπορείτε να έχετε αυτήν τη μακροεντολή στη Γραμμή εργαλείων γρήγορης πρόσβασης ή να την αποθηκεύσετε στο προσωπικό σας βιβλίο μακροεντολών.
Εδώ είναι ο κώδικας μακροεντολής VBA:
'Αυτός ο κώδικας θα επισημάνει όλα τα κενά κελιά του συνόλου δεδομένων Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
Σε αυτόν τον κωδικό, έχω καθορίσει τα κενά κελιά που θα επισημανθούν με το κόκκινο χρώμα. Μπορείτε να επιλέξετε άλλα χρώματα όπως μπλε, κίτρινο, κυανό κ.λπ.
Πώς να ταξινομήσετε δεδομένα κατά μία στήλη
Μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα για να ταξινομήσετε δεδομένα κατά την καθορισμένη στήλη.
Sub SortDataHeader () Range ("DataRange"). Ταξινόμηση κλειδιού1: = Εύρος ("A1"), σειρά 1: = xlΑύξουσα, κεφαλίδα: = xlΝαι Τέλος υπο
Σημειώστε ότι έχω δημιουργήσει μια ονομαστική περιοχή με το όνομα "DataRange" και την έχω χρησιμοποιήσει αντί για τις αναφορές κελιών.
Υπάρχουν επίσης τρεις βασικές παράμετροι που χρησιμοποιούνται εδώ:
- Key1 - Αυτό είναι το σημείο στο οποίο θέλετε να ταξινομήσετε το σύνολο δεδομένων. Στο παραπάνω παράδειγμα κώδικα, τα δεδομένα θα ταξινομηθούν με βάση τις τιμές στη στήλη Α.
- Παραγγελία- Εδώ πρέπει να καθορίσετε αν θέλετε να ταξινομήσετε τα δεδομένα σε αύξουσα ή φθίνουσα σειρά.
- Κεφαλίδα - Εδώ πρέπει να καθορίσετε εάν τα δεδομένα σας έχουν κεφαλίδες ή όχι.
Διαβάστε περισσότερα για τον τρόπο ταξινόμησης δεδομένων στο Excel χρησιμοποιώντας VBA.
Πώς να ταξινομήσετε δεδομένα κατά πολλαπλές στήλες
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Παρακάτω είναι ο κώδικας που θα ταξινομήσει τα δεδομένα με βάση πολλές στήλες:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlΝαι. Εφαρμογή λήξης με τέλος υπο
Σημειώστε ότι εδώ έχω καθορίσει να ταξινομήσω πρώτα με βάση τη στήλη Α και στη συνέχεια με βάση τη στήλη Β.
Η έξοδος θα είναι κάτι όπως φαίνεται παρακάτω:
Πώς να λάβετε μόνο το αριθμητικό μέρος από μια συμβολοσειρά στο Excel
Εάν θέλετε να εξαγάγετε μόνο το αριθμητικό μέρος ή μόνο το τμήμα κειμένου από μια συμβολοσειρά, μπορείτε να δημιουργήσετε μια προσαρμοσμένη συνάρτηση στο VBA.
Στη συνέχεια, μπορείτε να χρησιμοποιήσετε αυτήν τη συνάρτηση VBA στο φύλλο εργασίας (ακριβώς όπως οι κανονικές λειτουργίες του Excel) και θα εξαγάγει μόνο το αριθμητικό ή το τμήμα κειμένου από τη συμβολοσειρά.
Κάτι όπως φαίνεται παρακάτω:
Παρακάτω είναι ο κώδικας VBA που θα δημιουργήσει μια συνάρτηση εξαγωγής αριθμητικού μέρους από μια συμβολοσειρά:
«Αυτός ο κώδικας VBA θα δημιουργήσει μια συνάρτηση για να λάβει το αριθμητικό μέρος από μια συμβολοσειρά Λειτουργία GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) Για i = 1 σε StringLength If IsNumeric (Mid (CellRef, i, 1) ) Στη συνέχεια Result = Result & Mid (CellRef, i, 1) Next i GetNumeric = Result End function
Χρειάζεστε θέση σε κώδικα σε μια ενότητα και, στη συνέχεια, μπορείτε να χρησιμοποιήσετε τη συνάρτηση = GetNumeric στο φύλλο εργασίας.
Αυτή η συνάρτηση θα λάβει μόνο ένα όρισμα, το οποίο είναι η αναφορά κελιού του κελιού από το οποίο θέλετε να λάβετε το αριθμητικό μέρος.
Ομοίως, παρακάτω είναι η συνάρτηση που θα σας δώσει μόνο το τμήμα κειμένου από μια συμβολοσειρά στο Excel:
'Αυτός ο κώδικας VBA θα δημιουργήσει μια συνάρτηση για τη λήψη του τμήματος κειμένου από μια συμβολοσειρά Λειτουργία GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) Για i = 1 σε StringLength Εάν όχι (IsNumeric (Mid (CellRef, i, 1))) Στη συνέχεια Result = Result & Mid (CellRef, i, 1) Next i GetText = Result End function
Αυτοί είναι λοιπόν μερικοί από τους χρήσιμους κώδικες μακροεντολών του Excel που μπορείτε να χρησιμοποιήσετε στην καθημερινή σας εργασία για να αυτοματοποιήσετε εργασίες και να γίνετε πολύ πιο παραγωγικοί.
