Mukautettujen funktioiden luominen Excelissä

Mukautetuissa funktioissa, kuten makroissa, käytetään Visual Basic for Applications (VBA) -ohjelmointikieltä. Ne eroavat makroista kahdella merkittävällä tavalla. Ensinnäkin niissä käytetään Funktio-toimintosarjoja Sub-toimintosarjojen sijaan. Tämän johdosta ne alkavat Function-lausekkeella Sub-lausekkeen sijaan ja päättyvät End Function -lausekkeeseen End Sub -lausekkeen sijaan. Lisäksi ne suorittavat laskutoimituksia toimintojen sijaan. Tietynlaiset lausekkeet, kuten alueita valitsevat ja muotoilevat lausekkeet, eivät kuulu mukautettuihin funktioihin. Tässä artikkelissa opit luomaan ja käyttämään mukautettuja funktioita. Voit luoda funktioita ja makroja Visual Basic Editorissa (VBE), joka avautuu uuteen Excelistä erilliseen ikkunaan.

Oletetaan, että yrityksesi tarjoaa 10 prosentin määräalennusta tuotteen myynnistä, jos tilausmäärä on yli 100 kappaletta. Seuraavissa kappaleissa esitetään funktio, joka laskee tämän alennuksen.

Alla olevassa esimerkissä näkyy tilauslomake, joka sisältää kunkin nimikkeen määrän, hinnan, mahdollisen alennuksen ja tuloksena saatavan kokonaishinnan.

Jos haluat luoda mukautetun DISCOUNT-funktion tähän työkirjaan, toimi seuraavasti:

1. Avaa Visual Basic Editor painamalla Alt+F11 (paina Macissa FN+ALT+F11), ja valitse sitten Lisää > Moduuli. Uusi moduuli-ikkuna tulee näkyviin Visual Basic Editorin oikealle puolelle.

2. Kopioi ja liitä seuraava koodi uuteen moduuliin.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Pääset nyt käyttämään uutta DISCOUNT-funktiota. Sulje Visual Basic Editor, valitse solu G7 ja kirjoita seuraava:

=DISCOUNT(D7,E7)

Excel laskee 10 prosentin alennuksen 200 kappaleesta 47,50 dollarin kappalehinnalla ja palauttaa summan 950,00 dollaria.

VBA-koodisi ensimmäisellä rivillä Function DISCOUNT(quantity, price) ilmaisit, että DISCOUNT-funktio edellyttää kahta argumenttia: quantity ja price. Kun kutsut funktiota laskentataulukon solusta, sinun on sisällytettävä kutsuun nuo kaksi argumenttia. Kaavassa =DISCOUNT(D7,E7), D7 on quantity-argumentti ja E7 on price-argumentti. Voit nyt kopioida DISCOUNT-kaavan alueelle G8:G13, jotta saat alla näkyvät tulokset.

Tarkastellaan seuraavaksi sitä, miten Excel tulkitsee tämän funktiotoimintosarjan. Kun painat Enter-näppäintä, Excel etsii nimeä DISCOUNT nykyisestä työkirjasta ja havaitsee, että se on mukautettu funktio VBA-moduulissa. Sulkuihin merkityt argumenttien nimet quantity ja price, ovat paikkamerkkejä arvoille, joihin alennuksen laskenta perustuu.

Seuraavan koodilohkon If-lauseke tutkii määrä-argumenttia ja määrittää, onko myytyjen nimikkeiden määrä suurempi tai yhtä suuri kuin 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Jos myytyjen nimikkeiden määrä on suurempi tai yhtä suuri kuin 100, VBA suorittaa seuraavan lausekkeen, joka kertoo quantity-arvon price-arvolla ja kertoo sitten tuloksen 0,1:llä:

Discount = quantity * price * 0.1

Tulos tallennetaan muuttujaan Alennus. VBA-lauseketta, joka tallentaa arvon muuttujaan, kutsutaan määrityslauseeksi, koska se arvioi yhtäläisyysmerkin oikealla puolella olevan lausekkeen ja määrittää tuloksen vasemmalla olevan muuttujan arvoksi. Koska Alennus-muuttujalla on sama nimi kuin funktiotoimintosarjalla, muuttujaan tallennettu arvo palautetaan laskentataulukon kaavaan, joka kutsui DISCOUNT-funktiota.

Jos quantity on pienempi kuin 100, VBA suorittaa seuraavan lausekkeen:

Discount = 0

Lopuksi seuraava lauseke pyöristää Discount-muuttujalle määritetyn arvon kahden desimaalin tarkkuuteen:

Discount = Application.Round(Discount, 2)

VBA:ssa ei ole PYÖRISTÄ-funktiota, mutta Excelissä on. Jos haluat PYÖRISTÄ-funktiota tässä lausekkeessa, sinun tulee pyytää VBA:ta etsimään Pyöristä-menetelmä (funktio) Sovellus-objektista (Excel). Tämä tapahtuu lisäämällä Sovellus-sana Pyöristä-sanan eteen. Käytä tätä syntaksia, kun haluat käyttää Excel-funktiota VBA-moduulissa.

Mukautetun funktion tulee alkaa Function-lausekkeella ja päättyä End Function -lausekkeeseen. Funktion nimen lisäksi Function-lauseke yleensä määrittää yhden tai useampia argumentteja. Voit kuitenkin luoda funktion myös ilman argumentteja. Excel sisältää useita valmiita toimintoja kuten SATUNNAISLUKU ja NYT, joissa ei käytetä argumentteja.

Function-lausekkeen jälkeen funktiotoimintosarjassa tulee yksi tai useita VBA-lausekkeita, jotka tekevät päätöksiä ja suorittavat laskutoimituksia, joissa käytetään funktiolle lähetettyjä argumentteja. Sinun tulee myös lisätä funktiotoimintosarjan johonkin kohtaan lauseke, jossa funktion kanssa samannimiselle muuttujalle määritetään arvo. Tämän arvo palautetaan kaavalle, joka funktiota kutsuu.

Mukautettujen funktioiden VBA-avainsanojen määrä on pienempi kuin makroissa käytettävissä oleva luku. Mukautetut funktiot eivät saa tehdä muuta kuin palauttaa arvon laskentataulukon kaavaan tai lausekkeeseen, jota käytetään toisessa VBA-makrossa tai -funktiossa. Mukautetut funktiot eivät esimerkiksi voi muuttaa ikkunoiden kokoa, muokata solun kaavaa tai muuttaa solun tekstin fontti-, väri- tai kuvioasetuksia. Jos sisällytät tämäntyyppisen toimintokoodin funktion toimintosarjaan, funktio palauttaa #VALUE! -virheen.

Funktiotoimintosarjalla voidaan suorittaa ainoastaan (lukuun ottamatta laskutoimitusten suorittamista) valintaikkunan näyttäminen. Mukautetussa funktiossa voit InputBox-lausekkeen avulla kerätä syötettä funktion suorittavalta käyttäjältä. MsgBox-lausekkeella voit esittää tietoja käyttäjälle. Voit käyttää myös mukautettuja valintaikkunoita tai käyttäjälomakkeita, mutta tämä aihe ei sisälly tähän esittelyyn.

Jopa yksinkertaiset makrot ja mukautetut funktiot voivat olla vaikealukuisia. Voit muuntaa ne helpommin ymmärrettävään muotoon kirjoittamalla tekstiä kommentteina. Kommentti lisätään kirjoittamalla heittomerkki selittävän tekstin eteen. Esimerkiksi seuraavassa esimerkissä DISCOUNT-funktiolle on lisätty kommentteja. Tällaiset kommentit helpottavat VBA-koodin myöhempää ylläpitämistä. Jos haluat muokata koodia myöhemmin, sinun on kommentin ansiosta helpompi muistaa, mitä teit aiemmin.

Heittomerkki kehottaa Exceliä ohittamaan kaiken oikealla puolella samalla rivillä, jotta voit luoda kommentteja joko riveille itse tai VBA-koodia sisältävien rivien oikealle puolelle. Voit aloittaa suhteellisen pitkän koodilohkon kommentilla, joka selittää sen yleisen tarkoituksen, ja käyttää sitten tekstiin sitoutuvia kommentteja yksittäisten lausekkeiden dokumentointiin.

Toinen tapa dokumentoida makroja ja mukautettuja funktioita on antaa niille kuvaavia nimiä. Sen sijaan, että antaisit makrolle nimen Otsikot voisit esimerkiksi antaa nimeksi KuukausienOtsikot, joka kuvaa makroa paremmin. Makrojen ja mukautettujen funktioiden kuvaavat nimet ovat hyödyllisiä erityisesti silloin, kun olet luonut monia toimintosarjoja, joilla on saman tyyppiset mutta kuitenkin erilaiset tarkoitukset.

Makrojen ja mukautettujen funktioiden dokumentointitapa on makuasia. Tärkeintä on ylipäätään dokumentoida jollakin tavalla ja noudattaa tätä tapaa johdonmukaisesti.

Jotta voit käyttää mukautettua funktiota, työkirjan, joka sisältää moduulin, jossa loit funktion, on oltava avoinna. Jos työkirja ei ole avoinna, saat #NAME? -virhe, kun yrität käyttää funktiota. Jos viittaat funktioon toisessa työkirjassa, funktion nimen edellä on oltava sen työkirjan nimi, jossa funktio sijaitsee. Jos esimerkiksi luot DISCOUNT-nimisen funktion Personal.xlsb-nimiseen työkirjaan ja kutsut tätä funktiota toisesta työkirjasta, sinun on kirjoitettava =personal.xlsb!discount(), ei vain =discount().

Voit säästyä muutamilta näppäinpainalluksilta (ja mahdollisilta kirjoitusvirheiltä) valitsemalla mukautetut funktiot Lisää funktio -valintaikkunasta. Mukautetut funktiot näkyvät Käyttäjän määrittämät -luokassa:

Mukautetut funktiot on helpompi tuoda saataville tallentamalla ne erilliseen työkirjaan ja tallentamalla työkirjan sitten apuohjelmana. Voit sitten määrittää apuohjelman saatavilla olevaksi aina, kun Excel suoritetaan. Voit tehdä tämän seuraavasti:

Kun olet suorittanut nämä toimet, mukautetut funktiosi ovat käytettävissä aina, kun suoritat Excelin. Jos haluat lisätä funktiokirjastoon, palaa Visual Basic Editoriin. Kun katsot Visual Basic Editorin Project Exploreria VBAProject-otsikon alla, näet apuohjelmatiedostosi nimen mukaisesti nimetyn moduulin. Apuohjelmallasi on tunniste .xlam.

Jos kaksoisnapsautat moduulia Project Explorerissa, Visual Basic Editor näyttää funktiosi koodin. Jos haluat lisätä uuden funktion, aseta kohdistin sen End Function -lausekkeen perään, joka päättää koodi-ikkunassa viimeisen funktion, ja aloita kirjoittaminen. Voit luoda tähän tapaan niin monta funktiota kuin haluat, ja ne ovat aina käytettävissä Käyttäjän määrittämät -luokassa Lisää funktio -valintaikkunassa.

Tämän sisällön ovat alun perin kirjoittaneet Mark Dodge ja Craig Stinson osana kirjaansa Microsoft Office Excel 2007 Inside Out. Se on sittemmin päivitetty koskemaan myös Excelin uudempia versioita.