Työskentely Excel VBA -makrojen kanssa
Excelin makrot tallennetaan VBA -koodina, ja joskus haluat muokata tätä koodia suoraan. Tässä opetusohjelmassa käsitellään makrojen tarkastelemista ja muokkaamista, kuvataan joitain makrojen virheenkorjaustekniikoita ja annetaan joitain yleisiä muokkausesimerkkejä.
Näytä makrot
Makroluettelo voidaan näyttää Makrot -valintaikkunassa. Voit tarkastella tätä valintaikkunaa valitsemalla valintanauhan Kehittäjä -välilehden ja napsauttamalla Makrot -painiketta.
Jos useita työkirjoja on auki, kaikkien työkirjojen makrot näkyvät luettelossa. Aktiivisen työkirjan makrot näkyvät pelkästään nimellä, kun taas muiden työkirjojen makrojen edessä on työkirjan nimi ja huutomerkki (eli "Kirja2! MuuMakro").
Avaa muokattava makro
Makron valintaikkunan avulla voit avata makron koodin valitsemalla makron nimen ja napsauttamalla Muokkaa -painiketta. Tämä avaa makron VB -editorissa.
Vaihtoehtoisesti voit avata VB Editorin suoraan napsauttamalla Kehittäjä -välilehden Visual Basic -painiketta tai painamalla ALT+F11 -pikanäppäintä.
Tätä menetelmää käyttämällä sinun on siirryttävä haluamaasi makroon (jota kutsutaan myös "menettelyksi"). Käymme läpi VBA -editorin asettelun:
Yleiskatsaus VB Editorista
VB Editorissa on useita ikkunoita; Tässä opetusohjelmassa käsitellään projektiikkunaa, Ominaisuudet -ikkunaa ja Koodi -ikkunaa.
Projekti -ikkuna
Projekti -ikkuna näyttää jokaisen Excel -tiedoston omana projektinaan, ja kaikki projektin objektit on luokiteltu tyypin mukaan. Tallennetut makrot näkyvät Moduulit -luokassa, yleensä objektissa “Moduuli1”. (Jos projektissasi on useita moduuleja etkä ole varma, missä makro on tallennettu, avaa se yksinkertaisesti edellä mainitusta Makrot -valintaikkunasta.)
Ominaisuudet -ikkuna
Ominaisuudet -ikkuna näyttää objektin ominaisuudet ja niihin liittyvät arvot - esimerkiksi laskemalla laskentataulukko -objektia Projekti -ikkunassa saat näkyviin laskentataulukon ominaisuuksien luettelon. Kiinteistöjen nimet ovat vasemmalla ja kiinteistöjen arvot oikealla.
Kun valitset moduulin Projekti -ikkunassa, sillä on vain yksi ominaisuus, "(Nimi)". Voit muuttaa moduulin nimen kaksoisnapsauttamalla ominaisuuden arvoa, kirjoittamalla uuden nimen ja painamalla Enter. Moduulin nimen muuttaminen nimeää sen uudelleen projektiikkunassa, mikä on hyödyllistä, jos sinulla on paljon moduuleja.
Koodi -ikkunat
Koodi -ikkunat ovat erityisiä tekstieditoria, joissa voit muokata makron VBA -koodia. Jos haluat nähdä Moduuli1: ssä sijaitsevan makron koodin, kaksoisnapsauta 'Moduuli1' -painiketta projektiikkunassa.
Makrojen suorittaminen VB -editorissa
Makroja voidaan ajaa suoraan VB Editorista, josta on hyötyä testaukseen ja virheenkorjaukseen.
Makron ajaminen
- Kaksoisnapsauta projektiikkunassa testattavan makron sisältävää moduulia (avaa sen koodi-ikkuna)
- Aseta Koodi -ikkunassa kohdistin makron koodin kohtaan "Sub" ja "End Sub".
- Klikkaa Juosta -painiketta työkalupalkissa tai paina pikanäppäintä F5
"Askel läpi" makro
Sen sijaan, että suoritat makron kerralla, voit suorittaa makron rivi kerrallaan käyttämällä koodin "läpi" pikanäppäintä. Makro pysähtyy kullakin rivillä, joten voit varmistaa, että jokainen koodirivi tekee mitä odotat Excelissä. Voit myös estää makron jatkamisen milloin tahansa tällä menetelmällä.
Makron "läpivienti":
- Kaksoisnapsauta projektiikkunassa moduulia, joka sisältää testattavan makron (avaa sen koodi-ikkuna)
- Aseta Koodi -ikkunassa kohdistin makron koodin kohtaan
- Aloita vaiheittainen prosessi painamalla pikanäppäintä F8
- Paina F8 -näppäintä toistuvasti edetäksesi koodin suorittamiseen, mikä näkyy Koodi -ikkunan keltaisella korostuksella
- Voit lopettaa makron jatkamisen painamalla Nollaa -painiketta
Miksi muokata VBA -makroja?
Makrotallennin - vaikka se on tehokas - on myös hyvin rajallinen. Joissakin tapauksissa se tuottaa hitaita makroja, tallentaa toimintoja, joita et aikonut toistaa, tai tallentaa asioita, joita et luullut tekeväsi. Makrojen muokkaamisen opettaminen auttaa niitä toimimaan nopeammin, tehokkaammin ja ennakoitavammin.
Näiden ongelmien korjaamisen lisäksi voit myös saavuttaa massiivisen tuottavuuden kasvun, kun hyödynnät makrot. Makrojen ei tarvitse olla vain tehtävien tallenteita - makrot voivat sisältää logiikkaa, joten ne suorittavat tehtäviä vain tietyissä olosuhteissa. Vain muutamassa minuutissa voit koodata silmukoita, jotka toistavat tehtävän satoja tai tuhansia kertoja kerralla!
Alla on joitain käteviä vinkkejä makrokoodin optimointiin sekä työkaluja, joiden avulla makrot toimivat tehokkaammin ja älykkäämmin.
Yleisiä makroeditointiesimerkkejä
Nopeuta makroja
Jos sinulla on makro, joka kestää kauan, voi olla pari syytä, miksi se toimii hitaasti.
Yksi: kun makro suoritetaan, Excel näyttää kaiken reaaliajassa, vaikka se tapahtuisi Katso itse asiassa nopeastinäytetään teos on merkittävä suoritushitti. Yksi tapa saada Excel toimimaan huomattavasti nopeammin on kertomalla sille lopeta näytön päivittäminen:
'Disable Screen Updating Application.ScreenUpdating = False' Ota näytön päivityssovellus käyttöön. ScreenUpdating = True
Rivi "Application.ScreenUpdating = False" tarkoittaa, että et näe makroa toimivan, mutta se toimii paljon nopeammin. Huomaa, että sinun on aina asetettava ScreenUpdating -asetukseksi Tosi makron lopussa, tai muuten Excel ei ehkä toimi odotetulla tavalla myöhemmin!
Toinen tapa nopeuttaa makroja:poista makron automaattinen laskenta käytöstä. Jos olet työskennellyt monimutkaisten laskentataulukoiden kanssa, tiedät, että pienet muutokset voivat käynnistää tuhansia laskelmia, joiden suorittaminen vie aikaa, minkä vuoksi monet ihmiset poistavat automaattisen laskennan käytöstä Excelin asetuksissa. Voit myös vaihtaa tämän VBA -koodilla, joten makro toimii edelleen nopeasti muilla tietokoneilla. Tämä auttaa tilanteissa, joissa kopioit ja liität paljon kaavasoluja tai saat paljon laskuja käynnistymään, kun liität tietoja alueelle:
'Poista automaattinen laskentasovellus käytöstä.Calculation = xlCalculationManual' Ota automaattinen laskentasovellus käyttöön.
Lisää silmukoita ja logiikkaa (jos lausunnot)
Makrotallennin tallentaa kaikki toiminnot koodina VBA -kielellä. VBA on enemmän kuin vain tapa tallentaa toimintoja Excelissä - se on ohjelmointikieli, mikä tarkoittaa, että se voi sisältää koodia, joka tekee päätöksiä suoritettavista toiminnoista, tai toistaa toimintoja, kunnes ehto täyttyy.
Silmukkaa
Oletetaan, että halusit tehdä makron, joka valmisti raportin, ja osana tätä makroa sinun oli lisättävä työkirjaan yhdeksäntoista arkkia, yhteensä kaksikymmentä. Voit tallentaa itsesi napsauttamalla (+) -painiketta uudelleen ja uudelleen tai kirjoittaa silmukan, joka toistaa toiminnon puolestasi, kuten tämä:
Sub ReportPrep () Dim i As Long For i = 1 to 19 Sheets.Lisää seuraava i End Sub
Tässä esimerkissä käytämme a Silmukkaa varten, joka on eräänlainen silmukka, joka iteroi eri kohteiden läpi. Tässä valikoimamme on numerot 1-19, käyttäen muuttujaa nimeltä "i", jotta silmukka voi seurata. Silmukassamme on vain yksi toiminto, joka toistetaan välillä varten jaSeuraava rivejä (taulukon lisääminen), mutta voit lisätä silmukkaan niin paljon koodia kuin haluat tehdä, kuten muotoilla taulukon tai kopioida ja liittää tiedot jokaiseen arkkiin - mitä haluat toistaa.
Jos lausunnot
An Jos lausunto käytetään päättämään, suoritetaanko jokin koodi vai ei, käyttämällä loogista testiä päätöksen tekemiseksi. Tässä on yksinkertainen esimerkki:
Sub ClearIfSmall () If Selection.Value <100 then Selection.Clear End If End Sub
Tämä yksinkertainen esimerkki osoittaa, miten If -lause toimii - testat jonkin ehdon, joka on joko tosi tai epätosi (onko valitun solun arvo alle 100?), ja jos testi palauttaa arvon True, sisäinen koodi suoritetaan.
Tämän koodin puute on, että se testaa vain yhden solun kerrallaan (ja epäonnistuu, jos valitset useita soluja). Tämä olisi hyödyllisempää, jos voisit… selata jokaisen valitun solun läpi ja testata jokaista…
Sub ClearIfSmall () Dim c As Range for each c In Selection.Cells If c.Value <100 then c.Clear End If Next c End Sub
Tässä esimerkissä on hieman erilainen For -silmukka - tämä ei silmukkaa lukualueen läpi, vaan kiertää valinnan kaikkien solujen läpi käyttämällä c -muuttujaa seuratakseen. Silmukan sisällä c -arvoa käytetään määrittämään, onko solu tyhjennettävä vai ei.
Silmukat ja If -lauseet voidaan yhdistää haluamallasi tavalla - voit laittaa silmukat silmukoiden sisään tai yhden jos toisen sisäpuolelle tai käyttää If -painiketta päättääksesi, pitäisikö silmukka suorittaa ollenkaan.
<<>>
Poista vieritystehosteet
Yleinen syy makrokoodin muokkaamiseen on näytön vierityksen poistaminen. Makroa tallennettaessa saatat joutua saavuttamaan muut laskentataulukon alueet vierittämällä, mutta makrojen ei tarvitse vierittää tietojen käyttöön.
Vierittäminen voi sotkea koodisi satoilla tai jopa tuhansilla riveillä tarpeetonta koodia. Tässä on esimerkki koodista, joka tallennetaan, kun napsautat ja vedät vierityspalkkia:
Tällainen koodi on täysin tarpeeton ja se voidaan poistaa vaikuttamatta muihin toimintoihin. Vaikka haluat säilyttää vierityksen, tämä koodi voi silti tiivistyä silmukoksi.
Poista tarpeeton koodi
Tallennetut makrot lisäävät yleensä paljon tarpeetonta koodia, jotka eivät välttämättä vastaa sitä, mitä haluat makron tekevän. Ota esimerkiksi seuraava tallennettu koodi, joka tallentaa fontin nimen muuttamisen solussa:
Vaikka vain fontin nimeä muutettiin, kirjattiin yksitoista (11) fonttimuutosta, kuten fonttikoko, tekstitehosteet jne. Jos makron tarkoitus oli vain muuttaa fontin nimeä (jättäen kaikki muut ominaisuudet yksin), tallennettu makro ei toimi!
Voit muuttaa tämän makron niin, että se muuttaa vain fontin nimen:
Tämä makro ei toimi vain suunnitellulla tavalla, vaan se on myös paljon helpompi lukea.
Poista kohdistinliikkeet
Makroihin tallennetaan myös laskentataulukko- ja soluvalinnat. Tämä on ongelma, koska käyttäjä voi helposti menettää käsityksen siitä, mitä he juuri työskentelivät, jos kohdistin siirtyy toiseen paikkaan makron suorittamisen jälkeen.
Kuten vieritys, sinä saattaa joutua siirtämään kohdistinta ja valitsemaan eri soluja tehtävän suorittamiseksi, mutta makrojen ei tarvitse käyttää kohdistinta tietojen käyttämiseen. Harkitse seuraavaa koodia, joka kopioi alueen ja liittää sen sitten kolmeen muuhun taulukkoon:
Tässä koodissa on muutamia ongelmia:
- Käyttäjä menettää edellisen paikkansa työkirjassa
- Makro ei määritä, mitä taulukkoa kopioimmealkaen - tämä voi olla ongelma, jos makro ajettiin väärälle arkille
Lisäksi koodi on vaikeasti luettava ja tuhlaava. Nämä ongelmat voidaan ratkaista riittävän helposti:
Tässä koodissa on selvää, että kopioimme taulukosta 1, eikä aktiivisen laskentataulukon tai valitun alueen tarvitse muuttua tietojen liittämiseksi. (Yksi merkittävä muutos on "PasteSpecial" -sovelluksen käyttäminen "Liitä" -sovelluksen sijaan - Alueobjekteilla, kuten "Range (" C4 ""), on vain PasteSpecial -komennon käyttöoikeus.)
Aina kun koodi tulee täynnä viittauksia ".Select" - ja "Selection" -valintoihin, se on vihje siitä, että koodin optimointiin ja tehostamiseen on varaa.
