Kreirajte prilagođene funkcije u programu Excel

Prilagođene funkcije, kao što su makroi, koriste programski jezik Visual Basic for Applications (VBA ). Oni se razlikuju od makroa na dva značajna načina. Prvo koriste procedure funkcije umesto sub procedura. To jest, oni počinju sa izvodom Funkcije umesto izrazom "Podeb ", a završavaju se sa Funkcijom End umestosa End Sub. Drugo, oni obavljaju izračunavanja umesto da izvršavaju radnje. Određene vrste izraza, kao što su izjave koje biraju i oblikuju opsege, isključene su iz prilagođenih funkcija. U ovom članku ćete saznati kako da kreirate i koristite prilagođene funkcije. Da biste kreirali funkcije i makroe, radite sa programom Visual Basic Editor (VBE) koji se otvara u novom prozoru odvojenom od programa Excel.

Recimo da vaše preduzeće nudi količinski popust od 10 procenata na prodaju proizvoda, ako je porudžbina za više od 100 jedinica. U sledećim pasusima demonstriraćemo funkciju za izračunavanje ovog popusta.

Dolenavedeni primer prikazuje obrazac porudžbine koji navodi svaku stavku, količinu, cenu, popust (ako postoji) i dobijenu proširenu cenu.

Da biste u ovoj radnoj svesci kreirali prilagođenu funkciju DISCOUNT, pratite ove korake:

1. Pritisnite kombinaciju tastera Alt+F11 da biste otvorili Visual Basic Editor (na Mac računaru pritisnite FN+ALT+F11), a zatim izaberite stavku Umetni > modul. Novi prozor modula se pojavljuje sa desne strane programa Visual Basic Editor.

2. Kopirajte i nalepite sledeći kôd 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 da koristite novu funkciju DISCOUNT. Zatvorite Visual Basic Editor, izaberite ćeliju G7 i otkucajte sledeće:

=DISCOUNT(D7,E7)

Excel izračunava 10 procenata popusta na 200 jedinica sa 47,50 USD po jedinici i daje 950,00 USD.

U prvom redu VBA šifre funkcije DISCOUNT(količina, cena) naznačili ste da funkcija DISCOUNT zahteva dva argumenta , količinu i cenu. Kada pozovete funkciju u ćeliju radnog lista, morate da uključite ta dva argumenta. U formuli =DISCOUNT(D7,E7), D7 je argument količine , a E7 je argument cena . Sada možete da kopirate formulu DISCOUNT u G8:G13 da biste dobili dolenavedene rezultate.

Hajde da razmislimo o tome kako Excel tumači ovu proceduru funkcije. Kada pritisnete taster Enter, Excel traži ime DISCOUNT u trenutnoj radnoj svesci i pronalazi da je to prilagođena funkcija u VBA modulu. Imena argumenata u zagradama , količini i ceni su čuvari mesta za vrednosti na kojima se zasniva izračunavanje popusta.

Izraz If u sledećem bloku šifre ispituje argument količine i određuje da li je broj prodatih artikala veći ili jednak 100:

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

Ako je broj prodatih stavki veći od ili jednak 100, VBA izvršava sledeći izraz koji množi vrednost količine vrednošću cene, a zatim množi rezultat sa 0,1:

Discount = quantity * price * 0.1

Rezultat se skladišti kao promenljivi popust. VBA izjava koja skladišti vrednost u promenljivoj naziva se izjava o dodeli zato što procenjuje izraz sa desne strane znaka jednakosti i dodeljuje rezultat imenu promenljive sa leve strane. Pošto promenljiva "Popust" ima isto ime kao procedura funkcije, vrednost uskladištena u promenljivoj vraća se u formulu radnog lista koja se zove funkcija DISCOUNT.

Ako je količina manja od 100, VBA izvršava sledeći izraz:

Discount = 0

Na kraju, sledeći izraz zaokružuje vrednost dodeljenu promenljivoj Popust na dva decimalna mesta:

Discount = Application.Round(Discount, 2)

VBA nema funkciju ROUND, ali Excel ima. Stoga, da biste koristili funkciju ROUND u ovoj izjavi, recite VBA da potraži metod Round (funkcija) u objektu Aplikacija (Excel). To možete da uradite tako što ćete dodati reč Aplikacija pre reči Zaokruži. Koristite ovu sintaksu svaki put kada treba da pristupite Excel funkciji iz VBA modula.

Prilagođena funkcija mora da počinje izrazom funkcije i da se završi izrazom "Završi funkciju". Pored imena funkcije, izjava Funkcija obično navodi jedan ili više argumenata. Međutim, možete da kreirate funkciju bez argumenata. Excel uključuje nekoliko ugrađenih funkcija – RAND i NOW, na primer – koje ne koriste argumente.

Posle izjave Funkcija, procedura funkcije uključuje neke VBA izjave koje donose odluke i vrše izračunavanja pomoću argumenata prosleđenih funkciji. Na kraju, negde u proceduri funkcije morate da uključite izjavu koja dodeljuje vrednost promenljivoj sa istim imenom kao funkcija. Ova vrednost se vraća formuli koja poziva funkciju.

Broj VBA ključnih reči koje možete da koristite u prilagođenim funkcijama manji je od broja koji možete da koristite u makroima. Prilagođenim funkcijama nije dozvoljeno da rade ništa drugo osim da daju vrednost formuli u radnom listu ili izrazu koji se koristi u drugom VBA makrou ili funkciji. Na primer, prilagođene funkcije ne mogu da promene veličinu prozora, urede formulu u ćeliji ili promene opcije fonta, boje ili šare teksta u ćeliji. Ako ovu vrstu kôda radnje uključite u proceduru funkcije, funkcija vraća #VALUE! grešku.

Jedna radnja koju procedura funkcije može da izvrši (osim izvršavanja izračunavanja) jeste prikazivanje dijaloga. InputBox izraz možete da koristite u prilagođenoj funkciji kao način dobijanja unosa od korisnika koji izvršava funkciju. Možete da koristite MsgBox izjavu kao sredstvo za prenošenje informacija korisniku. Možete da koristite i prilagođene dijaloge ili korisničke obrasce, ali to je tema izvan opsega ovog uvoda.

Čak i jednostavne makroe i prilagođene funkcije može biti teško čitati. Možete da ih učinite lakšim za razumevanje tako što ćete otkucati tekst objašnjenja u obliku komentara. Komentare dodajete tako što pre teksta objašnjenja dodajete apostrof. Na primer, sledeći primer prikazuje funkciju DISCOUNT sa komentarima. Dodavanje ovakvih komentara olakšava vama ili drugima održavanje VBA koda dok vreme prolazi. Ako ubuduće treba da promenite kôd, lakše ćete razumeti šta ste prvobitno uradili.

Apostrof govori programu Excel da zanemari sve nadesno u istom redu, tako da možete sami da kreirate komentare u redovima ili na desnoj strani redova koji sadrže VBA kôd. Možete da započnete relativno dugački blok koda sa komentarom koji objašnjava njegovu ukupnu svrhu, a zatim da koristite umetnute komentare da biste dokumentovali pojedinačne izraze.

Drugi način za dokumentovanje makroa i prilagođenih funkcija jeste da im date opisna imena. Na primer, umesto da imenovanje makroa "Oznake ", možete da joj navedete " Mesečnelabele " da biste detaljnije opisali svrhu koju makro koristi. Korišćenje opisnih imena za makroe i prilagođene funkcije naročito je korisno kada ste napravili mnogo procedura, naročito ako kreirate procedure koje imaju slične, ali ne i identične svrhe.

Način na koji dokumentujte makroe i prilagođene funkcije je pitanje lične željene postavke. Važno je da usvojite neki metod dokumentacije i da je dosledno koristite.

Da biste koristili prilagođenu funkciju, radna sveska koja sadrži modul u kojem ste kreirali funkciju mora biti otvorena. Ako ta radna sveska nije otvorena, dobijate #NAME? prilikom pokušaja korišćenja funkcije. Ako upućujete na funkciju u drugoj radnoj svesci, ispred imena funkcije morate uneti ime radne sveske u kojoj se nalazi funkcija. Na primer, ako kreirate funkciju pod imenom DISCOUNT u radnoj svesci pod imenom "Personal.xlsb" i pozovete tu funkciju iz druge radne sveske, morate otkucati =personal.xlsb!discount(), a ne samo =discount().

Možete da sačuvate sebi neke pritiske na taster (i moguće greške u kucanju) tako što ćete izabrati prilagođene funkcije u dijalogu Umetanje funkcije. Prilagođene funkcije se pojavljuju u kategoriji Korisnički definisane:

Prilagođene funkcije možete lakše da učinite dostupnim u svakom trenutku tako što ćete ih uskladištiti u zasebnoj radnoj svesci, a zatim sačuvati tu radnu svesku kao programski dodatak. Zatim možete da učinite programski dodatak dostupnim svaki put kada pokrenete Excel. Evo kako to da uradite:

Kada pratite ove korake, prilagođene funkcije će biti dostupne svaki put kada pokrenete Excel. Ako želite da dodate u biblioteku funkcija, vratite se u Visual Basic Editor. Ako pogledate u programu Visual Basic Editor Project Explorer u okviru naslova VBA projekta, videćete modul nazvan po datoteci programskog dodatka. Programski dodatak će imati oznaku tipa datoteke .xlam.

Ako dvaput kliknete na taj modul u programu Project Explorer, Visual Basic Editor će prikazati kôd funkcije. Da biste dodali novu funkciju, postavite mesto umetanja posle izjave Funkcija završavanja koja završava poslednju funkciju u prozoru Kôd i počnite da kucate. Na ovaj način možete da kreirate koliko god funkcija vam je potrebno i one će uvek biti dostupne u kategoriji Korisnički definisane u dijalogu Umetanje funkcije .

Ovaj sadržaj su prvobitno kreirali Mark Dodž i Kreg Stinson kao deo njihove knjige Microsoft Office Excel 2007 Inside Out. Ona je od tada ažurirana tako da se primenjuje i na novije verzije programa Excel.