10 tapaa nopeuttaa makrojasi

Vältä liiallista viittausta

Kun objektin menetelmää tai ominaisuutta kutsutaan, sen on käytävä läpi OLE-komponentin IDispatch-liittymä. Kutsut näihin OLE-komponentteihin vievät aikaa, joten OLE-komponenttien viittausten määrän vähentäminen voi parantaa makrokoodin nopeutta.

Objektin ominaisuuksien tai menetelmien kutsumiseen  käytetään yleensä Object.Methodin esitystapaa  , eli "." symbolia käytetään ominaisuuksien ja menetelmien kutsumiseen.

Siksi menetelmä- tai ominaisuuskutsujen lukumäärä voidaan arvioida symbolien "." lukumäärän mukaan. Mitä vähemmän "." symboli, sitä nopeammin koodi suoritetaan.

Esimerkiksi seuraava lause sisältää 3 symbolia ".".

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

Seuraavassa lauseessa on vain yksi symboli ".".

Activewindow.Top = 100

Tässä on joitain temppuja symbolien "" määrän vähentämiseksi. juoksemaan nopeammin.

Ensinnäkin, kun sinun on viitattava samaan objektiin toistuvasti, voit asettaa objektin muuttujaksi puheluiden määrän vähentämiseksi. Esimerkiksi seuraava koodi vaatii kaksi puhelua linjaa kohden.

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

Koska  Sheets("Sheet1")-  objektiin on viitattava toistuvasti, se voidaan asettaa ensin muuttujaksi  sht  , jolloin kutakin koodia tarvitsee kutsua vain kerran.

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

Toiseksi, jos et halua ilmoittaa väliaikaista muuttujaa sht, voit käyttää myös   aiemmin mainittua With -lausetta. Kuten seuraavassa esimerkissä näkyy:

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

Kolmanneksi,  kun silmukoita on paljon, yritä pitää ominaisuudet ja menetelmät silmukan ulkopuolella.  Kun käytät uudelleen saman objektin ominaisuusarvoa silmukassa, voit ensin määrittää ominaisuuden arvon määritetylle muuttujalle silmukan ulkopuolella ja sitten käyttää silmukassa olevaa muuttujaa, mikä voi saavuttaa nopeamman nopeuden. Kuten seuraavassa esimerkissä näkyy:

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

Jokainen silmukka tässä esimerkissä saa solun Solut(1,2) Arvo-ominaisuuden. Jos määrität Cells(1.2):n Arvo-ominaisuuden muuttujalle ennen silmukan alkamista, saat nopeamman ajon. Kuten seuraavassa esimerkissä näkyy:

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

Yllä oleva koodi kutsuu  ThisWorkbook.Sheets("Sheet1")  aina silmukan yhteydessä. Voit tehdä tämän nopeammin käyttämällä  With  -käskyä siirtääksesi kutsun  ThisWorkbook.Sheets("Sheet1")  silmukan ulkopuolelle. Kuten seuraavassa esimerkissä näkyy:

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

Vältä muunnelmatyyppien käyttöä

Aloittelijat käyttävät yleensä mieluummin Variant-tyyppisiä muuttujia, joiden etuna on se, että ne ovat vähemmän monimutkaisia, koska minkä tahansa tyyppistä dataa voidaan käyttää ilman muistin ylivuoto-ongelmaa, jos tiedot ovat liian suuria Integer- tai Long-tietotyypeille. Varienmt-tyyppiset tiedot vaativat kuitenkin enemmän ylimääräistä muistitilaa kuin muut määritetyt tyypit (2 tavua kokonaislukutiedoille, 4 tavua pitkille tiedoille ja 16 tavua Variant-tiedoille), VBA vaatii enemmän aikaa Variant-tyyppisten tietojen käsittelyyn kuin muut määritetyt tyypit. tiedoista. Kuten seuraava esimerkki osoittaa.

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

Yllä olevassa koodissa rivit 8-13 tekevät 1 miljoonan muunnelmamuuttujien yhteen-, vähennys-, kerto- ja jakooperaatiota ja rivit 17-22 tekevät 1 miljoonan kokonaislukumuuttujien yhteen-, vähennys-, kerto- ja jakooperaation. Tietokoneellani Variant-muuttujan toiminta kesti noin  0,09375  sekuntia, kun taas Kokonaisluku-muuttujan toiminta noin  0,03125  sekuntia. Tulokset voivat vaihdella tietokoneittain, mutta  Variant-muuttujat ovat huomattavasti hitaampia kuin kokonaislukumuuttujat .

Tästä syystä  on suositeltavaa välttää Variant-muuttujien käyttöä, kun voit käyttää nimenomaisesti määritettyä tietotyyppiä .