VBA for Excel 2016 on nopea, mutta se ei aina ole tarpeeksi nopea. (Tietokoneohjelmat eivät ole koskaan tarpeeksi nopeita.) Jatka lukemista löytääksesi ohjelmointiesimerkkejä, joiden avulla voit nopeuttaa makrojasi.
Näytön päivityksen poistaminen käytöstä
Kun suoritat makroa, voit rentoutua ja katsella kaikkia makrossa tapahtuvia näytön toimintoja. Vaikka tämä voi olla opettavaista, makron toimimisen jälkeen se on usein ärsyttävää ja voi hidastaa makrosi suorituskykyä huomattavasti. Onneksi voit poistaa käytöstä näytön päivityksen, joka normaalisti tapahtuu makroa suoritettaessa. Voit poistaa näytön päivitykset käytöstä seuraavalla lauseella:
Application.ScreenUpdating = False
Jos haluat käyttäjän näkevän, mitä makron aikana tapahtuu, ota näytön päivitys takaisin käyttöön seuraavalla käskyllä:
Application.ScreenUpdating = Totta
Osoittaaksesi nopeuden eron, suorita tämä yksinkertainen makro, joka täyttää alueen numeroilla:
Sub FillRange()
Dim r as Long, c As Long
Himmeä numero yhtä pitkä
Numero = 0
Jos r = 1 - 50
Jos c = 1 - 50
Numero = numero + 1
Solut(r, c).Valitse
Solut(r, c). Arvo = numero
Seuraava c
Seuraava r
Lopeta ala
Näet jokaisen solun valitun ja arvon syötettävän soluihin. Lisää nyt seuraava lause toimenpiteen alkuun ja suorita se uudelleen:
Application.ScreenUpdating = False
Alue täyttyy paljon nopeammin, etkä näe tulosta ennen kuin makro on suoritettu ja näytön päivitys on (automaattisesti) asetettu arvoon True.
Kun suoritat koodin virheenkorjausta, ohjelman suorittaminen päättyy joskus jonnekin puoliväliin ilman, että olet laittanut näytön päivityksen takaisin päälle. Tämä aiheuttaa joskus sen, että Excelin sovellusikkuna ei vastaa ollenkaan. Pääsy tästä jäätyneestä tilasta on yksinkertainen: Palaa VBE:hen ja suorita seuraava käsky Välittömässä ikkunassa:
Application.ScreenUpdating = Totta
Automaattisen laskennan poistaminen käytöstä
Jos sinulla on laskentataulukko, jossa on monia monimutkaisia kaavoja, saatat huomata, että voit nopeuttaa asioita huomattavasti asettamalla laskentatilan manuaaliseksi makron suorittamisen aikana. Kun makro on valmis, aseta laskentatila takaisin automaattiseen.
Seuraava lause asettaa Excel-laskentatilan manuaaliseksi:
Application.Calculation = xlCalculationManual
Suorita seuraava käsky asettaaksesi laskentatilan automaattiseksi:
Application.Calculation = xlCalculationAutomatic
Jos koodisi käyttää soluja kaavatuloksilla, laskennan poistaminen käytöstä tarkoittaa, että soluja ei lasketa uudelleen, ellet nimenomaisesti kehota Exceliä tekemään niin!
Poistaa nämä ärsyttävät hälytysviestit
Kuten tiedät, makro voi suorittaa automaattisesti useita toimintoja. Monissa tapauksissa voit käynnistää makron ja mennä sitten viettämään aikaa taukohuoneessa, kun Excel tekee tehtävänsä. Jotkut Excel-toiminnot näyttävät kuitenkin viestejä, jotka edellyttävät ihmisen vastausta. Tämäntyyppiset viestit tarkoittavat, että et voi jättää Exceliä valvomatta sen suorittaessa makroa – ellet tiedä salaista temppua.
Voit ohjeistaa Exceliä olemaan näyttämättä tämäntyyppisiä hälytyksiä makron suorittamisen aikana.
Salainen temppu näiden hälytysviestien välttämiseksi on seuraavan VBA-lauseen lisääminen makroon:
Application.DisplayAlerts = False
Excel suorittaa oletustoiminnon tämän tyyppisille viesteille. Jos arkki poistetaan, oletustoiminto on Poista. Jos et ole varma, mikä oletustoiminto on, suorita testi nähdäksesi mitä tapahtuu.
Kun toimenpide päättyy, Excel palauttaa automaattisesti DisplayAlerts-ominaisuuden arvoon True. Jos sinun on otettava hälytykset takaisin käyttöön ennen toimenpiteen päättymistä, käytä tätä lausetta:
Application.DisplayAlerts = Tosi
Yksinkertaistaa objektiviittauksia
Kuten luultavasti jo tiedät, viittaukset esineisiin voivat olla hyvin pitkiä. Esimerkiksi täysin tarkennettu viittaus Range-objektiin voi näyttää tältä:
Työkirjat("OmaKirja.xlsx"). Työarkit("Sheet1") _
.Range("korko")
Jos makrosi käyttää usein tätä aluetta, voit luoda objektimuuttujan käyttämällä Set-komentoa. Esimerkiksi seuraava käsky määrittää tämän Range-objektin objektimuuttujalle nimeltä Rate:
Aseta määrä = työkirjat ("OmaKirja.xlsx") _
.Työtaulukot("Taulukko1"). Alue("Korko")
Kun olet määrittänyt tämän objektimuuttujan, voit käyttää muuttujaa Rate pitkän viittauksen sijaan. Voit esimerkiksi muuttaa Korkokorko-nimisen solun arvoa:
Hinta.Arvo = 0,085
Tämä on paljon helpompi kirjoittaa kuin seuraava lause:
Työkirjat ("OmaKirja.xlsx"). Työarkit ("Sheet1"). _
Väli("Korko") = 0,085
Koodauksen yksinkertaistamisen lisäksi objektimuuttujien käyttö nopeuttaa makrojasi huomattavasti.
Muuttujatyyppien ilmoittaminen
Sinun ei yleensä tarvitse huolehtia muuttujalle määrittämiesi tietojen tyypistä. Excel hoitaa kaikki yksityiskohdat puolestasi kulissien takana. Jos sinulla on esimerkiksi muuttuja nimeltä MyVar, voit määrittää tälle muuttujalle minkä tahansa tyypin numeron. Voit jopa määrittää sille tekstimerkkijonon myöhemmin toimenpiteen aikana.
Jos haluat, että toimenpiteesi suoritetaan mahdollisimman nopeasti, kerro Excelille, minkä tyyppistä dataa kullekin muuttujasi kohdennetaan. Tätä kutsutaan julistamisesta muuttujan tyyppi.
Yleensä kannattaa käyttää tietotyyppiä, joka vaatii pienimmän määrän tavuja, mutta silti pystyy käsittelemään kaikki sille osoitetut tiedot. Kun VBA toimii tietojen kanssa, suoritusnopeus riippuu VBA:n käytettävissä olevien tavujen määrästä. Toisin sanoen mitä vähemmän tavuja dataa käytetään, sitä nopeammin VBA voi käyttää ja käsitellä tietoja. Poikkeuksena tähän on Integer-tietotyyppi. Jos nopeus on kriittinen, käytä sen sijaan Long-tietotyyppiä.
Jos käytät objektimuuttujaa, voit määrittää muuttujan tietyksi objektityypiksi. Tässä on esimerkki:
Himmennysnopeus vaihteluvälinä
Aseta määrä = työkirjat ("OmaKirja.xlsx") _
.Työtaulukot("Taulukko1"). Alue("Korko")
With-End With -rakenteen käyttäminen
Pitääkö objektille asettaa useita ominaisuuksia? Koodisi toimii nopeammin, jos käytät With-End With -rakennetta. Lisäetuna on, että koodisi voi olla helpompi lukea.
Seuraavassa koodissa ei käytetä With-End With:tä:
Selection.Horizontal Alignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = Tosi
Valinta. Suunta = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False
Tässä on sama koodi, kirjoitettu uudelleen käyttämään With-End With:
Valinnan kanssa
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = Tosi
.Suunta = 0
.ShrinkToFit = False
.MergeCells = False
Lopeta kanssa
Kun käytät With-End With -toimintoa, varmista, että jokainen lause alkaa pisteellä.