Χρήση VBA συναρτήσεων σε επικυρώσεις και μορφοποιήσεις υπό όρους

Αν προσπαθήσετε να χρησιμοποιήσετε μια συνάρτηση που βρίσκεται σε ένα module του βιβλίου σας ή στο personal ή σε ένα πρόσθετο, σε μία επικύρωση δεδομένων ή σε μία μορφοποίηση υπό όρους, πιθανά θα πάρετε ένα απαγορευτικό μήνυμα:
«Δεν είναι δυνατή η χρήση αναφορών σε άλλα φύλλα ή βιβλία εργασίας για τα κριτήρια Μορφοποίησης υπό όρους / Επικύρωσης δεδομένων »
ή
«Δεν είναι δυνατή η εύρεση του καθορισμένου ονόματος περιοχής»
ή
«Δεν είναι δυνατή η χρήση αυτού του τύπου αναφοράς σε ένα τύπο Μορφοποίησης υπό όρους / Επικύρωσης δεδομένων»

Ενώ όλες οι ενσωματωμένες συναρτήσεις του excel μπορούν άφοβα να χρησιμοποιηθούν σε μορφοποιήσεις και επικυρώσεις, δεν ισχύει το ίδιο για συναρτήσεις χρήστη ή συναρτήσεις που έρχονται από ένα πρόσθετο.
Αλλά ας δούμε κάθε περίπτωση χωριστά.
α) Αν η vba συνάρτησή σας είναι αποθηκευμένη σε ένα module του βιβλίου σας, το excel επιτρέπει τη χρήση της συνάρτησης σε μορφοποίηση υπό όρους του ίδιου του βιβλίου, αλλά όχι σε επικύρωση δεδομένων.
β) Αν η vba συνάρτησή σας είναι αποθηκευμένη σε ένα άλλο βιβλίο ή στο personal βιβλίο σας ή έρχεται από ένα (ενεργοποιημένο) πρόσθετο, το excel δεν επιτρέπει τη χρήση της συνάρτησης ούτε σε μορφοποίηση υπό όρους ούτε σε επικύρωση δεδομένων.
γ) Ειδική φροντίδα χρειάζονται οι συναρτήσεις που έως και την έκδοση 2003 υπήρχαν στο πρόσθετο «Πακέτο εργαλείων ανάλυσης». Από την έκδοση 2007 και μετά οι συναρτήσεις αυτές είναι ενσωματωμένες στο excel και αποτελούν μόνιμα μέλη της συλλογής συναρτήσεων του. Μπορούν, λοιπόν, στις νεότερες εκδόσεις να χρησιμοποιηθούν άφοβα για επικυρώσεις και μορφοποιήσεις, αλλά θα έχετε ένα μικρό πρόβλημα συμβατότητας αν υποθηκεύσετε σαν βιβλίο 97-2003. Οι συναρτήσεις (δηλαδή οι επικυρώσεις και μορφοποιήσεις) θα δουλέψουν και στις παλιότερες εκδόσεις με ενεργοποιημένο το πρόσθετο, αλλά δεν θα επιδέχονται καμία μεταβολή και επεξεργασία.

Σκοπός του σημερινού σημειώματος είναι να σας παρουσιάσει μια τεχνική με την οποία θα μπορείτε να χρησιμοποιήσετε τις vba συναρτήσεις, όπου και αν αυτές βρίσκονται, σε επικυρώσεις και μορφοποιήσεις.
Ευτυχώς το excel επιτρέπει την αναπαράσταση συναρτήσεων από ονόματα και τη χρήση των ονομάτων αυτών σε μορφοποιήσεις και επικυρώσεις. Αυτόν τον έμμεσο τρόπο ας δούμε αναλυτικά με ένα παράδειγμα.
Πρώτα χρειαζόμαστε μία vba συνάρτηση. Η παρακάτω απλή συνάρτηση, η digitsum που επιστρέφει το άθροισμα των ψηφίων ενός αριθμού είναι ότι πρέπει. Γράψτε την σε ένα module ενός βιβλίου.

Function digitsum(n As Variant) As Integer
Dim j As Integer
For j = 1 To VBA.Len(n)
digitsum = digitsum + VBA.Val(VBA.Mid(n, j, 1))
Next
End Function

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

digitsumbook
Με επιλεγμένο το κελί A1 εισάγετε το όνομα:
namedigitsum =digitsum(!a1)
Επαναλαμβάνω ότι κατά την εισαγωγή του ονόματος πρέπει να είναι επιλεγμένο το κελί A1 ή εναλλακτικά, σαν όρισμα της συνάρτησης digitsum θα γράψετε την σχετική (και όχι απόλυτη με $$) διεύθυνση του κελιού που είναι επιλεγμένο εκείνη τη στιγμή. Αν κατά την εισαγωγή του ονόματος είναι επιλεγμένο το κελί C12, το όνομα θα εισαχθεί έτσι:
namedigitsum =digitsum(!C12)
Τώρα επιλέξτε τη στήλη Α και εισάγετε τη μορφοποίηση υπό όρους με τύπο:
=namedigitsum>20
Είστε έτοιμοι, αυτό είναι όλο.

Το ίδιο όνομα namedigitsum που αναπαριστά τη συνάρτηση digitsum, μπορεί να χρησιμοποιηθεί για επικύρωση δεδομένων. Θέλετε σε κάποια κελιά να επιτρέπεται μόνο η είσοδος αριθμών που τα ψηφία τους έχουν άθροισμα μεταξύ 10 και 20. Εισάγετε στα κελιά αυτά την επικύρωση με τύπο:
=AND(namedigitsum>10;namedigitsum<20)
Την ίδια ακριβώς τεχνική θα ακολουθήσετε αν η vba συνάρτηση έρχεται από κάποιο πρόσθετο, ενώ αν βρίσκεται στο personal βιβλίο σας τότε για την εισαγωγή του ονόματος η σύνταξη είναι:
namedigitsum=PERSONAL.XLSB!digitsum(!a1)
ή για excel<2007:
namedigitsum=PERSONAL.XLS!digitsum(!a1)

Δέστε, αν θέλετε, το παραπάνω παράδειγμα στο excel βιβλίο digitsumbook.xls

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

This entry was posted in excel, Επικύρωση, Μακροεντολές, Μορφοποίηση υπό όρους, Συναρτήσεις φύλλου, Συναρτήσεις Χρήστη, VBA and tagged , , , , , , , . Bookmark the permalink.

10 Responses to Χρήση VBA συναρτήσεων σε επικυρώσεις και μορφοποιήσεις υπό όρους

  1. Ο/Η ΣΤΕΦΑΝΙΑ ΣΙΜΩΝΕΤΗ λέει:

    Αγαπητέ κύριε. Θα ήθελα τη βοήθειά σας στο παρακάτω πρόβλημα που έχω, γνωρίζοντάς σας ότι δεν έχω ιδιάιτερες γνώσεις στο excell.
    Έχω ένα φύλλο εργασίας ΜΕ 250 ΠΕΡΊΟΥ ΓΡΑΜΜΈς Απο στα κελιά απ Α ΕΩΣ ΚΑΙ Κ ΕΧΩ ΣΤΑΘΕΡΑ ΣΤΟΙΧΕΊΑ ΟΠΩΣ ΟΝΜΑΤΕΠΩΝΥΜΑ, ΔΙΕΥΘΥΝΣΕΙΣ ΚΑΙ ΑΛΛΟΥ ΚΩΔΙΚΟΥΣ ΣΤΟ ΚΕΛΙ L ΕΧΩ ΠΟΣΑ ΔΙΑΦΟΡΕΤΙΚΑ ΓΙΑ ΚΑΘΕ ΓΡΑΜΜΗ . ΣΕ ΑΥΤΑ ΤΑ ΠΟΣΑ ΘΕΛΩ ΣΤΟ ΚΕΛΙ Μ ΝΑ ΜΠΟΡΩ ΝΑ ΒΓΑΖΩ ΠΟΣΟΣΤ0 6% ΚΑΙ ΑΥΤΟ ΝΑ ΑΠΟΔΙΔΕΤΑΙ ΑΡΙΘΜΗΤΙΚΑ ΣΤΟ ΣΥΚΓΕΚΡΙΜΕΝΟ ΚΕΛΙ (Μ) ΚΑΙ ΟΛΟΓΡΑΦΩΣ ΣΑΝ ΕΥΡΩ ΣΤΟ ΚΕΛΙ (Ν).
    ΣΑΣ ΓΩΝΡΙΖΩ ΟΤΙ ΧΡΗΣΙΜΟΠΟΙΩ ΕXCEL 2007/ Η ΒΟΗΘΕΙΑ ΣΑΣ ΘΑ ΜΟΥ ΕΙΝΑΙ ΠΟΛΥΤΙΜΗ. ΣΑΣ ΕΥΧΑΡΙΣΤΩ ΠΟΛΥ

  2. Ο/Η vioannis λέει:

    Στεφανία, δεν διευκρινίζεις ως προς τι είναι το ποσοστό, ποιος είναι ο παρονομαστής. Υποθέτω πως είναι το άθροισμα της στήλης L. Και επειδή δε ακόμα υποθέτω ότι στη μικρή αυτή βάση σου, θα εισάγονται συνεχώς νέες εγγραφές (γραμμές), λοιπόν, στο κελί Μ1 (ή μάλλον στο κελί Μ2, γιατί στο Μ1 θα έχεις κάποιο τίτλο) γράφεις τη συνάρτηση =L2/SUM(L:L) Στη συνέχεια δεξί κλικ πάνω στο κελί Μ2 και : Μορφοποίηση κελιών > Αριθμός > Ποσοστό. Μετά αντιγράφεις το κελί αυτό κάτω- κάτω έως το τέλος της βάσης σου.
    Για να γράφεις αριθμούς ολογράφως σε ευρώ θα χρειαστείς την συνάρτηση HellasEuro του ιστολογίου ( ή την TextNumber από το θέμα του ιστολογίου “Αριθμοί ολογράφως στο excel”
    Πως θα το κάνεις:
    Γύρνα το πληκτρολόγιο σου στα ελληνικά και αντίγραψε τον κώδικα στο θέμα του ιστολογίου “ Ευρώ Ολογράφως στην Ελληνική γλώσσα”
    Με ανοιχτό το excel βιβλίο σου, πάτα τον συνδυασμό πλήκτρων ALT+F11 για να εμφανιστεί ο επεξεργαστής της Visual Basic. Στο πάνω αριστερά παράθυρο Project, βρες το βιβλίο σου. Αν το βιβλίο λέγεται πχ BOOK1, εδώ θα το δεις ως: VBAProject(BOOK1). Επίλεξε (δεξί κλικ) τη γραμμή VBAProject(BOOK1), άνοιξε το μενού Insert και πάτα Module. Στην λευκή δεξιά περιοχή του module1 που θα δημιουργηθεί, κάνε επικόλληση του κώδικα που αντέγραψες. Όταν ολοκληρώσεις πάτησε ALT+Q για να κλείσει ο επεξεργαστής της Visual Basic και να ξαναγυρίσεις στο βιβλίο. Αποθήκευσε το βιβλίο σαν βιβλίο με δυνατότητα μακροεντολών (xlsm). Τώρα μπορείς να κάνεις χρήση της συνάρτησης HellasEuro όπως χρησιμοποιείς και εισάγεις και τις άλλες συναρτήσεις

  3. Ο/Η ΣΤΕΦΑΝΙΑ ΣΙΜΩΝΕΤΗ λέει:

    ΤΟ ΝΑ ΣΑΣ ΠΩ ΑΠΛΑ ΕΥΧΑΡΙΣΤΩ , ΘΑ ΗΤΑΝ ΑΧΑΡΙΣΤΙΑ ΕΚ ΜΕΡΟΥΣ ΜΟΥ. ΣΑΣ ΕΥΧΟΜΑΙ ΝΑ ΕΙΣΤΕ ΠΑΝΤΑ ΚΑΛΑ ΚΑΙ ΝΑ ΒΟΗΘΑΤΕ. ΜΕ ΣΩΣΑΤΕ…..

  4. Ο/Η ΜΑΡΩ λέει:

    Καλησπέρα και συγχαρητήρια για την δουλειά σου.
    Ξέρω να χειρίζομαι το excel αρκετά καλά αλλά υπάρχει ένα θέμα που δεν έχω καταφέρει ακόμα να βρω λύση. Στην εντολή if(A1=0;»Fs=10″;»Fs=8″). Προφανώς αναφέρομαι με τα » » σε κείμενο. Θέλω το s (και μόνο αυτό) να έχει άλλη μορφοποίηση από την γενική του κελιού, πχ να είναι γραμμένο ως δείκτης ή με άλλη γραμματοσειρά ή με άλλο χρώμα.
    Ευχαριστώ εκ των προτέρων

  5. Ο/Η vioannis λέει:

    Μάρω, δεν έχεις βρει λύση διότι δεν υπάρχει λύση μέσω της συνάρτησης. Οι συναρτήσεις μπορούν να επιστρέψουν τιμές, δεν μπορούν να κάνουν ενέργειες, δεν μπορούν να μορφοποιήσουν ένα κελί. Μπορείς όμως όταν γράφεις τη συνάρτηση στη θέση του s να επιλέξεις ένα ειδικό unicode χαρακτήρα. Πίνακα με unicode χαρακτήρες θα βρεις εδώ:
    http://www.ssec.wisc.edu/~tomw/java/unicode.html
    Μπορείς να τον μεταφέρεις στη συνάρτησή σου με απλή αντιγραφή – επικόλληση.
    Επίσης πίνακα με unicode χαρακτήρες μπορείς να δημιουργήσεις σε ένα φύλλο του excel, με τον τρόπο που περιγράφω στο σχετικό θέμα του ιστολογίου (“Ανακαλύψτε τους χιλιάδες Unicode χαρακτήρες του excel”). Μάλιστα κάνοντας χρήση της συνάρτησης UnicodeChar που περιγράφω στο θέμα αυτό, έγραψα τη συνάρτησή σου έτσι:

    =IF(A1=0;"F"&UnicodeChar(9416)&"=10";"F"&UnicodeChar(9416)&"=8")
    

    και πήρα:

     FⓈ=10 και FⓈ=8
    

    (Αν μάλιστα δουλεύεις στη νέα 2013 έκδοση του excel, χρησιμοποίησε τη νέα συνάρτηση =UNICHAR(9416)
    Τον χαρακτήρας αυτό (το γράμμα S μέσα σε κύκλο) εκτός από τον δεκαδικό κωδικό του (9416), μπορείς να τον γράψεις στο word από τον 16δικό κωδικό του. Γράψε στο word 24C8 και αμέσως πάτα Alt+X και θα πάρεις τον χαρακτήρα, από όπου μπορείς με αντιγραφή – επικόλληση να τον μεταφέρεις στη συνάρτησή σου. Δοκίμασε και τους χαρακτήρες 9442, 65363, 739, 983, 1003,1008 ή στο word αντίστοιχα: 24E2, FF53, 02E3,03D7, 03EB,03F0

  6. Ο/Η Φλώρα Ελευθερίου λέει:

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

  7. Ο/Η vioannis λέει:

    Βέβαια και γίνεται Φλώρα. Δες ένα μικρό παράδειγμα:
    Άνοιξε ένα νέο βιβλίο και στη στήλη Α1:Α100 του Φύλλου1 πέρασε μια οποιαδήποτε επικύρωση δεδομένων.
    Τώρα στον επεξεργαστή της VBA να γράψε τον παρακάτω λίγο κώδικα, στο Φύλλο1 του VBAProject του βιβλίου σου (και όχι σε ένα module).

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ChangeArea As Range
    Dim keli As Range
    Set ChangeArea = Range("G1:G100")
    Set keli = Intersect(Target, ChangeArea)
    If Not keli Is Nothing Then keli(1, -5) = keli.Value
    End Sub
    

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

  8. Ο/Η Νικόλας λέει:

    Γεια σας κύριε Γιάννη. Θα ήθελα (και πάλι) τη βοήθειά σας.!
    Είχα φτιάξει ένα απλό βιβλίο σε excel για να τιμολογώ. Πρόσφατα το έκανα λίγο πιο πλούσιο προσθέτοντας και μακροεντολές. Αλλά μιας και δεν τις κατέχω, είναι τροποποιημένες από διάφορες που βρήκα στο ίντερνετ. Δεν λέω πως δεν με καλύπτει, αλλά θα ήθελα το κάτι παραπάνω (για ευκολία περισσότερο).
    Έχω καταφέρει να συμπληρώνω αυτόματα πελάτες και κωδικούς, όπως επίσης να το αποθηκεύω σε pdf και να κάνω καταχώρηση σε άλλο φύλλο με τις πωλήσεις μου. (και όλα αυτά με ένα κουμπάκι!!! Ωραίος ο προγραμματισμός τελικά!!)
    Το θέμα μου είναι πως εκεί που κάνω καταχώρηση των παραστατικών που κόβω κάθε φορά, κατάφερα μόνο να τα βάζω στην 2η γραμμή του φύλλου και όχι στην τελευταία που θέλω. Δεν ξέρω αν γίνεται όπως το ζητάω βέβαια γιατί κάτω από τις καταχωρήσεις έχω τα σύνολα τα οποία θέλω να είναι και αυτά τελευταία. Να μπαίνει κάθε τιμολόγιο δηλαδή αμέσως κάτω από το προηγούμενο (και η γραμμή των συνόλων να κατεβαίνει κάθε φορά).
    Ο κώδικας που χρησιμοποίησα είναι αυτός:

    Sheets("ΕΣΟΔΑ").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
        Sheets("ΕΣΟΔΑ").Range("A3:N3").Copy
        Sheets("ΕΣΟΔΑ").Range("A2:N2").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
        Sheets("ΕΣΟΔΑ").Range("A2:N2").SpecialCells(xlCellTypeConstants).ClearContents
    

    Σας βάζω επίσης link του αρχείου αν θέλετε να ρίξετε μια ματιά μήπως και έχετε κάποια ιδέα…

    Ευχαριστώ πολύ για όλη τη βοήθεια που μου/μας δίνετε :)

    http://jmp.sh/PqqeR8w

  9. Ο/Η Νικόλας λέει:

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

        Sheets("ΕΣΟΔΑ").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
        Sheets("ΕΣΟΔΑ").Range("A3:N3").Copy
        Sheets("ΕΣΟΔΑ").Range("A2:N2").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
        Sheets("ΕΣΟΔΑ").Range("A2:N2").SpecialCells(xlCellTypeConstants).ClearContents
    'edw prosthetei kainourgia grammh sthn thesh ths 2 me tis morfopoiiseis kai tis sinartiseis tis prohgoumens 2
        Sheets("Τιμολόγιο").Range("L3").Copy
        Sheets("ΕΣΟΔΑ").Range("A2").PasteSpecial Paste:=xlPasteValues
        Sheets("Τιμολόγιο").Range("M6").Copy
        Sheets("ΕΣΟΔΑ").Range("B2").PasteSpecial Paste:=xlPasteValues
        Sheets("Φύλλο2").Range("A4").Copy
        Sheets("ΕΣΟΔΑ").Range("C2").PasteSpecial Paste:=xlPasteValues
        Sheets("Τιμολόγιο").Range("C10").Copy
        Sheets("ΕΣΟΔΑ").Range("D2").PasteSpecial Paste:=xlPasteValues
        Sheets("Τιμολόγιο").Range("M48").Copy
        Sheets("ΕΣΟΔΑ").Range("E2").PasteSpecial Paste:=xlPasteValues
    'edw kanei antigrafei kai epikolish twn dedomenwn apo to timologio sthn kainourgia pleon 2 grmmh twn esodwn
    Dim eRow As Object
            Set eRow = Sheets("ΕΣΟΔΑ").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
     'epomeno keli apo to teleutaio grammeno sth sthlh 1
        Sheets("ΕΣΟΔΑ").Range("A2:I2").Copy
        eRow.PasteSpecial Paste:=xlPasteValues
        Sheets("ΕΣΟΔΑ").Range("2:2").Delete
    'edw antigrafei kai epikola oloklirh thn kainourgia grammh 2 sthn prwth kenh grammh kai meta thn diagrafh teleiws (thn grammh 2),
    'etsi wste na einai etoimh gia thn epomenh kataxwrysh
    

    Δεν μπόρεσα να σκεφτώ κάτι καλύτερο, αφού τα κελιά που πρέπει να γίνεται η επικόλληση από το Τιμολόγιο, πρέπει να είναι πάντα τα ίδια (νομίζω δηλαδή). Και επίσης για να μπορέσω να έχω τα σύνολα των πράξεων κάτω από τις καταχωρήσεις των τιμολογίων (στο φύλλο «ΕΣΟΔΑ»),τα έβαλα στην γραμμή 1001 και μετά έκανα διαίρεση παραθύρου (δεν ήξερα πως υπάρχει τέτοιο πράγμα). Ίσως να μπορούσα να κάνω κάτι πιο απλό, αλλά έτσι μου φαίνονται σχετικά κατανοητά.

  10. Ο/Η vioannis λέει:

    Νίκο , όλοι οι κώδικες μπορούν να γραφτούν και διαφορετικά. Εδώ έκανες, ότι θα έκανε ένας χρήστης χειριστικά. Αφού σου παράγει σωστά αποτελέσματα, είναι καλός. Απλά αν με τα αλλεπάλληλα copy και paste σε διαφορετικά φύλλα, παίζει η οθόνη, για να το αντιμετωπίσεις, κάπου στην αρχή του κώδικα γράψε τη γραμμή
    Application.ScreenUpdating = False
    Και πριν το End Sub επανέφερε:
    Application.ScreenUpdating = True
    Αυτό θα κάνει και τον κώδικα πιο γρήγορο.

Τα σχόλια είναι απενεργοποιημένα.