EXCEL VBA-OHJELMOINTI

1 EXCEL VBA-OHJELMOINTI Aki Taanila Aki Taanila Sis lt Johdanto .. 1 Varoitus .. 2 Developer/Kehitysty kalut .. 2 Makrosuojaus .. 2 1 Nauhoitetut makrot .. 3 Makron nauhoittaminen .. 3 Makron 5 VBE Visual Basic Editor .. 5 Yhteenveto .. 9 2 Excelin objektimalli .. 10 Objektit ja kokoelmat .. 11 Objektiin viittaaminen .. 11 Menetelm t .. 12 Ominaisuudet .. 15 Workbooks ja Workbook .. 16 Worksheets ja Worksheet .. 17 Range .. 19 With - End With -rakenne .. 22 For Each - Next -rakenne .. 23 Chart-objekti .. 25 Yhteenveto .. 28 3 VBA .. 29 Muuttujat .. 29 Vakiot .. 32 VBA:n valmisfunktiot .. 32 VBA:n ehtorakenteita .. 34 VBA:n toistorakenteita .. 36 4 Ohjelmoijan apuv lineit .. 40 Omat funktiot .. 40 Ohjelman jakaminen aliohjelmiin.

2 41 Virheensieppaus .. 42 VBE:n tarjoamia apuv lineit .. 44 5 Lopuksi .. 46 Tapahtumaohjatut ohjelmat .. 46 Omat valintaikkunat .. 47 Vaativa harjoitus .. 48 1 Aki Taanila Johdanto Viimeisin versio t st monisteesta ja siihen liittyvist EXCEL -tiedostoista T m moniste on kirjoitettu versioille EXCEL 2007, EXCEL 2010 ja EXCEL 2013. Excelin k ytt voidaan automatisoida ja laajentaa Visual Basic for Applications -ohjelmien avulla. Visual Basic for Applications (VBA) -ohjelmointi on laaja aihepiiri. Yksinkertaisimmillaan VBA-ohjelma luodaan nauhoittamalla Exceliss suoritettava toimenpidesarja. Nauhoitettaessa EXCEL kirjoittaa VBA-ohjelmakoodin. Varsinainen hy ty VBA-ohjelmoinnista saadaan vasta kir-joittamalla ohjelmakoodia itse. VBA-ohjelmointia voidaan k ytt my s Accessin, PowerPointin ja Wordin ohjelmointiin.

3 T ss monisteessa esittelen EXCEL VBA-ohjelmoinnin alkeet. Oletan lukijan omaavan hyv t Ex-celin k ytt taidot, mutta mink nlaista aiempaa ohjelmointiosaamista ei tarvita. Aloittelevan EXCEL -ohjelmoijan kaksi suurinta haastetta ovat Excelin objektimallin ymm rt minen VBA-kielen t rkeimpien rakenteiden hallinta. Alussa Excelin objektimalli ja VBA aiheuttavat h mmennyst ja ep toivoa. Harjoittelemalla tar-peeksi kauan yksinkertaisten (usein my s hy dytt mien) esimerkkien avulla asiat alkavat selkiin-ty ja voit siirty vaativampien (hy dyllisten) ohjelmien kirjoittamiseen. Perusta hy dyllisten ohjelmien kirjoittamiseen rakennetaan t m n monisteen kolmessa ensimm isess luvussa. 1. Ensimm isess luvussa tutustutaan toimenpidesarjojen nauhoittamiseen makronauhurin avulla. Samalla saadaan ensimm inen kosketus VBE-ohjelmointiymp rist n (Visual Basic Editor).

4 2. Toisessa luvussa tutustutaan Excelin objektimalliin ja opitaan k ytt m n yleisimmin tarvit-tavia objekteja. 3. Kolmannessa luvussa opitaan VBA:n perusrakenteet (muuttujat, VBA:n funktiot, ehtoraken-teet ja toistorakenteet). Ohjelmoinnin keskeisin haaste ei kuitenkaan ole ohjelmointikieli ja sen oppiminen. Hy dyllinen ohjelma ratkaisee ongelman tai suorittaa teht v n. Keskeisin haaste on keksi ja suunnitella mi-ten ongelma saadaan ratkaistua tai teht v suoritettua. Ensiksi t ytyy osata kuvata vaihe vai-heelta ongelman ratkaisun tai teht v n suorittamisen kulku ja vasta sen j lkeen kannattaa miet-ti vaiheiden toteuttamista ohjelmointikielen avulla. T m moniste on oppimateriaali, jonka avulla voit hankkia itsellesi valmiudet EXCEL VBA-ohjel-moinnin aloittamiseksi. T m moniste ei ole EXCEL VBA-ohjelmoinnin k sikirja.

5 Jatkolukemiseksi suosittelen Walkenbach, J. EXCEL 2013 Power Programming with VBA. Wiley Publishing. Verkosta l ytyy runsaasti materiaalia ja valmiita ohjelmia esimerkiksi hakusanalla EXCEL VBA. 2 Aki Taanila Varoitus Ohjelmoijan t ytyy ajatella loogisesti ja analyyttisesti. Sen lis ksi t ytyy omata sinnikkyytt . Yk-sinkertaisimmatkaan ohjelman p tk t eiv t useinkaan toimi ensimm isill yrityksill . Ohjelmaa t ytyy s t ja testata uudelleen ja uudelleen kunnes se toimii. Olen lis nnyt mukaan muutamia harjoituksia. Jo ensimm isten harjoitusten kohdalla voit testata omaa sinnikkyytt si. Jos sinnikkyys ei riit ensimm isten harjoitusten suorittamiseen niin ehk t m ei ole sinun juttusi. Jos taas saat harjoitukset viety loppuun asti, niin sinussa on ainesta EXCEL VBA-ohjelmointiin, riippumatta siit kuinka monta yrityskertaa tarvitset onnistumiseen.

6 Developer/Kehitysty kalut EXCEL -ohjelmointia varten tarvitset k ytt si Excelin kehitysty kalut. Tarkista, l yd tk Develo-per/Kehitysty kalut -valintanauhan yl reunasta. Jos et l yd , niin ota Developer/Kehitysty kalut k ytt n seuraavasti: EXCEL 2007: 1. Napsauta Office-painiketta ja valitse EXCEL Options/Excelin asetukset. 2. Valitse vasemmasta reunasta Popular/K ytt j n asetukset. 3. Merkitse Show Developer tab in the ribbon/N yt kehitysty kalut valintanauhassa. 4. Valitse OK. EXCEL 2010 ja EXCEL 2013: 1. Valitse File-Options-Customize Ribbon/Tiedosto-Asetukset-Muokkaa valintanauhaa. 2. Valitse Main Tabs/P valintalehdet -luettelosta Developer/Kehitysty kalut. 3. Valitse OK. Jatkossa oletan, ett Developer/Kehitysty kalut ovat k yt ss . Makrosuojaus Exceliss on oletuksena makrosuojaus, joka est ohjelmien (ohjelmia kutsutaan my s mak-roiksi) suorittamisen ilman k ytt j n suostumusta.

7 Voit tarkistaa k ytt m si Excelin suojausta-son valitsemalla Developer/Kehitysty kalut -v lilehdelt Macro Security/Makrosuojaus. Suosi-teltava suojaustaso on Disable all macros with notification/Poista k yt st kaikki makrot ja ilmoita. T ll in ohjelmia sis lt v n ty kirjan avaaminen aiheuttaa varoituksen. Jos EXCEL varoittaa makroista, niin valitse Enable/Salli. 3 Aki Taanila 1 Nauhoitetut makrot Makron nauhoittaminen Voit nauhoittaa Excelill suoritettavan toimenpidesarjan seuraavasti: 1. Valitse Developer/Kehitysty kalut v lilehdelt Record Macro/Nauhoita makro. 2. M rit Record Macro/Nauhoita makro -lomakkeelle haluamasi tiedot (makron nimi ja tal-lennuspaikka sek mahdollisesti k ynnistyskirjain ja makron kuvaus). 3. Valitse OK. 4. Suorita nauhoitettavat toimet. Kumoa/Undo toiminnolla voit peruuttaa viimeisimm n toi-menpiteen nauhoituksen.

8 5. Valitse lopuksi Developer/Kehitysty kalut v lilehdelt Stop Recording/Lopeta nauhoitta-minen. Mihin makro tallennetaan Record Macro/Nauhoita makro -lomakkeen valinnalla makro voidaan valita tallennettavaksi seuraaviin paikkoihin: 1. Aktiiviseen ty kirjaan (This workbook) tai uuteen ty kirjaan (New workbook). Ty kirjan tal-lennusvaiheessa makroja sis lt v ty kirja t ytyy tallentaa tiedostomuodossa EXCEL Macro Enabled Workbook/ EXCEL ty kirja (makrot k yt ss ). Ty kirjan sis lt m t makrot ovat k y-tett viss aina, kun makrot sis lt v ty kirja on avoinna (ja makrojen suorittaminen on sal-littu). 2. Omaan makroty kirjaan (Personal Macro Workbook). T ll in makro tallentuu piilotettuun ty kirjaan , joka aukenee automaattisesti aina, kun EXCEL k ynnistet n. N in ollen Personal Macro Workbook -makrot ovat aina k ytett viss (olettaen, ett kirjaudut samalle koneelle samaa k ytt j tunnusta k ytt en).

9 Huomaa, ett kirja ei ole olemassa ennen kuin nauhoitat ensimm isen Personal Macro Workbook makron. Voit tarvittaessa tuoda ty kirjan n kyville komennolla View Un-hide/N yt N yt ja piilottaa sen uudelleen komennolla View Hide/N yt Piilota. Palaan my hemmin siihen, mist ty kirjan mukana tallennettu ohjelmakoodi l ytyy. K ynnistyskirjain Record Macro/Nauhoita makro -lomakkeella voit m ritt k ynnistyskirjaimen, joka yhdess CTRL-n pp imen kanssa k ynnist makron. K ynnistyskirjainten k yt ss kannattaa k ytt harkintaa (jos n pp inyhdistelm on yleisess k yt ss , kuten CTRL-c, niin n pp inyhdistelm n varaaminen makron k ynnist miseen voi aiheuttaa h mmennyst ). Huomaa, ett k ynnistyskir-jaimina voit k ytt my s isoja kirjaimia. My hemmin opit muita tapoja makron k ynnist mi-seen. Voit poistaa tai muuttaa olemassa olevan makron k ynnistyskirjaimen seuraavasti: 1.

10 Valitse Developer/Kehitysty kalut v lilehdelt Macros/Makrot. 2. Valitse makron nimi ja napsauta Options/Asetukset -painiketta. 3. Tee tarvittavat muutokset. 4. OK. 4 Aki Taanila Viittaustapa Makroa nauhoitettaessa viittaustapa voi olla kiinte tai suhteellinen. Kiinte viittausta k ytet-t ess esimerkiksi siirtyminen solusta A1 soluun A2 tallentuu siirtymisen t sm lleen soluun A2. Suhteellista viittausta k ytett ess siirtyminen solusta A1 soluun A2 tallentuu siirtymisen yksi solu alasp in (suhteessa siihen soluun, josta l hdet n). Tilanteeseen sopiva viittaustapa on aina harkittava tarkoin. Viittaustapa vaihdetaan napsauttamalla Developer/Kehitysty kalut -v lilehdelt Relative Refe-rence/Suhteelliset viittaukset (suhteelliset viittaukset ovat k yt ss , jos Relative Refe-rence/Suhteelliset viittaukset painike on eriv rinen kuin ymp r iv t painikkeet).