Χρήση συναρτήσεων φύλλου στην vba

Ένα από τα πλεονεκτήματα που έχει η σύνταξη κώδικα vba για το excel, είναι η δυνατότητα που υπάρχει, να μπορούν να χρησιμοποιηθούν οι περισσότερες συναρτήσεις φύλλου στον κώδικα. Επειδή οι συναρτήσεις φύλλου είναι πολλές και εξειδικευμένες, απλοποιούν πολύ τη γραφή του κώδικα. Για να υπολογιστεί η τοκοχρεωλυτική δόση ενός δανείου, δεν χρειάζεται να γραφτεί ειδικός κώδικας για αυτό, αφού υπάρχει η συνάρτηση φύλλου PMT που κάνει αυτή τη δουλειά, και η συνάρτηση μπορεί να κληθεί μέσα από την vba. Τις μόνες συναρτήσεις φύλλου που δεν επιτρέπει η vba να κληθούν, είναι αυτές για τις οποίες έχει η ίδια ισοδύναμες συναρτήσεις στη βιβλιοθήκη της, που κάνουν την ίδια εργασία. Παράδειγμα, δεν μπορεί να κληθεί η συνάρτηση φύλλου LEN(text) που μετρά το πλήθος των χαρακτήρων του ορίσματος text, γιατί η vba έχει μια ισοδύναμη συνάρτηση, την vba.Len που κάνει ακριβώς το ίδιο. Επιτρέπει όμως να κληθεί η συνάρτηση φύλλου REPLACE παρόλο που έχει στην βιβλιοθήκη της μια συνώνυμη την vba.Replace, αλλά οι δύο συναρτήσεις δεν επιστρέφουν τα ίδια πράγματα, δουλεύουν διαφορετικά.
Πως καλούνται οι συναρτήσεων φύλλου.
Για να κληθεί μια συνάρτηση φύλλου πρέπει να γραφτεί μία γραμμή κώδικα σαν αυτή:

Application.WorksheetFunction.Συνάρτηση(ορίσματα)

Ακριβολογώντας θα λέγαμε ότι: Οι συναρτήσεις φύλλου είναι μέλη του αντικειμένου WorksheetFunction, το οποίο με τη σειρά του είναι μέλος(ιδιότητα) του αντικειμένου Application.
Στην παρακάτω απλή ρουτίνα η συνάρτηση φύλλου ΜΑΧ επιστρέφει μέσω ενός πλαισίου μηνύματος (MsgBox), τη μέγιστη αριθμητική τιμή που θα βρει στα κελιά A1:B100 του Φύλλου1

Sub testI()
Dim m As Double
m = Application.WorksheetFunction.Max(Range("Φύλλο1!A1:B100"))
MsgBox m
End Sub

Η κλήση της συνάρτησης φύλλου (ΣΦ) μπορεί να γίνει και με τους παρακάτω δύο απλοποιημένους τρόπους:

WorksheetFunction.Συνάρτηση(ορίσματα)

ή

Application. Συνάρτηση(ορίσματα)

Η πρώτη, διότι το αντικείμενο WorksheetFunction μπορεί να κληθεί σαν υψηλού επιπέδου αντικείμενο, απευθείας, και όχι μόνο σαν ιδιότητα του αντικειμένου Application. Η δεύτερη διότι στα αρχαία χρόνια, πριν την έκδοση 1997 του excel, οι ΣΦ ήταν μέλη απευθείας του Application, και οι νεότερες εκδόσεις της vba θέλουν, και σωστά, να μπορούν να διαβάζουν και να εκτελούν ρουτίνες που έχουν γραφτεί τα αρχαία εκείνα χρόνια. Δεν υπάρχουν λόγοι να χρησιμοποιούνται οι δύο αυτές μορφές, το αντίθετο μάλιστα, γι αυτό συνιστάται ο πρώτος τρόπος κλίσης των ΣΦ.
Δεν υπάρχουν ουσιαστικές διαφορές στην συμπεριφορά των συναρτήσεων όταν καλούνται από το φύλλο ή την vba. Οπωσδήποτε όμως, χρειάζεται να γραφτεί κώδικας που να χειρίζεται τις περιπτώσεις όπου η συνάρτηση πιθανά να επιστρέψει ένα σφάλμα αλλιώς η εκτέλεση του κώδικα θα διακοπεί.
Ένα παράδειγμα
Ο παρακάτω κώδικας καλεί τη συνάρτηση φύλλου VLOOKUP η οποία αναζητά τη τιμή του κελίου D1 στην πρώτη στήλη του πίνακα A1:B25, για να επιστρέψει στο κελί E1 την αντίστοιχη τιμή από τη 2η στήλη του πίνακα.

Sub testII()
Dim lookup_value As String
Dim table_array As Range
Dim pl As Variant
lookup_value = Range("d1").Value
Set table_array = Range("a1:b25")
pl = Application.WorksheetFunction. _
    VLookup(lookup_value, table_array, 2, False)
Range("e1") = pl
End Sub

Πιο αναλυτικά, ο δίστηλος πίνακας A1:B25 περιέχει πόλεις και τον πληθυσμό τους. Εισάγετε πόλεις στο κελί D1 και η μακροεντολή επιστρέφει στο κελί E1 τον αντίστοιχο πληθυσμό. Όλα πάνε καλά, ώσπου ένας χρήστης εισάγει στο κελί D1 μια πόλη που δεν υπάρχει στον πίνακα. Αν η συνάρτηση VLOOKUP ήταν γραμμένη στο φύλλο θα επέστεφε ένα αξιοπρεπέστατο #Δ/Υ (δεν υπάρχει) και όλα θα ήταν εντάξει, αλλά ο κώδικας θα τραγικοποιήσει λίγο την κατάσταση, θα εμφανίσει ένα σφάλμα (λέγεται σφάλμα κατά το χρόνο εκτέλεσης) και θα σταματήσει.

WorksheetFunctionsInVBA

Μην πάρετε τοις μετρητοίς το μήνυμα «Δεν είναι δυνατή η κλήση της Vloopup από την WorksheetFunction», δεν το εννοεί γενικά, το εννοεί μόνο για την όμορφη Σύρο.
Πρέπει λοιπόν, να ελέγξετε αυτή την περίπτωση στον κώδικα. Υπάρχουν διάφοροι τρόποι. Ένας είναι, να του πείτε: Εντάξει, μη υπερβάλεις, αν βρεις σφάλμα πήγαινε παρακάτω. Η εντολή είναι:

On Error Resume Next

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

On Error GoTo 0

Ο παρακάτω κώδικας:

Sub testIII()
Dim lookup_value As String
Dim table_array As Range
Dim pl As Variant
lookup_value = Range("d1").Value
Set table_array = Range("a1:b25")
On Error Resume Next
pl = Application.WorksheetFunction. _
    VLookup(lookup_value, table_array, 2, False)
On Error GoTo 0
Range("e1") = pl
End Sub

θα τρέξει χωρίς πρόβλημα αν στο κελί D1 γράψουμε τη Σύρο ή άλλη πόλη που δεν υπάρχει στον πίνακα και θα επιστρέψει στο κελί E1 κενό.
Ίσως, να θέλετε πλήρη έλεγχο στο τι θα επιστραφεί σε περίπτωση σφάλματος. Ο παρακάτω κώδικας αν όλα πάνε καλά, υπολογίζει την τιμή της μεταβλητής pl από τη συνάρτηση vlookup, την τιμή αυτή εξάγει στο κελί E1 και τερματίζει (Exit Sub). Σε περίπτωση σφάλματος ο κώδικας μεταπηδά στη γραμμή telos, δεν ασχολείται καθόλου με τη vlookup, και γράφει «Δεν υπάρχει» στο κελί E1.

Sub testIV()
Dim lookup_value As String
Dim table_array As Range
Dim pl As Variant
lookup_value = Range("d1").Value
Set table_array = Range("a1:b25")
On Error GoTo telos
pl = Application.WorksheetFunction. _
    VLookup(lookup_value, table_array, 2, False)
Range("e1") = pl
Exit Sub
telos:
Range("e1") = "Δεν υπάρχει"
End Sub

Κλείνω το σημερινό σημείωμα με μια παραξενιά. Τρέξτε τον παρακάτω κώδικα και βάλτε στο κελί D1 τη Σύρο:

Sub testV()
Dim lookup_value As String
Dim table_array As Range
Dim pl As Variant
lookup_value = Range("d1").Value
Set table_array = Range("a1:b25")
pl = Application.VLookup(lookup_value, table_array, 2, False)
Range("e1") = pl
End Sub

Είναι η ίδια η Sub testII μόνο που η κλήση της vlookup έγινε με τον παλιό, προ του 1997 τρόπο, που υπάρχει ακόμα για λόγους συμβατότητας. Η μάκρο επιστρέφει στο κελί Ε1 χωρίς κανένα έλεγχο σφάλματος, την σωστή τιμή #Δ/Υ. Δεν ξέρω γιατί συμβαίνει αυτό…ή μάλλον ξέρω, ίσως γιατί ο παλιός είναι αλλιώς.

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