Stvaranje prilagođenih funkcija u programu Excel

Prilagođene funkcije, kao što su makronaredbe, koriste programski jezik jezika Visual Basic for Applications (VBA ). Razlikuju se od makronaredbi na dva značajna načina. Najprije umjesto podobrasca koristefunkcijske postupke . To znači da započinju s izjavom Funkcije umjesto podizbornikom i završavaju funkcijom End umjesto End Sub. Drugo, oni izvode izračune umjesto da izvršavaju akcije. Određene vrste naredbi, kao što su naredbe koje odabiru i oblikuju raspone, isključene su iz prilagođenih funkcija. U ovom ćete članku saznati kako stvoriti i koristiti prilagođene funkcije. Da biste stvorili funkcije i makronaredbe, radite s programom Visual Basic Editor (VBE) koji se otvara u novom prozoru odvojeno od programa Excel.

Pretpostavimo da vaša tvrtka nudi količinski popust od 10 posto na prodaju proizvoda, pod uvjetom da je narudžba veća od 100 jedinica. U sljedećim odlomcima prikazat ćemo funkciju za izračun tog popusta.

U primjeru u nastavku prikazan je obrazac za narudžbu koji navodi svaku stavku, količinu, cijenu, popust (ako postoji) i dobivenu proširenu cijenu.

Da biste stvorili prilagođenu funkciju DISCOUNT u ovoj radnoj knjizi, slijedite ove korake:

1. Pritisnite Alt + F11 da biste otvorili Visual Basic Editor (na Macu pritisnite FN + ALT + F11), a zatim kliknite Umetni > Modul. Na desnoj strani programa Visual Basic Editor pojavit će se novi prozor modula.

2. Kopirajte i zalijepite sljedeći kod u novi modul.

   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
   

Sada ste spremni za korištenje nove funkcije DISCOUNT. Zatvorite Visual Basic Editor, odaberite ćeliju G7 i upišite sljedeće:

=DISCOUNT(D7;E7)

Excel izračunava popust od 10 posto na 200 jedinica po cijeni od 47,50 USD po jedinici i vraća 950,00 USD.

U prvom retku VBA koda Funkcija DISCOUNT(količina, cijena) naznačili ste da funkcija DISCOUNT zahtijeva dva argumenta, količinu i cijenu. Kada funkciju pozovete u ćeliji radnog lista, morate uvrstiti ta dva argumenta. U formuli =DISCOUNT(D7,E7), D7 je argument količina, a E7 argument cijene. Sada možete kopirati formulu DISCOUNT u G8:G13 da biste dobili rezultate prikazane u nastavku.

Pogledajmo kako Excel tumači taj postupak funkcije. Kada pritisnete Enter, Excel traži naziv DISCOUNT u trenutnoj radnoj knjizi i pronalazi da je to prilagođena funkcija u VBA modulu. Nazivi argumenata u zagradama , količini i cijeni rezervirana su mjesta za vrijednosti na kojima se temelji izračun popusta.

Naredba If u sljedećem bloku koda provjerava argument količina i određuje je li broj prodanih artikala veći od ili jednak 100:

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

Ako je broj prodanih stavki veći ili jednak 100, VBA izvršava sljedeću naredbu koja množi vrijednost količine s vrijednošću cijene, a zatim rezultat množi sa 0,1:

Discount = quantity * price * 0.1

Rezultat se pohranjuje kao varijabla Popust. VBA naredba koja pohranjuje vrijednost u varijablu naziva se izjavom zadatka jer procjenjuje izraz s desne strane znaka jednakosti i dodjeljuje rezultat nazivu varijable s lijeve strane. Budući da varijabla Popust ima isti naziv kao i procedura funkcije, vrijednost pohranjena u varijabli vraća se u formulu radnog lista koja se zove funkcija DISCOUNT.

Ako je količina manja od 100, VBA izvršava sljedeću naredbu:

Discount = 0

Naposljetku, sljedeća naredba zaokružuje vrijednost dodijeljenu varijabli Popust na dva decimalna mjesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel ima. Stoga, da biste koristili ROUND u ovoj izjavi, recite VBA-u da potraži metodu Round (funkciju) u objektu Application (Excel). To možete učiniti dodavanjem riječi Aplikacija prije riječi Round. Tu sintaksu koristite kad god želite pristupiti funkciji programa Excel iz VBA modula.

Prilagođena funkcija mora započinjanje s izjavom Funkcija i završavati izjavom Završna funkcija. Uz naziv funkcije naredba Funkcija obično navodi jedan ili više argumenata. No možete stvoriti funkciju bez argumenata. Excel sadrži nekoliko ugrađenih funkcija – RAND i NOW – koje, primjerice, ne koriste argumente.

Nakon naredbe Funkcija procedura funkcije sadrži jednu ili više VBA naredbi koje donose odluke i izvode izračune pomoću argumenata proslijeđenih funkciji. Konačno, negdje u proceduri funkcije morate uvrstiti naredbu koja dodjeljuje vrijednost varijabli s istim nazivom kao funkcija. Ta se vrijednost vraća u formulu koja poziva funkciju.

Broj VBA ključnih riječi koje možete koristiti u prilagođenim funkcijama manji je od broja koji možete koristiti u makronaredbama. Prilagođenim funkcijama nije dopušteno učiniti ništa osim vratiti vrijednost formuli na radnom listu ili izrazu koji se koristi u nekoj drugoj VBA makronaredbi ili funkciji. Prilagođene funkcije, primjerice, ne mogu mijenjati veličinu prozora, uređivati formulu u ćeliji ili mijenjati mogućnosti fonta, boje ili uzorka teksta u ćeliji. Ako u proceduru funkcije uvrstite kod "akcije", funkcija vraća #VALUE! pogreška.

Jedna akcija koju procedura funkcije može izvršiti (osim izvođenja izračuna) jest prikaz dijaloškog okvira. Naredbu InputBox u prilagođenoj funkciji možete koristiti kao način primanja unosa od korisnika koji izvršava funkciju. Naredbu MsgBox možete koristiti kao sredstvo prenošenja informacija korisniku. Možete koristiti i prilagođene dijaloške okvire ili korisničke obrasce, ali to je predmet izvan opsega ovog uvoda.

Čak je i jednostavne makronaredbe i prilagođene funkcije teško čitati. Da biste ih lakše razumjeli, unesite tekst s objašnjenjem u obliku komentara. Komentare dodajete tako da ispred teksta s objašnjenjem dodate apostrof. U sljedećem je primjeru, primjerice, prikazana funkcija DISCOUNT s komentarima. Dodavanjem komentara poput ovih pojednostavnjuje se održavanje VBA koda tijekom vremena. Ako ubuduće morate promijeniti kod, lakše ćete shvatiti što ste izvorno učinili.

Apostrof programu Excel govori da zanemari sve desno u istom retku da biste komentare mogli stvarati sami ili na desnoj strani redaka koji sadrže VBA kod. Možete započeti relativno dugačak blok koda s komentarom koji objašnjava njezinu cjelokupnu svrhu, a zatim koristiti komentare u retku za dokument pojedinačne izjave.

Makronaredbe i prilagođene funkcije možete dokumentiranja i tako da im dodijelite opisne nazive. Umjesto da, primjerice , dodijelite naziv makronaredbi Natpisi makronaredbi, možete je dodijeliti nazivu MonthLabels da biste detaljnije opisali svrhu koju makronaredba služi. Korištenje opisnih naziva za makronaredbe i prilagođene funkcije osobito je korisno kada stvorite brojne postupke, osobito ako stvarate postupke koji imaju slične, ali ne identične svrhe.

Način dokumenta makronaredbi i prilagođenih funkcija stvar je osobnih preferenci. Važno je usvojiti neki način dokumentacije i dosljedno ga koristiti.

Da biste koristili prilagođenu funkciju, radna knjiga koja sadrži modul u kojem ste stvorili funkciju mora biti otvorena. Ako radna knjiga nije otvorena, dobit ćete #NAME? kada pokušate koristiti funkciju. Ako funkciju referencirate u drugoj radnoj knjizi, nazivu funkcije morate prethoditi naziv radne knjige u kojoj se funkcija nalazi. Ako, primjerice, stvorite funkciju pod nazivom DISCOUNT u radnoj knjizi pod nazivom Osobno.xlsb i pozovete tu funkciju iz druge radne knjige, morate upisati =personal.xlsb!discount(), a ne samo =discount().

Možete sami spremiti neke pritiske na tipke (i moguće pogreške pri tipkanju) odabirom prilagođenih funkcija u dijaloškom okviru Umetanje funkcije. Prilagođene funkcije prikazuju se u kategoriji Korisnički definirano:

Prilagođenim funkcijama u svakom trenutku možete pojednostavniti njihovo spremanje u zasebnu radnu knjigu, a zatim spremiti tu radnu knjigu kao dodatak. Nakon toga dodatak možete učiniti dostupnim svaki put kada pokrenete Excel. Evo kako to možete učiniti:

Kada slijedite ove korake, prilagođene funkcije bit će dostupne svaki put kada pokrenete Excel. Ako želite dodati u biblioteku funkcija, vratite se u Visual Basic Editor. Ako u programu Visual Basic Editor Project Explorer potražite pod naslovom VBAProject, vidjet ćete modul nazvan po datoteci dodatka. Vaš će dodatak imati proširenje .xlam.

Dvoklik na taj modul u programu Project Explorer uzrokuje da Visual Basic Editor prikaže kod funkcije. Da biste dodali novu funkciju, postavite točku unosa nakon naredbe Završna funkcija koja završava zadnju funkciju u prozoru Koda i počnite upisivati. Na taj način možete stvoriti koliko god funkcija trebate, a one će uvijek biti dostupne u kategoriji Korisnički definirano u dijaloškom okviru Umetanje funkcije.

Ovaj sadržaj izvorno su autori Mark Dodge i Craig Stinson u sklopu njihove knjige Microsoft Office Excel 2007 Inside Out. Od tada je ažurirana tako da se primjenjuje i na novije verzije programa Excel.