Stvaranje prilagođenih funkcija u programu Excel

Premda Excel sadrži mnogo ugrađenih funkcija radnog lista, vjerojatno nema funkciju za svaku vrstu izračuna koje izvodite. Dizajneri programa Excel nisu mogli predvidjeti potrebe svakog korisnika za izračunima. Umjesto toga, Excel vam omogućuje stvaranje prilagođenih funkcija, što je objašnjeno u ovom članku.

Stvaranje jednostavne prilagođene funkcije

Prilagođene funkcije, kao i makronaredbe, koriste programski jezik Visual Basic for Applications (VBA ). Razlikuju se od makronaredbi na dva važna načina. Za početak se umjesto podređenih procedura koriste funkcijske procedure. Drugim riječima, počinju naredbom Function , a ne izjavom Sub te završavaju s funkcijom End umjestos End Sub. Drugo, oni obavljaju izračune umjesto poduzimanja radnji. Određene vrste izjava, kao što su naredbe koje odabiru i oblikuju raspone, izuzete su iz prilagođenih funkcija. U ovom članku saznat ćete kako stvoriti i koristiti prilagođene funkcije. Funkcije i makronaredbe stvarate pomoću alata Visual Basic Editor (VBE), koji se otvara u novom prozoru neovisno o programu 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.

Primjer u nastavku prikazuje obrazac narudžbe koji navodi svaku stavku, količinu, cijenu, popust (ako postoji) i rezultirajuću proširenu cijenu.

Da biste u ovoj radnoj knjizi stvorili prilagođenu funkciju DISCOUNT, 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
   
   

Korištenje prilagođenih funkcija

Sada ste spremni za korištenje nove funkcije DISCOUNT. Zatvorite Visual Basic Editor, odaberite ćeliju G7 pa 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), naveli ste da su za funkciju DISCOUNT potrebna dva argumenta, količina i cijena. Kada funkciju pozivate u ćeliji radnog lista, morate navesti ta dva argumenta. U formuli =DISCOUNT(D7;E7) D7 je argument količine , a E7 argument cijene . Sada možete kopirati formulu POPUST 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čina i cijena, rezervirana su mjesta za vrijednosti na kojima se temelji izračun popusta.

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

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

Ako je broj prodanih artikala 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 varijabli naziva se naredbom dodjele jer ona procjenjuje izraz s desne strane znaka jednakosti i dodjeljuje rezultat nazivu varijable s lijeve strane. Budući da varijabla Diskont ima isti naziv kao procedura funkcije, vrijednost spremljena u varijabli vraća se u formulu radnog lista koja poziva funkciju Diskont.

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

Discount = 0

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

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel ima. Stoga, da biste koristili ROUND u toj naredbi, kazujete VBA da potraži metodu (funkciju) Round u objektu Application (Excel). To možete učiniti tako da prije riječi "Zaokružite" dodate riječ Aplikacija . Ovu sintaksu koristite kada morate pristupiti funkciji programa Excel iz VBA modula.

Objašnjenje pravila prilagođenih funkcija

Prilagođena funkcija mora započinjati naredbom Function i završavati naredbom End funkcije. Osim naziva funkcije, naredba funkcije 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, na primjer – koje ne koriste argumente.

Nakon naredbe funkcije, procedura funkcije obuhvaća jednu ili više VBA naredbi koje donose odluke i izvode izračune na temelju argumenata proslijeđenih funkciji. Na kraju, negdje u proceduru funkcije morate uvrstiti naredbu koja dodjeljuje vrijednost varijabli istog naziva kao i funkcija. Ta se vrijednost vraća u formulu koja poziva funkciju.

Korištenje ključnih riječi jezika VBA u prilagođenim funkcijama

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đene funkcije ne smiju obavljati ništa osim vraćanja vrijednosti u formulu na radnom listu ili izraz koji se koristi u drugoj VBA makronaredbi ili funkciji. Prilagođene funkcije, primjerice, ne mogu promijeniti veličinu prozora, urediti formulu u ćeliji ili promijeniti mogućnosti fonta, boje ili uzorka za tekst u ćeliji. Ako u proceduru funkcije uključite kod "akcije", funkcija vraća #VALUE! pogreška.

Jedina akcija koju funkcijska procedura može izvoditi (osim izvođenja izračuna) jest prikazivanje dijaloškog okvira. Naredbu InputBox možete koristiti u prilagođenoj funkciji kao sredstvo za dobivanje informacija od korisnika koji izvršava funkciju. Naredbu MsgBox možete koristiti za prijenos informacija korisniku. Možete koristiti i prilagođene dijaloške okvire ili korisničke obrasce, no to je tema izvan opsega ovog uvoda.

Dokumentiranje makronaredbi i prilagođenih funkcija

Čak i jednostavne makronaredbe i prilagođene funkcije mogu biti teško čitljive. Da biste ih lakše razumjeli, upišete tekst s objašnjenjima u obliku komentara. Komentare se dodaju tako da ispred teksta objašnjenja upišete apostrof. U sljedećem se primjeru, primjerice, prikazuje funkcija DISCOUNT s komentarima. Dodavanje ovakvih komentara vama i drugima olakšava održavanje VBA koda tijekom vremena. Ako kasnije budete trebali nešto promijeniti u kodu, lakše ćete shvatiti što ste izvorno učinili.

Apostrof govori programu Excel da zanemari sve desno u istom retku da biste komentare mogli stvarati u zasebnim recima ili na desnoj strani redaka koji sadrže VBA kod. Relativno dugačak blok koda možete započeti komentarom u kojem se objašnjava njegova opća namjena, a zatim umetnutim komentarima dokumentirati pojedinačne izjave.

Makronaredbe i prilagođene funkcije možete dokumentirati i tako da im dodijelite opisne nazive. Umjesto da joj primjerice, date naziv Natpisi, možete je nazvati Natpisi mjeseca da biste preciznije opisali svrhu makronaredbe. Korištenje opisnih naziva za makronaredbe i prilagođene funkcije osobito je korisno kada stvorite mnogo procedura, osobito ako stvarate procedure sa sličnim, ali ne i identičnim namjenama.

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

Dostupnost prilagođenih funkcija s bilo kojeg mjesta

Da biste koristili prilagođenu funkciju, mora biti otvorena radna knjiga koja sadrži modul u kojem ste stvorili funkciju. Ako ta radna knjiga nije otvorena, prikazuje vam se #NAME? kada pokušate koristiti funkciju. Ako se na funkciju pozivate u drugoj radnoj knjizi, prije naziva funkcije mora navesti naziv radne knjige u kojoj se funkcija nalazi. Na primjer, ako u radnoj knjizi pod nazivom Personal.xlsb stvorite funkciju pod nazivom DISCOUNT, a tu funkciju pozovete iz druge radne knjige, morate upisati =personal.xlsb!discount(), a ne samo =discount().

Ako odaberete prilagođene funkcije u dijaloškom okviru Umetanje funkcije, uštedjet ćete si neke pritiske na tipke (i moguće pogreške pri tipkanju). Prilagođene funkcije nalaze se u kategoriji Definirano korisnikom:

Jednostavniji način na koji možete prilagođene funkcije učiniti dostupnima u svakom trenutku jest da ih pohranite u zasebnu radnu knjigu, a zatim spremite tu radnu knjigu kao dodatak. Nakon toga možete dodatak učiniti dostupnim pri svakom pokretanju programa Excel. Evo kako to učiniti:

Nakon tih će vam koraka prilagođene funkcije biti dostupne svaki put kada pokrenete Excel. Ako želite dodati u biblioteku funkcija, vratite se u Visual Basic Editor. Ako u eksploreru projekta programa Visual Basic Editor pogledate pod naslovom VBA projekta, vidjet ćete modul nazvan po datoteci dodatka. Dodatak će imati datotečni nastavak .xlam.

Dvoklikom na taj modul u eksploreru projekta Visual Basic Editor prikazat će kod funkcije. Da biste dodali novu funkciju, postavite točku unosa nakon naredbe End Function kojom se završava zadnja funkcija u prozoru Kod i počnite pisati. Na taj način možete stvoriti koliko god je funkcija potrebno i one će uvijek biti dostupne u kategoriji Korisnički definirano u dijaloškom okviru Umetanje funkcije .

O autorima

Autori ovog sadržaja izvorno su Mark Dodge i Craig Stinson u sklopu svoje knjige Microsoft Office Excel 2007 Inside Out. U međuvremenu je ažuriran da bi se primjenjivao i na novije verzije programa Excel.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.