24 χρήσιμα παραδείγματα μακροεντολών Excel για αρχάριους VBA (έτοιμο για χρήση)

Η χρήση μακροεντολών Excel μπορεί να επιταχύνει την εργασία σας και να εξοικονομήσει πολύ χρόνο.

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

Αξίζει λοιπόν να έχετε ένα χρήσιμο σετ μακροεντολών VBA που μπορείτε να κρατήσετε στην πίσω τσέπη σας και να το χρησιμοποιήσετε όταν το χρειάζεστε.

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

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

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

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

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

Χρησιμοποιώντας τον κώδικα από το παράδειγμα μακροεντολής Excel

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

• Ανοίξτε το βιβλίο εργασίας στο οποίο θέλετε να χρησιμοποιήσετε τη μακροεντολή.
• Κρατήστε πατημένο το πλήκτρο ALT και πατήστε F11.Αυτό θα ανοίξειΕπεξεργαστής VB.
• Κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο στην Εξερεύνηση του Έργου.
• Μεταβείτε στο Insert -> Modules.
• Αντιγράψτε και επικολλήστε τον κωδικό στο παράθυρο κωδικού της μονάδας.

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

Αφού εισαγάγετε τον κώδικα στο βιβλίο εργασίας, πρέπει να τον αποθηκεύσετε με την επέκταση .XLSM ή .XLS.

Πώς να εκτελέσετε μια μακροεντολή

Μετά την αντιγραφή του κώδικα στο πρόγραμμα επεξεργασίας VB, τα βήματα για την εκτέλεση της μακροεντολής είναι τα εξής:

• Μεταβείτε στην καρτέλα προγραμματιστή.
• Κάντε κλικ στο Macro.

• Στο πλαίσιο διαλόγου Μακροεντολές, επιλέξτε τη μακροεντολή που θα εκτελεστεί.
• Κάντε κλικ στο κουμπί Εκτέλεση.

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

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

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

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

Εάν βρείτε σφάλματα στο άρθρο ή τον κώδικα, ενημερώστε με.

Παράδειγμα μακροεντολής Excel

Αυτό το άρθρο περιγράφει τα ακόλουθα παραδείγματα μακροεντολών:

Καταργήστε την απόκρυψη όλων των φύλλων εργασίας ταυτόχρονα

Εάν εργάζεστε σε ένα βιβλίο εργασίας με πολλά κρυφά φύλλα, πρέπει να αποκρύψετε αυτά τα φύλλα ένα προς ένα.Αυτό μπορεί να πάρει κάποιο χρόνο, εάν υπάρχουν πολλά κρυφά φύλλα.

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

"Αυτός ο κώδικας θα αποκρύψει όλα τα φύλλα στο βιβλίο εργασίας Sub UnhideAllWoksheets() Dim ws ως φύλλο εργασίας για κάθε ws στο ActiveWorkbook. Φύλλα εργασίας ws. Visible = xlSheetVisible Επόμενο ws End Sub

Ο παραπάνω κώδικας χρησιμοποιεί έναν βρόχο VBA (For Every) για επανάληψη σε κάθε φύλλο εργασίας του βιβλίου εργασίας.Στη συνέχεια αλλάζει την ορατή ιδιότητα του φύλλου σε ορατό.

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

Απόκρυψη όλων των φύλλων εργασίας εκτός από το ενεργό φύλλο εργασίας

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

"Αυτή η μακροεντολή θα αποκρύψει όλο το φύλλο εργασίας εκτός από το ενεργό φύλλο Sub HideAllExceptActiveSheet() Dim ws ως φύλλο εργασίας για κάθε ws Σε αυτό το βιβλίο εργασίας. Φύλλα εργασίας Εάν ws.Name <> ActiveSheet.Name Στη συνέχεια ws.Visible = xlSheetHidden Επόμενο ws Τέλος

Ταξινόμηση φύλλου εργασίας αλφαβητικά χρησιμοποιώντας 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).Name < Sheets(i).Name then Sheets(j).Move before:=Φύλλα(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Προστατέψτε όλα τα φύλλα εργασίας ταυτόχρονα

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

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

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

Καταργήστε την προστασία όλων των φύλλων εργασίας ταυτόχρονα

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

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

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

Αποκρύψτε όλες τις σειρές και τις στήλες

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

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

"Αυτός ο κώδικας θα αποκρύψει όλες τις σειρές και τις στήλες στο φύλλο εργασίας Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Καταργήστε τη συγχώνευση όλων των συγχωνευμένων κελιών

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

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

"Αυτός ο κώδικας θα καταργήσει τη συγχώνευση όλων των συγχωνευμένων κελιών Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Λάβετε υπόψη ότι προτείνω να χρησιμοποιήσετε την επιλογή "Κέντρο σε όλη την επιλογή" αντί της επιλογής "Συγχώνευση και στο κέντρο".

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

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

Μια καλή πρακτική είναι να αποθηκεύσετε το αρχείο με χρονική σήμανση.

Η χρήση μιας χρονικής σφραγίδας θα σας επιτρέψει να επιστρέψετε σε ένα αρχείο για να δείτε ποιες αλλαγές έγιναν ή ποια δεδομένα χρησιμοποιήθηκαν.

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

'Αυτός ο κώδικας θα αποθηκεύσει το αρχείο με μια χρονική σήμανση στο όνομά του Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Μορφή(Ημερομηνία, "ηη-μμ-εεεε") & "_" & Μορφή(Ώρα, "ωω-δδ") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp End Sub

Πρέπει να καθορίσετε τη θέση του φακέλου και το όνομα του αρχείου.

Στον παραπάνω κώδικα, "C:UsersUsernameDesktop είναι η θέση φακέλου που χρησιμοποιώ.Πρέπει να καθορίσετε τη θέση του φακέλου όπου θέλετε να αποθηκεύσετε το αρχείο.Επίσης, χρησιμοποίησα το γενικό όνομα "WorkbookName" ως πρόθεμα ονόματος αρχείου.Μπορείτε να καθορίσετε περιεχόμενο που σχετίζεται με το έργο ή την εταιρεία σας.

Αποθηκεύστε κάθε φύλλο εργασίας ως ξεχωριστό PDF

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

Αν και αυτό μπορεί να είναι μια χρονοβόρα διαδικασία εάν γίνει με το χέρι, το VBA επιταχύνει τα πράγματα.

Ακολουθεί ένας κώδικας VBA που αποθηκεύει κάθε φύλλο εργασίας ως ξεχωριστό PDF.

"Αυτός ο κώδικας θα αποθηκεύσει κάθε φύλλο εργασίας ως ξεχωριστό PDF Sub SaveWorkshetAsPDF() Dim ws ως φύλλο εργασίας για κάθε ws σε φύλλα εργασίας ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Επόμενο ws Τέλος

Στον παραπάνω κώδικα, έχω καθορίσει τη διεύθυνση της θέσης του φακέλου όπου θέλω να αποθηκεύσω το 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 AllowDeletingRueRows:=T

Σχετικό μάθημα: Πώς να κλειδώσετε κελιά στο Excel.

Προστατέψτε όλα τα φύλλα σε ένα βιβλίο εργασίας

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

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

Αυτός ο κωδικός θα περάσει από όλα τα φύλλα ένα προς ένα και θα τα προστατεύσει.

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

Εισαγάγετε μια σειρά μετά από κάθε σειρά της επιλογής

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

'Αυτός ο κώδικας θα εισάγει μια σειρά μετά από κάθε σειρά στην επιλογή Sub InsertAlternateRows() Dim rng ως εύρος Dim CountRow ως ακέραιος Dim i Ως ακέραιο σύνολο rng = Επιλογή CountRow = rng.EntireRow.Count Για i = 1 To CountRow ActiveCell.EntireRow. Εισαγάγετε ActiveCell.Offset(2, 0). Επιλέξτε Next i End Sub

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

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

Οι χρονικές σημάνσεις είναι αυτές που χρησιμοποιείτε όταν θέλετε να παρακολουθείτε τη δραστηριότητα.

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

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

"Αυτός ο κωδικός θα εισαγάγει μια χρονική σήμανση στο διπλανό κελί Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Τότε Application.EnableEvents = False Target.Offset(0, 1) = Μορφή(Τώρα(), "ηη-μμ-εεεε ωω:λλ:δδ") Application.EnableEvents = True End If Handler: End Sub

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

Επίσης, αυτός ο κωδικός λειτουργεί όταν η εισαγωγή δεδομένων γίνεται στη στήλη Α (σημειώστε ότι ο κωδικός έχει Target.Column = 1 γραμμή).Μπορείτε να το αλλάξετε ανάλογα.

Επισημάνετε εναλλακτικές σειρές στην επιλογή

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

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

"Αυτός ο κώδικας θα επισήμανε εναλλακτικές σειρές στην επιλογή Sub HighlightAlternateRows() Dim Myrange ως εύρος Dim Myrow ως εύρος Ορισμός Myrange = Επιλογή για κάθε Myrow στο Myrange.Rows Εάν Myrow.Row Mod 2 = 1 Τότε Myrow.Interior.Color = vbCyan End If Next 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 Τέλος Υπο

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

Ανανεώστε όλους τους συγκεντρωτικούς πίνακες σε ένα βιβλίο εργασίας

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

"Αυτός ο κώδικας θα ανανεώσει όλο τον Συγκεντρωτικό Πίνακα στο Βιβλίο Εργασίας Sub RefreshAllPivotTables() Dim PT ως Συγκεντρωτικός Πίνακας για κάθε PT στο ActiveSheet.PivotTables PT.RefreshTable Επόμενο PT End Sub

Μπορείτε να διαβάσετε περισσότερα για την ανανέωση συγκεντρωτικών πινάκων εδώ.

Αλλάξτε τα γράμματα των επιλεγμένων κελιών σε κεφαλαία

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

Χρησιμοποιήστε αυτόν τον κωδικό για να αλλάξετε αμέσως τα γράμματα του κειμένου στο επιλεγμένο κείμενο.

"Αυτός ο κωδικός θα αλλάξει την επιλογή σε κεφαλαία κεφαλαία Sub ChangeCase() Dim Rng As Range For Every Rng In Selection.Cells If Rng.HasFormula = False Τότε 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 ως Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

Σε αυτόν τον κώδικα, καθόρισα κενά κελιά που επισημαίνονται με κόκκινο χρώμα.Μπορείτε να επιλέξετε άλλα χρώματα όπως μπλε, κίτρινο, κυανό κ.λπ.

Πώς να ταξινομήσετε δεδομένα ανά στήλη

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

Sub SortDataHeader() 
Εύρος ("Εύρος δεδομένων"). Κλειδί ταξινόμησης1:=Εύρος ("A1"), Σειρά1:=xlΑύξουσα, Κεφαλίδα:=xlΝαι 
Sub End

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

Υπάρχουν επίσης τρεις βασικές παράμετροι που χρησιμοποιούνται εδώ:

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

Διαβάστε περισσότερα σχετικά με τον τρόπο ταξινόμησης δεδομένων στο Excel χρησιμοποιώντας VBA.

Πώς να ταξινομήσετε δεδομένα κατά πολλές στήλες

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων που μοιάζει με αυτό:

Ακολουθεί ο κώδικας για την ταξινόμηση δεδομένων με βάση πολλαπλές στήλες:

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range(" :C1") .Header = xlYes .Εφαρμογή End With End Sub

Σημειώστε ότι εδώ έχω ορίσει την ταξινόμηση στη στήλη Α πρώτα και μετά στη στήλη Β.

Η έξοδος θα μοιάζει με αυτό:

Πώς να λάβετε μόνο το αριθμητικό μέρος από μια συμβολοσειρά στο Excel

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

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

Οπως φαίνεται παρακάτω:

Εδώ είναι ο κώδικας VBA που θα δημιουργήσει μια συνάρτηση που εξάγει το αριθμητικό τμήμα από μια συμβολοσειρά:

Αυτός ο κώδικας VBA θα δημιουργήσει μια συνάρτηση για τη λήψη του αριθμητικού τμήματος από μια συμβολοσειρά Συνάρτηση GetNumeric(CellRef As String) Dim StringLength ως Integer StringLength = Len(CellRef) Για i = 1 To StringLength Αν IsNumeric(Mid(CellRef, i, 1) ) Στη συνέχεια Αποτέλεσμα = Αποτέλεσμα & Μέσο (CellRef, i, 1) Επόμενο i GetNumeric = Συνάρτηση Τέλος Αποτέλεσμα

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

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

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

"Αυτός ο κώδικας VBA θα δημιουργήσει μια συνάρτηση για τη λήψη του τμήματος κειμένου από μια συμβολοσειρά Συνάρτηση GetText(CellRef ως συμβολοσειρά) Dim StringLength ως ακέραιος StringLength = Len(CellRef) Για i = 1 έως StringLength εάν όχι (IsNumeric(Mid(CellRef, i, 1))) Έπειτα Αποτέλεσμα = Αποτέλεσμα & Μέσο (CellRef, i, 1) Επόμενο i GetText = Συνάρτηση Τέλος Αποτέλεσμα

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