Dom-Tehnologije-Ugrađene vizualne osnovne karakteristike. Prilagođene funkcije u VBA za Excel

Ugrađene vizualne osnovne karakteristike. Prilagođene funkcije u VBA za Excel

Prije nego počnete kreirati vlastite VBA funkcije, korisno je znati da Excel VBA ima veliku kolekciju unaprijed ugrađenih funkcija koje možete koristiti prilikom pisanja koda.

Spisak ovih funkcija može se videti u VBA editoru:

  • Otvorite Excel radnu svesku i pokrenite VBA editor (kliknite ovde Alt+F11), a zatim kliknite F2.
  • Izaberite biblioteku sa padajuće liste u gornjem levom uglu ekrana VBA.
  • Pojavljuje se lista ugrađenih VBA klasa i funkcija. Kliknite na naziv funkcije da biste ga prikazali na dnu prozora kratak opis. Pritiskom F1će otvoriti stranicu pomoći na mreži za tu funkciju.

osim toga, puna lista VBA ugrađene funkcije s primjerima mogu se naći na web stranici Visual Basic Developer Center.

Prilagođene funkcije i podprocedure u VBA

U programu Excel Visual Basic, skup naredbi koje izvršavaju određeni zadatak stavlja se u proceduru Funkcija(Funkcija) ili Sub(Podrutina). Glavna razlika između procedura Funkcija I Sub da li je to procedura Funkcija vraća rezultat, proceduru Sub- Ne.

Stoga, ako trebate izvršiti radnje i dobiti neki rezultat (na primjer, zbrojiti nekoliko brojeva), tada se obično koristi postupak Funkcija, a da biste jednostavno izvršili neke radnje (na primjer, promijenili formatiranje grupe ćelija), morate odabrati proceduru Sub.

Argumenti

Različiti podaci se mogu proslijediti VBA procedurama koristeći argumente. Lista argumenata je specificirana kada je procedura deklarirana. Na primjer, procedura Sub u VBA, dodaje navedeni cijeli broj (Integer) svakoj ćeliji u odabranom rasponu. Možete proslediti ovaj broj proceduri koristeći argument, kao što je ovaj:

Sub AddToCells(i As Integer) ... End Sub

Imajte na umu da imate argumente za procedure Funkcija I Sub u VBA je opciono. Neke procedure ne zahtijevaju argumente.

Opcioni argumenti

VBA procedure mogu imati opcione argumente. Ovo su argumenti koje korisnik može specificirati ako želi, a ako su izostavljeni, procedura koristi zadane vrijednosti za njih.

Vraćajući se na prethodni primjer, da biste učinili cjelobrojni argument funkciji opcionim, deklarirali biste je ovako:

Sub AddToCells (opciono i kao cijeli broj = 0)

U ovom slučaju, cjelobrojni argument i default će biti 0.

U proceduri može postojati nekoliko neobaveznih argumenata, svi su navedeni na kraju liste argumenata.

Prenošenje argumenata po vrijednosti i referenci

Argumenti u VBA mogu se proslediti proceduri na dva načina:

  • ByVal– prosljeđivanje argumenta po vrijednosti. To znači da se samo vrijednost (tj. kopija argumenta) prosljeđuje proceduri, i stoga će sve promjene unesene u argument unutar procedure biti izgubljene kada izađe iz procedure.
  • ByRef– prosljeđivanje argumenta referencom. To jest, proceduri se prosljeđuje stvarna adresa argumenta u memoriji. Sve promjene unesene u argument unutar procedure bit će sačuvane kada se procedura zatvori.

Korištenje ključnih riječi ByVal ili ByRef U deklaraciji procedure možete tačno odrediti kako se argument prosljeđuje proceduri. Ovo je dolje ilustrovano primjerima:

Zapamtite da se argumenti u VBA prosljeđuju kao referenca po defaultu. Drugim riječima, ako se ključne riječi ne koriste ByVal ili ByRef, tada će argument biti proslijeđen referencom.

Prije nego nastavite sa učenjem procedura Funkcija I Sub Detaljnije će biti korisno još jednom pogledati karakteristike i razlike između ove dvije vrste procedura. Sledi kratka rasprava o VBA procedurama Funkcija I Sub a prikazani su jednostavni primjeri.

VBA procedura "Funkcija"

VBA editor prepoznaje proceduru Funkcija

Funkcija...Kraj funkcija

Kao što je ranije pomenuto, procedura Funkcija u VBA (za razliku od Sub), vraća vrijednost. Sljedeća pravila se primjenjuju na povratne vrijednosti:

  • Tip podataka povratne vrijednosti mora biti deklariran u zaglavlju procedure Funkcija.
  • Varijabla koja sadrži povratnu vrijednost mora imati isti naziv kao i procedura Funkcija. Ova varijabla ne mora biti posebno deklarirana jer uvijek postoji kao sastavni dio procedure Funkcija.

Ovo je savršeno ilustrovano u sljedećem primjeru.

Primer VBA procedure “Funkcija”: Izvršite matematičku operaciju sa 3 broja

Ispod je primjer koda VBA procedure Funkcija, koji uzima tri argumenta tipa Dvostruko(brojevi s pomičnim zarezom dvostruke preciznosti). Kao rezultat, procedura vraća drugi broj tipa Dvostruko, jednako zbroju prva dva argumenta minus treći argument:

Funkcija SumMinus(dNum1 kao duplo, dNum2 kao duplo, dNum3 kao duplo) kao dvostruka summinus = dNum1 + dNum2 - dNum3 Krajnja funkcija

Ovo je vrlo jednostavna VBA procedura Funkcija ilustruje kako se podaci prosleđuju proceduri kroz argumente. Možete vidjeti da je tip podataka koji vraća procedura definiran kao Dvostruko(reči govore ovo Kao Double nakon liste argumenata). Također ovaj primjer pokazuje kako je rezultat postupka Funkcija je pohranjen u varijablu s istim imenom kao i ime procedure.

Pozivanje VBA procedure "Funkcija"

Ako je jednostavan postupak opisan gore Funkcija umetnut u modul u uređivaču Visual Basic-a, može se pozvati iz drugih VBA procedura ili koristiti na radnom listu u Excel radnoj knjizi.

Pozivanje VBA procedure "Funkcija" iz druge procedure

Procedura Funkcija može se pozvati iz druge VBA procedure jednostavnim dodeljivanjem ovoj proceduri promenljivoj. Sljedeći primjer pokazuje poziv proceduri SumMinus, koji je gore definisan.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Pozivanje VBA procedure "Funkcija" sa radnog lista

VBA procedura Funkcija može se pozvati iz Excel radnog lista na isti način kao i bilo koja druga ugrađena Excel funkcija. Stoga je procedura kreirana u prethodnom primjeru Funkcija – SumMinus može se pozvati unošenjem sljedećeg izraza u ćeliju radnog lista:

Zbroj Minus(10, 5, 2)

VBA procedura "Sub"

Urednik VBA shvata da je pred njim procedura Sub, kada naiđe na grupu naredbi zatvorenih između sljedećih naredbi za otvaranje i zatvaranje:

Sub...End Sub

VBA procedura "Sub": Primjer 1. Poravnanje po sredini i promjena veličine fonta u odabranom rasponu ćelija

Pogledajmo primjer jednostavne VBA procedure Sub, čiji je zadatak da promijeni formatiranje odabranog raspona ćelija. Ćelije su postavljene na centralno poravnanje (i okomito i vodoravno) i veličina fonta se mijenja u onu koju je odredio korisnik:

Sub Format_Centered_And_Sized(Opcijski iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

Ova procedura Sub izvodi radnje, ali ne vraća rezultate.

Ovaj primjer također koristi Opcijski argument iFontSize. Ako je argument iFontSize nije prosleđen u proceduru Sub, tada se njegova zadana vrijednost uzima kao 10. Međutim, ako je argument iFontSize prešao u proceduru Sub, tada će veličina fonta koju je odredio korisnik biti postavljena u odabranom rasponu ćelija.

VBA procedura “Sub”: Primjer 2. Poravnanje po sredini i primjena podebljanog na font u odabranom rasponu ćelija

Sljedeća procedura je slična onoj o kojoj smo upravo govorili, ali ovaj put, umjesto promjene veličine, primjenjuje podebljani stil fonta na odabrani raspon ćelija. Ovo je primjer postupka Sub, kojem se ne prosljeđuju argumenti:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Pozivanje "Sub" procedure u Excel VBA

Pozivanje VBA procedure "Sub" iz druge procedure

Za pozivanje VBA procedure Sub iz druge VBA procedure, morate napisati ključna riječ Zovi, naziv procedure Sub a zatim u zagradama argumenti procedure. Ovo je prikazano u primjeru ispod:

Sub main() Format poziva_Centered_And_Sized(20) End Sub

Ako je procedura Format_centrirano_i_veličinom ima više od jednog argumenata, moraju biti odvojeni zarezima. ovako:

Sub main() Poziv Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Pozivanje VBA procedure "Sub" sa radnog lista

Procedura Sub ne može se uneti direktno u ćeliju Excel radnog lista, kao što se može uraditi procedurom Funkcija, jer procedura Sub ne vraća vrijednost. Međutim, procedure Sub, koji nemaju argumente i deklarirani su kao Javno(kao što će biti pokazano kasnije) biće dostupni korisnicima radnog lista. Dakle, ako su jednostavne procedure opisane gore Sub umetnuti u modul u Visual Basic editoru, a zatim proceduru Format_centrirano_i_podebljano biće dostupan za upotrebu na Excel radnom listu i proceduri Format_centrirano_i_veličinom– neće biti dostupan jer ima argumente.

Evo jednostavnog načina da pokrenete (ili izvršite) proceduru Sub, dostupno sa radnog lista:

  • Kliknite Alt+F8(pritisnite tipku Alt i, dok ga držite pritisnutim, pritisnite taster F8).
  • Na listi makronaredbi koja se pojavi izaberite onaj koji želite da pokrenete.
  • Kliknite Izvrši(trčati)

Za izvođenje procedure Sub brzo i jednostavno, možete mu dodijeliti kombinaciju tipki. Da biste to učinili:

  • Kliknite Alt+F8.
  • Na listi makronaredbi koja se pojavi odaberite onaj kojem želite dodijeliti prečicu na tipkovnici.
  • Kliknite Opcije(Opcije) i unesite prečicu na tastaturi u dijaloški okvir koji se pojavi.
  • Kliknite OK i zatvorite dijaloški okvir Makro(Makro).

pažnja: Prilikom dodjeljivanja prečice na tipkovnici makrou, uvjerite se da se ne koristi kao standardna u Excelu (na primjer, Ctrl+C). Ako odaberete postojeću prečicu na tipkovnici, ona će biti ponovo dodijeljena makrou, a kao rezultat toga, korisnik može slučajno pokrenuti makro.

Opseg VBA procedure

Drugi dio ovog tutorijala raspravljao je o temi opsega varijabli i konstanti i uloge ključnih riječi Javno I Privatno. Ove ključne riječi se također mogu koristiti u vezi sa VBA procedurama:

Zapamtite da ako prije deklariranja VBA procedure Funkcija ili Sub ključna riječ nije umetnuta, tada je postavljeno zadano svojstvo za proceduru Javno(to jest, biće dostupan svuda u datom VBA projektu). Ovo se razlikuje od deklariranja varijabli, koje su po defaultu Privatno.

Rani izlazak iz VBA procedura “Funkcija” i “Sub”

Ako trebate prekinuti izvršavanje VBA procedure Funkcija ili Sub, bez čekanja na njegov prirodni završetak, onda postoje operatori za ovo Izlaz funkcija I Exit Sub. Upotreba ovih operatora je prikazana u nastavku koristeći primjer jednostavne procedure Funkcija, koji očekuje da će dobiti pozitivan argument za obavljanje daljnjih operacija. Ako je nepozitivna vrijednost proslijeđena proceduri, tada se daljnje operacije ne mogu izvoditi, tako da korisniku mora biti prikazana poruka o grešci i procedura mora odmah prekinuti:

Funkcija Iznos_PDV(sVAT_Stopa kao pojedinačna) Kao pojedinačni iznos_iznos PDV-a = 0 Ako sVAT_stopa<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

Imajte na umu da prije završetka procedure Funkcija – PDV_Iznos, ugrađena VBA funkcija je umetnuta u kod MsgBox, koji korisniku prikazuje iskačući prozor upozorenja.

Gotovo sav programski kod VBA modula sadržan je u procedurama dva tipa: Sub (potprogrami) i Function (funkcije). Glavni zadatak funkcije funkcije je da izračuna neku vrijednost i vrati je na tačku u kojoj se poziva funkcija funkcije.

Sintaksa procedure funkcije:

Funkcija Ime_funkcije(argumenti As) As

Function_Name = Povratna vrijednost

End Funkcija

Funkcionalne procedure se mogu koristiti u različitim izrazima.

Primjer 1

Na primjer, najjednostavnija procedura-funkcija Funkcija:

Funkcija F1(x Kao valuta) Kao valuta

Funkcija F1(x) se može koristiti u daljim proračunima (šifra modula). Procedura tipa Funkcija može se izvršiti samo ako je pozovete iz druge procedure. Da bi to uradila, procedura pozivanja mora dodeliti ime ove F1(x) nekoj promenljivoj.

Primjer 2

Na primjer, funkcija F1(x) se može koristiti u MySub() proceduri imenovanjem varijable "y" F1(x).

Dim y As Single 'Deklaracija varijable y

y = F1 (9) 'Odredite F1 (x) za vrijednost x=9

Debug.Print y 'Ispiši vrijednosti u prozoru Immediate

Funkcija F1 (x kao pojedinačna) kao pojedinačna

F1 = x^10 'Vrati vrijednost x na stepen od 10

Ovdje Funkcija F1(x)=$x^(10)$ za vrijednost $ x=9$ vraća vrijednost $3.486785E+09$ pozivnoj proceduri MySub(). Ako funkcija koja vraća vrijednost ili varijabla koja se koristi u VBA proceduri nema deklarirani tip podataka, zadani tip podataka će biti Variant.

VBA koristi i procedure funkcije i ugrađene funkcije.

Ugrađene funkcije sastoje se od dva dijela: imena (identifikatora) i argumenata. Ugrađene funkcije su gotove VBA formule koje izvode određene radnje nad izrazima i vraćaju neku vrijednost na mjestu njihovog poziva.

Funkcije vraćaju vrijednost rezultata umjesto svog imena, koje se koristi u daljnjim proračunima. Funkcije obično zahtijevaju argumente koji su napisani u zagradama odvojenim zarezima. Ali neke funkcije ne zahtijevaju argumente. Na primjer, funkcija Now(), koja ne zahtijeva argumente, vraća trenutni sistemski datum i vrijeme. Funkcije se mogu koristiti za kreiranje novih izraza ili funkcija.

Kako umetnuti funkciju u tekst programa? Da biste koristili funkciju u izrazima, morate unijeti njeno ime u VBA izraz. Da biste pozvali ugrađenu funkciju koja ne zahtijeva argumente, samo unesite njeno ime (na primjer, Now) u programski kod modula:

Sub MyDate()

Dim TD 'Deklarirajte TD varijablu

TD = Sada 'Odredite trenutni sistemski datum i vrijeme

Debug.Print TD ‘Ispiši vrijednosti u prozoru Immediate

Da biste pozvali funkciju koja zahtijeva unos jednog ili više argumenata, morate unijeti njeno ime na desnoj strani operatora dodjeljivanja s parametrima (vrijednostima argumenata) zatvorenim u zagradama. Na primjer, za pozivanje ugrađenog Function Log (N) s jednom promjenljivom N u proceduri tipa Sub, varijabli Log_N dodjeljuje se ime Log funkcije (50) sa vrijednošću argumenta 50.

Podprirodni logaritam()

Dim LogN ‘Deklarisajte varijablu LogN

Debug.Print LogN ‘Ispiši vrijednosti u prozoru Immediate

Napomena 1

Ovdje ugrađena funkcija Log(N) za vrijednost argumenta od 50 vraća vrijednost 3,91202300542815 na mjestu pozivanja Log(50) procedure poziva "Sub Natural_logarithm()". Funkcija se može pozvati ili korištenjem zasebnog VBA iskaza ili postavljanjem njenog imena sa listom vrijednosti argumenata (parametara) u formuli ili izrazu u VBA programu. VBA koristi ugniježđenje funkcija da skrati notaciju dozvoljavajući da se jedan poziv funkcije navede kao argument drugoj funkciji. U ovom slučaju, povratna vrijednost prve funkcije se koristi kao argument za sljedeću funkciju.

VBA ima veliki skup ugrađenih funkcija i rutina koje olakšavaju programiranje, a koje se mogu podijeliti u sljedeće kategorije:

  • matematički;
  • funkcije provjere tipa;
  • funkcije obrade stringova;
  • funkcija formatiranja;
  • funkcije konverzije formata;
  • funkcije datuma i vremena.

Matematičke funkcije

Matematičke funkcije uključuju:

  • Abs (x) - modul argumenta $x$;
  • Cos(x) - kosinus argumenta $x$;
  • Exp(x) - podizanje baze prirodnog logaritma na stepen $x$;
  • Log(x) - prirodni logaritam argumenta $x$;
  • Rnd - slučajni broj iz intervala;
  • Sin(x) - sinus argumenta $x$;
  • Sqr(x) - kvadratni korijen od $x$;
  • Atn(x) - arktangens od $x$;
  • Tan(x) – tangent od $x$;
  • Sgn(x) – znak za $x$.

Fix(x) i Int(x) obje funkcije odbacuju razlomak broja i vraćaju cjelobrojnu vrijednost. Razlika između ovih funkcija je za negativne vrijednosti argumenata. Int(x) vraća najbliži negativni cijeli broj manji od ili jednak x, a Fix(x) vraća najbliži negativni cijeli broj veći ili jednak x.

Funkcije provjere tipa

Evo funkcija koje određuju koji je tip varijable:

  • IsArray(x) – funkcija provjerava da li je varijabla niz;
  • IsDate(x) određuje da li je varijabla datum;
  • IsError(x) određuje da li je varijabla kod greške;
  • IsNull(x) određuje da li je varijabla prazna vrijednost;
  • IsNumeric(x) određuje da li je varijabla numerička vrijednost;
  • IsObject(x) određuje da li je varijabla objekt.

Funkcija formatiranja

Funkcija oblikovanja vraća vrijednost Variant (String) koja sadrži izraz formatiran prema sintaksi funkcije:

Format(Izraz[,Format [,Prvi dan u sedmici[,Prva sedmica u godini]]]), gdje je:

  • Izraz – obavezan argument (bilo koji važeći izraz – kombinacija ključnih riječi, operatora, varijabli i konstanti, čiji je rezultat niz, broj ili objekt);
  • Format je izborni parametar (bilo koji važeći naziv ili izraz formata definiran od strane korisnika).

Ako se imenu funkcije doda znak $, tada funkcija vraća vrijednost tipa String., a onda funkcija vraća vrijednost tipa String.

Kada kreirate vlastiti format brojeva, možete koristiti sljedeće znakove:

  • 0 – rezerviše poziciju digitalnog bita. Prikazuje cifru ili nulu. Ako broj koji se formatira ima bilo koju cifru na toj poziciji gdje postoji 0 u nizu formata, funkcija prikazuje tu cifru, onda se na toj poziciji prikazuje nula;
  • "#" - akcija ovog simbola je slična akciji 0 sa jedinom razlikom što se beznačajne nule ne prikazuju;
  • . – zadržava poziciju decimalnog separatora, određuje koliko cifara mora biti prikazano lijevo i desno od decimalnog zareza;
  • % - rezerviše procentualni prikaz broja;
  • . – razdvaja stotine od hiljada.

Funkcije konverzije formata

To uključuje:

  • Val(string) – vraća brojeve sadržane u nizu kao numeričku vrijednost odgovarajućeg tipa.
  • Str(broj) – vraća vrijednost Variant (String) koja je string reprezentacija broja.

Pored funkcija Val i Str, postoji niz funkcija za pretvaranje tipova izraza.

Funkcije obrade nizova

Među brojnim funkcijama su sljedeće:

  • Chr(code) – pretvara ASCII kod u string. Na primjer, Chr(10) izvodi novu liniju, Chr(13) izvodi vraćanje nosača;
  • Mid(string, pos[,length]) – vraća podniz stringa koji sadrži navedeni broj znakova, gdje je String izraz stringa iz kojeg se izdvaja podniz;
  • Pos – pozicija znaka u nizu od koje počinje željeni podniz;
  • Dužina – broj vraćenih znakova podniza;
  • Len(string) – vraća broj znakova u nizu.

Funkcije vremena i datuma

Vrati vrijednost Variant koja sadrži sistemski datum, trenutno vrijeme itd. Na primjer, funkcija Date vraća vrijednost koja sadrži sistemski datum.

Kreiranje prilagođene funkcije u VBA Excelu, njene sintakse i komponenti. Opis korisnički definirane funkcije i njenih argumenata. Application.MacroOptions metoda.

Prilagođena funkcija je VBA procedura koja izvodi određene proračune i vraća rezultat. Koristi se za umetanje u ćelije radnog lista programa Excel ili pozivanje iz drugih procedura.

Deklarisanje prilagođene funkcije

Sintaksa funkcije

Naziv funkcije ([ArgumentList]) [Operatori] [Naziv = izraz] [Operatori] [Naziv = izraz] Kraj funkcije

Funkcionalne komponente

  • Statički je neobavezna ključna riječ koja pokazuje da se vrijednosti varijabli deklariranih u funkciji čuvaju između poziva funkcije.
  • Ime- potrebna komponenta, naziv korisničke funkcije.
  • ArgumentList- opciona komponenta, jedna ili više varijabli koje predstavljaju argumente koji se prosljeđuju funkciji. Argumenti su zatvoreni u zagradama i odvojeni zarezima.
  • Operateri- opciona komponenta, blok operatora (instrukcije).
  • Ime = izraz- opciona* komponenta, koja dodeljuje naziv funkcije vrednosti izraza ili varijable. Tipično, vrijednost se dodjeljuje funkciji neposredno prije izlaska.
  • Izlaz funkcija- opciona komponenta, prisilni izlaz iz funkcije ako joj je već dodijeljena konačna vrijednost.

*Jedna od komponenti Ime = izraz treba smatrati obaveznim, jer ako funkciji ne dodijelite vrijednost, smisao njene upotrebe se gubi.

Feature Visibility

Vidljivost korisnički definirane funkcije određena je opcijskim javnim i privatnim ključnim riječima, koje se mogu specificirati prije izraza Funkcija (ili Static, ako se koristi).

Ključna riječ Javno označava da će funkcija biti dostupna za pozivanje iz drugih procedura u svim otvorenim modulima Excel radne knjige. Funkcija deklarirana kao Javno, pojavljuje se u dijalogu Čarobnjak za funkcije.

Ključna riječ Privatno označava da će funkcija biti dostupna za pozivanje iz drugih procedura samo unutar programskog modula u kojem se nalazi. Funkcija deklarirana kao Privatno, se ne pojavljuje u dijaloškom okviru Čarobnjak za funkcije, ali se može ručno unijeti u ćeliju.

Ako ključna riječ Public ili Private nije specificirana, smatra se da je funkcija po defaultu deklarirana kao javna.

Da biste osigurali da je prilagođena funkcija uvijek dostupna u svim otvorenim Excel radnim knjigama, sačuvajte je u Nedeklariranoj vidljivosti ili kao Javna. Ali ako planirate da prenesete radnu svesku sa korisnički definisanom funkcijom na drugi računar, kod funkcije mora biti u programskom modulu prenete radne sveske.

Primjer prilagođene funkcije

Kao primjer, razmotrit ćemo jednostavnu korisničku funkciju, čiji ćemo opis dodati u sljedećem paragrafu. Funkcija se zove “Division” i deklarirana je sa tipom podataka Variant, budući da njena povratna vrijednost može biti ili broj ili tekst. Argumenti Dividend i Divisor funkcije također su deklarirani kao Variants, budući da Excel ćelije mogu sadržavati numeričke vrijednosti različitih tipova, a IsNumeric funkcija također provjerava različite tipove podataka i zahtijeva da se njeni argumenti deklariraju kao Variants.

Funkcija Podjela (Dividend kao varijanta, djelilac kao varijanta) Kao varijanta Ako je brojčani(dividend) = False ili je brojčani(djelitelj) = netačan Tada Division = "Greška: Dividenda i djelitelj moraju biti brojevi!" Izlaz iz funkcije ElseIf Divisor = 0 Then Division = "Greška: deljenje sa nulom!" Izlaz iz funkcije Else Division = Dividend / Divisor End If End Funkcija

Ova funkcija dijeli vrijednosti dvije ćelije u Excel radnom listu. Prije podjele, provjeravaju se dva bloka uslova:

  • Ako dividenda ili djelitelj nije broj, funkcija vraća vrijednost: "Greška: Dividenda i djelitelj moraju biti brojevi!", a funkcija je prisiljena da izađe pomoću naredbe Exit Function.
  • Ako je djelitelj nula, funkcija vraća vrijednost: “Greška: podjela na nulu!”, a funkcija je prisiljena da izađe pomoću naredbe Exit Function.

Ako uvjeti koji se provjeravaju nisu ispunjeni (vrijednost se vraća False), brojevi se dijele i funkcija vraća kvocijent (rezultat dijeljenja).

Ovu funkciju možete kopirati u svoj standardni modul i ona će postati dostupna u odjeljku „Korisnički definirano“ čarobnjaka za funkcije. Pokušajte da umetnete funkciju Division u ćeliju radnog lista pomoću čarobnjaka i eksperimentirajte s njom.

Funkcija “Division” nema praktično značenje, ali dobro pokazuje kako se prilagođene funkcije deklariraju, kreiraju i rade u VBA Excel-u. To će također pomoći da se pokaže kako dodati opise funkcijama i argumentima. Možete se upoznati s punom korisničkom funkcijom.

Dodavanje opisa funkcije

Na listi funkcija koju prikazuje Čarobnjak, nemoguće je dodati ili urediti njihov opis. Lista makronaredbi vam omogućava da dodate opise procedurama, ali ne sadrži funkcije. Problem se rješava na sljedeći način:

  • Pokrenite Čarobnjak za funkcije, pogledajte kako se prikazuje naziv željene funkcije i zatvorite ga.
  • Otvorite ga i unesite naziv prilagođene funkcije u polje „Naziv makroa“.
  • Kliknite na dugme „Opcije“ i dodajte ili uredite opis u prozoru koji se otvori.
  • Kliknite na dugme "OK", a zatim u prozoru liste makroa - "Otkaži". Opis je spreman!

Dodavanje opisa na primjeru funkcije “Division”:

Opis funkcije Division u dijaloškom okviru Funkcija Argumenti čarobnjaka funkcija:


Koristeći prozor liste makroa, možete dodati opis same funkcije, ali ne i njene argumente. Ali to se može učiniti pomoću metode Application.MacroOptions.

Application.MacroOptions metoda

Metoda Application.MacroOptions vam omogućava da dodate opis prilagođenoj funkciji, dodelite prečicu na tastaturi, navedete kategoriju, dodate opise argumenata i dodate ili promenite druge opcije. Pogledajmo najčešće korištene karakteristike ove metode.

Primjer koda s metodom Application.MacroOptions:

Sub SubroutineName() Application.MacroOptions _ Macro:="FunctionName", _ Description:="Opis funkcije", _ Category:="Naziv kategorije", _ ArgumentDescriptions:=Niz("Opis 1", "Opis 2", " Opis 3", ...) End Sub

  • Ime potprograma- bilo koje jedinstveno ime pogodno za procedure imenovanja.
  • NameFunction- naziv funkcije čiji se parametri dodaju ili mijenjaju.
  • Opis funkcije- opis funkcije koja se dodaje ili mijenja.
  • Naziv kategorije- naziv kategorije u koju će se funkcija smjestiti. Ako nedostaje parametar Category, prilagođena funkcija će biti upisana u zadani odjeljak, User Defined. Ako navedeni naziv kategorije odgovara jednom od imena na standardnoj listi, funkcija će biti upisana u njega. Ako se takav naziv kategorije ne nalazi na listi, kreirat će se novi odjeljak sa ovim imenom i funkcija će biti smještena u njega.
  • "Opis 1", "Opis 2", "Opis 3", ...- opisi argumenata redoslijedom kojim se pojavljuju u deklaraciji korisničke funkcije.

Ova rutina se pokreće jednom, a zatim se može izbrisati ili koristiti kao predložak za podešavanje parametara drugih prilagođenih funkcija.

Sada, koristeći metodu Application.MacroOptions, pokušajmo promijeniti opis korisnički definirane funkcije "Division" i dodati opise argumenata.

Sub ChangeDescription() Application.MacroOptions _ Macro:="Division", _ Description:="Opis funkcije Division promijenjen metodom Application.MacroOptions", _ ArgumentDescriptions:=Niz("- bilo koja numerička vrijednost", "- numerički vrijednost različita od nule") Kraj pod

Nakon što jednom pokrenemo ovu potprogramu, dobijamo sljedeći rezultat:


Metoda Application.MacroOptions ne radi u , ali i ovdje možete pronaći rješenje. Dodajte opise prilagođenim funkcijama i njihovim argumentima u običnu radnu knjigu programa Excel, a zatim izvezite modul s funkcijama u bilo koji direktorij na vašem tvrdom disku i uvezite odatle u Ličnu radnu knjigu makroa. Svi opisi će biti sačuvani.

Dim nRezult As Integer

nMult1 = CInt(InputBox("Unesite prvi broj: ")) nMult2 = CInt(InputBox("Unesite drugi broj: ")) nResult = fMultiply(nMult1, nMult2)

Selection.InsertAfter nResult Selection.Collapse wdCollapseEnd

4. Za komentiranje koda AutoNew() , označite sav kod za ovu proceduru (uključujući javni pod AutoNew() i End Sub ) i kliknite na dugme Blok komentara na traci sa alatkama Uredi.

3.9. Ugrađene VBA jezičke funkcije

3.9.1. Šta su ugrađene funkcije

IN Programski jezik VBA nudi nekoliko desetinaugrađene funkcije. Dostupni su u bilo kom programu na VBA jeziku i nije bitno u kom se softverskom proizvodu nalazimo - Excel, Word, Access ili, na primer, AutoCAD. Koriste se vrlo aktivno, a u mnogim situacijama bez njih se ne može. Profesionalni programeri ih koriste potpuno automatski, ali bih savjetovao običnim korisnicima da provedu nekoliko sati upoznajući ih, jer bez poznavanja ovih funkcija neće moći efikasno da rade u VBA. Dodatni argument u prilog njihovom proučavanju je da se gotovo identičan skup funkcija nalazi u redovnom Visual Basicu i VBScript-u, a mnoge od ovih funkcija sa istim imenima i sintaksom nalaze se u drugim programskim jezicima - C++, Delphi, Java , JavaScript, itd. str.

IN U VBA pomoći, ugrađene funkcije su grupisane po slovu (slika 3.2).

Mnogi polaznici kurseva postavili su pitanje: postoji li pomoć za ove funkcije na ruskom? Nažalost, nisam uspio pronaći takve informacije, pa ću pokušati dati kratku referencu u ovoj knjizi. Zatim ćemo govoriti o većini aktivno korištenih funkcija VBA jezika (matematičke funkcije, kao što su kosinus ili tangenta, koje se gotovo nikada ne koriste u praktičnom radu, a nećemo razmatrati ni finansijske funkcije). Da bi se uštedio prostor, neće biti data potpuna sintaksa funkcija: glavna stvar je razumjeti što svaka funkcija radi i u kojim situacijama se može koristiti.

Funkcije u sljedećim odjeljcima grupirane su prema funkcionalnosti. Ako trebate pronaći informacije samo po imenu funkcije, možete koristiti indeks na kraju knjige.

Rice. 3.2. Pomoć za ugrađene funkcije

3.9.2. Funkcije konverzije i provjere tipa podataka

U VBA programima je vrlo uobičajeno pretvaranje vrijednosti iz jednog tipa podataka u drugi. Evo nekoliko tipičnih situacija kada to morate učiniti:

pretvaranje iz vrijednosti niza u numeričku vrijednost kada prima vrijednost od korisnika putem InputBox() ;

Pretvaranje vrijednosti datuma/vremena u vrijednost stringa kada trebamo dosljedno prikazati datum ili vrijeme bez obzira na regionalne postavke na računarima korisnika;

pretvaranje vrijednosti iz stringa u datum/vrijeme za korištenje posebnih funkcija datuma/vremena.

Za pretvaranje tipova podataka najčešće se koriste funkcije čije se ime sastoji od prefiksa “C” (od riječi Convert) i naziva tipa podataka. Lista ovih funkcija je sljedeća: CBool(), CByte(), CCur(), CDate(),

CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVERr().

Možete vidjeti što se na kraju dogodilo pomoću funkcije TypeName(), koja vraća naziv korištenog tipa podataka, na primjer:

nVar1 = CInt(InputBox("Unesite vrijednost")) MsgBox TypeName(nVar1)

U ovom slučaju, ova funkcija će vratiti "Integer".

Osim toga, postoji nekoliko drugih funkcija korisnih za konverziju.

Str() - omogućava vam da pretvorite numeričku vrijednost u string. Radi skoro istu stvar kao CStr(), ali umeće razmak ispred pozitivnih brojeva.

Val() - "izvlači" samo numeričku vrijednost iz mješavine brojeva i slova. U ovom slučaju, funkcija čita podatke s lijeva na desno i zaustavlja se na prvoj nenumeričkoj vrijednosti (jedina dozvoljena nenumerička vrijednost je tačka, koja će odvojiti cijeli broj od razlomka). Vrlo je zgodno kada zapisujemo mjerne jedinice ili valutu isprepletene brojčanim podacima.

IsNumeric() i IsDate() - provjerite konzistentnost vrijednosti kako ne bi bilo grešaka tokom konverzije. Da biste provjerili usklađenost s posebnim vrijednostima, možete koristiti funkcije IsArray(), IsEmpty(),

IsError(), IsMissing(), IsNull() i IsObject() . Sve ove funkcije se vraćaju

Tačno ili Netačno u zavisnosti od rezultata provjere vrijednosti koja im je proslijeđena.

Hex() i Oct() - Pretvorite decimalne podatke u string prikaz heksadecimalnih i oktalnih vrijednosti.

3.9.3. Funkcije niza

Ovo su najčešće korištene funkcije. Oni su stalno potrebni i morate ih dobro poznavati.

Asc() - ova funkcija vam omogućava da vratite numerički kod za proslijeđeni znak. Na primjer, Asc("D") će vratiti 68. Ova funkcija je korisna za određivanje sljedećeg ili prethodnog slova. Obično se koristi zajedno sa funkcijom Chr(), koja izvodi inverznu operaciju - vraća znak prema njegovom numeričkom kodu. Na primjer, ovaj kod u Excelu vam omogućava da uzastopno pišete slova ruske abecede od A do U u ćelijama A1 do A20:

Dim n, nCharCode kao cijeli broj n = 1

nCharCode = Asc("A") Uradi Dok n<= 20

ActiveWorkbook.ActiveSheet.Range("A" & n).Value = Chr(nCharCode)

VBA sintaksa i programske konstrukcije

nCharCode = nCharCode + 1 petlja

Varijante ove funkcije su AscB() i AscW(). AscB() vraća samo prvi bajt numeričkog koda za znak, dok AscW() vraća Unicode kod za znak.

Chr() - vraća znak po njegovom numeričkom kodu. Osim što se koristi zajedno s funkcijom Asc() (pogledajte prethodni primjer), ne možete bez nje u još jednoj situaciji: kada trebate ispisati uslužni karakter. Na primjer, u Wordu trebamo upisati vrijednost "Gazprom" (pod navodnicima). Navod je uslužni znak i pokušaj korištenja niza kao što je:

Selection.Text = ""Gazprom""

će rezultirati sintaksičkom greškom. I ovako će sve biti u redu:

Selection.Text = Chr(34) & "Gazprom" & Chr(34)

Postoje varijante ove funkcije - ChrB() i ChrW(). One rade slično kao iste opcije za funkciju Asc().

InStr() i InStrRev() su neke od najpopularnijih funkcija. Omogućava vam da otkrijete niz znakova u tijelu string varijable i vratite njenu poziciju. Ako sekvenca nije pronađena, onda se vraća 0 Funkcija InStr() pretražuje od početka niza, a InStrRev() pretražuje od kraja.

Left() , Right() , Mid() - omogućavaju vam da uzmete broj znakova koji navedete iz postojeće varijable stringa na lijevoj, desnoj ili sredini, respektivno.

Len() - vraća broj znakova u nizu (dužina niza). Često se koristi sa petljama, operacijama zamjene itd.

LCase() i UCase() - pretvaraju niz u mala i velika slova, respektivno. Često se koristi za pripremu vrijednosti za poređenje kada veliki i veliki slova nisu važni (prezimena, nazivi kompanija, gradova itd.).

LSet() i RSet() - ispunite jednu varijablu simbolima druge bez promjene njene dužine (lijevo i desno, respektivno). Dodatni znakovi su odsječeni, a razmaci se zamjenjuju za znakove koji nedostaju.

LTrim() , RTrim() , Trim() - uklanjaju razmake s lijeve, desne ili i lijeve i desne strane.

Zamijeni() - zamjenjuje jedan niz znakova drugim u nizu.

Razmak() i String() - vraćaju niz broja razmaka ili znakova koje navedete. Obično se koristi za formiranje

Uz VBA, možete kreirati prilagođenu funkciju koja se može koristiti u radnim listovima baš kao i obične funkcije.

Ovo je korisno kada postojeće Excel funkcije nisu dovoljne. U takvim slučajevima možete kreirati vlastitu korisnički definiranu funkciju (UDF) kako biste zadovoljili svoje specifične potrebe.

U ovom vodiču govorit ću o kreiranju i korištenju prilagođenih funkcija u VBA.

Šta je procedura funkcije u VBA?

Funkcionalna procedura je VBA kod koji izvodi proračune i vraća vrijednost (ili niz vrijednosti).

Koristeći proceduru Funkcija, možete kreirati funkciju koju možete koristiti u radnom listu (kao bilo koja obična Excel funkcija kao što je SUM ili VLOOKUP).

Nakon što kreirate funkciju funkcije koristeći VBA, možete je koristiti na tri načina:

  1. Kao formula u radnom listu, gdje može uzeti argumente kao ulaz i vratiti vrijednost ili niz vrijednosti.
  2. Kao dio vašeg VBA rutinskog koda ili drugog koda funkcije.
  3. U uslovnom formatu

Iako radni list već ima preko 450 ugrađenih Excel funkcija, možda će vam trebati prilagođena funkcija ako:

  • Ugrađene funkcije ne mogu učiniti ono što želite. U tom slučaju možete kreirati prilagođenu funkciju na osnovu vaših zahtjeva.
  • Ugrađene funkcije mogu obaviti posao, ali formula je duga i komplikovana. U ovom slučaju možete kreirati prilagođenu funkciju koja je laka za čitanje i korištenje

Imajte na umu da prilagođene funkcije kreirane pomoću VBA mogu biti znatno sporije od ugrađenih funkcija. Stoga su najprikladniji za situacije u kojima ne možete dobiti rezultat pomoću ugrađenih funkcija.

Funkcija protiv potprograma u VBA

“Podrutina” vam omogućava da izvršite skup koda dok “Funkcija” vraća vrijednost (ili niz vrijednosti).

Na primjer, ako imate listu brojeva (i pozitivnih i negativnih) i želite identificirati negativne brojeve, evo što možete učiniti s funkcijom i potprogramom.

Rutina može proći kroz svaku ćeliju u rasponu i može istaknuti sve ćelije koje u sebi imaju negativnu vrijednost. U ovom slučaju, rutina završava mijenjanjem svojstava objekta raspona (promjenom boje ćelija).

Sa prilagođenom funkcijom, možete je koristiti na jednom stupcu i može vratiti TRUE ako je vrijednost u ćeliji negativna i FALSE ako je pozitivna. Sa funkcijom ne možete promijeniti svojstva objekta. To znači da ne možete promijeniti boju ćelije pomoću same funkcije (međutim, to možete učiniti korištenjem uvjetnog oblikovanja s prilagođenom funkcijom).

Kada kreirate korisnički definiranu funkciju (UDF) koristeći VBA, možete koristiti funkciju na radnom listu kao i bilo koju drugu funkciju. Više o tome ću govoriti u odjeljku Različiti načini korištenja UDF-ova u Excel-u.

Kreiranje jednostavne korisnički definirane funkcije u VBA

Dozvolite mi da kreiram jednostavnu korisnički definisanu funkciju u VBA i da vam pokažem kako funkcioniše.

Kod u nastavku kreira funkciju koja izdvaja numeričke dijelove iz alfanumeričkog niza.

Funkcija GetNumeric(CellRef As String) kao Long Dim StringLength Kao cijeli broj StringLength = Len(CellRef) Za i = 1 do StringLength Ako je IsNumeric(Mid(CellRef, i, 1)) Tada je rezultat = Result & Mid(CellRef, i, 1) Sljedeće i GetNumeric = Funkcija kraja rezultata

Ako imate gornji kod u modulu, možete koristiti ovu funkciju u radnoj knjizi.

Ispod je kako se ova funkcija, GetNumeric, može koristiti u Excelu.

Sada, prije nego što vam kažem kako je ova funkcija kreirana u VBA-u i kako funkcionira, morate znati nekoliko stvari:

  • Kada kreirate funkciju u VBA, ona postaje dostupna u cijeloj radnoj knjizi, baš kao i svaka druga obična funkcija.
  • Kada unesete ime funkcije praćeno znakom jednakosti, Excel će vam pokazati naziv funkcije na listi odgovarajućih funkcija. U gornjem primjeru, kada sam unio =Get, Excel mi je pokazao listu koja je sadržavala moju prilagođenu funkciju.

Mislim da je ovo dobar primjer gdje možete koristiti VBA za kreiranje funkcije jednostavne za korištenje u Excelu. Isto možete učiniti i sa formulom (kao što je prikazano u ovom vodiču), ali postaje komplikovano i teško razumljivo. Sa ovim UDF-om trebate proslijediti samo jedan argument i dobit ćete rezultat.

Anatomija korisnički definirane funkcije u VBA

U gornjem odeljku dao sam vam kod i pokazao vam kako UDF funkcija radi na radnom listu.

Sada zaronimo i vidimo kako je ova funkcija kreirana. Trebali biste postaviti donji kod u modul u VB Editoru. Ovu temu pokrivam u odjeljku

Funkcija GetNumeric(CellRef As String) as Long " Ova funkcija dohvaća numerički dio iz niza Dim StringLength As Integer StringLength = Len(CellRef) Za i = 1 do StringLength If IsNumeric(Mid(CellRef, i, 1)) Tada je rezultat = Rezultat & Mid(CellRef, i, 1) Sljedeći i GetNumeric = Funkcija Kraj rezultata

Prvi red koda počinje riječju "Funkcija".

Ova riječ govori VBA da je naš kod funkcija (a ne potprogram). Iza riječi Funkcija slijedi naziv funkcije - GetNumeric. Ovo je ime koje ćemo koristiti na listu za korištenje ove funkcije.

  • Ime funkcije ne smije sadržavati razmake. Također, ne možete imenovati funkciju ako je u sukobu s imenom reference ćelije. Na primjer, ne možete nazvati funkciju ABC123 jer se ona također odnosi na ćeliju u Excel radnom listu.
  • Ne biste trebali svojoj funkciji dati isto ime kao postojeća funkcija. Ako to učinite, Excel će dati prednost ugrađenoj funkciji.
  • Možete koristiti podvlačenje ako želite razdvojiti riječi. Na primjer, Get_Numeric je valjano ime

Nakon imena funkcije slijede neki argumenti u zagradama. Ovo su argumenti koje naša funkcija treba od korisnika. Ovo su kao argumenti koje moramo pružiti ugrađenim funkcijama Excela. Na primjer, funkcija COUNTIF ima dva argumenta (opseg i kriterij).

Argumenti moraju biti navedeni u zagradama.

U našem primjeru postoji samo jedan argument - CellRef.

Također je korisno odrediti koji argument funkcija očekuje. U ovom primjeru, budući da ćemo prosljeđivati ​​referencu ćelije funkciji, možemo specificirati argument kao tip "Range". Ako ne navedete tip podataka, VBA će ga tretirati kao opciju (što znači da možete koristiti bilo koji tip podataka).


Ako imate više od jednog argumenata, isti možete navesti u zagradama, odvojenim zarezima. Kasnije u ovom vodiču ćemo vidjeti kako koristiti više argumenata u prilagođenoj funkciji.

Imajte na umu da je funkcija specificirana kao tip podataka "String". Ovo će reći VBA da će rezultat formule imati tip podataka String.

Ovdje mogu koristiti numerički tip podataka (kao što je Long ili Double), ali to će ograničiti raspon brojeva koji se vraćaju. Ako imam niz od 20 brojeva koji trebam izdvojiti iz zajedničkog niza, deklariranje funkcije kao Long ili Double će izazvati grešku (pošto će broj biti izvan opsega). Tako sam sačuvao izlazni tip podataka funkcije kao String.


Drugi red koda, onaj zeleni koji počinje apostrofom, je komentar. Kada čita kod, VBA ignoriše ovu liniju. Ovo možete koristiti za dodavanje opisa ili detalja o kodu.


Treći red koda deklarira varijablu StringLength kao tip podataka Integer. Ovo je varijabla u kojoj pohranjujemo vrijednost dužine stringa, koja se analizira pomoću formule.

Četvrti red deklarira varijablu Rezultat kao tip podataka String. Ovo je varijabla u kojoj ćemo izdvojiti brojeve iz alfanumeričkog niza.


Peti red dodeljuje dužinu niza u ulaznom argumentu varijabli "StringLength". Imajte na umu da se "CellRef" odnosi na argument koji će dati korisnik kada koristi formulu na radnom listu (ili kada je koristi u VBA - što ćemo vidjeti kasnije u ovom vodiču).


Šesti, sedmi i osmi red su dio For Next petlje. Petlja se izvršava onoliko puta koliko ima znakova u ulaznom argumentu. Ovaj broj je specificiran od strane LEN funkcije i dodijeljen varijabli "StringLength".

Dakle, petlja ide od "1 do Stringlength".

Unutar petlje, naredba IF ispituje svaki znak u stringu i, ako je numerički, dodaje taj numerički znak promenljivoj Result. Da bi to uradio, koristi funkciju MID u VBA.


Drugi posljednji red koda dodjeljuje vrijednost rezultata funkciji. Ova linija koda osigurava da funkcija vrati vrijednost "Rezultat" natrag u ćeliju (odakle je pozvana).


Posljednja linija koda je End Function. Ovo je obavezna linija koda koja govori VBA da se kod funkcije završava ovdje.


Gornji kod objašnjava različite dijelove tipične korisnički definirane funkcije kreirane u VBA. U sljedećim odjeljcima ćemo dublje zaroniti u ove elemente i vidjeti različite načine za izvođenje VBA funkcije u Excelu.

Argumenti u prilagođenoj funkciji u VBA

U gornjim primjerima, gdje smo kreirali prilagođenu funkciju za dobivanje numeričkog dijela alfanumeričkog niza (GetNumeric), funkcija je dizajnirana da prima jedan argument.

U ovom odeljku ću pokriti kako kreirati funkcije bez argumenata za funkcije koje uzimaju više argumenata (obaveznih i opcionih).

Kreiranje funkcije u VBA bez ikakvih argumenata

U Excel radnom listu imamo nekoliko funkcija koje ne uzimaju argumente (npr. RAND, DANAS, SADA).

Ove funkcije ne ovise o ulaznim argumentima. Na primjer, funkcija TODAY vraća trenutni datum, a RAND funkcija vraća nasumični broj između 0 i 1.

Istu funkciju možete kreirati u VBA.

Ispod je kod koji će vam dati naziv datoteke. Ne prihvaća nikakve argumente jer rezultat koji treba vratiti ne ovisi ni o jednom argumentu.

Gornji kod definira rezultat funkcije kao tip podataka String (želimo ime datoteke kao rezultat, što je niz).

Ova funkcija dodjeljuje vrijednost "ThisWorkbook.Name" funkciji, koja se vraća kada se funkcija koristi na radnom listu.

Ako je datoteka sačuvana, vraća ime sa ekstenzijom datoteke, u suprotnom samo daje ime.

Međutim, postoji jedan problem sa gore navedenim.

Ako se promijeni naziv datoteke, neće se automatski ažurirati. Obično se funkcija ažurira kada se promijene ulazni argumenti. Ali budući da nema argumenata za ovu funkciju, funkcija se ne izračunava ponovo (čak i ako promijenite naziv radne knjige, zatvorite je, a zatim je ponovo otvorite).

Ako želite, možete prisilno izvršiti ponovno izračunavanje koristeći prečicu na tipkovnici - Control + Alt + F9.

Da bi se formula ponovo izračunala kad god dođe do promjene na radnom listu, potreban vam je red koda koji ide uz nju.

Kôd u nastavku uzrokuje da se funkcija ponovo izračuna kad god dođe do promjene na radnom listu (baš kao i druge slične funkcije radnog lista kao što su DANAS ili RAND funkcija).

Funkcija Ime radne knjige() kao niz Application.Volatile True WorkbookName = ThisWorkbook.Name Kraj funkcije

Sada ako promijenite naziv radne knjige, ova funkcija će se ažurirati svaki put kada dođe do bilo kakve promjene u tabeli ili kada ponovo otvorite tu radnu svesku.

Kreiranje funkcije u VBA sa jednim argumentom

U jednom od gornjih odjeljaka, već smo vidjeli kako kreirati funkciju koja uzima samo jedan argument (gore opisana GetNumeric funkcija).

Kreirajmo još jednu jednostavnu funkciju koja uzima samo jedan argument.

Funkcija kreirana pomoću koda ispod pretvara referentni tekst u velika slova. Sada već imamo funkciju za ovo u Excelu, a ova funkcija vam samo pokazuje kako funkcionira. Ako to trebate učiniti, bolje je koristiti ugrađenu UPPER funkciju.

Funkcija ConvertToUpperCase(CellRef As Range) ConvertToUpperCase = UCase(CellRef) End Funkcija

Ova funkcija koristi funkciju UCase u VBA za promjenu vrijednosti varijable CellRef. Zatim dodjeljuje vrijednost funkciji ConvertToUpperCase.

Pošto ova funkcija uzima argument, ovdje ne moramo koristiti Application.Volatile dio. Čim se argument promijeni, funkcija će se automatski ažurirati.

Kreiranje funkcije u VBA sa više argumenata

Baš kao i funkcije radnog lista, možete kreirati funkcije u VBA koje uzimaju više argumenata.

Kod u nastavku će kreirati funkciju koja će izdvojiti tekst prije navedenog graničnika. Potrebna su dva argumenta - referenca ćelije sa tekstualnim nizom i separator.

Funkcija GetDataBeforeDelimiter(CellRef As Range, Delim As String) kao String Dim Result As String Dim DelimPosition kao cijeli broj DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 rezultat = Left(CellRef, Delim kao string) Dobiti kraj funkcije DelimPosition

Kada trebate koristiti više od jednog argumenta u korisnički definiranoj funkciji, sve argumente možete staviti u zagrade, odvojene zarezom.

Imajte na umu da za svaki argument možete specificirati tip podataka. U gornjem primjeru, "CellRef" je deklariran kao tip podataka Range, a "Delim" je deklariran kao tip podataka String. Ako ne navedete tip podataka, VBA ga smatra varijantnim podacima.

Kada koristite gornju funkciju u radnom listu, morate dati referencu ćelije koja ima tekst kao prvi argument i znak(ove) u dvostrukim navodnicima kao dvostruki navodnik.

Zatim provjerava položaj graničnika pomoću funkcije INSTR u VBA. Ova pozicija se zatim koristi za izdvajanje svih znakova prije graničnika (koristeći funkciju LIJEVO).

Konačno, dodeljuje rezultat funkciji.

Ova formula je daleko od savršene. Na primjer, ako unesete graničnik koji se ne nalazi u tekstu, pojavit će se greška. Sada možete koristiti funkciju IFERROR na radnom listu da biste se riješili grešaka, ili možete koristiti kod ispod koji vraća cijeli tekst kada ne može pronaći separator.

Funkcija GetDataBeforeDelimiter(CellRef As Range, Delim As String) kao String Dim Result As String Dim DelimPosition kao cijeli broj DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Ako DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Ovu funkciju možemo dodatno optimizirati.

Ako unesete tekst (od kojeg želite izdvojiti dio prije graničnika) direktno u funkcije, to će rezultirati greškom. Hajde... probaj!

Ovo se dešava kada smo naveli "CellRef" kao tip podataka opsega.

Ili ako želite da separator bude u ćeliji i koristite referencu ćelije umjesto da je čvrsto kodirate u formuli, ne možete to učiniti s gornjim kodom. To je zato što je Delim deklariran kao tip podataka niza.

Ako želite da funkcija ima fleksibilnost da prihvati direktan unos teksta ili reference na ćelije od korisnika, morate ukloniti deklaraciju tipa podataka. Ovo će stvoriti argument kao alternativni tip podataka koji može prihvatiti argumente bilo kojeg tipa i obraditi ih.

Kod u nastavku će to učiniti:

Funkcija GetDataBeforeDelimiter(CellRef, Delim) Kao niz zatamnjenja rezultat kao string Dim DelimPosition kao cijeli broj DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Ako DelimPosition< 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

Kreiranje funkcije u VBA sa opcionim argumentima

Excel ima mnogo funkcija, od kojih su neke opcione.

Na primjer, legendarna funkcija VLOOKUP ima 3 obavezna argumenta i jedan opcijski argument.


Opcioni argument, kao što ime sugeriše, nije obavezan. Ako ne unesete jedan od potrebnih argumenata, vaša funkcija će vam dati grešku, ali ako ne unesete neobavezni argument, vaša funkcija će raditi.

Ali neobavezni argumenti nisu beskorisni. Omogućuju vam da birate između niza opcija.

Na primjer, u funkciji VLOOKUP, ako ne navedete četvrti argument, VLOOKUP izvodi grubo podudaranje, a ako navedete posljednji argument kao FALSE (ili 0), onda izvodi točno podudaranje.

Zapamtite da neobavezni argumenti uvijek moraju doći iza svih potrebnih argumenata. Ne možete imati dodatne argumente na početku.

Sada da vidimo kako kreirati funkciju u VBA sa opcionim argumentima.

Funkcija sa samo opcijskim argumentom

Koliko ja znam, ne postoji ugrađena funkcija koja uzima samo neobavezne argumente (možda griješim, ali ne mogu se sjetiti nijedne takve funkcije).

Ali možemo ga napraviti sa VBA.

Ispod je kod za funkciju koja će vam dati trenutni datum u formatu dd-mm-gggg ako ne unesete nijedan argument (tj. ostavite ovo polje prazno), i u formatu "dd mmmm, gggg" ako unesete nešto - ili kao argument (tj. bilo šta da argument nije prazan).

Funkcija CurrDate(Opcionalno fmt kao varijanta) Dim Rezultat ako nedostaje(fmt) Zatim CurrDate = Format(Datum, "dd-mm-yyyy") Inače CurrDate = Format(Datum, "dd mmmm, gggg") Kraj ako Kraj funkcija

Imajte na umu da gornja funkcija koristi IsMissing da provjeri nedostaje li argument ili ne. Da biste koristili funkciju IsMissing, opcijski argument mora biti varijantni tip podataka.

Gornja funkcija radi bez obzira na to što unesete kao argument. U kodu samo provjeravamo da li je opcioni argument specificiran ili ne.

Ovo možete učiniti pouzdanijim uzimanjem samo određenih vrijednosti kao argumenata i prikazivanjem greške u suprotnom (kao što je prikazano u kodu ispod).

Funkcija CurrDate(Opcionalno fmt kao varijanta) Dim Rezultat Ako nedostaje(fmt) Zatim CurrDate = Format(Datum, "dd-mm-yyyy") ElseIf fmt = 1 Zatim CurrDate = Format(Datum, "dd mmmm, yyyy") Drugo CurrDate = CVERr(xlErrValue) End If End Funkcija

Gornji kod kreira funkciju koja prikazuje datum u formatu "dd-mm-gggg" ako nije dat argument i u formatu "dd mmmm, gggg" ako je argument 1. U svim ostalim slučajevima, greška je bačen.

Funkcija sa obaveznim i opcionim argumentima

Već smo vidjeli kod koji izdvaja numerički dio iz stringa.

Pogledajmo sada sličan primjer koji uzima i obavezne i neobavezne argumente.

Kod u nastavku kreira funkciju koja izdvaja dio teksta iz niza. Ako je opcijski argument TRUE, rezultat daje velikim slovima, a ako je opcijski argument FALSE ili izostavljen, daje rezultat kakav jest.

Funkcija GetText(CellRef As Range, Optional TextCase = False) Kao Niz Dim StringLength Kao Integer Dim Rezultat Kao String StringLength = Len(CellRef) Za i = 1 do StringLength Ako nije (IsNumeric(Mid(CellRef, i, 1))) Rezultat = Rezultat & Mid(CellRef, i, 1) Sljedeći i Ako TextCase = True, onda Result = UCase(Result) GetText = Funkcija Kraj rezultata

Imajte na umu da smo u gornjem kodu inicijalizirali vrijednost "TextCase" na False (pogledajte zagrade u prvom redu).

Time smo osigurali da opcijski argument počinje sa zadanom vrijednošću, koja je FALSE. Ako korisnik navede vrijednost kao TRUE, funkcija vraća tekst velikim slovima, a ako korisnik specificira opcijski argument kao FALSE ili ga preskoči, onda vraćeni tekst ostaje kakav jeste.

Kreiranje funkcije u VBA sa nizom kao argumentom

Do sada smo vidjeli primjere kreiranja funkcije sa opcionim/obaveznim argumentima, gdje su ti argumenti bili jedna vrijednost.

Također možete kreirati funkciju koja može uzeti niz kao argument. Postoje mnoge funkcije u Excel funkcijama radnog lista koje uzimaju argumente niza kao što su SUM, VLOOKUP, SUMIF, COUNTIF, itd.

Ispod je kod koji kreira funkciju koja daje zbir svih parnih brojeva u određenom rasponu ćelija.

Funkcija AddEven(CellRef as Range) Zatamni ćeliju kao raspon za svaku ćeliju u CellRef Ako je Numerički(Cell.Value) Onda ako je Cell.Value Mod 2 = 0 Tada Rezultat = Rezultat + Cell.Value End Ako Kraj Ako je sljedeća ćelija AddEven = Kraj rezultata Funkcija

Ovu funkciju možete koristiti u radnom listu i odrediti raspon ćelija koje uzimaju brojeve kao argument. Funkcija će vratiti jednu vrijednost - zbir svih parnih brojeva (kao što je prikazano ispod).


U gornjoj funkciji, umjesto jedne vrijednosti, dali smo niz (A1:A10). Da bi ovo funkcioniralo, morate biti sigurni da vaš tip podataka argumenta može prihvatiti niz.

U gornjem kodu sam naveo argument CellRef kao Range (koji može uzeti niz kao ulaz). Ovdje također možete koristiti varijantni tip podataka.

Kod ima petlju For Each koja prolazi kroz svaku ćeliju i provjerava da li broj nije. Ako nije, ništa se ne događa i prelazi u sljedeću ćeliju. Ako je u pitanju broj, provjerava da li je paran ili ne (pomoću funkcije MOD).

Na kraju se zbrajaju svi parni brojevi i zbroj se vraća funkciji.

Kreiranje funkcije s neograničenim brojem argumenata

Kada kreirate neke funkcije u VBA-u, možda nećete znati tačan broj argumenata koje korisnik želi dati. Stoga morate kreirati funkciju koja može uzeti onoliko argumenata koliko je potrebno i koristiti ih za vraćanje rezultata.

Primjer takve funkcije radnog lista je funkcija SUM. Možete dati više argumenata (kao što je ovaj):

= ZBIR (A1, A2: A4, B1: B20)

Gornja funkcija će dodati vrijednosti svim ovim argumentima. Također imajte na umu da ovo može biti jedna ćelija ili niz ćelija.

Možete kreirati takvu funkciju u VBA tako što ćete navesti posljednji argument (ili jedini argument) kao opcioni. Osim toga, ovom opcionom argumentu mora prethoditi ključna riječ "ParamArray".

ParamArray je modifikator koji vam omogućava da uzmete onoliko argumenata koliko želite. Imajte na umu da korištenje riječi ParamArray prije argumenta čini argument opcijskim. Međutim, ovdje ne morate koristiti riječ "Opcionalno".

Sada napravimo funkciju koja može uzeti proizvoljan broj argumenata i koja će dodati sve brojeve datim argumentima:

Funkcija AddArguments(ParamArray arglist() Kao varijanta) Za svaki arg U arglist AddArguments = AddArguments + arg Sljedeći arg End Funkcija

Gornja funkcija može uzeti bilo koji broj argumenata i dodati te argumente kako bi proizvela rezultat.

Imajte na umu da kao argument možete koristiti samo jednu vrijednost, referencu ćelije, boolean ili izraz. Ne možete dati niz kao argument. Na primjer, ako je jedan od vaših argumenata D8:D10, ova formula će vam dati grešku.

Ako želite koristiti oba argumenta iz više ćelija, trebate koristiti sljedeći kod:

Funkcija AddArguments(ParamArray arglist() Kao varijanta) Za svaki arg U arglist Za svaku ćeliju u argu AddArguments = AddArguments + ćelija Sljedeća ćelija Sljedeći arg Kraj Funkcija

Imajte na umu da ova formula radi s više ćelija i referenci niza, ali ne može rukovati tvrdo kodiranim vrijednostima ili izrazima. Možete kreirati robusniju funkciju provjeravanjem i rukovanjem ovim uvjetima, ali to nije cilj.

Ovdje je cilj pokazati kako ParamArray funkcionira tako da možete dozvoliti neograničen broj argumenata u funkciji. Ako vam je potrebna bolja funkcija od one kreirane u kodu iznad, koristite funkciju SUM na radnom listu.

Kreiranje funkcije koja vraća niz

Do sada smo vidjeli funkcije koje vraćaju jednu vrijednost.

Uz VBA, možete kreirati funkciju koja vraća varijantu koja sadrži cijeli niz vrijednosti.

Formule niza su također dostupne kao ugrađene funkcije u Excel listovima. Ako ste upoznati sa formulama niza u Excelu, znate da se one unose pomoću tipki Control + Shift + Enter (ne samo Enter). Više o formulama niza možete pročitati ovdje. Ako ne znate formule niza, ne brinite, nastavite čitati.

Kreirajmo formulu koja vraća niz od tri broja (1,2,3).

Kod u nastavku će to učiniti.

Funkcija ThreeNumbers() Kao varijanta Dim NumberValue(1 do 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue Krajnja funkcija

U gornjem kodu smo naveli funkciju ThreeNumbers kao opciju. To mu omogućava da sadrži niz vrijednosti.

Varijabla NumberValue je deklarirana kao niz od 3 elementa. Sadrži tri vrijednosti i dodjeljuje ga funkciji Tri broja.

Ovu funkciju možete koristiti u radnom listu. Otkucajte ovu funkciju i pritisnite Control+Shift+Enter (držite Control i Shift, a zatim pritisnite Enter).


Kada to učinite, vratit će 1 u ćeliju, ali u stvarnosti sadrži sve tri vrijednosti. Da biste to provjerili, koristite sljedeću formulu:

=MAX(tri broja())

Koristite gornju funkciju sa Control + Shift + Enter. Primijetit ćete da je rezultat sada 3 jer su to najveće vrijednosti u nizu koje vraća Max funkcija, koja dobiva tri broja kao rezultat naše prilagođene funkcije, ThreeNumbers.

Možete koristiti istu tehniku ​​da kreirate funkciju koja vraća niz naziva mjeseci, kao što je prikazano u kodu ispod:

Funkcija Mjeseci() Kao varijanta Dim MonthName(1 do 12) MonthName(1) = "January" MonthName(2) = "Februar" MonthName(3) = "March" MonthName(4) = "April" MonthName(5) = "May" MonthName(6) = "June" MonthName(7) = "July" MonthName(8) = "August" MonthName(9) = "September" MonthName(10) = "Oktobar" MonthName(11) = "Novembar " MonthName(12) = "Decembar" Mjeseci = Funkcija kraja Ime mjeseca

Sada, kada unesete funkciju =Months() u Excel radni list i koristite Control + Shift + Enter, vratit će se cijeli niz naziva mjeseci. Imajte na umu da u ćeliji vidite samo januar jer je to prva vrijednost u nizu. To ne znači da niz vraća samo jednu vrijednost.


Da vam pokažem činjenicu da vraća sve vrijednosti, učinite ovo - odaberite ćeliju s formulom, idite na traku formule, odaberite cijelu formulu i pritisnite F9. Ovo će vam pokazati sve vrijednosti koje funkcija vraća.

Ovo možete koristiti koristeći formulu INDEX u nastavku da dobijete listu svih naziva mjeseci odjednom.

=INDEX(Mjeseci(),RED())


Dakle, isti kod u kojem kreiramo funkciju Mjeseci će postati kraći kao što je prikazano u nastavku:

Funkcija Mjeseci() Kao varijantni mjeseci = Niz("januar", "februar", "mart", "april", "maj", "jun", _ "jul", "avgust", "septembar", "oktobar" , "Novembar", "Decembar") Završna funkcija

Gornja funkcija koristi funkciju Array za dodjelu vrijednosti direktno ovoj funkciji.

Imajte na umu da sve gore kreirane funkcije vraćaju horizontalni niz vrijednosti. To znači da ako odaberete 12 horizontalnih ćelija (recimo A1:L1) i unesete formulu =Mjeseci() u ćeliju A1, dobit ćete sve nazive mjeseci.

Ali što ako želite ove vrijednosti u vertikalnom rasponu ćelija.

To možete učiniti pomoću formule TRANSPOSE na radnom listu.

Jednostavno odaberite 12 vertikalnih ćelija (susjednih) i unesite formulu ispod.


Razumijevanje opsega prilagođene funkcije u Excelu

Funkcija može imati dva opsega - javno ili privatno.

  • Dijeljeni opseg znači da je funkcija dostupna svim listovima u radnoj knjizi, te svim procedurama (pomoćnim i funkcionalnim) u svim modulima u radnoj knjizi. Ovo je korisno kada želite da pozovete funkciju iz potprograma (videćemo kako se to radi u sledećem odeljku).
  • Privatni opseg znači da je funkcija dostupna samo u modulu u kojem postoji. Ne možete ga koristiti u drugim modulima. Nećete ga vidjeti ni na listi funkcija na radnom listu. Na primjer, ako je naziv vaše funkcije "Mjeseci()" i unesete funkciju u Excel (nakon znaka =), ona vam neće prikazati naziv funkcije. Međutim, i dalje ga možete koristiti ako unesete naziv formule

Ako ništa ne navedete, funkcija je po defaultu javna.

Ispod je funkcija koja je privatna funkcija:

Privatna funkcija Ime radne knjige() kao niz Naziv radne knjige = Ova radna knjiga.Naziv Kraj funkcije

Ovu funkciju možete koristiti u potprogramima i procedurama u istim modulima, ali je ne možete koristiti u drugim modulima. Ova funkcija se također neće pojaviti na radnom listu.

Kod ispod će ovu funkciju učiniti javnom. Ovo će se također pojaviti na listu.

Funkcija Ime radne knjige() Kao string Ime radne knjige = Ova radna knjiga.Naziv Kraj funkcije

Različiti načini korištenja korisnički definirane funkcije u Excelu

Jednom kada kreirate prilagođenu funkciju u VBA, možete je koristiti na različite načine.

Pogledajmo prvo kako koristiti funkcije na radnom listu.

Korištenje prilagođenih funkcija u radnim listovima

Već smo vidjeli primjere korištenja funkcije kreirane u VBA na radnom listu.

Sve što treba da uradite je da unesete naziv funkcije i ona će se pojaviti u intellisense.

Imajte na umu da da bi se funkcija pojavila na radnom listu, ona mora biti javna funkcija (kao što je opisano u gornjem dijelu).

Također možete koristiti dijaloški okvir Umetanje funkcije za umetanje prilagođene funkcije (pomoću koraka u nastavku). Ovo će raditi samo za javne funkcije.

  • Idite na karticu Podaci.
  • Kliknite na Umetni funkciju.

  • U dijaloškom okviru Umetanje funkcije odaberite Korisnički definirano kao kategoriju. Ova opcija se pojavljuje samo kada imate funkciju u VB editoru (i javnu funkciju).

  • Odaberite funkciju sa liste svih javno dostupnih prilagođenih funkcija.
  • Kliknite OK

Gornji koraci će umetnuti funkciju u radni list. Takođe prikazuje dijalog Argumenti funkcije, koji će vam dati detaljne informacije o argumentima i rezultatu.


Možete koristiti korisnički definiranu funkciju kao i bilo koju drugu funkciju u Excelu. To također znači da ga možete koristiti s drugim ugrađenim Excel funkcijama. Na primjer. Formula u nastavku će dati naslov radne knjige velikim slovima:

=UPPER(Naziv radne sveske())

Korištenje korisnički definiranih funkcija u VBA procedurama i funkcijama

Nakon što ste kreirali funkciju, možete je koristiti u drugim potprocedurama.

Ako je funkcija javna, može se koristiti u bilo kojoj proceduri u istom ili drugom modulu. Ako je Privatno, može se koristiti samo u istom modulu.

Ispod je funkcija koja vraća naziv radne knjige.

Funkcija Ime radne knjige() Kao string Ime radne knjige = Ova radna knjiga.Naziv Kraj funkcije

Procedura ispod poziva funkciju, a zatim prikazuje ime u okviru za poruku.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

Također možete pozvati funkciju iz druge funkcije.

U kodovima ispod, prvi kod vraća naziv radne knjige, a drugi vraća ime velikim slovima pozivanjem prve funkcije.

Funkcija WorkbookName() Kao niz WorkbookName = ThisWorkbook.Name Kraj funkcije Funkcija WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) End Funkcija

Pozivanje prilagođene funkcije iz drugih radnih knjiga

Ako imate funkciju u radnoj knjizi, možete je pozvati u drugim radnim knjigama.

Postoji nekoliko načina da to učinite:

  1. Kreiranje dodatka
  2. Funkcija spremanja u ličnom makrou
  3. Veza na funkciju iz druge radne knjige.

Kreiranje dodatka

Kreiranjem i instaliranjem dodatka dobijate prilagođenu funkciju koja je dostupna u svim radnim sveskama.

Recimo da ste kreirali prilagođenu funkciju - "GetNumeric" i želite da bude u svim radnim knjigama. Da biste to učinili, kreirajte novu radnu knjigu i postavite kod funkcije u modul ove nove radne knjige.

  • Idite na karticu Datoteka i kliknite na Sačuvaj kao.
  • U dijaloškom okviru Sačuvaj kao promijenite tip Sačuvaj kao u .xlam. Ime koje dodijelite datoteci bit će ime vašeg dodatka. U ovom primjeru, datoteka je sačuvana pod imenom GetNumeric.
    • Primijetit ćete da se putanja do datoteke u kojoj je sačuvana automatski mijenja. Možete koristiti zadano ili ga promijeniti ako želite.

  • Otvorite novu Excel radnu svesku i idite na karticu Programer.
  • Odaberite opciju Excel dodaci.

  • U dijaloškom okviru Add-Ins, pregledajte i locirajte sačuvanu datoteku i kliknite na OK.

Dodatak je sada aktiviran.

Sada možete koristiti prilagođene funkcije u svim radnim knjigama.

Spremanje funkcije u ličnu radnu knjigu makroa

Lična makro radna sveska je skrivena radna sveska na vašem sistemu koja se otvara svaki put kada otvorite Excel.

Ovo je mjesto gdje možete pohraniti makroe i pristupiti im iz bilo koje radne knjige. Ovo je odlično mjesto za pohranjivanje onih makroa koje želite često koristiti.

Podrazumevano, vaš Excel nema ličnu radnu svesku sa makroima. Morate ga kreirati tako što ćete snimiti makro i spremiti ga u svoju ličnu knjigu makroa.

Veza na funkciju iz druge knjige

Dok će prve dvije metode (kreiranje dodatka i korištenje lične makro radne knjige) raditi u svim situacijama, ako želite da referencirate funkciju iz druge radne knjige, ta radna knjiga mora biti otvorena.

Recimo da imate radnu svesku pod nazivom "Formula Workbook" i ona ima funkciju pod nazivom "GetNumeric".

Da biste koristili ovu funkciju u drugoj radnoj knjizi (dok je radna knjiga sa formulom otvorena), možete koristiti sljedeću formulu:

=’Radna sveska sa formulom’!GetNumeric(A1)

Gornja formula će koristiti prilagođenu funkciju u datoteci Workbook with Formula i dati vam rezultat.

Imajte na umu da, budući da naziv knjige sadrži razmake, mora biti stavljen u jednostruke navodnike.

Korištenje escape naredbe iz VBA

Ako želite izaći iz funkcije dok se kod izvršava, to možete učiniti pomoću naredbe Exit Function.

Kod ispod izdvaja prva tri numerička znaka iz alfanumeričkog tekstualnog niza. Nakon što primi tri znaka, funkcija izlazi i vraća rezultat.

Funkcija GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) Za i = 1 do StringLength Ako je J = 3 Zatim izađite iz funkcije If IsNumeric(Mid(CellRef, i, 1)1) Tada J = J + Rezultat = Rezultat & Mid(CellRef, i, 1) GetNumericFirstThree = Rezultat Kraj ako Sljedeća i End funkcija

Gornja funkcija provjerava broj numeričkih znakova i kada dobije 3 numerička znaka, izlazi iz funkcije u sljedećoj petlji.

Otklanjanje grešaka u prilagođenoj funkciji

Postoji nekoliko metoda koje možete koristiti prilikom otklanjanja grešaka u korisnički definiranoj funkciji u VBA:

Otklanjanje grešaka u prilagođenoj funkciji pomoću okvira za poruke

Koristite funkciju da prikažete okvir za poruku sa određenom vrijednošću.

Prikazana vrijednost može biti zasnovana na onome što želite provjeriti. Na primjer, ako želite provjeriti radi li se kod ili ne, radit će bilo koja poruka, a ako želite provjeriti da li su petlje pokrenute ili ne, možete prikazati određenu vrijednost ili brojač petlje.

Otklanjanje grešaka u prilagođenoj funkciji postavljanjem tačke prekida

Postavite tačku prekida tako da možete ići korak po korak kroz svaki red. Da biste postavili tačku prekida, odaberite liniju koju želite i pritisnite F9 ili kliknite na sivu okomitu oblast koja se nalazi lijevo od redova koda. Bilo koja od ovih metoda bi umetnula tačku prekida (vidjet ćete crvenu tačku u sivom području).


Jednom kada postavite tačku prekida i izvršite funkciju, ona se penje do linije tačke prekida i zatim se zaustavlja. Sada možete vidjeti kod pomoću tipke F8. Pritisnite F8 jednom da pređete na sledeći red u kodu.

Otklanjanje grešaka u prilagođenoj funkciji pomoću Debug.Print u kodu

Možete koristiti naredbu Debug.Print u svom kodu da dobijete vrijednosti specificiranih varijabli/argumenata u neposrednom prozoru.

Na primjer, u kodu ispod, koristio sam Debug.Print da dobijem vrijednost dvije varijable - "j" i "Result".

Funkcija GetNumericFirstThree(CellRef As Range) As Long Dim StringLength As Integer StringLength = Len(CellRef) Za i = 1 do StringLength Ako je J = 3 Zatim izađite iz funkcije If IsNumeric(Mid(CellRef, i, 1)1) Tada J = J + Rezultat = Rezultat & Mid(CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Rezultat Kraj ako Sljedeća i Kraj funkcija

Kada se ovaj kod izvrši, u neposrednom prozoru prikazuje sljedeće.

Ugrađene funkcije Excela naspram VBA prilagođene funkcije

Postoji nekoliko jakih prednosti korištenja Excelovih ugrađenih funkcija u odnosu na prilagođene funkcije kreirane u VBA.

  • Ugrađene funkcije su mnogo brže od VBA funkcija.
  • Kada kreirate izveštaj/kontrolnu tablu koristeći VBA funkcije i pošaljete ga klijentu/kolegi, oni ne moraju da brinu o tome da li su makroi omogućeni ili ne. U nekim slučajevima, klijenti/kupci se uplaše kada vide žutu traku upozorenja (koja jednostavno traži od njih da omoguće makroe).
  • Uz ugrađene funkcije Excela, ne morate brinuti o ekstenzijama datoteka. Ako imate makronaredbe ili korisnički definirane funkcije u radnoj knjizi, morate ih spremiti u .xlsm formatu

Iako postoji mnogo dobrih razloga za korištenje ugrađenih funkcija Excela, u nekim je slučajevima bolje koristiti prilagođenu funkciju.

  • Bolje je koristiti prilagođenu funkciju ako je vaša inline formula ogromna i složena. Ovo postaje još relevantnije kada vam je potreban neko drugi da ažurira vaše formule. Na primjer, ako imate ogromnu formulu koja se sastoji od mnogo različitih funkcija, čak i promjena reference ćelije može biti zamorna i sklona greškama. Umjesto toga, možete kreirati prilagođenu funkciju koja uzima samo jedan ili dva argumenta i obavlja sve pozadinske poslove.
  • Kada trebate učiniti nešto što ne mogu učiniti ugrađene funkcije Excela. Primjer ovoga bi bio kada želite izvući sve numeričke znakove iz stringa. U takvim slučajevima, prednosti korištenja prilagođene funkcije gar nadmašuju njene nedostatke.

Gdje staviti VBA kod za prilagođenu funkciju

Kada kreirate prilagođenu funkciju, morate postaviti kod u prozor koda za radnu knjigu u kojoj želite da koristite funkciju.

Ispod su uputstva za postavljanje koda za funkciju "GetNumeric" u radnu svesku.


Povezane publikacije: