Stvaranje korisnički definirane funkcije (UDF) u Excelu VBA (Ultimate Guide)

S VBA -om možete stvoriti prilagođenu funkciju (koja se naziva i korisnički definirana funkcija) koja se može koristiti na radnim listovima kao i obične funkcije.

To je korisno ako postojeće Excel funkcije nisu dovoljne. U takvim slučajevima možete stvoriti vlastitu prilagođenu korisnički definiranu funkciju (UDF) koja će zadovoljiti vaše posebne potrebe.

U ovom ću vodiču pokriti sve o stvaranju i korištenju prilagođenih funkcija u VBA -i.

Ako ste zainteresirani za učenje VBA na jednostavan način, provjerite moj Mrežna Excel VBA obuka.

Što je postupak funkcije u VBA?

Postupak funkcije je VBA kôd koji izvodi izračune i vraća vrijednost (ili niz vrijednosti).

Pomoću postupka Funkcija možete stvoriti funkciju koju možete koristiti na radnom listu (baš kao i bilo koju redovitu Excel funkciju, poput SUM -a ili VLOOKUP -a).

Kada ste stvorili postupak funkcije pomoću VBA -a, možete ga koristiti na tri načina:

1. Kao formula na radnom listu, gdje može uzeti argumente kao ulaze i vratiti vrijednost ili niz vrijednosti.
2. Kao dio vašeg koda VBA podrutine ili drugog koda funkcije.
3. U uvjetnom oblikovanju.

Iako je na radnom listu već dostupno 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 stvoriti prilagođenu funkciju na temelju vaših zahtjeva.
• Ugrađene funkcije mogu obaviti posao, ali formula je duga i komplicirana. U tom slučaju možete stvoriti prilagođenu funkciju koja se lako čita i koristi.

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

Funkcija vs. Potprogram u VBA

"Potprogram" vam omogućuje izvršavanje skupa koda, dok "funkcija" vraća vrijednost (ili niz vrijednosti).

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

Podprogram može petljati kroz svaku ćeliju u rasponu i može označiti sve ćelije koje u sebi imaju negativnu vrijednost. U tom slučaju potprogram na kraju mijenja svojstva objekta raspona (promjenom boje ćelija).

S prilagođenom funkcijom možete je koristiti u zasebnom stupcu i može vratiti TRUE ako je vrijednost u ćeliji negativna i FALSE ako je pozitivna. Pomoću funkcije ne možete promijeniti svojstva objekta. To znači da ne možete promijeniti boju ćelije sa samom funkcijom (međutim, to možete učiniti pomoću uvjetnog oblikovanja s prilagođenom funkcijom).

Kada kreirate Korisnički definiranu funkciju (UDF) pomoću VBA -a, tu funkciju možete koristiti na radnom listu kao i bilo koju drugu funkciju. O tome ću više govoriti u odjeljku 'Različiti načini korištenja korisnički definirane funkcije u Excelu'.

Stvaranje jednostavne korisnički definirane funkcije u VBA

Dopustite mi da stvorim jednostavnu korisnički definiranu funkciju u VBA-u i pokažem vam kako to radi.

Kod u nastavku stvara funkciju koja će izdvojiti numeričke dijelove iz alfanumeričkog niza.

Funkcija GetNumeric (CellRef As String) kao Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Zatim Result = Result & Mid (CellRef, i, 1) Sljedeće i GetNumeric = Krajnja funkcija rezultata

Kada gornji kôd imate u modulu, ovu funkciju možete koristiti u radnoj knjizi.

U nastavku donosimo kako ova funkcija - GetNumeric - može se koristiti u Excelu.

Prije nego što vam kažem kako je ova funkcija stvorena u VBA -i i kako funkcionira, postoji nekoliko stvari koje biste trebali znati:

• Kada kreirate funkciju u VBA -i, ona postaje dostupna u cijeloj radnoj knjizi kao i svaka druga regularna funkcija.
• Kada upišete naziv funkcije iza koje slijedi znak jednakosti, Excel će vam prikazati naziv funkcije na popisu odgovarajućih funkcija. U gornjem primjeru, kada sam ušao = Get, Excel mi je pokazao popis koji ima moju prilagođenu funkciju.

Vjerujem da je ovo dobar primjer kada pomoću VBA možete stvoriti funkciju jednostavnu za korištenje u Excelu. Istu stvar možete učiniti i s formulom (kao što je prikazano u ovom vodiču), ali to postaje komplicirano i teško razumljivo. S ovim UDF -om trebate unijeti samo jedan argument i dobit ćete rezultat.

Anatomija korisnički definirane funkcije u VBA

U gornjem odjeljku dao sam vam kôd i pokazao vam kako UDF funkcija radi na radnom listu.

Hajdemo sada duboko zaroniti i vidjeti kako je ova funkcija stvorena. Dolje navedeni kôd morate smjestiti u modul u VB Editor. Ovu temu obrađujem u odjeljku - ‘Gdje staviti VBA kôd za korisnički definiranu funkciju’.

Funkcija GetNumeric (CellRef As String) as Long 'Ova funkcija izdvaja numerički dio iz niza Dim StringLength As Integer StringLength = Len (CellRef) Za i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Zatim rezultat = Rezultat i sredina (CellRef, i, 1) Dalje i GetNumeric = Funkcija završetka rezultata

Prvi redak koda počinje riječju - Funkcija.

Ova riječ govori VBA -i da je naš kôd funkcija (a ne potprogram). Nakon riječi Funkcija slijedi naziv funkcije - GetNumeric. Ovo je naziv koji ćemo koristiti na radnom listu za korištenje ove funkcije.

• Naziv funkcije ne može sadržavati razmake. Također, ne možete imenovati funkciju ako se kosi s imenom reference ćelije. Na primjer, ne možete imenovati funkciju ABC123 jer se također odnosi na ćeliju na radnom listu programa Excel.
• Ne biste trebali dati svojoj funkciji isto ime kao i postojećoj funkciji. Ako to učinite, Excel bi dao prednost ugrađenoj funkciji.
• Ako želite razdvojiti riječi, možete koristiti donju crtu. Na primjer, Get_Numeric je prihvatljivo ime.

Nakon naziva funkcije slijede neki argumenti u zagradi. Ovo su argumenti koje bi naša funkcija trebala od korisnika. To su baš kao i argumenti koje moramo dostaviti Excel -ovim ugrađenim funkcijama. Na primjer, u funkciji COUNTIF postoje dva argumenta (raspon i kriteriji)

Unutar zagrada morate navesti argumente.

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

Također je dobra praksa navesti kakav argument funkcija očekuje. U ovom primjeru, budući da ćemo funkciju hraniti referencom ćelije, možemo navesti argument kao tip 'Raspon'. Ako ne navedete vrstu podataka, VBA bi je smatrao varijantom (što znači da možete koristiti bilo koju vrstu podataka).

Ako imate više argumenata, možete ih navesti u istim zagradama - odvojene zarezom. Kasnije ćemo u ovom vodiču vidjeti kako koristiti više argumenata u korisnički definiranoj funkciji.

Imajte na umu da je funkcija navedena kao tip podataka "String". To bi VBA -i reklo da će rezultat formule biti tipa podataka String.

Iako ovdje mogu koristiti numerički tip podataka (poput Long ili Double), to bi ograničilo raspon brojeva koje može vratiti. Ako imam niz od 20 brojeva koji moram izdvojiti iz ukupnog niza, deklariranje funkcije kao Long ili Double donijelo bi pogrešku (jer bi broj bio izvan svog raspona). Stoga sam zadržao izlazni tip podataka funkcije kao String.

Drugi redak koda - onaj u zelenoj boji koji počinje apostrofom - je komentar. Prilikom čitanja koda, VBA zanemaruje ovaj redak. Ovo možete upotrijebiti za dodavanje opisa ili pojedinosti o kodu.

Treći redak koda deklarira varijablu 'StringLength' kao cijeli tip podataka. Ovo je varijabla u koju pohranjujemo vrijednost duljine niza koji se analizira formulom.

Četvrti redak deklarira varijablu Result kao vrstu podataka String. Ovo je varijabla u kojoj ćemo izdvojiti brojeve iz alfanumeričkog niza.

Peti redak dodjeljuje duljinu niza u ulaznom argumentu varijabli 'StringLength'. Imajte na umu da se 'CellRef' odnosi na argument koji će dati korisnik dok koristi formulu na radnom listu (ili je koristi u VBA - što ćemo vidjeti kasnije u ovom vodiču).

Šesti, sedmi i osmi red dio su petlje For Next. Petlja se izvodi onoliko puta koliko znakova ima u ulaznom argumentu. Ovaj broj daje funkcija LEN i dodjeljuje se varijabli 'StringLength'.

Dakle, petlja ide od '1 do Stringlength'.

Unutar petlje, izraz IF analizira svaki znak niza i ako je numerički, dodaje taj numerički znak varijabli Result. Za to koristi funkciju MID u VBA.

Drugi posljednji redak koda dodjeljuje vrijednost rezultata funkciji. Ovaj redak koda osigurava da funkcija vraća vrijednost "Rezultat" natrag u ćeliju (odakle se zove).

Zadnji redak koda je End Function. Ovo je obavezna linija koda koja govori VBA -i da kod funkcije završava ovdje.

Gornji kôd objašnjava različite dijelove tipične prilagođene funkcije stvorene u VBA -i. U sljedećim odjeljcima dublje ćemo zaroniti u te elemente te vidjeti i različite načine izvođenja VBA funkcije u Excelu.

Argumenti u korisnički definiranoj funkciji u VBA

U gornjim primjerima, gdje smo stvorili korisnički definiranu funkciju za dobivanje numeričkog dijela iz alfanumeričkog niza (GetNumeric), funkcija je dizajnirana da uzme jedan jedini argument.

U ovom odjeljku pokazat ću kako stvoriti funkcije koje ne uzimaju argumente u odnosu na one koje uzimaju više argumenata (obavezni kao i izborni argumenti).

Stvaranje funkcije u VBA bez ikakvih argumenata

U radnom listu programa Excel imamo nekoliko funkcija koje ne uzimaju argumente (poput RAND, TODAY, NOW).

Ove funkcije ne ovise o ulaznim argumentima. Na primjer, funkcija TODAY vratila bi trenutni datum, a funkcija RAND će vratiti slučajni broj između 0 i 1.

Takvu sličnu funkciju možete stvoriti i u VBA -i.

Ispod je kôd koji će vam dati naziv datoteke. Ne uzima nikakve argumente jer rezultat koji treba vratiti ne ovisi o nijednom argumentu.

Funkcija WorkbookName () As String WorkbookName = ThisWorkbook.Name Završna funkcija

Gornji kôd navodi rezultat funkcije kao vrstu podataka String (rezultat koji želimo je naziv datoteke - koji je niz).

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

Ako je datoteka spremljena, vraća naziv s nastavkom datoteke, inače jednostavno daje naziv.

Gore navedeno ipak ima jedan problem.

Ako se naziv datoteke promijeni, neće se automatski ažurirati. Normalno, funkcija se osvježava kad god dođe do promjene ulaznih argumenata. No budući da u ovoj funkciji nema argumenata, funkcija se ne preračunava (čak i ako promijenite naziv radne knjige, zatvorite je, a zatim ponovno otvorite).

Ako želite, možete prisiliti ponovno izračunavanje pomoću tipkovnog prečaca - Control + Alt + F9.

Da biste formulu ponovno izračunali kad god dođe do promjene na radnom listu, morate unijeti redak koda u nju.

Donji kod čini da se funkcija ponovno izračunava kad god dođe do promjene na radnom listu (baš kao i druge slične funkcije radnog lista, poput funkcije TODAY ili RAND).

Funkcija WorkbookName () Kao String Application.Volatile True WorkbookName = ThisWorkbook.Name Završna funkcija

Sada, ako promijenite naziv radne knjige, ova će se funkcija ažurirati kad god dođe do promjene na radnom listu ili pri ponovnom otvaranju ove radne knjige.

Stvaranje funkcije u VBA s jednim argumentom

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

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

Funkcija stvorena donjim kodom pretvorila bi navedeni tekst u velika slova. Sada već imamo funkciju za to u Excelu, a ova je funkcija samo da vam pokaže kako to radi. Ako to morate učiniti, bolje je koristiti ugrađenu funkciju GORNJE.

Funkcija ConvertToUpperCase (CellRef kao raspon) ConvertToUpperCase = UCase (CellRef) Krajnja funkcija

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

Budući da ova funkcija uzima argument, ne moramo ovdje koristiti Application.Volatile. Čim se argument promijeni, funkcija bi se automatski ažurirala.

Stvaranje funkcije u VBA s više argumenata

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

Kod u nastavku stvorio bi funkciju koja će izdvojiti tekst prije navedenog razdjelnika. Potrebna su dva argumenta - referenca ćelije koja sadrži tekstualni niz i graničnik.

Funkcija GetDataBeforeDelimiter (CellRef As Range, Delim As String) kao String Dim Rezultat Kao String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Rezultat = Lijevo (CellRef, DelimPitBetupDesekDelDetalDeftDetalDemontatDemoNetaDeftDetaDetalDetaDetalDetaDesekDetalDesekDesekDetalDetaDetalDesekDesekDetalDetaDesekDetalDesekDetalDesekDetalDesekDetalDesekDetalDetaDesek)

Kada trebate koristiti više od jednog argumenta u korisnički definiranoj funkciji, svi argumenti mogu biti u zagradama, odvojeni zarezom.

Imajte na umu da za svaki argument možete navesti vrstu podataka. U gornjem primjeru, 'CellRef' je deklariran kao tip podataka raspona, a 'Delim' je deklariran kao tip podataka String. Ako ne navedete nijedan tip podataka, VBA smatra da se radi o varijantnom tipu podataka.

Kada koristite gornju funkciju na radnom listu, trebate dati referencu ćelije koja ima tekst kao prvi argument i znak (i) za razdvajanje u dvostrukim navodnicima kao drugi argument.

Zatim provjerava položaj graničnika pomoću funkcije INSTR u VBA. Taj se položaj tada koristi za izdvajanje svih znakova prije graničnika (pomoću funkcije LIJEVO).

Konačno, dodjeljuje rezultat funkciji.

Ova formula je daleko od savršene. Na primjer, ako unesete graničnik koji se ne nalazi u tekstu, dao bi pogrešku. Sada možete upotrijebiti funkciju IFERROR na radnom listu da biste se riješili pogrešaka ili možete upotrijebiti donji kod koji vraća cijeli tekst kada ne može pronaći razdjelnik.

Funkcija GetDataBeforeDelimiter (CellRef As Range, Delim As String) kao String Dim Result Kao String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 then DelimPosition = Len (CellR) CellRef, DelimPosition) GetDataBeforeDelimiter = Funkcija završetka rezultata

Ovu funkciju možemo dodatno optimizirati.

Ako tekst (iz kojeg želite izdvojiti dio prije graničnika) unesete izravno u funkciju, to će vam dati pogrešku. Samo naprijed … isprobajte!

To se događa jer smo naveli 'CellRef' kao tip podataka raspona.

Ili, ako želite da graničnik bude u ćeliji i upotrijebite referencu ćelije umjesto da je kodirate u formuli, to ne možete učiniti s gornjim kodom. To je zato što je Delim deklariran kao niz podataka tipa.

Ako želite da funkcija ima fleksibilnost za prihvaćanje izravnog unosa teksta ili referenci ćelije od korisnika, morate ukloniti deklaraciju tipa podataka. To bi na kraju argument učinilo varijantnim tipom podataka, koji može uzeti bilo koju vrstu argumenta i obraditi ga.

Kôd u nastavku bi ovo učinio:

Funkcija GetDataBeforeDelimiter (CellRef, Delim) Kao rezultat zatamnjenja niza Kao niza zatamnjenja DelimPosition Kao cijeli broj DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ako je DelimPosition <0 Tada je DelimPosition = Len (CellRef) ResolPreostani, CellRef) Rezultat GetDataBeforeDelimiter = Funkcija kraja rezultata

Stvaranje funkcije u VBA -u s izbornim argumentima

U Excelu postoji mnogo funkcija u kojima su neki od argumenata izborni.

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

Neobavezni argument, kao što naziv govori, nije obavezan za navođenje. Ako ne navedete jedan od obveznih argumenata, vaša će vam funkcija dati pogrešku, ali ako ne navedete izborni argument, vaša bi funkcija radila.

No, izborni argumenti nisu beskorisni. Omogućuju vam odabir iz niza opcija.

Na primjer, u funkciji VLOOKUP, ako ne navedete četvrti argument, VLOOKUP izvršava približno pretraživanje, a ako zadnji argument navedete kao FALSE (ili 0), tada se točno podudara.

Upamtite da izborni argumenti uvijek moraju doći iza svih potrebnih argumenata. Ne možete imati izborne argumente na početku.

Pogledajmo sada kako stvoriti funkciju u VBA -u s izbornim argumentima.

Funkcija samo s neobaveznim argumentom

Koliko ja znam, ne postoji ugrađena funkcija koja uzima samo neobavezne argumente (ovdje mogu pogriješiti, ali ne mogu se sjetiti takve funkcije).

Ali možemo stvoriti jedan s VBA.

Dolje je kôd funkcije koja će vam dati trenutni datum u formatu dd-mm-gggg ako ne unesete nijedan argument (tj. Ostavite prazno), i u formatu "dd mmmm, ggggg" ako unesete bilo što kao argument (tj. bilo što tako da argument nije prazan).

Funkcija CurrDate (neobavezno fmt kao varijanta) Rezultat zatamnjenja ako nedostaje (fmt) Zatim CurrDate = Format (datum, "dd-mm-ggggg") Inače CurrDate = Format (datum, "dd mmmm, gggg") Kraj ako je funkcija prekinuta

Imajte na umu da gornja funkcija koristi 'IsMissing' za provjeru nedostaje li argument ili ne. Da biste koristili funkciju IsMissing, vaš izborni argument mora biti varijantnog tipa podataka.

Gornja funkcija radi bez obzira na to što unesete kao argument. U kodu provjeravamo samo je li opcijski argument naveden ili nije.

To možete učiniti robusnijim ako uzmete samo određene vrijednosti kao argumente i prikažete pogrešku u ostalim slučajevima (kao što je prikazano u donjem kodu).

Funkcija CurrDate (neobavezno fmt kao varijanta) Rezultat zatamnjenja Ako nedostaje (fmt) Tada je CurrDate = Format (Datum, "dd-mm-gggg") OstaloIf fmt = 1 Tada je CurrDate = Format (Datum, "dd mmmm, gggg") Ostalo CurrDate = CVErr (xlErrValue) Funkcija kraj ako je kraj

Gornji kôd stvara funkciju koja prikazuje datum u "dd-mm-ggggg" formatu ako argument nije naveden, te u "dd mmmm, yyyy" formatu kada je argument 1. On daje pogrešku u svim ostalim slučajevima.

Funkcija s obaveznim, kao i izbornim argumentima

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

Pogledajmo sada sličan primjer koji uzima i potrebne i izborne argumente.

Kod u nastavku stvara funkciju koja izdvaja tekstualni dio iz niza. Ako je neobavezni argument TRUE, on daje rezultat velikim slovima, a ako je neobavezni argument FALSE ili je izostavljen, daje rezultat takav kakav jest.

Funkcija GetText (CellRef As Range, Optional TextCase = False) Kao niz Dim Dim StringLength Kao cijeli broj Dim Rezultat Kao String StringLength = Len (CellRef) Za i = 1 Za StringLength Ako nije (IsNumeric (Mid (CellRef, i, 1))) Tada Rezultat = Rezultat i sredina (CellRef, i, 1) Dalje i Ako je TextCase = True, tada je rezultat = UCase (rezultat) GetText = Krajnja funkcija rezultata

Imajte na umu da smo u gornjem kodu inicijalizirali vrijednost "TextCase" kao False (pogledajte unutar zagrada u prvom retku).

Time smo osigurali da izborni argument počinje zadanom vrijednošću, koja je FALSE. Ako korisnik navede vrijednost kao TRUE, funkcija vraća tekst velikim slovima, a ako korisnik navede neobavezni argument kao FALSE ili ga izostavi, tada će tekst biti takav kakav jest.

Stvaranje funkcije u VBA -i s nizom kao argumentom

Do sada smo vidjeli primjere stvaranja funkcije s neobaveznim/potrebnim argumentima - gdje su ti argumenti bili jedna vrijednost.

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

Dolje je kôd koji stvara funkciju koja daje zbroj svih parnih brojeva u navedenom rasponu ćelija.

Funkcija AddEven (CellRef kao raspon) Dim Cell As Range Za svaku ćeliju u CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Funkcija

Ovu funkciju možete koristiti na radnom listu i navesti raspon ćelija koje kao argument imaju brojeve. Funkcija bi vratila jednu vrijednost - zbroj svih parnih brojeva (kao što je prikazano u nastavku).

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 naveo sam argument CellRef kao Raspon (koji može uzeti niz kao ulaz). Ovdje možete upotrijebiti i varijantni tip podataka.

U kodu postoji petlja za svaku koja prolazi kroz svaku ćeliju i provjerava je li broj ne. Ako nije, ništa se ne događa i prelazi u sljedeću ćeliju. Ako je to broj, provjerava je li paran ili nije (pomoću funkcije MOD).

Na kraju se dodaju svi parni brojevi i zbroj se dodjeljuje funkciji.

Stvaranje funkcije s neodređenim brojem argumenata

Prilikom stvaranja nekih funkcija u VBA -u možda nećete znati točan broj argumenata koje korisnik želi dati. Stoga je potrebno stvoriti funkciju koja može prihvatiti što više argumenata i upotrijebiti ih za vraćanje rezultata.

Primjer takve funkcije radnog lista je funkcija SUM. Možete mu unijeti više argumenata (poput ovog):

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

Gornja funkcija dodala bi vrijednosti u sve ove argumente. Također imajte na umu da to mogu biti jedna ćelija ili niz ćelija.

Takvu funkciju možete stvoriti u VBA -i tako da zadnji argument (ili bi mogao biti jedini argument) bude neobavezan. Također, ovom izbornom argumentu treba prethoditi ključna riječ ‘ParamArray’.

‘ParamArray’ je modifikator koji vam omogućuje da prihvatite onoliko argumenata koliko želite. Imajte na umu da upotreba riječi ParamArray prije argumenta čini argument neobaveznim. Međutim, ovdje ne morate koristiti riječ Neobavezno.

Sada stvorimo funkciju koja može prihvatiti proizvoljan broj argumenata i dodati sve brojeve u navedenim argumentima:

Funkcija AddArguments (ParamArray arglist () As Variant) Za svaki arglist na arglist AddArguments = AddArguments + arg Sljedeći arg Kraj funkcija

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

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

Ako želite koristiti oba argumenta s više ćelija, morate upotrijebiti donji kod:

Funkcija AddArguments (ParamArray arglist () Kao varijanta) Za svaki arglist na arglist -u Za svaku ćeliju u arg -u AddArguments = AddArguments + Ćelija Sljedeća ćelija Sljedeća arg funkcija završetka

Imajte na umu da ova formula radi s više ćelija i referencama polja, međutim, ne može obraditi tvrdo kodirane vrijednosti ili izraze. Provjerom i liječenjem ovih stanja možete stvoriti robusniju funkciju, ali to ovdje nije namjera.

Ovdje je namjera pokazati vam kako ParamArray radi kako biste mogli dopustiti neograničen broj argumenata u funkciji. U slučaju da želite bolju funkciju od one koju je stvorio gornji kôd, upotrijebite funkciju SUM na radnom listu.

Stvaranje funkcije koja vraća niz

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

S VBA -om možete stvoriti funkciju koja vraća varijantu koja može sadržavati cijeli niz vrijednosti.

Formule niza dostupne su i kao ugrađene funkcije u radnim listovima programa Excel. Ako ste upoznati s formulama polja u Excelu, znali biste da se unose pomoću Control + Shift + Enter (umjesto samo Enter). Ovdje možete pročitati više o formulama polja. Ako ne znate o formulama polja, ne brinite, nastavite čitati.

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

Kod ispod bi to učinio.

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 kao varijantu naveli funkciju ‘ThreeNumbers’. To mu omogućuje da drži niz vrijednosti.

Varijabla ‘NumberValue’ deklarirana je kao niz s 3 elementa. Ona sadrži tri vrijednosti i dodjeljuje je funkciji 'Tri broja'.

Ovu funkciju možete koristiti na radnom listu tako da unesete funkciju i pritisnete tipku Control + Shift + Enter (držite tipke Control i Shift, a zatim pritisnite Enter).

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

= MAX (Tri broja ())

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

Možete koristiti istu tehniku ​​za stvaranje funkcije koja vraća niz naziva mjeseci kako je prikazano u donjem kodu:

Mjeseci funkcija () Kao varijanta Dim Ime mjeseca (1 do 12) Ime mjeseca (1) = "Mjesec" Ime mjeseca (2) = "Veljača" Ime mjeseca (3) = "Ožujak" Ime mjeseca (4) = "Travanj" Mjesec (5) = Mjesečni naziv "Svibanj" (6) = "Lipanjski" Mjesečni naziv (7) = "Srpanjski" Mjesečni naziv (8) = "Kolovoz" Mjesečni naziv (9) = "Rujan" Mjesečni naziv (10) = "Listopadski" Mjesečni naziv (11) = "studeni "MonthName (12) =" December "Mjeseci = Funkcija kraja mjeseca

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

Da biste vam pokazali činjenicu da vraća sve vrijednosti, učinite to - odaberite ćeliju s formulom, idite na traku s formulama, odaberite cijelu formulu i pritisnite F9. Ovo će vam pokazati sve vrijednosti koje funkcija vraća.

To možete upotrijebiti pomoću donje formule INDEX da biste odjednom dobili popis svih naziva mjeseci.

= INDEKS (Mjeseci (), ROW ())

Ako imate puno vrijednosti, nije dobra praksa dodjeljivati ​​ove vrijednosti jednu po jednu (kao što smo učinili gore). Umjesto toga, možete koristiti funkciju Array u VBA.

Dakle, isti kod u kojem stvaramo funkciju 'Mjeseci' postao bi kraći kao što je prikazano u nastavku:

Mjeseci funkcija () Kao varijantni mjeseci = niz ("siječanj", "veljača", "ožujak", "travanj", "svibanj", "lipanj", _ "srpanj", "kolovoz", "rujan", "listopad" , "Studeni", "Prosinac") Završna funkcija

Gornja funkcija koristi funkciju Array za dodjeljivanje vrijednosti izravno funkciji.

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

No što ako te vrijednosti želite u okomitom rasponu ćelija.

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

Jednostavno odaberite 12 okomitih ćelija (susjednih) i unesite donju formulu.

Razumijevanje opsega korisnički definirane funkcije u Excelu

Funkcija može imati dva opsega - Javnost ili Privatna.

• A Javni opseg znači da je funkcija dostupna za sve listove u radnoj knjizi, kao i za sve postupke (pod i funkcija) na svim modulima u radnoj knjizi. To je korisno kada želite pozvati funkciju iz potprograma (vidjet ćemo kako se to radi u sljedećem odjeljku).
• A Privatni opseg znači da je funkcija dostupna samo u modulu u kojem postoji. Ne možete ga koristiti u drugim modulima. Također ga nećete vidjeti na popisu funkcija na radnom listu. Na primjer, ako je naziv vaše funkcije ‘Mjeseci ()’, a funkciju unesete u Excel (iza znaka =), neće vam prikazati naziv funkcije. No, ipak ga možete koristiti ako unesete naziv formule.

Ako ništa ne navedete, funkcija je prema zadanim postavkama javna funkcija.

Ispod je funkcija koja je privatna:

Privatebook WorkbookName () As String WorkbookName = ThisWorkbook.Name Završna funkcija

Ovu funkciju možete koristiti u potprogramima i postupcima u istim modulima, ali je ne možete koristiti u drugim modulima. Ova se funkcija također ne bi prikazala na radnom listu.

Kod ispod bi ovu funkciju učinio javnom. Također će se pojaviti na radnom listu.

Funkcija WorkbookName () As String WorkbookName = ThisWorkbook.Name Završna funkcija

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

Nakon što ste stvorili korisnički definiranu funkciju u VBA-i, možete je koristiti na mnogo različitih načina.

Prvo ćemo razmotriti kako koristiti funkcije na radnom listu.

Korištenje UDF -ova u radnim listovima

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

Sve što trebate učiniti je unijeti naziv funkcije i on će se pojaviti u intellisenseu.

Imajte na umu da za prikaz funkcije na radnom listu mora biti javna funkcija (kao što je objašnjeno u gornjem odjeljku).

Također možete upotrijebiti dijaloški okvir Umetanje funkcije za umetanje korisnički definirane funkcije (koristeći donje korake). To bi radilo samo za javne funkcije.

• Idite na karticu Podaci.
• Kliknite opciju "Umetni funkciju".
• U dijaloškom okviru Umetanje funkcije odaberite kategoriju koju je definirao korisnik. Ova se opcija prikazuje samo ako imate funkciju u VB Editoru (a funkcija je javna).
• Odaberite funkciju s popisa svih funkcija koje definira javni korisnik.
• Pritisnite gumb U redu.

Gore navedeni koraci ubacili bi funkciju u radni list. Također prikazuje dijaloški okvir Argumenti funkcija koji će vam dati pojedinosti o argumentima i rezultatu.

Korisnički definiranu funkciju možete koristiti kao i bilo koju drugu funkciju u Excelu. To također znači da ga možete koristiti s drugim ugrađenim funkcijama programa Excel. Na primjer. donja formula dala bi naziv radne knjige velikim slovima:

= GORNJI (Ime radne knjige ())

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

Kad ste stvorili funkciju, možete je koristiti i u drugim podprocedurama.

Ako je funkcija javna, može se koristiti u bilo kojem postupku 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 WorkbookName () As String WorkbookName = ThisWorkbook.Name Završna funkcija

Postupak u nastavku poziva funkciju, a zatim prikazuje ime u okviru za poruku.

Sub ShowWorkbookName () MsgBox WorkbookName Kraj Sub

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

U donjim kodovima prvi kôd vraća naziv radne knjige, a drugi vraća velika slova pozivanjem prve funkcije.

Funkcija WorkbookName () As String WorkbookName = ThisWorkbook.Name Završna funkcija

Funkcija WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Kraj funkcija

Pozivanje korisnički definirane funkcije iz drugih radnih knjiga

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

Postoji više načina za to:

1. Izrada programskog dodatka
2. Funkcija spremanja u osobnu radnu knjigu makronaredbi
3. Referenciranje funkcije iz druge radne knjige.

Izrada programskog dodatka

Stvaranjem i instaliranjem dodatka imat ćete prilagođenu funkciju dostupnu u svim radnim knjigama.

Pretpostavimo da ste stvorili prilagođenu funkciju - "GetNumeric" i želite je u svim radnim knjigama. Da biste to učinili, izradite novu radnu knjigu i unesite šifru funkcije u modul u ovoj novoj radnoj knjizi.

Sada slijedite donje korake da biste ga spremili kao dodatak, a zatim ga instalirali u Excel.

• Idite na karticu Datoteka i kliknite Spremi kao.
• U dijaloškom okviru Spremi kao promijenite vrstu "Spremi kao" u .xlam. Naziv koji dodijelite datoteci bio bi naziv vašeg dodatka. U ovom primjeru datoteka se sprema s imenom GetNumeric.
  • Primijetit ćete da se putanja datoteke u koju se sprema automatski mijenja. Možete koristiti zadanu ili je promijeniti ako želite.
• Otvorite novu radnu knjigu programa Excel i idite na karticu Developer.
• Kliknite opciju Dodaci programa Excel.
• U dijaloškom okviru Dodaci pregledajte i pronađite datoteku koju ste spremili, a zatim kliknite U redu.

Sada je dodatak aktiviran.

Sada možete koristiti prilagođenu funkciju u svim radnim knjigama.

Spremanje funkcije u osobnu makro radnu knjigu

Osobna makro radna knjiga skrivena je radna knjiga u vašem sustavu koja se otvara svaki put kada otvorite Excel aplikaciju.

To je mjesto gdje možete pohraniti makro kodove i zatim pristupiti tim makroima iz bilo koje radne knjige. To je izvrsno mjesto za spremanje onih makronaredbi koje želite često koristiti.

Prema zadanim postavkama, u vašem Excelu ne postoji osobna radna knjiga makronaredbi. Morate ga stvoriti snimanjem makronaredbe i spremanjem u radnu knjigu Personal macro.

Detaljne korake o stvaranju i spremanju makronaredbi u osobnoj radnoj knjizi makronaredbi možete pronaći ovdje.

Referenciranje funkcije iz druge radne knjige

Dok bi prve dvije metode (stvaranje dodatka i korištenje osobne makro radne knjige) funkcionirale u svim situacijama, ako želite referencirati funkciju iz druge radne knjige, ta radna knjiga mora biti otvorena.

Pretpostavimo da imate radnu knjigu s imenom 'Radna bilježnica s formulom ', a ima funkciju s imenom 'GetNumeric '.

Za korištenje ove funkcije u drugoj radnoj knjizi (dok je Radna bilježnica s formulom je otvoren), možete koristiti donju formulu:

= 'Radna knjiga s formulom'! GetNumeric (A1)

Gornja formula će koristiti korisnički definiranu funkciju u Radna bilježnica s formulom datoteku i dati vam rezultat.

Imajte na umu da budući da naziv radne knjige ima razmake, morate je staviti u pojedinačne navodnike.

Korištenje Izjave funkcije izlaza VBA

Ako želite izaći iz funkcije dok je kôd pokrenut, to možete učiniti pomoću izraza 'Exit Function'.

Kod u nastavku izvukao bi prva tri numerička znaka iz alfanumeričkog tekstualnog niza. Čim dobije tri znaka, funkcija završava i vraća rezultat.

Funkcija GetNumericFirstThree (CellRef As Range) Kao Duga Dim Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Zatim izađite iz funkcije If IsNumeric (Mid (CellRef, i, 1)) Tada J = J + 1 Rezultat = Rezultat i sredina (CellRef, i, 1) GetNumericFirstThree = Rezultat završava ako je funkcija Next i End

Gornja funkcija provjerava broj znakova koji su numerički, a kada dobije 3 numerička znaka, napušta funkciju u sljedećoj petlji.

Otklanjanje pogrešaka korisnički definirane funkcije

Postoji nekoliko tehnika koje možete koristiti za ispravljanje pogrešaka korisnički definirane funkcije u VBA-i:

Otklanjanje pogrešaka prilagođene funkcije pomoću okvira za poruke

Upotrijebite funkciju MsgBox za prikaz okvira s porukom s određenom vrijednošću.

Vrijednost koju prikazujete može se temeljiti na onome što želite testirati. Na primjer, ako želite provjeriti izvršava li se kôd ili ne, svaka bi poruka radila, a ako želite provjeriti rade li petlje ili ne, možete prikazati određenu vrijednost ili brojač petlji.

Otklanjanje pogrešaka prilagođene funkcije postavljanjem točke prekida

Postavite točku prekida kako biste mogli prolaziti kroz svaki redak jedan po jedan. Da biste postavili točku prekida, odaberite redak na kojem želite i pritisnite F9 ili kliknite na sivo okomito područje lijevo od linija koda. Bilo koja od ovih metoda unijela bi točku prekida (vidjet ćete crvenu točku u sivom području).

Nakon što ste postavili točku prekida i izvršili funkciju, ona ide do crte točke prekida, a zatim se zaustavlja. Sada možete proći kroz kôd pomoću tipke F8. Jednom pritiskom na F8 prelazite na sljedeći redak koda.

Otklanjanje pogrešaka prilagođene funkcije pomoću Debug.Print u kodu

Možete koristiti Debug.Print izraz u svom kodu da biste dobili vrijednosti navedenih varijabli/argumenata u neposrednom prozoru.

Na primjer, u donjem kodu upotrijebio sam Debug.Print za dobivanje vrijednosti dviju varijabli - "j" i "Rezultat"

Funkcija GetNumericFirstThree (CellRef As Range) Kao Duga Dim Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Zatim izađite iz funkcije If IsNumeric (Mid (CellRef, i, 1)) Tada J = J + 1 Result = Result & Mid (CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Rezultat završava ako je Next i End Funkcija

Kada se ovaj kôd izvrši, u neposrednom prozoru prikazuje sljedeće.

Ugrađene funkcije programa Excel vs. VBA Korisnički definirana funkcija

Postoji nekoliko jakih prednosti korištenja ugrađenih funkcija programa Excel u odnosu na prilagođene funkcije stvorene u VBA.

• Ugrađene funkcije puno su brže od funkcija VBA.
• Kad kreirate izvješće/nadzornu ploču pomoću VBA funkcija i pošaljete ga klijentu/kolegi, oni se neće morati brinuti jesu li makronaredbe omogućene ili ne. U nekim se slučajevima klijenti/kupci uplaše vidjevši upozorenje na žutoj traci (koja ih jednostavno traži da omoguće makronaredbe).
• S ugrađenim funkcijama programa Excel ne morate brinuti o proširenjima datoteka. Ako u radnoj knjizi imate makronaredbe ili korisnički definirane funkcije, morate ih spremiti u .xlsm.

Iako postoji mnogo jakih razloga za korištenje ugrađenih funkcija programa Excel, u nekoliko je slučajeva bolje koristiti korisnički definiranu funkciju.

• Bolje je koristiti korisnički definiranu funkciju ako je vaša ugrađena formula velika i komplicirana. To postaje još važnije kada trebate nekoga drugog da ažurira formule. Na primjer, ako imate ogromnu formulu koja se sastoji od mnogo različitih funkcija, čak i promjena reference na ćeliju može biti dosadna i sklona pogreškama. Umjesto toga, možete stvoriti prilagođenu funkciju koja uzima samo jedan ili dva argumenta i obavlja sve teške poslove.
• Kad morate obaviti nešto što se ne može učiniti pomoću ugrađenih funkcija programa Excel. Primjer toga može biti kada želite izdvojiti sve numeričke znakove iz niza. U takvim slučajevima korist od korisnički definirane funkcije gar nadmašuje njene negativne strane.

Gdje staviti VBA kôd za korisnički definiranu funkciju

Prilikom izrade prilagođene funkcije morate unijeti kôd u prozor koda za radnu knjigu u kojoj želite funkciju.

U nastavku su navedeni koraci za unos koda za funkciju "GetNumeric" u radnu knjigu.

1. Idite na karticu Developer.
2. Kliknite na opciju Visual Basic. Ovo će otvoriti VB editor u pozadini.
3. U oknu Project Explorer u VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite umetnuti kôd. Ako ne vidite Project Explorer, idite na karticu View i kliknite na Project Explorer.
4. Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula u vašu radnu knjigu.
5. Kopirajte i zalijepite kôd u prozor modula.

Možda će vam se svidjeti i sljedeći Excel VBA vodiči:

• Rad s ćelijama i rasponima u Excelu VBA.
• Rad s radnim listovima u Excelu VBA.
• Rad s radnim bilježnicama pomoću VBA.
• Kako koristiti petlje u Excelu VBA.
• Excel VBA događaji - jednostavan (i potpun) vodič
• Korištenje IF Onda drugih izjava u VBA.
• Kako snimiti makro u Excelu.
• Kako pokrenuti makro u Excelu.
• Kako sortirati podatke u Excelu pomoću VBA (Korak po korak vodič).
• Excel VBA InStr funkcija - objašnjeno s primjerima.