Excelis kohandatud funktsiooni loomine

Sarnaselt makrodele salvestatakse kohandatud funktsioonid programmeerimiskeeles Microsoft Visual Basic for Applications (VBA). Need erinevad makrodest kahel viisil. Esmalt kasutatakse neid Sub-toimingute asemel Function-toiminguid. Ehk siis need algavad lausega Function, mitte lausega Sub, ja lõppevad lausega End Function, mitte lausega End Sub. Teiseks need mitte ei käivita toiminguid, vaid teevad arvutusi. Teatud tüüpi laused (nt vahemike valimiseks ja vormindamiseks) pole kohandatud funktsioonide raames saadaval. Selles artiklis antakse ülevaade selle kohta, kuidas luua ja kasutada kohandatud funktsioone. Funktsioone ja makrosid saab luua Visual Basic Editoris (VBE), mis avaneb Excelist eraldi uues aknas.

Oletame, et teie ettevõte pakub üle 100 toote ostmisel allahindlust toote hinnast 10% ulatuses. Järgmistes lõikudes on kirjeldatud selle allahindluse arvutamiseks kasutatavat funktsiooni.

Allpool toodud näites on tellimisvorm, kus on kirjas tooted, kogused, hinnad, allahindlused (kui neid on) ja lõplik laiendatud hind.

Selles töövihikus kohandatud DISCOUNT-funktsiooni loomiseks tehke järgmist.

1. Vajutage Visual Basic Editori avamiseks klahvikombinatsiooni Alt+F11 (Mac-arvutis vajutage klahvikombinatsiooni FN+ALT+F11) ja seejärel valige Lisa > Moodul. Visual Basic Editori paremas servas kuvatakse uus mooduliaken.

2. Kopeerige ja kleepige järgmine kood uude moodulisse.

   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
   

Uus funktsioon DISCOUNT on nüüd kasutamiseks valmis. Sulgege Visual Basic Editor, valige lahter G7 ja tippige järgmine rida:

=DISCOUNT(D7,E7)

Excel arvutab 200 toote kohta 10%-se allahindluse 47,50 dollarit ja tagastab hinna 950,00 dollarit.

VBA-koodi esimesel real on funktsioonile DISCOUNT(kogus, hind) määratud kaks argumenti: kogus ja hind. Töölehe lahtris funktsiooni kasutades tuleb lisada need kaks argumenti. Valemis =DISCOUNT(D7,E7) on D7 kogus ja E7 on hind. Nüüd saate valemi DISCOUNT kopeerida lahtrisse G8:G13, et saada allpool näidatud tulem.

Vaatame, kuidas Excel seda funktsioonitoimingut tõlgendab. Sisestusklahvi (Enter) vajutamisel otsib Excel praegusest töövihikust nime DISCOUNT ja avastab, et tegu on kohandatud funktsiooniga VBA moodulis. Sulgudega ümbritsetud argumendinimed (kogus ja hind) on nende väärtuste kohatäited, mille põhjal allahindlus arvutatakse.

Järgmises koodiplokis olev If-lause analüüsib koguse argumenti ja selgitab välja, kas tooteid on müüdud vähemalt 100 tükki.

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

Kui müüdud toodete arv on 100 või suurem, käivitab VBA järgmise lause, mis korrutab koguse väärtus hinna väärtusega ja jagab tulemi 0,1-ga.

Discount = quantity * price * 0.1

Tulem salvestatakse muutujana Discount. Sellist VBA-lauset, mis salvestab väärtuse muutujana, nimetatakse määramislauseks, sest see leiab paremal oleva avaldise väärtuse ja määrab tulemi vasakul oleva muutuja väärtuseks. Kuna muutuja Discount nimi on sama nagu funktsioonitoimingul, tagastatakse muutujas talletatav väärtus sellele töölehe valemile, mis funktsiooni DISCOUNT algselt kutsus.

Kui kogus on alla 100, käivitab VBA järgmise lause:

Discount = 0

Lõpuks ümardab järgmine lause muutujale Discount määratud väärtuse kahe kümnendkohani:

Discount = Application.Round(Discount, 2)

VBA-s pole funktsiooni ROUND, aga Excelis on. Seega selles lauses funktsiooni ROUND kasutamiseks tuleb VBA-l lasta otsida meetodit (funktsiooni) Round objektist Application (Excel). Selleks tuleb sõna „Round“ ette lisada sõna Application. Kasutage seda süntaksit alati VBA-mooduli kaudu Exceli funktsioonile juurdepääsemiseks.

Kohandatud funktsiooni alguses peab olema lause Function ja lõpus lause End Function. Lisaks funktsiooni nimele määratakse lauses Function tavaliselt ka mõni argument. Kuid saate luua funktsiooni ka ilma argumentideta. Excelis on mitu sisseehitatud funktsiooni, mis ei kasuta argumente (nt RAND ja NOW).

Lausele Function järgnev funktsioonitoiming sisaldab ühte või mitut VBA-lauset, mis teevad otsuseid ja arvutusi funktsioonile edastatud argumentide põhjal. Samuti tuleb funktsioonitoimingusse lisada lause, mis määrab funktsiooniga sama nime kandva muutuja väärtuse. See väärtus tagastatakse valemile, mis selle funktsiooni kutsus.

Kohandatud funktsioonides kasutatavate VBA-märksõnade arv on väiksem kui makrodes kasutatav arv. Kohandatud funktsioonid ei tohi teha muud peale töölehel oleva valemi väärtuse tagastamise või mõnes muus VBA makros või funktsioonis kasutatava avaldise. Näiteks ei saa kohandatud funktsioonid muuta akende suurust, redigeerida lahtris valemit ega muuta lahtri teksti fonti, värvi ega mustri suvandeid. Kui lisate funktsiooniprotseduuri sedalaadi toimingukoodi, tagastab funktsioon #VALUE! #VALUE!.

Ainuke toiming, mille funktsioonitoimingu raames saab esile kutsuda (peale arvutamise), on dialoogiboksi kuvamine. Kohandatud funktsioonis saab lause InputBox abil lasta funktsiooni käivitanud kasutajal määrata sisendväärtus. Lause MsgBox abil saate kasutajale teavet edastada. Lisaks saate kasutada kohandatud dialoogibokse ja kasutajavorme (UserForms), aga see pole enam selle sissejuhatuse teema.

Ka lihtsaid makrosid ja kohandatud funktsioone võib olla keeruline lugeda. Nende lugemise hõlbustamiseks saate lisada selgitavad tekstid kommentaaridena. Kommentaari sisestamiseks lisage teksti ette ülakoma. Järgmises näites on funktsiooni DISCOUNT koos kommentaaridega. Selliste kommentaaride lisamine aitab teil või teistel VBA-koodi ka edaspidi hõlpsalt hallata. Kui teil on tarvis tulevikus koodi muuta, saate paremini aru, mida te algselt tegite.

Ülakoma ütleb Excelile, et kõike, mis on ülakoma järel samal real, tuleb ignoreerida, nii et saate kommentaare lisada otse ridadele või VBA-koodi sisaldavatest ridadest paremale. Võite näiteks pikema koodiploki algusesse lisada kommentaari, mis selgitab selle otstarvet, ja kasutada reasiseseid kommentaare, et kirjeldada üksikuid lauseid.

Teine võimalus makrode ja kohandatud funktsioonide dokumenteerimiseks on panna neile kirjeldavad nimed. Näiteks selle asemel, et panna makro nimeks Sildid, võite nimeks panna KuuSildid, et kirjeldada makro eesmärki veel paremini. Makrode ja kohandatud funktsioonide kirjeldavad nimed on eriti kasulikud siis, kui olete loonud palju toiminguid ja eriti, kui olete loonud toiminguid, mis on sarnased, kuid mitte identsed.

Makrode ja kohandatud funktsioonide dokumenteerimise viis sõltub isiklikest eelistustest. Oluline on seda teha konkreetset meetodit järgides ja järjekindlalt.

Kohandatud funktsiooni kasutamiseks peab teie loodud moodulit sisaldav töövihik olema avatud. Kui see töövihik pole avatud, saate #NAME? kui proovite funktsiooni kasutada. Kui viitate funktsioonile mõnes muus töövihikus, peate funktsiooni nime ette märkima selle töövihiku nime, kus funktsioon asub. Näiteks kui loote töövihikus Nimega Personal.xlsb funktsiooni DISCOUNT ja kutsute seda funktsiooni mõnest muust töövihikust, peate tippima =personal.xlsb!discount(), mitte lihtsalt =discount().

Klahvivajutuste säästmiseks (ja kirjavigade vältimiseks) saate valida oma kohandatud funktsiooni dialoogiboksis Funktsiooni lisamine. Teie kohandatud funktsioon kuvatakse kategoorias Kasutaja määratletud:

Hõlpsam viis kohandatud funktsioonide igal ajal saadavaks muutmiseks saate need salvestada eraldi töövihikusse ja seejärel salvestada selle töövihiku lisandmoodulina. Selle töövihiku saate Excelis alati kättesaadavaks muuta. Selleks tehke järgmist.

Pärast nende juhiste järgimist on kohandatud funktsioonid iga kord Exceli käitamisel kättesaadavad. Oma funktsiooniteegi täiendamiseks naaske Visual Basic Editori. Visual Basic Editori Project Exploreris on pealkirja VBAProject all teie lisandmooduli järgi nimetatud mooduli nimi. Teie lisandmoodulil on laiend .xlam.

Project Exploreris selle mooduli topeltklõpsamisel kuvab Visual Basic Editor teie funktsiooni koodi. Uue funktsiooni lisamiseks viige järjepunkt koodiakna viimast lauset sisaldava lause End Function lõppu, ja hakake tippima. Sel viisil saate luua nii palju funktsioone kui soovite ning need on alati saadaval dialoogiboksi Funktsiooni lisamine kategoorias Kasutaja määratletud.

See sisu oli algselt osa Mark Dodge‘i ja Craig Stinsoni raamatust Microsoft Office Excel 2007 Inside Out (Microsoft Office Excel 2007 – põhjalik ülevaade). Sellesse on hiljem lisatud teave Exceli uuemate versioonide kohta.