Πώς να δημιουργήσετε το δικό σας πρόσθετο για το Microsoft Excel

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

Η πιο εύκολη επιλογή είναι να αποθηκεύσετε τον κώδικα μακροεντολής απευθείας στο αρχείο εργασίας μεταβαίνοντας στο πρόγραμμα επεξεργασίας της Visual Basic χρησιμοποιώντας τη συντόμευση πληκτρολογίου άλλος+F11 και προσθέτοντας μια νέα κενή μονάδα μέσω του μενού Εισαγωγή – Ενότητα:

Ωστόσο, υπάρχουν πολλά μειονεκτήματα με αυτή τη μέθοδο:

• Εάν υπάρχουν πολλά αρχεία εργασίας και χρειάζεται μια μακροεντολή παντού, όπως μια μακροεντολή για τη μετατροπή τύπων σε τιμές, τότε θα πρέπει να αντιγράψετε τον κώδικα σε κάθε βιβλίο.
  
• Δεν πρέπει να ξεχαστεί αποθήκευση αρχείου σε μορφή με δυνατότητα μακροεντολής (xlsm) ή σε δυαδική μορφή βιβλίου (xlsb).
  
• Όταν ανοίγετε ένα τέτοιο αρχείο μακροπροστασία θα εκδίδει κάθε φορά μια προειδοποίηση που πρέπει να αναγνωριστεί (καλά, ή να απενεργοποιεί εντελώς την προστασία, κάτι που μπορεί να μην είναι πάντα επιθυμητό).

Μια πιο κομψή λύση θα ήταν η δημιουργία το δικό σας πρόσθετο (Πρόσθετο Excel) – ένα ξεχωριστό αρχείο ειδικής μορφής (xlam) που περιέχει όλες τις «αγαπημένες» σας μακροεντολές. Τα πλεονεκτήματα αυτής της προσέγγισης:

• Θα είναι αρκετό συνδέστε το πρόσθετο μία φορά στο Excel – και μπορείτε να χρησιμοποιήσετε τις διαδικασίες και τις λειτουργίες VBA σε οποιοδήποτε αρχείο σε αυτόν τον υπολογιστή. Επομένως, δεν απαιτείται η εκ νέου αποθήκευση των αρχείων εργασίας σας σε μορφές xlsm- και xlsb, γιατί. ο πηγαίος κώδικας δεν θα αποθηκευτεί σε αυτά, αλλά στο αρχείο πρόσθετου.
  
• Απάτη Δεν θα σε ενοχλούν ούτε οι μακροεντολές. Τα πρόσθετα είναι, εξ ορισμού, αξιόπιστες πηγές.
• Μπορώ ξεχωριστή καρτέλα στην κορδέλα του Excel με ωραία κουμπιά για την εκτέλεση πρόσθετων μακροεντολών.
• Το πρόσθετο είναι ένα ξεχωριστό αρχείο. Του εύκολο στη μεταφορά από υπολογιστή σε υπολογιστή, μοιραστείτε το με συναδέλφους ή ακόμα και πουλήστε το 😉
  

Ας προχωρήσουμε σε όλη τη διαδικασία δημιουργίας του δικού σας πρόσθετου Microsoft Excel βήμα προς βήμα.

Βήμα 1. Δημιουργήστε ένα αρχείο πρόσθετου

Ανοίξτε το Microsoft Excel με ένα κενό βιβλίο εργασίας και αποθηκεύστε το με οποιοδήποτε κατάλληλο όνομα (για παράδειγμα MyExcelAddin) σε μορφή πρόσθετου με την εντολή Αρχείο – Αποθήκευση ως ή κλειδιά F12, προσδιορίζοντας τον τύπο αρχείου Πρόσθετο Excel:

Λάβετε υπόψη ότι από προεπιλογή το Excel αποθηκεύει πρόσθετα στο φάκελο C:UsersYour_nameAppDataRoamingMicrosoftAddIns, αλλά, κατ' αρχήν, μπορείτε να καθορίσετε οποιονδήποτε άλλο φάκελο είναι κατάλληλος για εσάς.

Βήμα 2. Συνδέουμε το πρόσθετο που δημιουργήθηκε

Τώρα το πρόσθετο που δημιουργήσαμε στο τελευταίο βήμα MyExcelAddin πρέπει να συνδεθεί στο Excel. Για να το κάνετε αυτό, μεταβείτε στο μενού Αρχείο – Επιλογές – Πρόσθετα (Αρχείο — Επιλογές — Πρόσθετα), κάντε κλικ στο κουμπί Σχετικά (Πηγαίνω) στο κάτω μέρος του παραθύρου. Στο παράθυρο που ανοίγει, κάντε κλικ στο κουμπί Βαθμολογία Κριτικής (Ξεφυλλίζω) και καθορίστε τη θέση του αρχείου πρόσθετου μας.

Εάν τα κάνατε όλα σωστά, τότε το δικό μας MyExcelAddin θα πρέπει να εμφανίζεται στη λίστα με τα διαθέσιμα πρόσθετα:

Βήμα 3. Προσθέστε μακροεντολές στο πρόσθετο

Το πρόσθετο μας είναι συνδεδεμένο στο Excel και λειτουργεί με επιτυχία, αλλά δεν υπάρχει ακόμη μία μακροεντολή σε αυτό. Ας το γεμίσουμε. Για να το κάνετε αυτό, ανοίξτε το πρόγραμμα επεξεργασίας Visual Basic με τη συντόμευση πληκτρολογίου άλλος+F11 ή με κουμπί Visual Basic αυτί προγραμματιστή (Προγραμματιστής). Εάν καρτέλες προγραμματιστή δεν είναι ορατό, μπορεί να εμφανιστεί μέσω Αρχείο – Επιλογές – Ρύθμιση κορδέλας (Αρχείο — Επιλογές — Προσαρμογή κορδέλας).

Θα πρέπει να υπάρχει ένα παράθυρο στην επάνω αριστερή γωνία του προγράμματος επεξεργασίας Σχέδιο (αν δεν είναι ορατό, ενεργοποιήστε το από το μενού Προβολή — Project Explorer):

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

Μπορείτε είτε να πληκτρολογήσετε τον κώδικα από την αρχή (αν ξέρετε πώς να προγραμματίζετε), είτε να τον αντιγράψετε από κάπου έτοιμο (πράγμα πολύ πιο εύκολο). Ας εισαγάγουμε, για δοκιμή, τον κωδικό μιας απλής αλλά χρήσιμης μακροεντολής στην προστιθέμενη κενή ενότητα:

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

Η μακροεντολή μας FormulasToValues, όπως μπορείτε εύκολα να φανταστείτε, μετατρέπει τύπους σε τιμές σε ένα προεπιλεγμένο εύρος. Μερικές φορές αυτές οι μακροεντολές ονομάζονται επίσης διαδικασίες. Για να το εκτελέσετε, πρέπει να επιλέξετε κελιά με τύπους και να ανοίξετε ένα ειδικό πλαίσιο διαλόγου Μακροεντολές από την καρτέλα προγραμματιστή (Προγραμματιστής — Μακροεντολές) ή συντόμευση πληκτρολογίου άλλος+F8. Κανονικά, αυτό το παράθυρο εμφανίζει διαθέσιμες μακροεντολές από όλα τα ανοιχτά βιβλία εργασίας, αλλά οι μακροεντολές πρόσθετων δεν είναι ορατές εδώ. Παρόλα αυτά, μπορούμε να εισάγουμε το όνομα της διαδικασίας μας στο πεδίο όνομα μακροεντολής (Όνομα μακροεντολής)και στη συνέχεια κάντε κλικ στο κουμπί τρέξιμο (τρέξιμο) – και η μακροεντολή μας θα λειτουργήσει:

Εδώ μπορείτε επίσης να αντιστοιχίσετε μια συντόμευση πληκτρολογίου για γρήγορη εκκίνηση μιας μακροεντολής - το κουμπί είναι υπεύθυνο για αυτό παράμετροι (Επιλογές) στο προηγούμενο παράθυρο Macro:

Κατά την αντιστοίχιση πλήκτρων, λάβετε υπόψη ότι έχουν διάκριση πεζών-κεφαλαίων και διάκριση διάταξης πληκτρολογίου. Έτσι, εάν αντιστοιχίσετε έναν συνδυασμό όπως Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally αλλαγήγια να πάρετε το κεφαλαίο γράμμα.

Για ευκολία, μπορούμε επίσης να προσθέσουμε ένα κουμπί για τη μακροεντολή μας στη γραμμή εργαλείων γρήγορης πρόσβασης στην επάνω αριστερή γωνία του παραθύρου. Για να το κάνετε αυτό, επιλέξτε Αρχείο – Επιλογές – Γραμμή εργαλείων γρήγορης πρόσβασης (Αρχείο — Επιλογές — Προσαρμογή της γραμμής εργαλείων γρήγορης πρόσβασης), και στη συνέχεια στην αναπτυσσόμενη λίστα στο επάνω μέρος του παραθύρου την επιλογή Μακροεντολές. Μετά από αυτό η μακροεντολή μας FormulasToValues μπορεί να τοποθετηθεί στον πίνακα με το κουμπί Πρόσθεση (Προσθήκη) και επιλέξτε ένα εικονίδιο για αυτό με το κουμπί Αλλαγή (Επεξεργασία):

Βήμα 4. Προσθέστε λειτουργίες στο πρόσθετο

Αλλά μακρο-διαδικασίες, υπάρχουν επίσης μακροεντολές συναρτήσεων ή όπως λέγονται UDF (Συνάρτηση καθορισμένη από το χρήστη = συνάρτηση καθορισμένη από το χρήστη). Ας δημιουργήσουμε μια ξεχωριστή ενότητα στο πρόσθετο μας (εντολή μενού Εισαγωγή – Ενότητα) και επικολλήστε εκεί τον κωδικό της παρακάτω συνάρτησης:

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

Σημειώστε ότι η σύνταξη μιας συνάρτησης είναι διαφορετική από μια διαδικασία:

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

Σημειώστε επίσης ότι αυτή η συνάρτηση δεν είναι απαραίτητη και ότι είναι αδύνατο να εκτελεστεί όπως η προηγούμενη διαδικασία μακροεντολής μέσω του πλαισίου διαλόγου Μακροεντολές και κουμπί τρέξιμο. Μια τέτοια συνάρτηση μακροεντολής θα πρέπει να χρησιμοποιείται ως τυπική συνάρτηση φύλλου εργασίας (SUM, IF, VLOOKUP…), δηλαδή απλώς εισάγετε σε οποιοδήποτε κελί, προσδιορίζοντας την τιμή του ποσού με ΦΠΑ ως όρισμα:

… ή εισάγετε μέσω του τυπικού πλαισίου διαλόγου για την εισαγωγή μιας συνάρτησης (κουμπί fx στη γραμμή τύπων), επιλέγοντας μια κατηγορία Ορίζεται Χρήστη (Ορισμένο από τον χρήστη):

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

1. Ανοίξτε το πρόγραμμα επεξεργασίας Visual Basic με μια συντόμευση πληκτρολογίου άλλος+F11
2. Επιλέξτε το πρόσθετο στον πίνακα Project και πατήστε το πλήκτρο F2για να ανοίξετε το παράθυρο του Object Browser
3. Επιλέξτε το πρόσθετο έργο σας από την αναπτυσσόμενη λίστα στο επάνω μέρος του παραθύρου
4. Κάντε δεξί κλικ στη συνάρτηση που εμφανίζεται και επιλέξτε εντολή Ιδιοκτησίες.
5. Εισαγάγετε μια περιγραφή της λειτουργίας στο παράθυρο Περιγραφή
6. Αποθηκεύστε το αρχείο πρόσθετου και επανεκκινήστε το excel.

Μετά την επανεκκίνηση, η συνάρτηση θα πρέπει να εμφανίσει την περιγραφή που καταχωρήσαμε:

Βήμα 5. Δημιουργήστε μια πρόσθετη καρτέλα στη διεπαφή

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

Πληροφορίες σχετικά με τις καρτέλες που εμφανίζονται από προεπιλογή περιέχονται στο βιβλίο και πρέπει να μορφοποιηθούν σε ειδικό κώδικα XML. Ο ευκολότερος τρόπος για να γράψετε και να επεξεργαστείτε έναν τέτοιο κώδικα είναι με τη βοήθεια ειδικών προγραμμάτων - επεξεργαστών XML. Ένα από τα πιο βολικά (και δωρεάν) είναι το πρόγραμμα του Maxim Novikov Ribbon XML Editor.

Ο αλγόριθμος για την εργασία με αυτό είναι ο εξής:

1. Κλείστε όλα τα παράθυρα του Excel, ώστε να μην υπάρχει διένεξη αρχείων όταν επεξεργαζόμαστε τον κώδικα XML του πρόσθετου.
2. Εκκινήστε το πρόγραμμα Ribbon XML Editor και ανοίξτε το αρχείο MyExcelAddin.xlam σε αυτό
3. Με κουμπί καρτέλες στην επάνω αριστερή γωνία, προσθέστε το απόσπασμα κώδικα για τη νέα καρτέλα:
4. Πρέπει να βάλετε κενά εισαγωγικά id την καρτέλα και την ομάδα μας (οποιαδήποτε μοναδικά αναγνωριστικά) και σε επιγραφή – τα ονόματα της καρτέλας μας και μια ομάδα κουμπιών σε αυτήν:
5. Με κουμπί κουμπί στο αριστερό πλαίσιο, προσθέστε έναν κενό κωδικό για το κουμπί και προσθέστε ετικέτες σε αυτό:

   — ετικέτα είναι το κείμενο στο κουμπί

   — εικόνα Mso — αυτό είναι το υπό όρους όνομα της εικόνας στο κουμπί. Χρησιμοποίησα ένα κόκκινο εικονίδιο κουμπιού που ονομάζεται AnimationCustomAddExitDialog. Τα ονόματα όλων των διαθέσιμων κουμπιών (και υπάρχουν αρκετές εκατοντάδες από αυτά!) μπορούν να βρεθούν σε μεγάλο αριθμό τοποθεσιών στο Διαδίκτυο εάν αναζητήσετε τις λέξεις-κλειδιά "imageMso". Για αρχή, μπορείτε να πάτε εδώ.

   - onAction – αυτό είναι το όνομα της διαδικασίας επανάκλησης – μια ειδική σύντομη μακροεντολή που θα εκτελέσει την κύρια μακροεντολή μας FormulasToValues. Μπορείτε να ονομάσετε αυτή τη διαδικασία όπως θέλετε. Θα το προσθέσουμε λίγο αργότερα.

6. Μπορείτε να ελέγξετε την ορθότητα όλων των ενεργειών χρησιμοποιώντας το κουμπί με ένα πράσινο σημάδι επιλογής στο επάνω μέρος της γραμμής εργαλείων. Στο ίδιο σημείο, κάντε κλικ στο κουμπί με μια δισκέτα για να αποθηκεύσετε όλες τις αλλαγές.
7. Κλείστε το Ribbon XML Editor
8. Ανοίξτε το Excel, μεταβείτε στο πρόγραμμα επεξεργασίας της Visual Basic και προσθέστε μια διαδικασία επανάκλησης στη μακροεντολή μας KillFormulasώστε να εκτελεί την κύρια μακροεντολή μας για την αντικατάσταση τύπων με τιμές.
9. Αποθηκεύουμε τις αλλαγές και, επιστρέφοντας στο Excel, ελέγχουμε το αποτέλεσμα:

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

• Τι είναι οι μακροεντολές, πώς να τις χρησιμοποιήσετε στην εργασία σας, πού να λάβετε κώδικα μακροεντολών στη Visual Basic.
• Πώς να δημιουργήσετε μια οθόνη εκκίνησης όταν ανοίγετε ένα βιβλίο εργασίας στο Excel
• Τι είναι το Personal Macro Book και πώς να το χρησιμοποιήσετε