Luento 9 - Makrot, omat funktiot ja työkirjan ominaisuudet

Lyhyt kertaus edellisen luennon asioista

Esimerkkejä taulukkolaskentatyökalujen käytöstä:

• Tietokanta- eli luettelofunktioita käytetään luettelomuodossa olevan tiedon ehdolliseen laskentaan. Ehtoalueella voidaan määritellä tarkat ehdot.
• Lajittelu/Järjestäminen (eng. Sort) - Voidaan järjestää solualue kolmen sarakkeen mukaan laskevaan tai nousevaan järjestykseen.
• Pikasuodatus (engl. AutoFilter) - Suodatetaan erilaisten ehtojen avulla näkyville halutut tiedot.
• Oikeellisuustarkastukset (engl. Validation) - Tarkistetaan käyttäjän antama syötettä soluissa.
• Datalomake (engl. Dataform) - Syötetään tietoja listamuodossa olevaan taulukkoon.
  • Ongelmia oikeellisuustarkastusten ja datalomakkeen käytössä!
  • Datalomakkeen käyttämä tietoalue on alettava solualueen A1:B2-sisältä.
• Erikoissuodatus (engl. Advanced Filter) - Suodatetaan tietoja erillisen ehtoalueen avulla.
  • Ehtoalueen ehdot valmiiksi ennen suodatusta.
  • Voitiin kopioida suodatuksen lopputulos toiseen laskentataulukkoon eli laskentalomakkeelle.
  • Ehtoalueelle voidaan hakea tiedot toisista soluista. Esim. =IF(ISBLANK(I8);">="&MIN(B:B);">="&I8)
• Pivot-taulun raportti
  • Ristiintaulukoitu raportti
  • Voidaan ottaa tietoja useammaltakin alueelta. Esimerkiksi tulot ja menot voidaan laittaa samaan taulukkoon.

Makrot

• Makroja käytetään sekä tekstinkäsittelyssä, taulukkolaskennassa että esitysgrafiikassa.
• Makro on kokoelma erilaisia toimenpiteitä, jotka kootaan yhdeksi kokonaisuudeksi eli makroksi.
• Esimerkiksi taulukon suodattaminen voidaan nauhoittaa makroksi.
• Makrokielenä toimii esimerkiksi Microsoft Excelissä VBA tai ohjelman oma makrokieli.
• Makrojen nimiin ei kannata laittaa välilyöntejä.
• Makrovirukset ja niiden leviäminen täytyy huomioida makrojen kanssa työskenneltäessä.

Makrojen suunnittelu

• Makroon tehtävät toimet kannattaa suunnitella huolellisesti etukäteen!
• Suunnitelma kannattaa tehdä tarkkaan vaihe vaiheelta.
• Suunnitelmasta pitää löytyä jokainen "alkeistoimenpide" eli esimerkiksi kopiointi, liittäminen, valitseminen jne.
• Muutamia huomioitavia asioita:
  • Suojaukset kannattaa ottaa jo alussa mukaan.
  • Ilman työkaluja tehtävät lisäykset vakiopaikkaan.
  • Tarkista makron lopuksi onko kaikki piilotetut rivit edelleen piilossa.
  • Tarkista kuinka rivin lisääminen tehdään oikeasti makrossa.
  • Makroissa tuhottavat asiat eivät ole enää palautettavissa Edit | Undo -valikkokomennolla

Makrojen tekeminen

• Nauhoitetaan käyttäjän toimet
  • Makro suoritetaan nopeammin kuin se on nauhoitettu.
  • Ei tule virheitä (suunnittelu).
  • Ei tarvitse ymmärtää ohjelmointia ollenkaan.
  • Makroja voidaan nauhoittaa
    • absoluuttisesti soluviittausten avulla.
    • suhteellisesti nauhoituksen aloistuspaikan suhteen (Stop Recording -työkaluriviltä löytyvä painike).
  • Nauhoittamisen yhteydessä kannattaa käyttää laskentataulukoissa suojauksia.
• Kirjoitetaan käsin
  • Todella arka kirjoitusvirheille.
  • Täytyy tietää kohtuullisen paljon ohjelmoinnista.
  • Saadaan kuitenkin tehtyä hyvin monipuolisia ja tehokkaita asioista. (Esim. Virukset)
• Nauhoitetaan perustoiminnot ja muutetaan makroa hieman käsin.
  • Tehokas tapa jo pienellä opettelulla.
  • Vaatii hieman enemmän tietoa makroista.
  • Esimerkiksi muuttamalla soluviittaukset nimetyiksi alueiksi voidaan parantaa makron toimintaa.
  • Muokkaaminen onnistuu Visual Basic -editorissa.

Makron rakenne

• Makron nimi - Sub Kirjoita_soluun()
• Kommentit - ' Kommentti
• Makron toiminnot - Range("A3").Select
• Makron loppu - End Sub

Painikkeen liittäminen makroon

• Nauhoitettuihin makroihin kannattaa liittää painike jo alussa.
• Liittäminen onnistuu Forms (suom. Lomakkeet)-työkalupainikerivin avulla. (Huom! Ei Control Toolbox)
• Työkalupainikerivistä löytyy Button (suom. Painike), johon makron liittäminen on helppoa.
• Valitaan työkalu ja raahataan työpöydällä painikkeen kokoinen alue.
• Raahaamisen jälkeen avautuvasta ikkunasta voidaan valita painikkeeseen liitettävä makro.
• Makro toimii tämän jälkeen painiketta painamalla.
• Omaan käyttöön tuleva makro voidaan yhdistää myös pikanäppäimeen.

Nauhoittamalla tehtyjä makroesimerkkejä

Seuraavassa esitellään muutamia nauhoittamalla tehtyjä makroesimerkkejä. Esimerkkeihin on liitetty myös parannusehdotuksia, joilla makroista saadaan toimivampia ongelmaan, johon ne on toteutettu. Esimerkeissä on ensin lyhyt suunnitelma makroon tulevista toimenpiteistä ja tämän jälkeen löytyy vastaava makrokoodi. Esimerkeistä saa parhaiten tietoa vertailemalla suunnitelmaa ja makrokoodia keskenään.

Lisaa_lahdeviite-makro

Seuraavassa esimerkki tekstinkäsittelyssä (Word) joillekin käyttökelpoisesta makrosta. Loput esimerkistä ovat Excelissä nauhoitettuja makroja. Makron tarkoituksena on mahdollistaa pitkien lähdeviitteiden lisääminen tekstin joukkoon pikanäppäintä painamalla. Välttämättä toimintaan ei tarvita makroa, koska vastaava toiminta voidaan tehdä AutoCorrect-ominaisuuden avulla.

Sub lahdeviite()
    Selection.TypeText Text:="[Kukkanen, 2002, sivut ]"
End Sub

Kirjoita_soluun-makro

Seuraava esimerkki on yksinkertaisin mahdollinen makroesimerkki, jossa valitaan solu ja kirjoitetaan soluun tietoa.

Suunnitelma

• Valitse solu A3.
• Kirjoita soluun teksti "Kukka".

Toteutus

Sub Kirjoita_soluun()
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Kukka"
End Sub

Kopioi-makro

Makrolla kopioidaan valittu solualue toiseen paikkaan.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse solut, jotka kopioidaan.
• Kopioi solut leikepöydälle.
• Valitse paikka, johon solut liitetään.
• Liitä solut leikepöydältä.
• Suojaa laskentataulukko uudelleen.

Toteutus

Sub kopioi()
    ActiveSheet.Unprotect
    Range("A1:C6").Select
    Selection.Copy
    Range("G8").Select
    ActiveSheet.Paste
    ActiveSheet.Protect
End Sub

Lisaa-makro

Makron tarkoituksena on mahdollistaa säätilataulukkoon lisääminen ja muokkaaminen. Makrossa ei varsinaisesti lisätä tai muokata säätilataulukkoa, vaan ainoastaan avataan datalomake, jolla muutokset voidaan suorittaa.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse aktiiviseksi yksi solu saatilataulukosta. (Rajoitus: Taulukon otsikkorivin solujen täytyy sijaita solualueella A1:B2)
• Käynnistä datalomake (Data | Form).
• Ei tehdä ollenkaan toimenpiteitä datalomakkeella!
• Sammuta datalomake.
• Suojaa laskentataulukko uudelleen.

Toteutus

Sub lisaa()
    ActiveSheet.Unprotect
    Range("A1").Select
    ActiveSheet.ShowDataForm
    ActiveSheet.Protect
End Sub

Jarjesta-makro

Makro järjestää säätilataulukon lämpötilan mukaan nousevaan järjestykseen.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse järjestettävä alue (kaikki sarakkeet).
• Järjestä sarakkeet Nimi-sarakkeen mukaan nousevaan järjestykseen.
• Suojaa laskentataulukon uudelleen.

Toteutus

Sub jarjesta()

    ActiveSheet.Unprotect
    Columns("A:D").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveSheet.Protect
End Sub

Makroa voitaisiin parantaa siten, että käytettäisiin sarakeviittausten (A:D) sijasta nimettyä aluetta saatila. Tällöin makron sarakkeiden valinnan tekevä rivi pitäisi muuttaa seuraavaksi:

Columns("saatila").Select

Suodata-makro

Suodata makrolla on tarkoitus suodattaa solualuetta käyttäjän antamien ehtojen perusteella erikoissuodatustyökalulla. Työkalun erikoispiirteenä on suodatuksen toteuttaminen erillisen ehtoalueen avulla.

Esivalmistelut

• Erikoissuodatukseen tarvittava ehtoalue laitettava kuntoon.
• Alueen ehdot laitettava kuntoon etukäteen.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse koko tenttitaulu.
• Suodata erikoissuodatuksella näkyviin jokin alue.
• Suojaa laskentataulukko uudelleen.

Toteutus

Sub suodata()
    ActiveSheet.Unprotect
    Columns("A:D").Select
    Range("A1:D30").AdvancedFilter Action:=xlFilterInPlace, _ 
		CriteriaRange:=Range("I1:L2"), Unique:=False
    ActiveSheet.Protect
End Sub

Edelliseen makroon parannuksena voitaisiin vaihtaa suodatettavan solualueen ja ehtoalueen viittaus nimetyille alueille, jolloin makro toimisi myös alueen laajenemisen yhteydessä. Seuraavassa suodatuksen toteuttava rivi muutettuna yleiskäyttöisemmäksi.

Range("saatila").AdvancedFilter Action:=xlFilterInPlace, _
				CriteriaRange:=Range("ehdot"), Unique:=False

Nayta_kaikki-makro

Seuraavalla makrolla saadaan näkyviin erikoissuodatuksella suodatetut rivit.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse näytä kaikki.
• Suojaa laskentataulukko uudelleen.
• Jos alueella on muista syistä piilotettuja (engl. hidden) soluja, niin ne on piilotettava uudelleen ShowAll-komennon jälkeen.

Toteutus

Sub naytakaikki()
    ActiveSheet.Unprotect
    ActiveSheet.ShowAllData
    ActiveSheet.Protect
End Sub

Paivita_kaavio

Kaavioiden tekemisessä on ongelmana usein kaavioalueen sarjojen lisääntyminen. Lisääntyneet sarjat eivät tule näkyviin kaavion päivitysyrityksistä huolimatta, koska kaavioon tallentuu nimetyn alueen sijasta soluviittaukset. Tämän vuoksi kaavioille voi olla hyödyllistä nauhoittaa kaavion päivittävä makro.

Sub kaavion_paivitys()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Kurssi1").Range("A2:A7,I2:I7"), _
        PlotBy:=xlRows
End Sub

Edellisessä makrossa on viittaukset suoritetaan suoraan soluihin, joten päivityksen varmistamiseksi täytyy solualue viittaus A2:A7,I2:I7 muuttaa nimetyksi alueeksi yhteensa myös makrossa.

Muutamia hieman vaikeampia esimerkkejä

Esimerkkien makroihin on jouduttu tekemään paljon korjauksia käsin ja toimenpiteita on tullut kohtuullisen paljon. Kannattaa pyrkiä toteuttamaan toiminnot esimerkkejä helpommin, mutta joskus se ei onnistu!

Lisaa_harkka-makro

Seuraavassa on hieman toisenlainen esimerkki tietojen kysymisestä. Kysyminen tehdään erillisen InputBoxin avulla. Tämän vuoksi makrossa joudutaan kopioimaan tarvittavia kaavoja. Lisäys voidaan totettaa huomattavasti helpommin datalomakkeen avulla.

Suunnitelma

• Poista laskentataulukon suojaus.
• Valitse solu A3.
• Lisää kokonainen rivi.
• Valitse aktiiviseksi alue A2:F2
• Kopioi alue leikepöydälle.
• Valitse solu A3.
• Liitä leikepöydän tiedot soluihin.
• Valitse solu A3.
• Kirjoita soluun ID.
• Valitse solu C3.
• Kirjoita soluun BONUS.
• Valitse solu D3.
• Kirjoita soluun HT1PVM.
• Valitse solu F3.
• Kirjoita soluun HT2PVM.
• Suojaa laskentataulukko uudelleen.
• Valitse solu A3.

Toteutus

Sub lisaa_harkka()
    ActiveSheet.Unprotect
    Range("A3").Select
    Selection.EntireRow.Insert
    Range("A2:F2").Select
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A3").Select
    ActiveCell.FormulaR1C1 = InputBox("Anna opis ID")
    Range("C3").Select
    ActiveCell.FormulaR1C1 = InputBox("Anna Bonus")
    Range("D3").Select
    ActiveCell.FormulaR1C1 = InputBox("Anna HT1 PVM")
    Range("E3").Select
    ActiveCell.FormulaR1C1 = InputBox("Anna HT2PVM")
    Range("A3").Select
    ActiveSheet.Protect
End Sub

Suojaukset päälle kaikkiin laskentalomakkeisiin

Seuraavassa on pieni työkalu suojausten päälle laittamiseksi kaikkiin laskentataulukoihin. Kun tehdään pientä taulukkolaskentasovellusta, niin usein on tarpeen testata suojausten toimintaa. Tällöin tarvitaan usein suojaukset päälle asettavaa makroa.

Sub suojaukset_paalle()
    Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
        Sheets(i).Protect
    Next i
    Application.ScreenUpdating = True
    
End Sub

Suojaukset pois kaikilta laskentalomakkeilta

Seuraavassa on pieni työkalu suojausten poistamiseksi laskentataulukoista. Kun tehdään pientä taulukkolaskentasovellusta, niin usein on tarpeen testata suojausten toimintaa tai esimerkiksi muuttaa tyylin ominaisuuksia. Toimet eivät välttämättä onnistu, jos kaikista laskentataulukoista ei ole poistettu suojauksia. Tällöin tarvitaan tarvitaan suojaukset nopeasti poistavaa makroa.

Sub suojaukset_pois()
    Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect
    Next i
    Application.ScreenUpdating = True

End Sub

Excel-taulukon muuttaminen HTML-taulukoksi

Eräs makrojen sovellusesimerkki on työkalu Excel-taulukon muuttamiseksi HTML-taulukoksi. Makro täytyy kirjoittaa kokonaan käsin, joten sen tekeminen ei onnistu ilman jonkinlaista ohjelmointikielen tuntemusta. Jos haluat lisätietoja tai kokeilla makron toimintaa, niin tutustu tarkempiin ohjeisiin.

Lisäominaisuuksia makroihin

Seuraavassa on esitelty muutamia makroihin lisättäviä ja makroilla saavutettavia lisäominaisuuksia.

Kommunikointi käyttäjän kanssa

Käyttäjän kanssa voidaan kommuninoida muutamalla helppokäyttöisellä elementillä, jotka voidaan lisätä makroon.

• INPUTBOX - Kysytään käyttäjältä tietoa!
• MSGBOX - Tiedotetaan käyttäjälle asioita!

Aloitus- ja lopetustoimenpiteet

• Työkirjan avaamisen yhteydessä suoritetaan auto_open-makro.
• Työkirjan sulkemisen yhteydessä suoritetaan auto_close-makro.
• Makroilla kannattaa tehdä erilaisia valmistavia toimenpiteitä, jos se on tarpeen.
• Tervehdysten tai vastaavien sijoittaminen makroon luultavasti vain ärsyttää käyttäjää.

Omien funktioiden tekeminen

Excelin makrokielen (VBA) avulla voidaan tehdä omia taulukkolaskentafunktioita, joita voidaan käyttää apuna laskennassa. Omat funktiot voidaan lisätä normaalisti ohjatun funktion lisäämistä valikkokomennolla Insert | Function. Oma funktio löytyy käyttäjän määrittelemien (engl. User Defined) funktioiden joukosta.

Funktiot joudutaan kirjoittamaan kokonaan käsin, joten niiden tekeminen vaatii jonkinlaista ohjelmointikielen tuntemusta. Seuraava omasumma-funktio on yksikertaisin esimerkki oman funktion toteuttamisesta. Funktio laskee kahden luvun tai kahdessa solussa olevien lukujen summan. Esimerkkifunktio ei ole käyttökelpoinen, koska taulukkolaskentaohjelmista löytyy parempia funktioita kyseiseen käyttötarkoitukseen, mutta se on riittävän yksikertainen esimerkki funktion toiminnasta.

Function omasumma(ekaluku As Integer, tokaluku As Integer) As Integer

' Petri Heinonen 20.02.2002
' Tämä on esimerkki mahdollisimman yksinkertaisesta itsetehdystä funktiosta
' Funktiolla voidaan laskea kahden parametrina annetun solun summan.
' Funktio
omasumma = ekaluku + tokaluku

' Jos funktion halutaan palauttavan sijaintisoluunsa tuloksen,
' niin palauttaminen voidaan toteuttaa sijoittamalla
' haluttu arvo funktion nimeen edellisen esimerkin mukaisesti.


End Function

Esimerkki funktion kutsusta ja sen palauttamasta arvosta.

• =omasumma(A12;B12) saa arvon 171, kun solussa A12 on luku 104 ja solussa B12 on luku 67.

Seuraava funktio on hieman edellistä käyttökelpoisempi. Funktiolla lasketaan kahden luvun tai soluarvon perusteella X^Y+1-sarjan arvoja. Kyseinen kaava voitaisiin toki toteuttaa suoraan laskutoimituksena soluviittauksilla, mutta sen tekeminen funktioksi helpottaa tulevaisuudessa kyseisen kaavan käyttöä.

Function SARJA(X As Integer, Y As Integer)
' Esimerkki omasta taulukkolaskentafunktiosta
' Funktio laskee kaavanmukaisen tuloksen annetuista luvuista
    
    SARJA = X ^ Y + 1

End Function

Esimerkkejä funktion kutsusta ja sen palauttamista arvoista.

• =SARJA(1;2) saa arvon 2.
• =SARJA(3;2) saa arvon 10.
• =SARJA(A6;B6) saa arvon 26, jos solussa A6 on arvo 5 ja solussa B6 on arvo 2.

Makrovirukset

Leviäminen

• Sovellusohjelmatiedostoissa olevien makrojen mukana.
• Sähköpostitse ja levykkeillä tiedostojen mukana.

Tarttuminen

• Tartuttaa sovellusohjelmatiedostoissa olevia makroja.
• Tarttuu vain samalla sovellusohjelmalla tehtyihin tiedostoihin.

Suojautuminen kaikkia viruksia vastaan

• Hanki virustorjuntaohjelma ja huolehdi sen päivityksestä
• Huolehdi muidenkin ohjelmistojen ja käyttöjärjestelmän päivityksestä!
• Tarkista aina saamasi dokumentit ennen niiden aukaisemista!
• Ole varovainen sähköpostien kanssa! (Kaikkia posteja ei kannata avata suoraan!)
• Älä lähetä sovellusohjelmatiedostoja sähköpostitse eli pyri lähettämään dokumenttisi tavallisena (ASCII-) tekstinä!
• Tarkista dokumentit ennen niiden lähettämistä!
• Poista sähköpostiohjelmista liitetiedostojen automaattinen aukaisu!
• Kytke virusvaroitukset päälle sovellusohjelmista!
• Pidä torjuntaohjelma aktiivisena taustalla!
• Pidä levykkeet kirjoitussuojattuina, äläkä unohda niitä koneeseen ja kiellä levykkeeltä käynnistyminen (Käynnistyslohkovirus)

Tuhot

• Piloista aina arkaluontoisten dokumenttien lähettämiseen uutisryhmiin.

Erikoispiirre

• Toimii useammassakin ympäristössä.

Poistaminen

• Torjuntaohjelmalla puhdistaminen.

Taulukkolaskennan työkirjamalli

Taulukkolaskennan työkirjamalliin voidaan tallentaa

• sivun asetukset, joita ovat
  • sivun suunta sekä skaalaus,
  • marginaalit,
  • ylä- ja alatunniste ja
  • tulostusasetukset (esim. arkille mahtuminen),
• sekä makrot
• ja sovelluksen sisältö.

Esimerkiksi opettaja voi tallentaa työkirjamalliksi opiskelijoiden arvosanojen laskentaan käyttämänsä sovelluksen. Ennen tallentamista hän on tyhjentänyt sovelluksen opiskelijoista. Opettaja voi käyttää eri luokille tai vuosikursseille aina uutta työkirjamalliin perustuvaa opiskelijoiden arvosanojen laskentasovellusta.