Nämä ovat show-muistiinpanoni esiintymisestäni Call for Help -palvelun jaksossa 33 TechTV Kanadassa.
VBA-makrotallennin
Jokainen vuodesta 1995 myyty Excel-kopio sisältää uskomattoman tehokkaan Visual Basic for Applications (VBA) -solun, joka piiloutuu solujen taakse. Jos sinulla on Excel, sinulla on VBA.
VBA antaa sinun automatisoida kaiken, mitä voit tehdä Excelissä, sekä tehdä enemmän asioita, jotka eivät yleensä ole mahdollisia Excelissä.
Meille onneksi Microsoft sisälsi Exceliin makrotallentimen. Näin voit kirjoittaa VBA-koodin olematta ohjelmoija. Valitettavasti makrotallennin on puutteellinen. Se ei tuota koodia, joka toimii joka kerta. Makrotallennin vie sinut lähelle, mutta monissa tapauksissa sinun on korjattava koodi hieman, jotta se toimii luotettavasti. Muissa tapauksissa sinun on tiedettävä, milloin suhteellista tallennusta käytetään makron toimimiseen.
Tracy Syrstad ja minä kirjoitimme VBA: n ja makrot Microsoft Excel 2016: n auttamaan sinua ymmärtämään tallennetun koodin rajoituksia ja opettamaan kuinka korjata nauhoitettu koodi mihin tahansa, joka toimii aina.
Esityksessä osoitin makron tallennusprosessin, joka toimii täydellisesti, jos osaat käyttää Suhteellinen viite -painiketta. Joku oli antanut minulle Excel-laskentataulukon, jossa oli satoja nimiä ja osoitteita. He halusivat postitustarroja. Jos olet joskus käyttänyt Microsoft Word -postin yhdistämistoimintoa, tiedät, että tarvitset kukin kenttä laskentataulukon uudessa sarakkeessa. Sen sijaan tässä tietyssä laskentataulukossa tiedot menivät sarakkeen A alapuolelle.
Yhden tarran korjaaminen on melko tylsää.
- Aloita solun osoittimella sarakkeen A nimessä.
- Napsauta alanuolta siirtyäksesi osoitteeseen
- Ctrl + x leikkaa
- Ylänuoli, oikea nuoli siirtyäksesi sarakkeeseen B nimen vieressä
- Ctrl + v liittääksesi
- Alanuoli kahdesti, vasen nuoli kerran siirtyäksesi kaupunkiin
- Ctrl + x leikkaa
- Ylänuoli kahdesti, oikea nuoli kolmesti
- Ctrl + v liittääksesi
- Vasen nuoli kahdesti, alanuoli kerran siirtyäksesi nyt tyhjälle riville.
- Pidä vaihtonäppäintä painettuna, kun painat alanuolta kahdesti
- Alt + edr poistaa tyhjät rivit
- Ylänuoli ja alanuoli valitaksesi vain nimen uudelleen.
Tässä on animaatio, joka näyttää nämä vaiheet:
Excelin asettaminen makrojen sallimista varten
Vaikka jokainen Excel-kopio sisältää VBA: n, Microsoft poistaa oletusarvoisesti makrojen suorittamisen käytöstä. Makrojen suorittaminen edellyttää kertaluonteista säätöä. Avaa Excel. Valitse valikosta Työkalut> Makro> Suojaus. Jos makroturvataso on tällä hetkellä asetettu korkealle, vaihda se keskitasoksi.
Valmistautuminen makron tallentamiseen
Ajattele tehtävän saavuttamiseksi tarvittavia vaiheita. Haluat varmistaa, että aloitat solun osoittimen nimen kanssa ja lopetat sen seuraavalla nimellä. Tämän avulla voit suorittaa makron toistuvasti useita kertoja. Kun vaiheet ovat valmiina, käynnistä makrotallennin. Valitse valikosta Työkalut> Makro> Tallenna uusi makro.
Jos käytät tätä makroa useita päiviä, sinun on annettava makrolle hyödyllinen nimi. Jos kertaluonteisen tehtävän automatisointi on kertaluonteinen makro, Makron nimen jättäminen Makro1: ksi on todennäköisesti hyvä. Tärkeä kenttä tässä on pikanäppäinkenttä. Jos luot makron kertakäyttöön, määritä makro pikanäppäimelle, kuten Ctrl + a - Ctrl + a on melko helppo painaa toistuvasti, koska näppäimet ovat lähellä toisiaan. Jos aiot luoda makron, jota käytät päivittäin, haluat määrittää makron avaimelle, joka ei ole jo tärkeä pikanäppäinyhdistelmä. Ctrl + j tai Ctrl + k ovat hyviä valintoja.
Kertakäyttöiset makrot tulisi tallentaa tähän työkirjaan. Jos haluat käyttää makroa toistuvasti useissa eri työkirjoissa, voit tallentaa makron henkilökohtaiseen makron työkirjaan.
Sinulle näytetään nyt pienin työkalurivi kaikessa Excelissä; Lopeta tallennus -työkalurivi. Siinä on vain kaksi kuvaketta ja se näyttää tältä:
Jos tämä työkalupalkki on omalla tavallasi, älä sulje sitä napsauttamalla X-merkkiä. Tartu sen sijaan siniseen palkkiin ja vedä työkalurivi uuteen paikkaan. Työkalurivin siirtämistä ei tallenneta makroon. Jos suljet vahingossa työkalurivin, hanki se takaisin valitsemalla Näytä> Työkalurivit> Lopeta nauhoitus. Tämä toiminto kuitenkin tallennetaan.
Työkalurivin ensimmäinen painike on "Lopeta tallennus" -painike. Tämä on itsestään selvää. Kun olet lopettanut makron tallentamisen, paina pysäyttämisen työkaluriviä.
Tärkeämpi (ja harvoin ymmärrettävä) painike on "Suhteellinen viite" -painike.
Nykyisessä esimerkissämme sinun on painettava Suhteellinen viite -painiketta ennen aloittamista. Jos tallennat makron, jonka suhteelliset viitteet ovat päällä, Excel tallentaa seuraavat vaiheet:
- Siirrä yksi solu alaspäin
- Leikkaa nykyinen solu
- Siirrä yksi solu ylöspäin
- Siirrä yksi solu oikealle
- Liitä
- jne.
Jos sen sijaan tallennat makron ilman suhteellisia viitteitä, makro tallentaa seuraavat vaiheet:
- Siirry soluun A4
- Leikkaa solu A4
- Siirry soluun A3
- Siirry soluun B3
- Liitä soluun B3
- jne.
Tämä olisi kamalasti väärin - meidän on makro toimittava soluissa, jotka ovat 1 ja 2 solua makron alkupisteestä. Kun suoritat makron uudestaan ja uudestaan, aloituskohta on rivi 4, rivi 5, rivi 6 jne. Makron nauhoittaminen ilman, että suhteelliset viitteet ovat päällä, makro ajetaan aina rivillä 3 lähtökohtana.
Toimi seuraavasti:
- Valitse Suhteellinen viite -painike Pysäytä tallennus -työkaluriviltä
- Solun osoittimen pitäisi olla jo sarakkeessa A olevassa nimessä.
- Napsauta alanuolta siirtyäksesi osoitteeseen
- Ctrl + x leikkaa
- Ylänuoli, oikea nuoli siirtyäksesi sarakkeeseen B nimen vieressä
- Ctrl + v liittääksesi
- Alanuoli kahdesti, vasen nuoli kerran siirtyäksesi kaupunkiin
- Ctrl + x leikkaa
- Ylänuoli kahdesti, oikea nuoli kolmesti
- Ctrl + v liittääksesi
- Vasen nuoli kahdesti, alanuoli kerran siirtyäksesi nyt tyhjälle riville.
- Pidä vaihtonäppäintä painettuna, kun painat alanuolta kahdesti
- Alt + edr poistaa tyhjät rivit
- Valitse nuoli ylös ja alas osoittamalla seuraava nimi luettelosta.
- Napsauta Pysäytä tallennus -työkaluriviä
- Tallenna työkirja
- Testaa makro lyömällä yllä määritettyä pikanäppäintä. Sen pitäisi korjata luettelon seuraava nimi täydellisesti
Kun näet, että makro toimii haluamallasi tavalla, voit pitää Ctrl + a -näppäimen alhaalla ja vahvistaa nopeasti muutaman nimen sekunnissa. Koko 500 nimen luettelo voidaan korjata muutamassa minuutissa.
Bonusvinkki - Ei näytä
Voit kietoa makron helposti yksinkertaiseksi silmukaksi, jotta se korjaa kaikki 500 nimeä ilman, että sinun on painettava Ctrl + useita satoja kertoja.
Napsauta Alt + F11-näppäintä avataksesi makron muokkausohjelma.
Jos et näe Project - VBAProject -ruutua, paina Ctrl + r.
Napsauta hiiren kakkospainikkeella Module1 ja valitse Näytä koodi. Näet koodin, joka näyttää tältä:
Nyt sinun ei tarvitse ymmärtää, mitä tämä koodi tekee, mutta tiedät, että haluamme suorittaa kaiken siinä makrossa kerran jokaiselle nimellemme. Jos tiedät, että nimiä on 462, voit lisätä 2 riviä koodin suorittamiseksi 462 kertaa. Lisää makron yläosaan uusi rivi sanoilla " For i = 1 to 462". Lisää makron alaosaan rivi, joka sanoo " Next i". Tämä käskee VBA: ta suorittamaan koodin 462 kertaa.
Johtopäätös
Tämän yksinkertaisen makron jälkeen näytin esimerkin erittäin monimutkaisen makron esityksestä. Tämä makro loi pivot-taulukot ja kaaviot yrityksen 46 osastolle. Tämä raportti kesti aikaisemmin 40 tuntia kuukaudessa. VBA-makro toimii nyt ja luo kaikki 46 raporttia alle 2 minuutissa.
Kirja VBA ja makrot Microsoft Excel 2016 vie sinut oppimiskäyrään, jotta voit päästä yksinkertaisten tämän tyyppisten makrojen tallentamisesta erittäin monimutkaisiin raportteihin, jotka voivat säästää satoja tunteja vuodessa. Tietenkin, jos et halua oppia VBA: ta, palkkaa Excel-konsultti suunnittelemaan raportin sinulle.
