10 τρόποι για να επιταχύνετε τις μακροεντολές σας

Αποφύγετε την υπερβολική αναφορά

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

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

Επομένως, ο αριθμός των κλήσεων μεθόδου ή ιδιοτήτων μπορεί να κριθεί σύμφωνα με τον αριθμό των συμβόλων ".". Όσο λιγότερο το "." σύμβολο, τόσο πιο γρήγορα εκτελείται ο κωδικός.

Για παράδειγμα, η ακόλουθη δήλωση περιλαμβάνει 3 σύμβολα ".".

ThisWorkbook.Sheet1.Range("A1").Value = 100

Η ακόλουθη δήλωση έχει μόνο ένα σύμβολο ".".

Activewindow.Top = 100

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

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

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

Επειδή το  αντικείμενο Sheets("Sheet1")  πρέπει να αναφέρεται επανειλημμένα, μπορεί να οριστεί πρώτα σε μια μεταβλητή  sht  , έτσι ώστε κάθε κώδικας να χρειάζεται να καλείται μόνο μία φορά.

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

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

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

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

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

Κάθε βρόχος σε αυτό το παράδειγμα λαμβάνει την ιδιότητα Value του κελιού Cells(1,2). Εάν αντιστοιχίσετε την ιδιότητα Value του Cells(1.2) σε μια μεταβλητή πριν από την έναρξη του βρόχου, θα έχετε ταχύτερη εκτέλεση. Όπως φαίνεται στο παρακάτω παράδειγμα:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

Ο παραπάνω κωδικός καλεί  ThisWorkbook.Sheets("Sheet1")  κάθε φορά που κάνει βρόχο. Μπορείτε να το κάνετε πιο γρήγορα χρησιμοποιώντας τη  δήλωση With  για να μετακινήσετε την κλήση στο  ThisWorkbook.Sheets("Sheet1")  εκτός του βρόχου. Όπως φαίνεται στο παρακάτω παράδειγμα:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

Αποφύγετε τη χρήση τύπων παραλλαγών

Οι αρχάριοι συνήθως προτιμούν να χρησιμοποιούν μεταβλητές τύπου Variant, οι οποίες έχουν το πλεονέκτημα ότι είναι λιγότερο περίπλοκες, επειδή οποιοσδήποτε τύπος δεδομένων μπορεί να χρησιμοποιηθεί χωρίς πρόβλημα υπερχείλισης μνήμης, εάν τα δεδομένα είναι πολύ μεγάλα για τους τύπους δεδομένων Integer ή Long. Ωστόσο, τα δεδομένα τύπου Varienmt απαιτούν περισσότερο χώρο μνήμης από τους άλλους καθορισμένους τύπους (2 byte για ακέραια δεδομένα, 4 byte για δεδομένα μεγάλου μήκους και 16 byte για δεδομένα παραλλαγής), η VBA απαιτεί περισσότερο χρόνο για την επεξεργασία δεδομένων τύπου παραλλαγής από άλλους καθορισμένους τύπους των δεδομένων. Όπως δείχνει το παρακάτω παράδειγμα.

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

Στον παραπάνω κώδικα, οι γραμμές 8 έως 13 κάνουν 1 εκατομμύριο πράξεις πρόσθεσης, αφαίρεσης, πολλαπλασιασμού και διαίρεσης μεταβλητών Variant και οι γραμμές 17 έως 22 κάνουν 1 εκατομμύριο πράξεις πρόσθεσης, αφαίρεσης, πολλαπλασιασμού και διαίρεσης μεταβλητών ακέραιου αριθμού. Στον υπολογιστή μου, η λειτουργία της μεταβλητής Variant χρειάστηκε περίπου  0,09375  δευτερόλεπτα, ενώ η λειτουργία της μεταβλητής Integer χρειάστηκε περίπου  0,03125  δευτερόλεπτα. Τα αποτελέσματα μπορεί να διαφέρουν από υπολογιστή σε υπολογιστή, αλλά  οι μεταβλητές Variant είναι σημαντικά πιο αργές από τις μεταβλητές Integer .

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