Δημιουργία συνάρτησης καθορισμένης από το χρήστη (UDF) στο Excel VBA (Ultimate Guide)

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

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

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

Εάν ενδιαφέρεστε να μάθετε VBA με τον εύκολο τρόπο, ελέγξτε το δικό μου Online Εκπαίδευση Excel VBA.

Τι είναι μια Διαδικασία Λειτουργίας στο VBA;

Μια διαδικασία συνάρτησης είναι ένας κώδικας VBA που εκτελεί υπολογισμούς και επιστρέφει μια τιμή (ή έναν πίνακα τιμών).

Χρησιμοποιώντας μια διαδικασία συνάρτησης, μπορείτε να δημιουργήσετε μια συνάρτηση που μπορείτε να χρησιμοποιήσετε στο φύλλο εργασίας (όπως κάθε κανονική συνάρτηση Excel, όπως SUM ή VLOOKUP).

Όταν έχετε δημιουργήσει μια διαδικασία συνάρτησης χρησιμοποιώντας VBA, μπορείτε να τη χρησιμοποιήσετε με τρεις τρόπους:

1. Ως τύπος στο φύλλο εργασίας, όπου μπορεί να λάβει ορίσματα ως εισόδους και επιστρέφει μια τιμή ή έναν πίνακα τιμών.
2. Ως μέρος του κώδικα υπορουτίνας VBA ή άλλου κωδικού λειτουργίας.
3. Σε μορφοποίηση υπό όρους.

Ενώ υπάρχουν ήδη 450+ ενσωματωμένες λειτουργίες Excel διαθέσιμες στο φύλλο εργασίας, μπορεί να χρειαστείτε μια προσαρμοσμένη συνάρτηση εάν:

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

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

Λειτουργία Vs. Υπορουτίνα στο VBA

Μια «Υπορουτίνα» σας επιτρέπει να εκτελέσετε ένα σύνολο κώδικα ενώ μια «Λειτουργία» επιστρέφει μια τιμή (ή έναν πίνακα τιμών).

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

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

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

Όταν δημιουργείτε μια συνάρτηση καθορισμένη από το χρήστη (UDF) χρησιμοποιώντας το VBA, μπορείτε να χρησιμοποιήσετε αυτήν τη συνάρτηση στο φύλλο εργασίας όπως κάθε άλλη συνάρτηση. Θα καλύψω περισσότερα σχετικά με αυτό στην ενότητα «Διαφορετικοί τρόποι χρήσης μιας συνάρτησης καθορισμένης από το χρήστη στο Excel».

Δημιουργία απλής συνάρτησης καθορισμένης από τον χρήστη σε VBA

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

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

Συνάρτηση GetNumeric (CellRef As String) as Long Dim StringLength As Integer StringLength = Len (CellRef) Για i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Στη συνέχεια Result = Result & Mid (CellRef, i, 1) Επόμενο i GetNumeric = Λειτουργία λήξης αποτελεσμάτων

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

Παρακάτω είναι πώς λειτουργεί αυτή η λειτουργία - GetNumeric - μπορεί να χρησιμοποιηθεί στο Excel.

Τώρα πριν σας πω πώς δημιουργείται αυτή η λειτουργία στο VBA και πώς λειτουργεί, υπάρχουν μερικά πράγματα που πρέπει να γνωρίζετε:

• Όταν δημιουργείτε μια συνάρτηση στο VBA, γίνεται διαθέσιμη σε ολόκληρο το βιβλίο εργασίας, όπως κάθε άλλη κανονική λειτουργία.
• Όταν πληκτρολογείτε το όνομα της συνάρτησης ακολουθούμενο από το σύμβολο ίσο προς, το Excel θα σας δείξει το όνομα της συνάρτησης στη λίστα των συναρτήσεων που ταιριάζουν. Στο παραπάνω παράδειγμα, όταν εισήγαγα = Λήψη, το Excel μου έδειξε μια λίστα που είχε την προσαρμοσμένη λειτουργία μου.

Πιστεύω ότι αυτό είναι ένα καλό παράδειγμα όταν μπορείτε να χρησιμοποιήσετε το VBA για να δημιουργήσετε μια απλή στη χρήση συνάρτηση στο Excel. Μπορείτε να κάνετε το ίδιο πράγμα και με έναν τύπο (όπως φαίνεται σε αυτό το σεμινάριο), αλλά αυτό γίνεται περίπλοκο και δύσκολο να κατανοηθεί. Με αυτό το UDF, χρειάζεται μόνο να περάσετε ένα όρισμα και να έχετε το αποτέλεσμα.

Ανατομία μιας συνάρτησης που ορίζεται από το χρήστη στο VBA

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

Τώρα ας βουτήξουμε βαθιά και δούμε πώς δημιουργείται αυτή η λειτουργία. Πρέπει να τοποθετήσετε τον παρακάτω κώδικα σε μια ενότητα στο VB Editor. Καλύπτω αυτό το θέμα στην ενότητα - "Πού να τοποθετήσετε τον κώδικα VBA για μια συνάρτηση που καθορίζεται από τον χρήστη".

Συνάρτηση GetNumeric (CellRef As String) as Long 'Αυτή η συνάρτηση εξάγει το αριθμητικό μέρος από τη συμβολοσειρά Dim StringLength As Integer StringLength = Len (CellRef) Για i = 1 έως StringLength Εάν IsNumeric (Mid (CellRef, i, 1)) Τότε Αποτέλεσμα = Αποτέλεσμα & μεσαίο (CellRef, i, 1) Επόμενο i GetNumeric = Λειτουργία τερματισμού αποτελέσματος

Η πρώτη γραμμή του κώδικα ξεκινά με τη λέξη - Λειτουργία.

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

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

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

Μέσα στην παρένθεση, πρέπει να καθορίσετε τα ορίσματα.

Στο παράδειγμά μας, υπάρχει μόνο ένα όρισμα - CellRef.

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

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

Λάβετε υπόψη ότι η συνάρτηση ορίζεται ως ο τύπος δεδομένων "String". Αυτό θα έλεγε στη VBA ότι το αποτέλεσμα του τύπου θα ήταν τύπου δεδομένων συμβολοσειράς.

Παρόλο που μπορώ να χρησιμοποιήσω έναν αριθμητικό τύπο δεδομένων εδώ (όπως Long ή Double), αυτό θα περιόριζε το εύρος των αριθμών που μπορεί να επιστρέψει. Εάν έχω μια συμβολοσειρά 20 αριθμών που πρέπει να εξαγάγω από τη συνολική συμβολοσειρά, η δήλωση της συνάρτησης ως Long ή Double θα έφερνε σφάλμα (καθώς ο αριθμός θα ήταν εκτός του εύρους της). Ως εκ τούτου, έχω διατηρήσει τον τύπο δεδομένων εξόδου λειτουργίας ως συμβολοσειρά.

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

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

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

Η πέμπτη γραμμή εκχωρεί το μήκος της συμβολοσειράς στο όρισμα εισόδου στη μεταβλητή "StringLength". Σημειώστε ότι το "CellRef" αναφέρεται στο επιχείρημα που θα δοθεί από τον χρήστη κατά τη χρήση του τύπου στο φύλλο εργασίας (ή τη χρήση του σε VBA - το οποίο θα δούμε αργότερα σε αυτό το σεμινάριο).

Η έκτη, η έβδομη και η όγδοη γραμμή είναι το μέρος του βρόχου For For. Ο βρόχος εκτελείται όσες φορές υπάρχουν πολλοί χαρακτήρες στο όρισμα εισόδου. Αυτός ο αριθμός δίνεται από τη συνάρτηση LEN και εκχωρείται στη μεταβλητή ‘StringLength’.

Έτσι ο βρόχος τρέχει από το '1 στο Stringlength'.

Μέσα στον βρόχο, η δήλωση IF αναλύει κάθε χαρακτήρα της συμβολοσειράς και αν είναι αριθμητική, προσθέτει αυτόν τον αριθμητικό χαρακτήρα στη μεταβλητή Αποτέλεσμα. Χρησιμοποιεί τη λειτουργία MID στο VBA για να το κάνει αυτό.

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

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

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

Επιχειρήματα σε συνάρτηση καθορισμένη από το χρήστη στο VBA

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

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

Δημιουργία συνάρτησης στο VBA χωρίς κανένα επιχείρημα

Στο φύλλο εργασίας του Excel, έχουμε πολλές λειτουργίες που δεν λαμβάνουν επιχειρήματα (όπως RAND, TODAY, NOW).

Αυτές οι συναρτήσεις δεν εξαρτώνται από κανένα όρισμα εισόδου. Για παράδειγμα, η συνάρτηση TODAY θα επιστρέψει την τρέχουσα ημερομηνία και η συνάρτηση RAND θα επιστρέψει έναν τυχαίο αριθμό μεταξύ 0 και 1.

Μπορείτε να δημιουργήσετε παρόμοια λειτουργία και στο VBA.

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

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Ο παραπάνω κώδικας καθορίζει το αποτέλεσμα της συνάρτησης ως τύπο δεδομένων συμβολοσειράς (καθώς το αποτέλεσμα που θέλουμε είναι το όνομα του αρχείου - το οποίο είναι συμβολοσειρά).

Αυτή η συνάρτηση εκχωρεί την τιμή του "ThisWorkbook.Name" στη συνάρτηση, η οποία επιστρέφεται όταν η συνάρτηση χρησιμοποιείται στο φύλλο εργασίας.

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

Τα παραπάνω όμως έχουν ένα θέμα.

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

Εάν θέλετε, μπορείτε να αναγκάσετε έναν επανυπολογισμό χρησιμοποιώντας τη συντόμευση πληκτρολογίου - Control + Alt + F9.

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

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

Function WorkbookName () As String Application.Voileile True WorkbookName = ThisWorkbook.Name End Function

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

Δημιουργία συνάρτησης στο VBA με ένα επιχείρημα

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

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

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

Λειτουργία ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Λειτουργία λήξης

Αυτή η συνάρτηση χρησιμοποιεί τη συνάρτηση UCase στο VBA για να αλλάξει την τιμή της μεταβλητής CellRef. Στη συνέχεια, εκχωρεί την τιμή στη συνάρτηση ConvertToUpperCase.

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

Δημιουργία συνάρτησης στο VBA με πολλαπλά επιχειρήματα

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

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

Συνάρτηση GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBefore Endel

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

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

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

Στη συνέχεια ελέγχει τη θέση του οριοθέτη χρησιμοποιώντας τη συνάρτηση INSTR στο VBA. Αυτή η θέση χρησιμοποιείται στη συνέχεια για την εξαγωγή όλων των χαρακτήρων πριν από τον οριοθέτη (χρησιμοποιώντας τη συνάρτηση ΑΡΙΣΤΕΡΑ).

Τέλος, εκχωρεί το αποτέλεσμα στη συνάρτηση.

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

Λειτουργία GetDataBeforeDelimiter (CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Εάν DelimPosition <0 Τότε DelimPosition = Len (CellRef) Αποτέλεσμα = Left (Αριστερά) CellRef, DelimPosition) GetDataBeforeDelimiter = Λειτουργία τερματισμού αποτελέσματος

Μπορούμε να βελτιστοποιήσουμε περαιτέρω αυτήν τη λειτουργία.

Εάν εισαγάγετε το κείμενο (από το οποίο θέλετε να εξαγάγετε το τμήμα πριν από τον οριοθέτη) απευθείας στη συνάρτηση, θα σας εμφανίσει σφάλμα. Προχώρα… δοκίμασέ το!

Αυτό συμβαίνει καθώς έχουμε ορίσει το ‘CellRef’ ως τύπο δεδομένων εύρους.

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

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

Ο παρακάτω κώδικας θα το έκανε αυτό:

Λειτουργία GetDataBeforeDelimiter (CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Εάν DelimPosition <0 Στη συνέχεια DelimPosition = Len (CellRef) Αποτέλεσμα = Αριστερά (CellRef), GetDataBeforeDelimiter = Λειτουργία λήξης αποτελεσμάτων

Δημιουργία συνάρτησης στο VBA με προαιρετικά επιχειρήματα

Υπάρχουν πολλές συναρτήσεις στο Excel όπου ορισμένα από τα ορίσματα είναι προαιρετικά.

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

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

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

Για παράδειγμα, στη συνάρτηση VLOOKUP, εάν δεν καθορίσετε το τέταρτο όρισμα, το VLOOKUP πραγματοποιεί μια κατά προσέγγιση αναζήτηση και αν ορίσετε το τελευταίο όρισμα ως ΛΑΘΟΣ (ή 0), τότε ταιριάζει ακριβώς.

Να θυμάστε ότι τα προαιρετικά ορίσματα πρέπει πάντα να έρχονται μετά από όλα τα απαιτούμενα ορίσματα. Δεν μπορείτε να έχετε προαιρετικά επιχειρήματα στην αρχή.

Τώρα ας δούμε πώς να δημιουργήσουμε μια συνάρτηση στο VBA με προαιρετικά ορίσματα.

Λειτουργία μόνο με ένα προαιρετικό επιχείρημα

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

Μπορούμε όμως να δημιουργήσουμε ένα με VBA.

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

Λειτουργία CurrDate (Προαιρετικό fmt ως παραλλαγή) Dim Αποτέλεσμα Αν IsMissing (fmt) Στη συνέχεια CurrDate = Μορφή (Ημερομηνία, "ημ-μμ-εεεε") Άλλο CurrDate = Μορφή (Ημερομηνία, "ημ mmmm, εεεε") Λήξη Εάν τελειώσει η συνάρτηση

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

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

Μπορείτε να το κάνετε πιο ισχυρό λαμβάνοντας μόνο συγκεκριμένες τιμές ως ορίσματα και εμφανίζοντας σφάλμα στις υπόλοιπες περιπτώσεις (όπως φαίνεται στον παρακάτω κώδικα).

Συνάρτηση CurrDate (Προαιρετικό fmt ως παραλλαγή) Dim Αποτέλεσμα Αν IsMissing (fmt) Στη συνέχεια CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 Στη συνέχεια CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Λειτουργία τερματισμού αν τερματισμού

Ο παραπάνω κώδικας δημιουργεί μια συνάρτηση που εμφανίζει την ημερομηνία σε μορφή "ημ-μμ-εεεε" εάν δεν παρέχεται κανένα όρισμα, και σε μορφή "ηη mmmm, εεεε" όταν το όρισμα είναι 1. Δίνει σφάλμα σε όλες τις άλλες περιπτώσεις.

Λειτουργία με Απαιτούμενα καθώς και Προαιρετικά Επιχειρήματα

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

Τώρα ας ρίξουμε μια ματιά σε ένα παρόμοιο παράδειγμα που παίρνει τόσο απαιτούμενα όσο και προαιρετικά ορίσματα.

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

Λειτουργία GetText (CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Στη συνέχεια Αποτέλεσμα = Αποτέλεσμα & Μεσαίο (CellRef, i, 1) Επόμενο i Εάν TextCase = True Τότε Result = UCase (Result) GetText = Λειτουργία τερματισμού αποτελέσματος

Σημειώστε ότι στον παραπάνω κώδικα, έχουμε αρχικοποιήσει την τιμή του 'TextCase' ως False (δείτε μέσα στην παρένθεση στην πρώτη γραμμή).

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

Δημιουργία συνάρτησης στο VBA με έναν πίνακα ως επιχείρημα

Μέχρι τώρα έχουμε δει παραδείγματα δημιουργίας μιας συνάρτησης με Προαιρετικά/Απαιτούμενα ορίσματα - όπου αυτά τα ορίσματα ήταν μια ενιαία τιμή.

Μπορείτε επίσης να δημιουργήσετε μια συνάρτηση που μπορεί να λάβει έναν πίνακα ως όρισμα. Στις λειτουργίες του φύλλου εργασίας του Excel, υπάρχουν πολλές συναρτήσεις που λαμβάνουν ορίσματα πίνακα, όπως SUM, VLOOKUP, SUMIF, COUNTIF κ.λπ.

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

Συνάρτηση AddEven (CellRef as Range) Dim Cell As Range Για κάθε κελί στο CellRef If IsNumeric (Cell.Value) Στη συνέχεια, αν Cell.Value Mod 2 = 0 Τότε Αποτέλεσμα = Αποτέλεσμα + Κελί. Τερματισμός Τιμής Αν Λήξη Αν Επόμενο Κελί Προσθήκη ΕΔΩ = Αποτέλεσμα Τέλος Λειτουργία

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

Στην παραπάνω συνάρτηση, αντί για μία μόνο τιμή, έχουμε παράσχει έναν πίνακα (A1: A10). Για να λειτουργήσει αυτό, πρέπει να βεβαιωθείτε ότι ο τύπος δεδομένων του ορίσματος μπορεί να δεχτεί έναν πίνακα.

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

Στον κώδικα, υπάρχει ένας βρόχος Για κάθε που περνάει από κάθε κελί και ελέγχει αν είναι ένας αριθμός όχι. Αν δεν είναι, τίποτα δεν συμβαίνει και μετακινείται στο επόμενο κελί. Εάν είναι αριθμός, ελέγχει αν είναι ζυγό ή όχι (χρησιμοποιώντας τη λειτουργία MOD).

Στο τέλος, προστίθενται όλοι οι ζυγοί αριθμοί και το άθροισμα επιστρέφεται στη συνάρτηση.

Δημιουργία συνάρτησης με αόριστο αριθμό επιχειρημάτων

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

Ένα παράδειγμα τέτοιας συνάρτησης φύλλου εργασίας είναι η συνάρτηση SUM. Μπορείτε να του δώσετε πολλά ορίσματα (όπως αυτό):

= SUM (A1, A2: A4, B1: B20)

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

Μπορείτε να δημιουργήσετε μια τέτοια συνάρτηση στο VBA έχοντας το τελευταίο όρισμα (ή θα μπορούσε να είναι το μόνο όρισμα) ως προαιρετικό. Επίσης, σε αυτό το προαιρετικό όρισμα θα πρέπει να προηγείται η λέξη -κλειδί «ParamArray».

Το ‘ParamArray’ είναι ένας τροποποιητής που σας επιτρέπει να αποδέχεστε όσα επιχειρήματα θέλετε. Σημειώστε ότι η χρήση της λέξης ParamArray πριν από ένα όρισμα καθιστά το όρισμα προαιρετικό. Ωστόσο, δεν χρειάζεται να χρησιμοποιήσετε τη λέξη Προαιρετικό εδώ.

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

Λειτουργία AddArguments (ParamArray arglist () As Variant) Για κάθε arg In arglist AddArguments = AddArguments + arg Επόμενο arg Τέλος συνάρτησης

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

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

Εάν θέλετε να μπορείτε να χρησιμοποιείτε και τα δύο ορίσματα πολλών κελιών, πρέπει να χρησιμοποιήσετε τον παρακάτω κώδικα:

Συνάρτηση AddArguments (ParamArray arglist () As Variant) Για κάθε arg In arglist για κάθε κελί In arg AddArguments = AddArguments + Cell Next Cell Next arg End End

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

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

Δημιουργία μιας συνάρτησης που επιστρέφει έναν πίνακα

Μέχρι στιγμής έχουμε δει συναρτήσεις που επιστρέφουν μία μόνο τιμή.

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

Οι τύποι πίνακα είναι επίσης διαθέσιμοι ως ενσωματωμένες συναρτήσεις στα φύλλα εργασίας του Excel. Εάν είστε εξοικειωμένοι με τους τύπους συστοιχιών στο Excel, θα γνωρίζετε ότι αυτές καταχωρούνται χρησιμοποιώντας το Control + Shift + Enter (αντί για το Enter). Μπορείτε να διαβάσετε περισσότερα για τους τύπους συστοιχιών εδώ. Εάν δεν γνωρίζετε για τους τύπους συστοιχιών, μην ανησυχείτε, συνεχίστε να διαβάζετε.

Ας δημιουργήσουμε έναν τύπο που επιστρέφει έναν πίνακα τριών αριθμών (1,2,3).

Ο παρακάτω κωδικός θα το έκανε αυτό.

Λειτουργία ThreeNumbers () As Variant Dim NumberValue (1 To 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Function

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

Η μεταβλητή «NumberValue» δηλώνεται ως πίνακας με 3 στοιχεία. Διατηρεί τις τρεις τιμές και τις εκχωρεί στη συνάρτηση "ThreeNumbers".

Μπορείτε να χρησιμοποιήσετε αυτήν τη λειτουργία στο φύλλο εργασίας εισάγοντας τη συνάρτηση και πατώντας το πλήκτρο Control + Shift + Enter (κρατήστε πατημένα τα πλήκτρα Control και Shift και στη συνέχεια πατήστε Enter).

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

= MAX (ThreeNumbers ())

Χρησιμοποιήστε την παραπάνω λειτουργία με το Control + Shift + Enter. Θα παρατηρήσετε ότι το αποτέλεσμα είναι τώρα 3, καθώς είναι οι μεγαλύτερες τιμές στον πίνακα που επιστρέφει η συνάρτηση Max, η οποία παίρνει τους τρεις αριθμούς ως αποτέλεσμα της συνάρτησης που ορίσαμε από τον χρήστη - ThreeNumbers.

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

Λειτουργία Months () As Variant Dim MonthName (1 To 12) MonthName (1) = "January" MonthName (2) = "February" MonthName (3) = "March" MonthName (4) = "April" MonthName (5) = "Μάιος" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November "MonthName (12) =" December "Months = MonthName End Function

Τώρα όταν εισάγετε τη συνάρτηση = Μήνες () στο φύλλο εργασίας του Excel και χρησιμοποιείτε το Control + Shift + Enter, θα επιστρέψει ολόκληρο τον πίνακα των ονομάτων των μηνών. Σημειώστε ότι βλέπετε μόνο τον Ιανουάριο στο κελί, καθώς αυτή είναι η πρώτη τιμή στον πίνακα. Αυτό δεν σημαίνει ότι ο πίνακας επιστρέφει μόνο μία τιμή.

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

Μπορείτε να το χρησιμοποιήσετε χρησιμοποιώντας τον παρακάτω τύπο INDEX για να λάβετε μια λίστα με όλα τα ονόματα των μηνών με τη μία.

= INDEX (Μήνες (), ΣΕΙΡΑ ())

Τώρα αν έχετε πολλές τιμές, δεν είναι καλή πρακτική να αντιστοιχίσετε αυτές τις τιμές μία προς μία (όπως κάναμε παραπάνω). Αντ 'αυτού, μπορείτε να χρησιμοποιήσετε τη συνάρτηση Array στο VBA.

Έτσι, ο ίδιος κώδικας όπου δημιουργούμε τη συνάρτηση «Μήνες» θα γίνει συντομότερος όπως φαίνεται παρακάτω:

Μήνες Λειτουργίας () Ως Παραλλαγές Μήνες = Πίνακας ("Ιανουάριος", "Φεβρουάριος", "Μάρτιος", "Απρίλιος", "Μάιος", "Ιούνιος", _ "Ιούλιος", "Αύγουστος", "Σεπτέμβριος", "Οκτώβριος" , "Νοέμβριος", "Δεκέμβριος") Λειτουργία Τέλους

Η παραπάνω συνάρτηση χρησιμοποιεί τη συνάρτηση Array για να εκχωρήσει τις τιμές απευθείας στη συνάρτηση.

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

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

Μπορείτε να το κάνετε αυτό χρησιμοποιώντας τον τύπο TRANSPOSE στο φύλλο εργασίας.

Απλώς επιλέξτε 12 κατακόρυφα κελιά (συνεχόμενα) και εισαγάγετε τον παρακάτω τύπο.

Κατανόηση του πεδίου εφαρμογής μιας συνάρτησης που καθορίζεται από το χρήστη στο Excel

Μια συνάρτηση μπορεί να έχει δύο πεδία - Δημόσιο ή Ιδιωτικός.

• ΕΝΑ Δημόσιο πεδίο εφαρμογής σημαίνει ότι η συνάρτηση είναι διαθέσιμη για όλα τα φύλλα του βιβλίου εργασίας καθώς και για όλες τις διαδικασίες (Sub and Function) σε όλες τις ενότητες του βιβλίου εργασίας. Αυτό είναι χρήσιμο όταν θέλετε να καλέσετε μια συνάρτηση από μια υπορουτίνα (θα δούμε πώς γίνεται αυτό στην επόμενη ενότητα).
• ΕΝΑ Ιδιωτικό πεδίο σημαίνει ότι η συνάρτηση είναι διαθέσιμη μόνο στη μονάδα στην οποία υπάρχει. Δεν μπορείτε να το χρησιμοποιήσετε σε άλλες ενότητες. Επίσης, δεν θα το δείτε στη λίστα λειτουργιών στο φύλλο εργασίας. Για παράδειγμα, εάν το όνομα της συνάρτησης σας είναι «Μήνες ()» και πληκτρολογήσετε συνάρτηση στο Excel (μετά το σύμβολο =), δεν θα σας εμφανίσει το όνομα της συνάρτησης. Ωστόσο, μπορείτε ακόμα να το χρησιμοποιήσετε εάν εισαγάγετε το όνομα του τύπου.

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

Παρακάτω είναι μια συνάρτηση που είναι ιδιωτική λειτουργία:

Private Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

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

Ο παρακάτω κώδικας θα δημοσιοποιούσε αυτήν τη λειτουργία. Θα εμφανιστεί επίσης στο φύλλο εργασίας.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Διαφορετικοί τρόποι χρήσης μιας συνάρτησης που καθορίζεται από το χρήστη στο Excel

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

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

Χρήση UDF σε φύλλα εργασίας

Έχουμε ήδη δει παραδείγματα χρήσης μιας συνάρτησης που δημιουργήθηκε στο VBA στο φύλλο εργασίας.

Το μόνο που χρειάζεται να κάνετε είναι να εισαγάγετε το όνομα των συναρτήσεων και εμφανίζεται στο intellisense.

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

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

• Μεταβείτε στην καρτέλα Δεδομένα.
• Κάντε κλικ στην επιλογή «Εισαγωγή συνάρτησης».
• Στο παράθυρο διαλόγου Εισαγωγή συνάρτησης, επιλέξτε User Defined as the category. Αυτή η επιλογή εμφανίζεται μόνο όταν έχετε μια λειτουργία στο πρόγραμμα επεξεργασίας VB (και η λειτουργία είναι δημόσια).
• Επιλέξτε τη λειτουργία από τη λίστα όλων των δημόσιων λειτουργιών που ορίζονται από το χρήστη.
• Κάντε κλικ στο κουμπί Ok.

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

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

= ΠΑΝΩ (Όνομα βιβλίου εργασίας ())

Χρήση λειτουργιών που καθορίζονται από το χρήστη σε διαδικασίες και λειτουργίες VBA

Όταν έχετε δημιουργήσει μια συνάρτηση, μπορείτε να τη χρησιμοποιήσετε και σε άλλες υπο-διαδικασίες.

Εάν η συνάρτηση είναι δημόσια, μπορεί να χρησιμοποιηθεί σε οποιαδήποτε διαδικασία στην ίδια ή διαφορετική ενότητα. Εάν είναι ιδιωτικό, μπορεί να χρησιμοποιηθεί μόνο στην ίδια ενότητα.

Παρακάτω είναι μια συνάρτηση που επιστρέφει το όνομα του βιβλίου εργασίας.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Η παρακάτω διαδικασία καλεί τη συνάρτηση και στη συνέχεια εμφανίζει το όνομα σε ένα πλαίσιο μηνυμάτων.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

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

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

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Function WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) End Function

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

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

Υπάρχουν πολλοί τρόποι για να το κάνετε αυτό:

1. Δημιουργία πρόσθετου
2. Λειτουργία αποθήκευσης στο Personal Macro Workbook
3. Αναφορά της συνάρτησης από άλλο βιβλίο εργασίας.

Δημιουργία πρόσθετου

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

Ας υποθέσουμε ότι έχετε δημιουργήσει μια προσαρμοσμένη συνάρτηση - «GetNumeric» και την θέλετε σε όλα τα βιβλία εργασίας. Για να το κάνετε αυτό, δημιουργήστε ένα νέο βιβλίο εργασίας και έχετε τον κωδικό συνάρτησης σε μια ενότητα σε αυτό το νέο βιβλίο εργασίας.

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

• Μεταβείτε στην καρτέλα Αρχείο και κάντε κλικ στο Αποθήκευση ως.
• Στο παράθυρο διαλόγου Αποθήκευση ως, αλλάξτε τον τύπο «Αποθήκευση ως» σε .xlam. Το όνομα που θα εκχωρήσετε στο αρχείο θα είναι το όνομα του πρόσθετου σας. Σε αυτό το παράδειγμα, το αρχείο αποθηκεύεται με το όνομα GetNumeric.
  • Θα παρατηρήσετε ότι η διαδρομή του αρχείου όπου θα αποθηκευτεί αλλάζει αυτόματα. Μπορείτε να χρησιμοποιήσετε το προεπιλεγμένο ή να το αλλάξετε αν θέλετε.
• Ανοίξτε ένα νέο βιβλίο εργασίας του Excel και μεταβείτε στην καρτέλα Προγραμματιστής.
• Κάντε κλικ στην επιλογή Πρόσθετα Excel.
• Στο πλαίσιο διαλόγου Πρόσθετα, περιηγηθείτε και εντοπίστε το αρχείο που αποθηκεύσατε και κάντε κλικ στο κουμπί OK.

Τώρα το πρόσθετο έχει ενεργοποιηθεί.

Τώρα μπορείτε να χρησιμοποιήσετε την προσαρμοσμένη συνάρτηση σε όλα τα βιβλία εργασίας.

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

Το Personal Macro Workbook είναι ένα κρυφό βιβλίο εργασίας στο σύστημά σας που ανοίγει κάθε φορά που ανοίγετε την εφαρμογή Excel.

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

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

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

Αναφορά της συνάρτησης από άλλο βιβλίο εργασίας

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

Ας υποθέσουμε ότι έχετε ένα βιβλίο εργασίας με το όνομα «Τετράδιο εργασίας με τύπο », και έχει τη λειτουργία με το όνομα «GetNumeric '.

Για να χρησιμοποιήσετε αυτήν τη λειτουργία σε άλλο βιβλίο εργασίας (ενώ το Τετράδιο εργασίας με τύπο είναι ανοιχτό), μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο:

= 'Τετράδιο εργασίας με τύπο'! GetNumeric (A1)

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

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

Χρήση της δήλωσης λειτουργίας εξόδου VBA

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

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

Λειτουργία GetNumericFirstThree (CellRef As Range) Long Long StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Στη συνέχεια Έξοδος από τη συνάρτηση If IsNumeric (Mid (CellRef, i, 1)) Στη συνέχεια J = J + 1 Αποτέλεσμα = Αποτέλεσμα & μεσαίο (CellRef, i, 1) GetNumericFirstThree = Λήξη αποτελέσματος εάν η επόμενη λειτουργία τερματισμού

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

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

Υπάρχουν μερικές τεχνικές που μπορείτε να χρησιμοποιήσετε κατά τον εντοπισμό σφαλμάτων σε μια λειτουργία που ορίζεται από το χρήστη στο VBA:

Αποσφαλμάτωση προσαρμοσμένης λειτουργίας χρησιμοποιώντας το πλαίσιο μηνυμάτων

Χρησιμοποιήστε τη λειτουργία MsgBox για να εμφανίσετε ένα πλαίσιο μηνυμάτων με μια συγκεκριμένη τιμή.

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

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

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

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

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

Μπορείτε να χρησιμοποιήσετε τη δήλωση Debug.Print στον κώδικά σας για να λάβετε τις τιμές των καθορισμένων μεταβλητών/ορισμάτων στο άμεσο παράθυρο.

Για παράδειγμα, στον παρακάτω κώδικα, έχω χρησιμοποιήσει το Debug.Print για να λάβω την τιμή δύο μεταβλητών - "j" και "Result"

Συνάρτηση GetNumericFirstThree (CellRef As Range) Long Long StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric (Mid (CellRef, i, 1)) Στη συνέχεια J = J + 1 Αποτέλεσμα = Αποτέλεσμα & Μεσαίο (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Λήξη Αποτέλεσμα Αν Συνάρτηση Next i End

Όταν εκτελείται αυτός ο κώδικας, εμφανίζει τα ακόλουθα στο άμεσο παράθυρο.

Ενσωματωμένες λειτουργίες Excel Vs. Λειτουργία καθορισμένη από τον χρήστη VBA

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

• Οι ενσωματωμένες λειτουργίες είναι πολύ πιο γρήγορες από τις λειτουργίες VBA.
• Όταν δημιουργείτε μια αναφορά/πίνακα ελέγχου χρησιμοποιώντας λειτουργίες VBA και την αποστέλλετε σε έναν πελάτη/συνάδελφο, δεν θα χρειάζεται να ανησυχεί εάν οι μακροεντολές είναι ενεργοποιημένες ή όχι. Σε ορισμένες περιπτώσεις, οι πελάτες/πελάτες φοβούνται βλέποντας μια προειδοποίηση στην κίτρινη γραμμή (η οποία τους ζητά απλώς να ενεργοποιήσουν τις μακροεντολές).
• Με ενσωματωμένες λειτουργίες Excel, δεν χρειάζεται να ανησυχείτε για τις επεκτάσεις αρχείων. Εάν έχετε μακροεντολές ή λειτουργίες που ορίζονται από το χρήστη στο βιβλίο εργασίας, πρέπει να το αποθηκεύσετε σε .xlsm.

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

• Είναι καλύτερα να χρησιμοποιήσετε μια συνάρτηση που ορίζεται από τον χρήστη εάν ο ενσωματωμένος τύπος σας είναι τεράστιος και περίπλοκος. Αυτό γίνεται ακόμα πιο σχετικό όταν χρειάζεστε κάποιον άλλο για να ενημερώσετε τους τύπους. Για παράδειγμα, εάν έχετε έναν τεράστιο τύπο που αποτελείται από πολλές διαφορετικές λειτουργίες, ακόμη και η αλλαγή μιας αναφοράς σε ένα κελί μπορεί να είναι κουραστική και επιρρεπής σε σφάλματα. Αντ 'αυτού, μπορείτε να δημιουργήσετε μια προσαρμοσμένη συνάρτηση που παίρνει μόνο ένα ή δύο ορίσματα και κάνει όλη τη βαριά ανύψωση του backend.
• Όταν πρέπει να κάνετε κάτι που δεν μπορεί να γίνει με ενσωματωμένες λειτουργίες του Excel. Ένα παράδειγμα αυτού μπορεί να είναι όταν θέλετε να εξαγάγετε όλους τους αριθμητικούς χαρακτήρες από μια συμβολοσειρά. Σε τέτοιες περιπτώσεις, το όφελος από τη χρήση μιας συνάρτησης gar καθορίζεται από τα αρνητικά της.

Πού να τοποθετήσετε τον κώδικα VBA για μια συνάρτηση που καθορίζεται από το χρήστη

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

Παρακάτω είναι τα βήματα για να βάλετε τον κώδικα για τη συνάρτηση "GetNumeric" στο βιβλίο εργασίας.

1. Μεταβείτε στην καρτέλα Προγραμματιστής.
2. Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
3. Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα. Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
4. Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
5. Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.

Μπορεί επίσης να σας αρέσουν τα ακόλουθα μαθήματα Excel VBA:

• Εργασία με κελιά και εύρη στο Excel VBA.
• Εργασία με φύλλα εργασίας στο Excel VBA.
• Εργασία με βιβλία εργασίας χρησιμοποιώντας VBA.
• Πώς να χρησιμοποιήσετε βρόχους στο Excel VBA.
• Excel VBA Events - Ένας εύκολος (και πλήρης) οδηγός
• Χρήση IF then Else Statements στο VBA.
• Πώς να καταγράψετε μια μακροεντολή στο Excel.
• Πώς να εκτελέσετε μια μακροεντολή στο Excel.
• Πώς να ταξινομήσετε δεδομένα στο Excel χρησιμοποιώντας το VBA (Οδηγός βήμα προς βήμα).
• Λειτουργία Excel VBA InStr - Εξηγείται με παραδείγματα.