Excel VBA -opetusohjelma - Kuinka kirjoittaa koodi taulukkolaskentaohjelmaan Visual Basicia käyttämällä

Johdanto

Tämä on opetusohjelma koodin kirjoittamisesta Excel-laskentataulukoissa Visual Basic for Applications (VBA) -ohjelmalla.

Excel on yksi Microsoftin suosituimmista tuotteista. Vuonna 2016 Microsoftin toimitusjohtaja sanoi "Ajattele maailmaa ilman Exceliä. Se on minulle vain mahdotonta." Ehkä maailma ei voi ajatella ilman Exceliä.

  • Vuonna 1996 Microsoft Excel (lähde) oli yli 30 miljoonaa käyttäjää.
  • Nykyään Microsoft Exceliä on arviolta 750 miljoonaa käyttäjää. Se on hieman enemmän kuin Euroopan väestö ja 25 kertaa enemmän käyttäjiä kuin vuonna 1996.

Olemme yksi iso onnellinen perhe!

Tässä opetusohjelmassa opit VBA: sta ja koodin kirjoittamisesta Excel-laskentataulukkoon Visual Basicin avulla.

Edellytykset

Tämän opetusohjelman ymmärtämiseen ei tarvita aikaisempaa ohjelmointikokemusta. Tarvitset kuitenkin:

  • Microsoft Excelin perus- ja keskitason tuntemus
  • Jos haluat seurata tämän artikkelin VBA-esimerkkejä, tarvitset pääsyn Microsoft Exceliin, mieluiten uusimpaan versioon (2019), mutta Excel 2016 ja Excel 2013 toimivat hyvin.
  • Halu kokeilla uusia asioita

Oppimistavoitteet

Tämän artikkelin aikana opit:

  1. Mikä VBA on
  2. Miksi käytät VBA: ta
  3. Kuinka määritellä Excel kirjoittaa VBA
  4. Kuinka ratkaista joitain todellisia ongelmia VBA: lla

Tärkeitä käsitteitä

Tässä on joitain tärkeitä käsitteitä, jotka sinun tulisi tuntea ymmärtääksesi tämän opetusohjelman täysin.

Objektit : Excel on olio-suuntautunut, mikä tarkoittaa, että kaikki on objekti - Excel-ikkuna, työkirja, taulukko, kaavio, solu. VBA antaa käyttäjien manipuloida ja suorittaa toimintoja objektien kanssa Excelissä.

Jos sinulla ei ole kokemusta olio-ohjelmoinnista ja tämä on upouusi konsepti, anna hetken antaa sen uppoaa!

Menettelytavat : menettely on osa Visual Basic Editoriin kirjoitettua VBA-koodia, joka suorittaa tehtävän. Joskus tätä kutsutaan myös makroksi (lisätietoja makroista alla). Menettelyjä on kahdenlaisia:

  • Alirutiinit: ryhmä VBA-lauseita, joka suorittaa yhden tai useamman toiminnon
  • Funktiot: VBA-lauseiden ryhmä, joka suorittaa yhden tai useamman toiminnon ja palauttaa yhden tai useamman arvon

Huomaa: toiminnot voivat toimia aliohjelmien sisällä. Näet myöhemmin.

Makrot : Jos olet viettänyt aikaa edistyneempien Excel-toimintojen oppimiseen, olet todennäköisesti kohdannut "makron" käsitteen. Excel-käyttäjät voivat tallentaa makroja, jotka koostuvat käyttäjän komennoista / näppäinpainalluksista / napsautuksista, ja toistaa niitä salamannopeasti toistuvien tehtävien suorittamiseksi. Tallennetut makrot tuottavat VBA-koodin, jonka voit sitten tutkia. On todella hauskaa tallentaa yksinkertainen makro ja sitten tarkastella VBA-koodia.

Muista, että joskus makron tallentaminen voi olla helpompaa ja nopeampaa kuin VBA-menettelyn käsin koodaaminen.

Ehkä esimerkiksi työskentelet projektinhallinnassa. Kerran viikossa sinun on muutettava projektinhallintajärjestelmästäsi viety raaka raportti kauniisti muotoilluksi, puhtaaksi johtajuuden raportiksi. Ylibudjetin hankkeiden nimet on muotoiltava lihavoidulla punaisella tekstillä. Voit tallentaa muotoilumuutokset makroksi ja suorittaa ne aina, kun sinun on tehtävä muutos.

Mikä on VBA?

Visual Basic for Applications on Microsoftin kehittämä ohjelmointikieli. Jokainen Microsoft Office -sovelluksen ohjelmisto on mukana VBA-kielellä ilman lisäkustannuksia. VBA antaa Microsoft Office -käyttäjille mahdollisuuden luoda pieniä ohjelmia, jotka toimivat Microsoft Office -ohjelmistoissa.

Ajattele VBA: ta kuin pizzauunia ravintolassa. Excel on ravintola. Keittiössä on tavalliset kaupalliset laitteet, kuten suuret jääkaapit, uunit ja tavalliset öljyuunit - nämä ovat kaikki Excelin perusominaisuuksia.

Mutta entä jos haluat tehdä puulämmitteistä pizzaa? Et voi tehdä sitä tavallisessa kaupallisessa leivinuunissa. VBA on pizzauuni.

Yum.

Miksi käyttää VBA: ta Excelissä?

Koska puulämmitteinen pizza on paras!

Mutta vakavasti.

Monet ihmiset viettävät paljon aikaa Excelissä osana työpaikkaansa. Aika Excelissä liikkuu myös eri tavalla. Olosuhteista riippuen 10 minuuttia Excelissä voi tuntua ikuisuudelta, jos et pysty tekemään mitä tarvitset, tai 10 tuntia voi kulua hyvin nopeasti, jos kaikki sujuu hyvin. Milloin sinun pitäisi kysyä itseltäsi, miksi ihmeessä vietän 10 tuntia Excelissä?

Joskus nuo päivät ovat väistämättömiä. Mutta jos vietät 8-10 tuntia päivittäin Excelissä toistuvien tehtävien suorittamisessa, toistamalla paljon samoja prosesseja, yrität siivota tiedoston muita käyttäjiä tai jopa päivität muita tiedostoja, kun Excel-tiedostoon on tehty muutoksia, VBA-menettely voi vain olla ratkaisu sinulle.

Harkitse VBA: n käyttöä, jos haluat:

  • Automatisoi toistuvat tehtävät
  • Luo käyttäjille helppoja tapoja olla vuorovaikutuksessa laskentataulukkojesi kanssa
  • Käsittele suuria tietomääriä

Aloittaminen kirjoittamaan VBA Excelissä

Kehittäjä-välilehti

VBA: n kirjoittamiseksi sinun on lisättävä Kehittäjä-välilehti nauhaan, jotta näet tällaisen nauhan.

Kehittäjä-välilehden lisääminen nauhaan:

  1. Valitse Tiedosto-välilehdeltä Asetukset> Mukauta valintanauhaa.
  2. Valitse Mukauta valintanauhaa ja Päävälilehdet-kohdasta Kehittäjä-valintaruutu.

Kun olet osoittanut välilehden, Kehittäjä-välilehti pysyy näkyvissä, ellet poista valintaruudun valintaa tai sinun on asennettava Excel uudelleen. Lisätietoja on Microsoftin ohjekirjassa.

VBA-editori

Siirry Kehittäjä-välilehteen ja napsauta Visual Basic -painiketta. Uusi ikkuna avautuu - tämä on Visual Basic Editor. Tätä opetusohjelmaa varten sinun on vain perehdyttävä Project Explorer -ruutuun ja Property Properties -ruutuun.

Excel VBA -esimerkkejä

Luodaan ensin tiedosto, jota voimme pelata.

  1. Avaa uusi Excel-tiedosto
  2. Tallenna se makrokäyttöisenä työkirjana (.xlsm)
  3. Valitse Kehittäjä-välilehti
  4. Avaa VBA-editori

Ratsastetaan muutamalla helpolla esimerkillä, joiden avulla kirjoitat koodia laskentataulukkoon Visual Basicin avulla.

Esimerkki 1: Näytä viesti, kun käyttäjät avaavat Excel-työkirjan

Valitse VBA-editorissa Lisää -> Uusi moduuli

Kirjoita tämä koodi Moduuli-ikkunaan (älä liitä!):

Sub Auto_Open ()

MsgBox ("Tervetuloa XYZ-työkirjaan.")

End Sub

Tallenna, sulje työkirja ja avaa työkirja uudelleen. Tämän valintaikkunan pitäisi näkyä.

Ta da!

Kuinka se tekee sen?

Riippuen ohjelmoinnin tuntemuksesta, sinulla voi olla joitain arvauksia. Se ei ole erityisen monimutkainen, mutta meneillään on melko paljon:

  • Ala (lyhenne sanoista "Alirutiini"): muista alusta alkaen "VBA-lauseiden ryhmä, joka suorittaa yhden tai useamman toiminnon".
  • Auto_Open: tämä on tietty alirutiini. Se suorittaa koodisi automaattisesti, kun Excel-tiedosto avautuu - tämä on tapahtuman käynnistävä toimenpide. Auto_Open toimii vain, kun työkirja avataan manuaalisesti; se ei toimi, jos työkirja avataan toisen työkirjan koodilla (Workbook_Open tekee sen, lue lisää näiden kahden välisestä erosta).
  • Oletuksena aliohjelman käyttöoikeus on julkinen. Tämä tarkoittaa, että mikä tahansa muu moduuli voi käyttää tätä aliohjelmaa. Kaikki tämän opetusohjelman esimerkit ovat julkisia aliohjelmia. Tarvittaessa voit julistaa alirutiinit yksityisiksi. Tätä voidaan tarvita joissakin tilanteissa. Lisätietoja aliohjelman käyttömuokkaajista.
  • msgBox: tämä on funktio - VBA-lauseiden ryhmä, joka suorittaa yhden tai useamman toiminnon ja palauttaa arvon. Palautettu arvo on viesti "Tervetuloa XYZ-työkirjaan".

Lyhyesti sanottuna tämä on yksinkertainen alirutiini, joka sisältää toiminnon.

Milloin voisin käyttää tätä?

Ehkä sinulla on erittäin tärkeä tiedosto, jota käytetään harvoin (sanotaan kerran neljännes), mutta päivitetään automaattisesti päivittäin toisella VBA-menettelyllä. Kun siihen pääsee, sitä käyttävät monet ihmiset useilla osastoilla, koko yrityksessä.

  • Ongelma: Suurimman osan ajasta, kun käyttäjät käyttävät tiedostoa, he ovat hämmentyneitä tämän tiedoston tarkoituksesta (miksi se on olemassa), kuinka sitä päivitetään niin usein, kuka ylläpitää sitä ja kuinka heidän pitäisi olla vuorovaikutuksessa sen kanssa. Uusilla työntekijöillä on aina paljon kysymyksiä, ja sinun on esitettävä nämä kysymykset yhä uudelleen.
  • Ratkaisu: luo käyttäjäviesti, joka sisältää ytimekkään vastauksen kaikkiin näihin usein vastattuihin kysymyksiin.

Esimerkkejä todellisesta maailmasta

  • Käytä MsgBox-toimintoa näyttääksesi viestin tapahtumasta: käyttäjä sulkee Excel-työkirjan, käyttäjä tulostaa, uusi taulukko lisätään työkirjaan jne.
  • Käytä MsgBox-toimintoa näyttääksesi viestin, kun käyttäjän on täytettävä ehto ennen Excel-työkirjan sulkemista
  • Käytä InputBox-toimintoa saadaksesi tietoja käyttäjältä

Esimerkki # 2: Anna käyttäjän suorittaa toinen menettely

Valitse VBA-editorissa Lisää -> Uusi moduuli

Kirjoita tämä koodi Moduuli-ikkunaan (älä liitä!):

Sub UserReportQuery ()

Hämärä UserInput niin kauan

Hämärä Vastaus kokonaislukuna

UserInput = vbYesNo

Answer = MsgBox ("Käsitelläänkö XYZ-raporttia?", UserInput)

Jos Vastaus = vbKyllä, ProcessReport

End Sub

Aliprosessiraportti ()

MsgBox ("Kiitos XYZ-raportin käsittelystä.")

End Sub

Tallenna ja siirry takaisin Excelin Kehittäjä-välilehdelle ja valitse Button-vaihtoehto. Napsauta solua ja määritä UserReportQuery-makro painikkeeseen.

Napsauta nyt painiketta. Tämän viestin tulisi näyttää:

Napsauta "kyllä" tai paina Enter.

Jälleen kerran, tada!

Huomaa, että toissijainen aliohjelma, ProcessReport, voi olla mikä tahansa . Esittelen lisää mahdollisuuksia esimerkissä # 3. Mutta ensin...

Kuinka se tekee sen?

Tämä esimerkki perustuu edelliseen esimerkkiin ja siinä on melko paljon uusia elementtejä. Käydään läpi uudet asiat:

  • Dim UserInput As Long: Dim on lyhenne sanoista "dimension" ja antaa sinun ilmoittaa muuttujien nimet. Tässä tapauksessa UserInput on muuttujan nimi ja Long on tietotyyppi. Selkeällä englannilla tämä rivi tarkoittaa "Tässä muuttuja nimeltä" UserInput ", ja se on pitkä muuttujatyyppi."
  • Himmennä vastaus kokonaislukuna: ilmoittaa toisen muuttujan nimeltä "Vastaus", jonka tietotyyppi on kokonaisluku. Lisätietoja tietotyypeistä on täällä.
  • UserInput = vbYesNo: määrittää arvon muuttujalle. Tässä tapauksessa vbYesNo, joka näyttää Kyllä- ja Ei-painikkeet. Painiketyyppejä on monia , lue lisää täältä.
  • Answer = MsgBox ("Käsittele XYZ-raportti?", UserInput): määrittää muuttujan Answer arvoksi MsgBox-funktion ja UserInput-muuttujan. Kyllä, muuttuja muuttujan sisällä.
  • Jos Answer = vbYes Sitten ProcessReport: tämä on ”If-lause”, ehdollinen käsky, jonka avulla voimme sanoa, onko x totta, tee sitten y. Jos tässä tapauksessa käyttäjä on valinnut “Kyllä”, suorita ProcessReport-aliohjelma.

Milloin voisin käyttää tätä?

Tätä voitaisiin käyttää monella, monella tavalla. Tämän toiminnallisuuden arvo ja monipuolisuus määritetään enemmän siten, mitä toissijainen aliohjelma tekee.

Esimerkiksi, ehkä sinulla on tiedosto, jota käytetään kolmen eri viikoittaisen raportin luomiseen. Nämä raportit on muotoiltu dramaattisesti eri tavoin.

  • Ongelma: Aina kun jokin näistä raporteista on luotava, käyttäjä avaa tiedoston ja muuttaa muotoilua ja kaavioita. niin edelleen ja niin edelleen. Tätä tiedostoa muokataan laajasti vähintään 3 kertaa viikossa, ja se vie vähintään 30 minuuttia joka kerta, kun sitä muokataan.
  • Ratkaisu: Luo yksi painike kutakin raporttityyppiä kohden, joka muotoilee uudelleen tarvittavat raporttien komponentit ja luo tarvittavat kaaviot.

Esimerkkejä todellisesta maailmasta

  • Luo valintaikkuna, jolla käyttäjä voi täyttää tietyt tiedot automaattisesti useilla taulukoilla
  • Käytä InputBox-toimintoa saadaksesi tietoja käyttäjältä, jotka sitten täytetään useille arkeille

Esimerkki # 3: Lisää numeroita alueelle seuraavaa silmukkaa käyttämällä

Silmukat ovat erittäin hyödyllisiä, jos joudut suorittamaan toistuvia tehtäviä tietylle arvoalueelle - matriiseille tai solualueille. Selkeässä englanniksi silmukka sanoo "jokaiselle x: lle, tee y".

Valitse VBA-editorissa Lisää -> Uusi moduuli

Kirjoita tämä koodi Moduuli-ikkunaan (älä liitä!):

AlasilmukkaEsimerkki ()

Dim X kokonaislukuna

Jos X = 1-100

Alue ("A" ja X). Arvo = X

Seuraava X

End Sub

Tallenna ja siirry takaisin Excelin Kehittäjä-välilehteen ja valitse Makrot-painike. Suorita LoopExample-makro.

Tämän pitäisi tapahtua:

Jne, kunnes 100. rivi.

Kuinka se tekee sen?

  • Hämärä X kokonaislukuna: ilmoittaa muuttujan X kokonaislukutietotyypiksi.
  • Jos X = 1 - 100: tämä on For-silmukan alku. Yksinkertaisesti sanottuna, se käskee silmukan toistamaan, kunnes X = 100. X on laskuri . Silmukka jatkuu, kunnes X = 100, suorittaa viimeisen kerran ja pysähtyy.
  • Alue ("A" ja X). Arvo = X: tämä ilmoittaa silmukan alueen ja sen, mitä siihen tulisi lisätä. Koska X = 1 aluksi, ensimmäinen solu on A1, jolloin silmukka laittaa X: n tähän soluun.
  • Seuraava X: tämä käskee silmukan jatkamaan uudelleen

Milloin voisin käyttää tätä?

For-Next -silmukka on yksi VBA: n tehokkaimmista toiminnoista; on olemassa lukuisia mahdollisia käyttötapauksia. Tämä on monimutkaisempi esimerkki, joka vaatisi useita logiikkakerroksia, mutta se kommunikoi For-Next -silmukoiden mahdollisuuksien maailmasta.

Ehkä sinulla on luettelo kaikista leipomossasi sarakkeessa A myytävistä tuotteista, sarakkeessa B olevan tuotteen tyyppi (kakut, munkkeja tai muffineja), sarakkeessa C olevien ainesosien kustannukset ja kunkin tuotetyypin keskimääräiset markkinakustannukset toinen arkki.

Sinun on selvitettävä, mikä on kunkin tuotteen vähittäishinta. Ajattelet sen olevan ainesosien hinta plus 20%, mutta myös 1,2% alle markkinoiden keskiarvon, jos mahdollista. For-Next -silmukka antaisi sinun tehdä tämän tyyppisen laskennan.

Esimerkkejä todellisesta maailmasta

  • Käytä silmukkaa, jossa on sisäkkäin if-lause, tiettyjen arvojen lisäämiseksi erilliseen matriisiin vain, jos ne täyttävät tietyt ehdot
  • Suorita matemaattiset laskelmat kullekin alueen arvolle, esimerkiksi laske lisämaksut ja lisää ne arvoon
  • Selaa merkkijonon jokaista merkkiä ja pura kaikki numerot
  • Valitse satunnaisesti joukko arvoja taulukosta

Johtopäätös

Nyt kun olemme puhuneet pizzasta ja muffineista ja oh-joo, kuinka kirjoittaa VBA-koodi Excel-laskentataulukoihin, tehdään oppimisen tarkistus. Katso, voitko vastata näihin kysymyksiin.

  • Mikä on VBA?
  • Kuinka pääsen aloittamaan VBA: n käytön Excelissä?
  • Miksi ja milloin käyttäisit VBA: ta?
  • Mitä ongelmia voisin ratkaista VBA: lla?

Jos sinulla on oikea käsitys siitä, miten voisit vastata näihin kysymyksiin, niin tämä onnistui.

Olitpa satunnainen käyttäjä tai tehokäyttäjä, toivon, että tämä opetusohjelma antoi hyödyllistä tietoa siitä, mitä voidaan saavuttaa vain pienellä koodilla Excel-laskentataulukoissasi.

Hyvää koodausta!

Oppimateriaalit

  • Excel VBA -ohjelmointi nukkeille, John Walkenbach
  • Aloita VBA, Microsoft Documentation
  • VBA: n oppiminen Excelissä, Lynda

Hieman minusta

Olen Chloe Tucker, taiteilija ja kehittäjä Portlandissa Oregonissa. Entisenä kouluttajana etsin jatkuvasti oppimisen ja opetuksen tai tekniikan ja taiteen risteystä. Ota yhteyttä minuun Twitterissä @_chloetucker ja tarkista verkkosivustoni osoitteessa chloe.dev.