Tässä opetusohjelmassa käsitellään VBA -makrojen ja muiden parhaiden käytäntöjen nopeuttamista.
Asetukset VBA -koodin nopeuttamiseksi
Alla on useita vinkkejä VBA -koodin nopeuttamiseen. Vinkit on järjestetty löyhästi tärkeyden mukaan.
Helpoin tapa parantaa VBA -koodisi nopeutta on poistaa näytön päivitys käytöstä ja poistaa käytöstä automaattiset laskelmat. Nämä asetukset on poistettava käytöstä kaikissa suurissa toimenpiteissä.
Poista näytön päivitys käytöstä
Oletuksena Excel näyttää työkirjan muutokset reaaliajassa, kun VBA-koodi suoritetaan. Tämä aiheuttaa massiivisen hidastumisen käsittelynopeudessa, kun Excel tulkitsee ja näyttää muutokset kullekin koodiriville.
Näytön päivityksen poistaminen käytöstä:
| 1 | Application.ScreenUpdating = Väärä |
Makron lopussa sinun on otettava näytön päivitys uudelleen käyttöön:
| 1 | Application.ScreenUpdating = Totta |
Kun koodisi on käynnissä, sinun on ehkä "päivitettävä" näyttö. "Päivitä" -komentoa ei ole. Sen sijaan sinun on otettava näytön päivitys uudelleen käyttöön ja poistettava se käytöstä.
Aseta laskutoimitukseksi Manuaalinen
Aina kun solun arvoa muutetaan, Excelin on seurattava "laskentapuuta" kaikkien riippuvien solujen laskemiseksi uudelleen. Lisäksi aina, kun kaavaa muutetaan, Excelin on päivitettävä "laskentapuu" kaikkien riippuvien solujen uudelleenlaskennan lisäksi. Työkirjasi koosta riippuen nämä uudelleenlaskennat voivat aiheuttaa makrojesi toimimisen kohtuuttoman hitaasti.
Laskutoiminnon asettaminen manuaaliseksi:
| 1 | Application.Calculation = xlManual |
Koko työkirjan laskeminen manuaalisesti uudelleen:
| 1 | Laskea |
Huomaa, että voit myös laskea vain arkin, alueen tai yksittäisen solun tarvittaessa nopeuden parantamiseksi.
Automaattisten laskelmien palauttaminen (toimenpiteen lopussa):
| 1 | Application.Calculation = xlAutomatic |
Tärkeä! Tämä on Excel -asetus. Jos et aseta laskutoimituksia uudelleen automaattiseksi, työkirjaa ei lasketa uudelleen ennen kuin kerrot sen.
Näet suurimmat parannukset yllä olevista asetuksista, mutta on olemassa useita muita asetuksia, jotka voivat vaikuttaa:
Poista tapahtumat käytöstä
Tapahtumat ovat ”laukaisijoita”, jotka aiheuttavat erityistä tapahtuman menettelyt juosta. Esimerkkejä ovat: kun jokin laskentataulukon solu muuttuu, kun laskentataulukko on aktivoitu, kun työkirja avataan, ennen kuin työkirja tallennetaan jne.
Tapahtumien poistaminen käytöstä voi aiheuttaa pieniä nopeusparannuksia, kun makroja suoritetaan, mutta nopeuden parannus voi olla paljon suurempi, jos työkirjasi käyttää tapahtumia. Ja joissakin tapauksissa tapahtumien poistaminen käytöstä on välttämätöntä, jotta vältetään loputtomien silmukoiden luominen.
Tapahtumien poistaminen käytöstä:
| 1 | Application.EnableEvents = Väärä |
Tapahtumien ottaminen uudelleen käyttöön:
| 1 | Application.EnableEvents = Tosi |
Poista PageBreaks käytöstä
PageBreaksin poistaminen käytöstä voi auttaa tietyissä tilanteissa:
- Olet aiemmin asettanut PageSetup -ominaisuuden asianomaiselle laskentataulukolle ja VBA -menettely muuttaa monien rivien tai sarakkeiden ominaisuuksia
- TAI VBA -menettelysi pakottaa Excelin laskemaan sivunvaihdot (näyttämällä tulostuksen esikatselun tai muokkaamalla PageSetupin ominaisuuksia).
PageBreaksin poistaminen käytöstä:
| 1 | ActiveSheet.DisplayPageBreaks = Väärä |
PageBreaksin ottaminen uudelleen käyttöön:
| 1 | ActiveSheet.DisplayPageBreaks = Totta |
Parhaat käytännöt VBA -nopeuden parantamiseksi
Vältä aktivointia ja valintaa
Kun tallennat makroa, näet monia aktivointi- ja valintamenetelmiä:
| 12345678 | Sub Slow_Example ()Sheets ("Sheet2"). ValitseAlue ("D9"). ValitseActiveCell.FormulaR1C1 = "esimerkki"Alue ("D12"). ValitseActiveCell.FormulaR1C1 = "demo"Alue ("D13"). ValitseEnd Sub |
Objektien aktivointi ja valitseminen on yleensä tarpeetonta, ne lisäävät sekavuutta koodiin ja ovat erittäin aikaa vieviä. Sinun tulisi välttää näitä menetelmiä, jos mahdollista.
Parannettu esimerkki:
| 1234 | Sub Fast_Example ()Sheets ("Sheet2"). Alue ("D9"). FormulaR1C1 = "example"Sheets ("Sheet2"). Alue ("D12"). FormulaR1C1 = "demo"End Sub |
Vältä kopiointia ja liittämistä
Kopiointi vaatii huomattavaa muistia. Valitettavasti et voi käskeä VBA: ta tyhjentämään sisäistä muistia. Sen sijaan Excel tyhjentää sisäisen muistin (näennäisesti) tietyin väliajoin. Joten jos teet monia kopiointi- ja liittämistoimia, saatat vaurioittaa liikaa muistia, mikä voi hidastaa koodiasi huomattavasti tai jopa kaataa Excelin.
Kopioinnin ja liittämisen sijaan harkitse solujen arvo -ominaisuuksien asettamista.
| 123456789 | Sub CopyPaste ()'HitaamminAlue ("a1: a1000"). Kopioi alue ("b1: b1000")'NopeamminAlue ("b1: b1000"). Arvo = Alue ("a1: a1000"). ArvoEnd Sub |
Käytä For For silmukoita For For Loopsin sijaan
Kun silmukat kierretään objektien läpi, jokaiselle silmukka on nopeampi kuin For Loop. Esimerkki:
Tämä silmukalle:
| 123456 | Alisilmukka1 ()himmeä kuin alueI = 1 - 100Solut (i, 1) Arvo = 1Seuraavaksi minäEnd Sub |
| 123456 | Alisilmukka2 ()Himmennä solu alueenaJokaiselle alueen solulle ("a1: a100")solu. Arvo = 1Seuraava soluEnd Sub |
Ilmoita muuttujat / käytä vaihtoehtoa Selkeä
VBA ei edellytä muuttujien ilmoittamista, ellet lisää vaihtoehtoa Explicit moduulin yläosaan:| 1 | Vaihtoehto Selkeä |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Käytä - Lopeta lausunnot
Jos viittaat samoihin objekteihin useita kertoja (esim. Alueet, laskentataulukot, työkirjat), harkitse With Statementin käyttöä. Se on nopeampi käsitellä, helpottaa koodin lukemista ja yksinkertaistaa koodia.Esimerkki lausunnosta:| 12345678 | Sub Faster_Example ()Sheets ("Sheet2").Range ("D9"). KaavaR1C1 = "esimerkki".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = Totta.Range ("D12"). Font.Bold = tosiLopetaEnd Sub |
| 123456 | Sub Slow_Example ()Sheets ("Sheet2"). Alue ("D9"). FormulaR1C1 = "example"Sheets ("Sheet2"). Alue ("D12"). FormulaR1C1 = "demo"Sheets ("Sheet2"). Alue ("D9"). Font.Bold = TosiSheets ("Sheet2"). Alue ("D12"). Font.Bold = TosiEnd Sub |
Vinkkejä edistyneisiin parhaisiin käytäntöihin
Suojaa UserInterfaceOnly
On hyvä käytäntö suojata laskentataulukoita suojaamattomien solujen muokkaamiselta, jotta loppukäyttäjä (tai sinä!) Ei vahingoittaisi työkirjaa vahingossa. Tämä kuitenkin myös suojaa laskentataulukoita, ettei VBA voi tehdä muutoksia. Sinun on siis poistettava laskentataulukot ja suojattava ne uudelleen, mikä on erittäin aikaa vievää, kun se tehdään monilla arkeilla.
| 12345 | Osa UnProtectSheet ()Sheets (“sheet1”). Poista salasanan suojaus ”'Muokkaa taulukkoa 1Sheets ("sheet1"). Suojaa "salasana"End Sub |
Sen sijaan voit suojata arkit asetuksella UserInterfaceOnly: = True. Tämä mahdollistaa VBA: n tehdä muutoksia arkkeihin ja silti suojata ne käyttäjältä.
| 1 | Sheets (“sheet1”). Suojaa salasana: = "password", UserInterFaceOnly: = True |
Tärkeä! UserInterFaceOnly palautuu arvoon False joka kerta, kun työkirja avautuu. Jotta voit käyttää tätä mahtavaa ominaisuutta, sinun on asetettava asetus Workbook_Open- tai Auto_Open -tapahtumien avulla aina, kun työkirja avataan.
Aseta tämä koodi Tämä työkirja -moduuliin:
| 123456 | Yksityinen alityökirja_Avaa ()Dim ws laskentataulukkonaJokaiselle viikolle laskentataulukoissaws.Protect Password: = "password", UserInterFaceOnly: = TosiSeuraava wsEnd Sub |
tai tämä koodi missä tahansa tavallisessa moduulissa:
| 123456 | Yksityinen osa Auto_Open ()Dim ws laskentataulukkonaJokaiselle viikolle laskentataulukoissaws.Protect Password: = "password", UserInterFaceOnly: = TosiSeuraava wsEnd Sub |
Muokkaa suuria alueita taulukkojen avulla
Suurten solualueiden manipulointi voi olla hyvin aikaa vievää (esim. 100 000+). Sen sijaan, että kiertäisit solualueita, manipuloisit kutakin solua, voit ladata solut taulukkoon, käsitellä jokaisen taulukon kohteen ja tulostaa taulukon takaisin alkuperäisiin soluihinsa. Solujen lataaminen matriiseiksi manipulointia varten voi olla paljon nopeampaa.
| 1234567891011121314151617181920212223242526272829303132 | Alasilmukka -alue ()Himmennä solu alueenaDim tKäynnistä kaksinkertaisenatStart = AjastinJokaiselle alueen solulle ("A1: A100000")soluarvo = solu.arvo * 100Seuraava soluDebug.Print (ajastin - tStart) ja "sekunnit"End SubSub LoopArray ()Dim arr VaihtoehtonaDim -kohde VaihtoehtonaDim tKäynnistä kaksinkertaisenatStart = Ajastinarr = Alue ("A1: A100000"). ArvoJokaiselle tuotteelle Saapitem = item * 100Seuraava kohdeAlue ("A1: A100000"). Arvo = saapDebug.Print (ajastin - tStart) ja "sekuntia"End Sub |
