Ένα ξεχασμένο όριο

Χρήση της συνάρτησης φύλλου transpose, στην vba.

Στο προηγούμενο σημείωμα είχαμε ασχοληθεί με τις συναρτήσεις φύλλου που μπορούν να κληθούν μέσα από την vba. Η συνάρτηση TRANSPOSE είναι μία από αυτές, αλλά η χρήση-της στη vba είναι αρκετά διαφορετική. Στο φύλλο, η transpose, δέχεται σαν όρισμα μία (και μόνο μία περιοχή) κελιών, και την αποδίδει αλλάζοντας την κατεύθυνση της . Μια στήλη θα την μετατρέψει σε γραμμή, μια γραμμή σε στήλη, και μια ορθογώνια περιοχή 3 γραμμών και 5 στηλών θα τη μετατρέψει σε περιοχή 3 στηλών και 5 γραμμών. Δέστε εδώ την περιγραφή της συνάρτησης στη βοήθεια της Microsoft, και σε αυτό εδώ το σημείωμα του ιστολογίου, θα βρείτε μια πιο αναλυτική περιγραφή για τη συνάρτηση. Όπως είναι φυσικό η transpose έχει έναν περιορισμό στις διαστάσεις της περιοχής που θα μετασχηματίσει. Αν μετατρέψετε μια ολόκληρη γραμμή σε στήλη, η γραμμή θα περιέχει ακριβώς 16384 κελιά, γιατί αυτός είναι ο αριθμός κελιών μιας γραμμής. Αν θέλετε να μετατρέψετε μια στήλη σε γραμμή, από τη στήλη που περιέχει περισσότερα από ένα εκατομμύριο κελιά, θα πρέπει να πάρετε ένα τμήμα με το πολύ 16384 κελιά, ώστε να μπορεί να την υποδεχτεί η γραμμή. Τα όρια αυτά ισχύουν για το excel 2007, 2010 και 2013. Για τις παλιότερες εκδόσεις, η μεγαλύτερη διάσταση της περιοχής που μπορούσε να μετασχηματίσει η TRANSPOSE, ήταν 256 κελιά γιατί τόσες στήλες περιείχε ένα φύλλο.

excel version

Πλήθος γραμμών και στηλών στις διάφορες εκδόσεις του excel

Τα παραπάνω ίσως ακούγονται σαν αυτονόητα, αλλά θα δείξουν τη διαφορά που έχει η συνάρτηση όταν καλείται στο περιβάλλον της vba. Ο παρακάτω λίγος κώδικας δημιουργεί ένα πίνακα (array) με 4 στοιχεία, τον myArray, και τα στοιχεία του ένα-ένα τα εξάγει σε μία γραμμή (στα κελιά Α1 έως D1)

Sub OutputArray_I()
Dim myArray As Variant
myArray = Array("a", "b", "c", "d")
Range("A1:D1") = myArray
End Sub

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

Range("A1:A4") = myArray

δεν θα τα έχετε, διότι οι vba πίνακες επιστρέφουν πάντα ένα διάνυσμα-γραμμή και θέλουν γραμμή για να εναποθέσουν τα στοιχεία τους. Έτσι αναλαμβάνει η transpose, ή πιο καλά η Application.WorksheetFunction.Transpose για να αλλάξει τη διάταξη του πίνακα:

Sub OutputArray_II()
Dim myArray As Variant
myArray = Array("a", "b", "c", "d")
Range("A1:A4") = Application.WorksheetFunction.Transpose(myArray)
End Sub

verticalΟ παραπάνω κώδικας θα επιστρέψει σωστά στα κελιά Α1 ως Α4 τα στοιχεία του πίνακα.

 

 

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

Sub testA()
Dim i As Long
Dim Var(1 To 60000) As Variant
Range("A:A").Clear
For i = 1 To UBound(Var)
Var(i) = Fix(100 * Rnd)
Next i
Range("A1:A" & UBound(Var)) = Application.WorksheetFunction.Transpose(Var)
End Sub

Η μάκρο αυτή δείχνει ότι: όταν η συνάρτηση καλείται από την vba, δεν έχει τον περιορισμό του φύλλου στο πλήθος στοιχείων που μπορεί να χειριστεί. Λογικό είναι να σκεφτούμε ότι το φυσικό όριο της Application.Transpose, αφού εναποθέτουμε τα στοιχεία του πίνακα σε στήλη, είναι το πλήθος των κελιών της στήλης, δηλαδή 1.048.576 στοιχεία. Δεν είναι όμως έτσι. Αν ξανατρέξετε την παραπάνω μάκρο για ένα πίνακα με 70.000 στοιχεία θα πάρετε ένα μήνυμα λάθους, και η εκτέλεσή της θα διακοπεί:

vbacode

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

.

TheGreatEscape

Όμως, τα όρια υπάρχουν για να τα ξεπερνάμε και τα εμπόδια για να τα παρακάμπτουμε. Η παρακάτω ρουτίνα δημιουργεί ένα πίνακα με ένα εκατομμύριο στοιχεία και κατόπιν ένας βρόγχος for..next διατρέχει όλα τα στοιχεία του πίνακα από το πρώτο έως το τελευταίο (UBound), και ένα- ένα τα τοποθετεί στα κελιά της στήλης Α.

Sub testB()
Dim i As Long
Dim Var(1 To 1000000) As Variant
Range("A:A").Clear
For i = 1 To UBound(Var)
Var(i) = Fix(100 * Rnd)
Next i
For i = 1 To UBound(Var)
Cells(i, "A") = Var(i)
Next i
End Sub 

Όμως η διαδικασία αυτή είναι χρονοβόρα. Σε 3-4 υπολογιστές που την δοκίμασα, χρειάστηκε από 45 έως 65 δευτερόλεπτα, χρόνος πολύ μεγάλος για κάτι τόσο απλό. Αν θέλετε να κάνετε δικές σας δοκιμές, για διάφορο πλήθος στοιχείων, και να χρονομετρήσετε τα αποτελέσματα, χρησιμοποιήστε τον παρακάτω κώδικα. Σας ρωτά για το πλήθος των στοιχείων που θέλετε να έχει ο πίνακας και κατόπιν εναποθέτει τα στοιχεία στη στήλη Α. Όταν ολοκληρώσει, σας πληροφορεί πόσο χρόνο χρειάστηκε. Τρέξτε τον κώδικα μέσα από ένα νέο κενό φύλλο.

Sub testC()
Dim StartTime, PauseTime
Dim i As Long
Dim Var() As Variant
Dim box As Long
Range("A:A").Clear
box = Application.InputBox(Prompt:= "Πλήθος αριθμών", Type:=1)
If box > 0 Then
If box > ActiveSheet.Rows.Count Then box = ActiveSheet.Rows.Count
StartTime = Timer
ReDim Var(1 To box)
For i = 1 To box
Var(i) = Fix(100 * Rnd)
Next i
For i = 1 To box
Cells(i, "A") = Var(i)
Next i
PauseTime = Timer
MsgBox PauseTime - StartTime
End If
End Sub

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

Sub testD()
Dim i As Long
Dim Var(1 To 1000000, 0) As Variant
Range("A:A").Clear
For i = 1 To UBound(Var)
Var(i, 0) = Fix(100 * Rnd)
Next i
Range("A1:A" & UBound(Var)) = Var
End Sub

Στη δήλωση του πίνακα:

Dim Var(1 To 1000000, 0) As Variant

και στη σχηματισμό του:

Var(i, 0) = Fix(100 * Rnd)

έγινε χρήση μίας δεύτερης μηδενικής ψευτο-διάστασης και αυτό ήταν αρκετό ώστε η vba να θεωρήσει τον πίνακα δισδιάστατο και να τον επιστρέψει κάθετα. Τρέξτε τη μάκρο αυτή, και θα δείτε ότι θα χρειαστούν 1-2 δεύτερα για να γεμίσει η στήλη Α με αριθμούς.

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

One Response to Ένα ξεχασμένο όριο

  1. Ο/Η Γιώργος λέει:

    εγώ προς το παρόν μένω στον «Πεταλούδα» και τη μηχανή του. Αχ βρε Γιάννη…

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