Ustvarjanje uporabniško določene funkcije (UDF) v Excelu VBA (Ultimate Guide)

Z VBA lahko ustvarite funkcijo po meri (imenovano tudi uporabniško definirana funkcija), ki jo lahko uporabite na delovnih listih tako kot običajne funkcije.

Te so v pomoč, če obstoječe funkcije programa Excel niso dovolj. V takih primerih lahko ustvarite lastno uporabniško definirano funkcijo (UDF), ki bo ustrezala vašim posebnim potrebam.

V tej vadnici bom obravnaval vse o ustvarjanju in uporabi funkcij po meri v VBA.

Če vas zanima učenje VBA na enostaven način, si oglejte moj Spletno usposabljanje Excel VBA.

Kaj je postopek delovanja v VBA?

Postopek funkcije je koda VBA, ki izvaja izračune in vrne vrednost (ali niz vrednosti).

S postopkom Function lahko ustvarite funkcijo, ki jo lahko uporabite na delovnem listu (tako kot vse običajne Excelove funkcije, na primer SUM ali VLOOKUP).

Ko ste s funkcijo VBA ustvarili postopek funkcije, ga lahko uporabite na tri načine:

1. Kot formula na delovnem listu, kjer lahko vzame argumente kot vhodne podatke in vrne vrednost ali niz vrednosti.
2. Kot del kode podprograma VBA ali druge kode funkcije.
3. V pogojnem oblikovanju.

Čeprav je na delovnem listu že na voljo 450+ vgrajenih Excelovih funkcij, boste morda potrebovali funkcijo po meri, če:

• Vgrajene funkcije ne morejo narediti, kar želite. V tem primeru lahko ustvarite funkcijo po meri glede na vaše zahteve.
• Vgrajene funkcije lahko opravijo delo, vendar je formula dolga in zapletena. V tem primeru lahko ustvarite funkcijo po meri, ki je enostavna za branje in uporabo.

Upoštevajte, da so lahko funkcije po meri, ustvarjene z uporabo VBA, bistveno počasnejše od vgrajenih funkcij. Zato so te najbolj primerne za situacije, ko z vgrajenimi funkcijami ne morete doseči rezultata.

Funkcija vs. Podprogram v VBA

"Podprogram" vam omogoča izvajanje nabora kode, medtem ko "funkcija" vrne vrednost (ali niz vrednosti).

Na primer, če imate seznam števil (pozitivnih in negativnih) in želite identificirati negativna števila, je to, kar lahko storite s funkcijo in podprogramom.

Podprogram lahko prehaja skozi vsako celico v obsegu in lahko označi vse celice z negativno vrednostjo. V tem primeru podprogram na koncu spremeni lastnosti predmeta obsega (s spremembo barve celic).

S funkcijo po meri jo lahko uporabite v ločenem stolpcu in lahko vrne TRUE, če je vrednost v celici negativna, in FALSE, če je pozitivna. S funkcijo ne morete spremeniti lastnosti predmeta. To pomeni, da ne morete spremeniti barve celice s samo funkcijo (lahko pa to storite s pogojnim oblikovanjem s funkcijo po meri).

Ko ustvarite uporabniško določeno funkcijo (UDF) z uporabo VBA, jo lahko uporabite na delovnem listu tako kot katero koli drugo funkcijo. Več o tem bom obravnaval v razdelku »Različni načini uporabe uporabniško določene funkcije v Excelu«.

Ustvarjanje preproste uporabniško določene funkcije v VBA

Naj ustvarim preprosto uporabniško definirano funkcijo v VBA in vam pokažem, kako deluje.

Spodnja koda ustvari funkcijo, ki bo izvlekla številske dele iz alfanumeričnega niza.

Funkcija GetNumeric (CellRef As String) kot Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Potem Result = Result & Mid (CellRef, i, 1) Naprej i GetNumeric = Končna funkcija rezultata

Ko imate v modulu zgornjo kodo, lahko to funkcijo uporabite v delovnem zvezku.

Spodaj je opisano, kako deluje ta funkcija - GetNumeric - se lahko uporablja v Excelu.

Preden vam povem, kako je ta funkcija ustvarjena v VBA in kako deluje, morate vedeti nekaj stvari:

• Ko ustvarite funkcijo v VBA, je ta na voljo v celotnem delovnem zvezku, tako kot katera koli druga običajna funkcija.
• Ko vnesete ime funkcije, ki mu sledi znak enakega, bo Excel prikazal ime funkcije na seznamu ujemajočih se funkcij. V zgornjem primeru, ko sem vnesel = Get, mi je Excel pokazal seznam, ki je imel mojo funkcijo po meri.

Menim, da je to dober primer, ko lahko z VBA ustvarite preprosto funkcijo v Excelu. Enako lahko storite tudi s formulo (kot je prikazano v tej vadnici), vendar to postane zapleteno in težko razumljivo. S tem UDF -jem morate posredovati le en argument in dobili boste rezultat.

Anatomija uporabniško določene funkcije v VBA

V zgornjem razdelku sem vam dal kodo in vam pokazal, kako funkcija UDF deluje na delovnem listu.

Zdaj pa se poglobimo in poglejmo, kako je ta funkcija ustvarjena. Spodnjo kodo morate postaviti v modul v urejevalniku VB. To temo obravnavam v razdelku - "Kam postaviti kodo VBA za uporabniško določeno funkcijo".

Funkcija GetNumeric (CellRef As String) as Long 'Ta funkcija izvleče številski del iz niza Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Potem rezultat = Rezultat in sredina (CellRef, i, 1) Naprej i GetNumeric = Končna funkcija rezultata

Prva vrstica kode se začne z besedo - funkcija.

Ta beseda pove VBA, da je naša koda funkcija (in ne podprogram). Besedi Funkcija sledi ime funkcije - GetNumeric. To ime bomo uporabili na delovnem listu za uporabo te funkcije.

• Ime funkcije ne sme vsebovati presledkov. Prav tako ne morete poimenovati funkcije, če je v nasprotju z imenom sklica na celico. Funkcije na primer ne morete poimenovati ABC123, saj se nanaša tudi na celico v Excelovem delovnem listu.
• Funkciji ne smete dati istega imena kot obstoječi. Če to storite, bo Excel dal prednost vgrajeni funkciji.
• Če želite ločiti besede, lahko uporabite podčrtaj. Get_Numeric je na primer sprejemljivo ime.

Imenu funkcije sledijo nekateri argumenti v oklepaju. To so argumenti, ki bi jih naša funkcija potrebovala od uporabnika. To so podobni argumenti, ki jih moramo posredovati vgrajenim funkcijam programa Excel. Na primer v funkciji COUNTIF obstajata dva argumenta (obseg in merila)

V oklepaju morate podati argumente.

V našem primeru obstaja samo en argument - CellRef.

Prav tako je dobra praksa določiti, kakšen argument funkcija pričakuje. V tem primeru, ker bomo funkcijo hranili kot referenco celice, lahko argument podamo kot tip »Razpon«. Če ne podate podatkovnega tipa, bi VBA menil, da je to različica (kar pomeni, da lahko uporabite kateri koli tip podatkov).

Če imate več argumentov, jih lahko podate v istih oklepajih - ločenih z vejico. V nadaljevanju te vadnice bomo videli, kako uporabiti več argumentov v uporabniško določeni funkciji.

Upoštevajte, da je funkcija podana kot podatkovni tip "String". To bi VBA povedalo, da bi bil rezultat formule podatkovnega tipa String.

Čeprav lahko tukaj uporabim številski tip podatkov (na primer dolg ali dvojni), bi to omejilo obseg številk, ki jih lahko vrne. Če imam niz, dolg 20 številk, ki ga moram izvleči iz celotnega niza, bi razglasitev funkcije kot dolga ali dvojna povzročila napako (saj bi bila številka izven njenega obsega). Zato sem ohranil izhodni podatkovni tip funkcije kot String.

Druga vrstica kode - tista v zeleni barvi, ki se začne z apostrofom - je komentar. Pri branju kode VBA ignorira to vrstico. To lahko uporabite za dodajanje opisa ali podrobnosti o kodi.

Tretja vrstica kode razglasi spremenljivko „StringLength“ kot podatkovni tip Integer. To je spremenljivka, kjer shranimo vrednost dolžine niza, ki se analizira s formulo.

Četrta vrstica razglasi spremenljivko Rezultat kot podatkovni tip String. To je spremenljivka, pri kateri bomo izvlekli številke iz alfanumeričnega niza.

Peta vrstica dodeli dolžino niza v vhodnem argumentu spremenljivki 'StringLength'. Upoštevajte, da se "CellRef" nanaša na argument, ki ga bo uporabnik navedel pri uporabi formule na delovnem listu (ali pri uporabi v VBA - kar bomo videli kasneje v tej vadnici).

Šesta, sedma in osma vrstica so del zanke For Next. Zanka teče tolikokrat, kolikor znakov je v vhodnem argumentu. To številko poda funkcija LEN in je dodeljena spremenljivki „StringLength“.

Tako zanka teče od '1 do Stringlength'.

V zanki stavek IF analizira vsak znak niza in če je številski, doda ta številčni znak spremenljivki Result. Za to uporablja funkcijo MID v VBA.

Druga zadnja vrstica kode dodeljuje vrednosti rezultata funkciji. Ta vrstica kode zagotavlja, da funkcija vrne vrednost »Rezultat« nazaj v celico (od koder se pokliče).

Zadnja vrstica kode je End Function. To je obvezna vrstica kode, ki pove VBA, da se koda funkcije tukaj konča.

Zgornja koda razlaga različne dele tipične funkcije po meri, ustvarjene v VBA. V naslednjih razdelkih se bomo poglobili v te elemente in si ogledali tudi različne načine izvajanja funkcije VBA v Excelu.

Argumenti v uporabniško določeni funkciji v VBA

V zgornjih primerih, kjer smo ustvarili uporabniško definirano funkcijo za pridobitev številskega dela iz alfanumeričnega niza (GetNumeric), je bila funkcija zasnovana tako, da sprejme en sam argument.

V tem razdelku bom obravnaval, kako ustvariti funkcije, ki ne sprejemajo argumentov za tiste, ki sprejmejo več argumentov (obvezne in neobvezne argumente).

Ustvarjanje funkcije v VBA brez argumentov

V Excelovem delovnem listu imamo več funkcij, ki ne sprejemajo argumentov (na primer RAND, TODAY, NOW).

Te funkcije niso odvisne od vhodnih argumentov. Funkcija TODAY bi na primer vrnila trenutni datum, funkcija RAND pa naključno število med 0 in 1.

Takšno podobno funkcijo lahko ustvarite tudi v VBA.

Spodaj je koda, ki vam bo dala ime datoteke. Ne sprejema nobenih argumentov, saj rezultat, ki ga mora vrniti, ni odvisen od nobenega argumenta.

Funkcija WorkbookName () Kot String WorkbookName = ThisWorkbook.Name Končna funkcija

Zgornja koda podaja rezultat funkcije kot podatkovni tip String (rezultat, ki ga želimo, je ime datoteke - to je niz).

Ta funkcija funkciji dodeli vrednost »ThisWorkbook.Name«, ki se vrne, ko je funkcija uporabljena na delovnem listu.

Če je datoteka shranjena, vrne ime s pripono datoteke, drugače pa preprosto poda ime.

Zgoraj pa obstaja ena težava.

Če se ime datoteke spremeni, se ne bo samodejno posodobilo. Običajno se funkcija osveži vsakič, ko pride do spremembe vhodnih argumentov. Ker pa v tej funkciji ni argumentov, se funkcija ne preračuna (tudi če spremenite ime delovnega zvezka, ga zaprite in nato znova odprite).

Če želite, lahko prisilno preračunate z bližnjico na tipkovnici - Control + Alt + F9.

Če želite, da se formula preračuna vsakič, ko pride do spremembe na delovnem listu, morate zanjo vnesti vrstico kode.

Spodnja koda omogoča, da se funkcija preračuna vsakič, ko pride do spremembe na delovnem listu (tako kot druge podobne funkcije delovnega lista, kot sta funkcija TODAY ali RAND).

Funkcija WorkbookName () Kot String Application.Volatile True WorkbookName = ThisWorkbook.Name End Funkcija

Če spremenite ime delovnega zvezka, se bo ta funkcija posodobila vsakič, ko pride do spremembe na delovnem listu ali ko znova odprete ta delovni zvezek.

Ustvarjanje funkcije v VBA z enim argumentom

V enem od zgornjih razdelkov smo že videli, kako ustvariti funkcijo, ki sprejme le en argument (zgoraj opisana funkcija GetNumeric).

Ustvarimo še eno preprosto funkcijo, ki ima le en argument.

Funkcija, ustvarjena s spodnjo kodo, bi referenčno besedilo pretvorila v velike črke. Zdaj že imamo funkcijo za to v Excelu in ta funkcija je samo zato, da vam pokaže, kako deluje. Če morate to storiti, je bolje uporabiti vgrajeno funkcijo UPPER.

Funkcija ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Končna funkcija

Ta funkcija uporablja funkcijo UCase v VBA za spreminjanje vrednosti spremenljivke CellRef. Nato vrednost dodeli funkciji ConvertToUpperCase.

Ker ima ta funkcija argument, nam ni treba uporabiti aplikacije Application.Volatile. Takoj, ko se argument spremeni, bi se funkcija samodejno posodobila.

Ustvarjanje funkcije v VBA z več argumenti

Tako kot funkcije delovnega lista lahko tudi v VBA ustvarite funkcije, ki sprejmejo več argumentov.

Spodnja koda bi ustvarila funkcijo, ki bo izvlekla besedilo pred določenim ločilnikom. Potrebuje dva argumenta - sklic na celico z besedilnim nizom in ločilo.

Funkcija GetDataBeforeDelimiter (CellRef Kot Range, ločilo Kot String) As String Dim Rezultat Kot String Dim DelimPosition Kot Integer DelimPosition = instr (1, CellRef, ločilo, vbBinaryCompare) - 1. Rezultat = Levi (CellRef, DelimPosition) GetDataBeforeDelimiter = Rezultat End Function

Če morate v uporabniško definirani funkciji uporabiti več argumentov, so lahko vsi argumenti v oklepaju ločeni z vejico.

Upoštevajte, da lahko za vsak argument podate podatkovni tip. V zgornjem primeru je bil „CellRef“ razglašen za podatkovni tip obsega, „Delim“ pa kot podatkovni tip String. Če ne podate nobenega tipa podatkov, VBA meni, da gre za različen tip podatkov.

Ko uporabljate zgornjo funkcijo na delovnem listu, morate podati sklic na celico, ki ima besedilo kot prvi argument, in ločila (-je) v dvojnih narekovajih kot drugi argument.

Nato s funkcijo INSTR v VBA preveri položaj ločevalnika. Ta položaj se nato uporabi za ekstrahiranje vseh znakov pred ločilnikom (z uporabo funkcije LEFT).

Končno funkciji dodeli rezultat.

Ta formula še zdaleč ni popolna. Če na primer vnesete ločilo, ki ga v besedilu ne najdete, bi to povzročilo napako. Zdaj lahko uporabite funkcijo IFERROR na delovnem listu, da se znebite napak, ali pa uporabite spodnjo kodo, ki vrne celotno besedilo, ko ne najde ločila.

Funkcija GetDataBeforeDelimiter (CellRef As Range, Delim As String) kot niz Dim Rezultat Kot String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Če DelimPosition <0 Potem DelimPosition = Len (CellR) CellRef, DelimPosition) GetDataBeforeDelimiter = Končna funkcija rezultata

To funkcijo lahko dodatno optimiziramo.

Če besedilo (iz katerega želite izvleči del pred ločilnikom) vnesete neposredno v funkcijo, bi se vam prikazala napaka. Kar naprej … poskusite!

To se zgodi, ko smo kot tip podatkov za obseg določili „CellRef“.

Ali pa, če želite, da je razmejevalnik v celici in uporabite sklic na celico, namesto da ga trdo kodirate v formuli, tega ne morete storiti z zgornjo kodo. To je zato, ker je bil Delim razglašen za niz podatkovnega tipa.

Če želite, da ima funkcija prilagodljivost za sprejemanje neposrednega vnosa besedila ali sklicev na celice od uporabnika, morate odstraniti deklaracijo podatkovnega tipa. Tako bi argument na koncu postal različen podatkovni tip, ki lahko sprejme katero koli vrsto argumenta in ga obdela.

Spodnja koda bi to naredila:

Funkcija GetDataBeforeDelimiter (CellRef, Delim) Kot rezultat zatemnitve niza Kot niz zatemnitve DelimPosition kot celo število DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Če je DelimPosition <0 Potem je DelimPosition = Len (CellRef) ResultRef GetDataBeforeDelimiter = Končna funkcija rezultata

Ustvarjanje funkcije v VBA z izbirnimi argumenti

V Excelu je veliko funkcij, pri katerih so nekateri argumenti neobvezni.

Legendarna funkcija VLOOKUP ima na primer 3 obvezne argumente in en izbirni argument.

Izbirni argument, kot že ime pove, ni obvezen za podajanje. Če ne podate enega od obveznih argumentov, bo vaša funkcija povzročila napako, če pa ne določite izbirnega argumenta, bi vaša funkcija delovala.

Toda izbirni argumenti niso neuporabni. Omogočajo vam izbiro med številnimi možnostmi.

Na primer, v funkciji VLOOKUP, če ne podate četrtega argumenta, VLOOKUP izvede približen pregled in če zadnji argument navedete kot FALSE (ali 0), se natančno ujema.

Ne pozabite, da morajo neobvezni argumenti vedno slediti za vsemi zahtevanimi argumenti. Na začetku ne morete imeti neobveznih argumentov.

Zdaj pa poglejmo, kako ustvariti funkcijo v VBA z izbirnimi argumenti.

Funkcija samo z neobveznim argumentom

Kolikor vem, ni vgrajene funkcije, ki bi sprejemala samo izbirne argumente (tukaj se lahko motim, vendar si ne morem omisliti nobene take funkcije).

Lahko pa ga ustvarimo z VBA.

Spodaj je koda funkcije, ki vam bo dala trenutni datum v obliki dd-mm-llll, če ne vnesete nobenega argumenta (tj. Pustite prazno), in v obliki "dd mmmm, llll", če vnesete karkoli kot argument (tj. karkoli, da argument ni prazen).

Funkcija CurrDate (neobvezno fmt kot varianta) Rezultat zatemnitve, če manjka (fmt) Potem je CurrDate = Format (datum, "dd-mm-llll") Sicer CurrDate = Format (datum, "dd mmmm, llll") Konec funkcije

Upoštevajte, da zgornja funkcija uporablja 'IsMissing' za preverjanje, ali argument manjka ali ne. Če želite uporabiti funkcijo IsMissing, mora biti vaš izbirni argument različnega podatkovnega tipa.

Zgornja funkcija deluje ne glede na to, kaj vnesete kot argument. V kodi preverjamo le, ali je naveden izbirni argument ali ne.

To lahko naredite bolj robustno, če za argumente vzamete samo določene vrednosti in v preostalih primerih prikažete napako (kot je prikazano v spodnji kodi).

Funkcija CurrDate (neobvezno fmt kot varianta) Rezultat zatemnitve, če manjka (fmt) Potem je CurrDate = Format (datum, "dd-mm-llll") ElseIf fmt = 1 Potem je CurrDate = Format (datum, "dd mmmm, llll") Drugi CurrDate = CVErr (xlErrValue) Funkcija End, če je konec

Zgornja koda ustvari funkcijo, ki prikazuje datum v obliki »dd-mm-llll«, če argument ni naveden, in v obliki »dd mmmm, llll«, ko je argument 1. V vseh drugih primerih daje napako.

Funkcija z obveznimi in izbirnimi argumenti

Videli smo že kodo, ki izvleče številski del iz niza.

Zdaj pa poglejmo podoben primer, ki vključuje tako zahtevane kot neobvezne argumente.

Spodnja koda ustvari funkcijo, ki izvleče del besedila iz niza. Če je izbirni argument TRUE, poda rezultat z velikimi črkami, če pa je izbirni argument FALSE ali je izpuščen, poda rezultat takšen, kot je.

Funkcija GetText (CellRef kot obseg, izbirno TextCase = False) Kot niz Dim StringLength Kot celo število Dim Rezultat kot String StringLength = Len (CellRef) Za i = 1 Za StringLength Če ne (IsNumeric (Mid (CellRef, i, 1))) Potem Rezultat = Rezultat in sredina (CellRef, i, 1) Naprej i Če je TextCase = True, potem Rezultat = UCase (Rezultat) GetText = Končna funkcija rezultata

Upoštevajte, da smo v zgornji kodi vrednost 'TextCase' inicializirali kot False (poglejte v oklepaju v prvi vrstici).

S tem smo zagotovili, da se izbirni argument začne s privzeto vrednostjo, ki je FALSE. Če uporabnik poda vrednost kot TRUE, funkcija vrne besedilo z velikimi tiskanimi črkami, če pa uporabnik opcijski argument navede kot FALSE ali ga izpusti, je vrnjeno besedilo takšno, kot je.

Ustvarjanje funkcije v VBA z nizom kot argumentom

Doslej smo videli primere ustvarjanja funkcije z izbirnimi/zahtevanimi argumenti - kjer so bili ti argumenti ena sama vrednost.

Ustvarite lahko tudi funkcijo, ki lahko za argument vzame matriko. V funkcijah delovnega lista Excel je veliko funkcij, ki sprejemajo argumente matrike, na primer SUM, VLOOKUP, SUMIF, COUNTIF itd.

Spodaj je koda, ki ustvari funkcijo, ki poda vsoto vseh parnih števil v določenem obsegu celic.

Funkcija AddEven (CellRef kot obseg) Dim Cell As Range Za vsako celico v CellRef If IsNumeric (Cell.Value) Potem Če Cell.Value Mod 2 = 0 Potem Result = Result + Cell.Value End Če se konča Če je naslednja celica AddEven = Result End Funkcija

To funkcijo lahko uporabite na delovnem listu in podate obseg celic, ki imajo za argument številke. Funkcija bi vrnila eno samo vrednost - vsoto vseh parnih števil (kot je prikazano spodaj).

V zgornji funkciji smo namesto ene vrednosti podali matriko (A1: A10). Če želite, da to deluje, se prepričajte, da lahko vaš podatkovni tip argumenta sprejme matriko.

V zgornji kodi sem argument CellRef navedel kot obseg (ki lahko vzame matriko kot vhod). Tu lahko uporabite tudi različico podatkovnega tipa.

V kodi je zanka za vsako, ki gre skozi vsako celico in preveri, ali je število ne. Če ni, se nič ne zgodi in se premakne v naslednjo celico. Če gre za številko, preveri, ali je sodo ali ne (z uporabo funkcije MOD).

Na koncu se dodajo vse parne številke in vsota se funkciji dodeli nazaj.

Ustvarjanje funkcije z nedoločenim številom argumentov

Med ustvarjanjem nekaterih funkcij v VBA morda ne boste vedeli natančnega števila argumentov, ki jih želi uporabnik navesti. Zato je treba ustvariti funkcijo, ki lahko sprejme toliko predloženih argumentov, in jih uporabiti za vrnitev rezultata.

Primer takšne funkcije delovnega lista je funkcija SUM. Vanj lahko navedete več argumentov (na primer tega):

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

Zgornja funkcija bi dodala vrednosti vsem tem argumentom. Upoštevajte tudi, da so to lahko ena sama celica ali niz celic.

Takšno funkcijo lahko ustvarite v VBA tako, da imate zadnji argument (ali pa bi lahko bil edini argument) neobvezen. Pred tem izbirnim argumentom bi morala biti tudi ključna beseda „ParamArray“.

'ParamArray' je modifikator, ki vam omogoča, da sprejmete toliko argumentov, kot želite. Upoštevajte, da je z uporabo besede ParamArray pred argumentom argument neobvezen. Vendar vam tukaj ni treba uporabiti besede Izbirno.

Zdaj pa ustvarimo funkcijo, ki lahko sprejme poljubno število argumentov in bi dodala vsa števila v navedenih argumentih:

Funkcija AddArguments (ParamArray arglist () As Variant) Za vsak arglist na arglist AddArguments = AddArguments + arg Naslednji arg Konec funkcije

Zgornja funkcija lahko sprejme poljubno število argumentov in te argumente doda za rezultat.

Upoštevajte, da lahko kot argument uporabite samo eno vrednost, sklic na celico, logično vrednost ali izraz. Za argument ne morete navesti matrike. Na primer, če je eden od vaših argumentov D8: D10, bi vam ta formula povzročila napako.

Če želite uporabljati oba večcelična argumenta, morate uporabiti spodnjo kodo:

Funkcija AddArguments (ParamArray arglist () As Variant) Za vsak arglist v arglistu Za vsako celico v arg AddArguments = AddArguments + Celica Naslednja celica Naslednja celica Naslednja arg funkcija

Upoštevajte, da ta formula deluje z več celicami in sklici na matriko, vendar ne more obdelati trdo kodiranih vrednosti ali izrazov. Robustnejšo funkcijo lahko ustvarite s preverjanjem in obravnavo teh stanj, vendar to tukaj ni namen.

Namen tukaj je pokazati, kako deluje ParamArray, tako da lahko v funkciji dovolite neomejeno število argumentov. Če želite boljšo funkcijo od tiste, ki jo ustvari zgornja koda, uporabite funkcijo SUM na delovnem listu.

Ustvarjanje funkcije, ki vrne niz

Doslej smo videli funkcije, ki vračajo eno samo vrednost.

Z VBA lahko ustvarite funkcijo, ki vrne različico, ki lahko vsebuje celotno paleto vrednosti.

Formule matrike so na voljo tudi kot vgrajene funkcije v Excelovih delovnih listih. Če poznate matrične formule v Excelu, bi vedeli, da jih vnesete s tipkama Control + Shift + Enter (namesto samo Enter). Več o formulah matrike si lahko preberete tukaj. Če ne poznate formul matrike, ne skrbite, nadaljujte z branjem.

Ustvarimo formulo, ki vrne niz treh števil (1,2,3).

Spodnja koda bi to naredila.

Funkcija ThreeNumbers () Kot varianta Dim NumberValue (1 do 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Funkcija

V zgornji kodi smo kot varianto določili funkcijo 'ThreeNumbers'. To mu omogoča shranjevanje niza vrednosti.

Spremenljivka „NumberValue“ je deklarirana kot matrika s 3 elementi. Vsebuje tri vrednosti in jih dodeli funkciji 'ThreeNumbers'.

To funkcijo lahko uporabite na delovnem listu tako, da vnesete funkcijo in pritisnete tipko Control + Shift + Enter (držite tipki Control in Shift in nato pritisnite Enter).

Ko to storite, se v celici vrne 1, v resnici pa vse tri vrednosti. Če želite to preveriti, uporabite naslednjo formulo:

= MAX (tri številke ())

Uporabite zgornjo funkcijo s tipkama Control + Shift + Enter. Opazili boste, da je rezultat zdaj 3, saj gre za največje vrednosti v matriki, ki jo vrne funkcija Max, ki dobi tri številke kot rezultat naše uporabniško določene funkcije - ThreeNumbers.

Z isto tehniko lahko ustvarite funkcijo, ki vrne niz imen mesecev, kot prikazuje spodnja koda:

Funkcija Meseci () Kot varianta Dim Mesečno ime (1 do 12) Mesečno ime (1) = "Januarsko" Mesečno ime (2) = "Februarsko" Mesečno ime (3) = "Marec" Mesečno ime (4) = "April" Mesečno ime (5) = "May" Mesečno Ime (6) = "Junijsko" Mesečno Ime (7) = "Julijsko" Mesečno Ime (8) = "Avgustovsko" Mesečno Ime (9) = "Septembrsko" Mesečno Ime (10) = "Oktobrsko" Mesečno Ime (11) = "November "MonthName (12) =" December "Months = MonthName End Funkcija

Zdaj, ko v Excelov delovni list vnesete funkcijo = Months () in uporabite Control + Shift + Enter, bo vrnila celotno paleto imen mesecev. Upoštevajte, da v celici vidite samo januar, saj je to prva vrednost v matriki. To ne pomeni, da matrika vrne samo eno vrednost.

Če želite prikazati dejstvo, da vrača vse vrednosti, naredite to - izberite celico s formulo, pojdite na vrstico s formulami, izberite celotno formulo in pritisnite F9. To vam bo pokazalo vse vrednosti, ki jih funkcija vrne.

To lahko uporabite s spodnjo formulo INDEX, da dobite seznam vseh imen mesecev naenkrat.

= INDEX (Meseci (), ROW ())

Če imate veliko vrednot, ni dobra praksa, da te vrednosti dodelite eno za drugo (kot smo storili zgoraj). Namesto tega lahko uporabite funkcijo Array v VBA.

Torej bi se ista koda, pri kateri ustvarimo funkcijo "Meseci", skrajšala, kot je prikazano spodaj:

Funkcijski meseci () Kot različni meseci = niz ("januar", "februar", "marec", "april", "maj", "junij", _ "julij", "avgust", "september", "oktober" , "November", "December") Končaj funkcijo

Zgornja funkcija uporablja funkcijo Array za dodelitev vrednosti neposredno funkciji.

Upoštevajte, da vse zgoraj ustvarjene funkcije vračajo vodoravno polje vrednosti. To pomeni, da če izberete 12 vodoravnih celic (recimo A1: L1) in v celico A1 vnesete formulo = Months (), se vam prikažejo vsa imena mesecev.

Kaj pa, če želite te vrednosti v navpičnem razponu celic.

To lahko storite s formulo TRANSPOSE na delovnem listu.

Preprosto izberite 12 navpičnih celic (sosednjih) in vnesite spodnjo formulo.

Razumevanje obsega uporabniško določene funkcije v Excelu

Funkcija ima lahko dva področja uporabe - Javno ali Zasebno.

• A Javni obseg pomeni, da je funkcija na voljo za vse liste v delovnem zvezku, pa tudi za vse postopke (pod in funkcija) v vseh modulih v delovnem zvezku. To je uporabno, če želite poklicati funkcijo iz podprograma (kako bomo to naredili, bomo videli v naslednjem razdelku).
• A Zasebni obseg pomeni, da je funkcija na voljo samo v modulu, v katerem obstaja. Ne morete ga uporabljati v drugih modulih. Prav tako ga ne boste videli na seznamu funkcij na delovnem listu. Na primer, če je ime vaše funkcije „Meseci ()“ in funkcijo vnesete v Excel (za znakom =), vam ne bo prikazalo imena funkcije. Lahko pa ga vseeno uporabite, če vnesete ime formule.

Če ne določite ničesar, je funkcija privzeto javna.

Spodaj je zasebna funkcija:

Zasebna funkcija Delovni zvezekName () Kot niz Delovni zvezekName = Ta delovni zvezek. Končna funkcija imena

To funkcijo lahko uporabite v podprogramih in postopkih v istih modulih, ne morete pa je uporabljati v drugih modulih. Ta funkcija prav tako ne bo prikazana na delovnem listu.

Spodnja koda bi to funkcijo objavila. Prikazal se bo tudi na delovnem listu.

Funkcija WorkbookName () Kot String WorkbookName = ThisWorkbook.Name Končna funkcija

Različni načini uporabe uporabniško določene funkcije v Excelu

Ko ustvarite uporabniško definirano funkcijo v VBA, jo lahko uporabite na različne načine.

Najprej poglejmo, kako uporabljati funkcije na delovnem listu.

Uporaba UDF -jev v delovnih listih

Na delovnem listu smo že videli primere uporabe funkcije, ustvarjene v VBA.

Vse kar morate storiti je, da vnesete ime funkcije in se prikaže v interllisense.

Upoštevajte, da mora biti funkcija prikazana na delovnem listu javna funkcija (kot je razloženo v zgornjem razdelku).

Uporabite lahko tudi pogovorno okno Vstavi funkcijo, da vstavite uporabniško določeno funkcijo (po korakih spodaj). To bi delovalo samo za javne funkcije.

• Pojdite na zavihek Podatki.
• Kliknite možnost »Vstavi funkcijo«.
• V pogovornem oknu Vstavi funkcijo kot kategorijo izberite Uporabniško določeno. Ta možnost se prikaže samo, če imate funkcijo v urejevalniku VB (in je funkcija javna).
• Izberite funkcijo s seznama vseh funkcij, ki jih definira javni uporabnik.
• Kliknite gumb V redu.

Zgornji koraki bi funkcijo vstavili na delovni list. Prikaže tudi pogovorno okno Funkcijski argumenti, ki vam bo dal podrobnosti o argumentih in rezultatu.

Uporabniško definirano funkcijo lahko uporabite tako kot katero koli drugo funkcijo v Excelu. To tudi pomeni, da ga lahko uporabljate z drugimi vgrajenimi Excelovimi funkcijami. Na primer. spodnja formula bi dala ime delovnega zvezka z velikimi tiskanimi črkami:

= ZGORNJE (Ime delovnega zvezka ())

Uporaba uporabniško določenih funkcij v postopkih in funkcijah VBA

Ko ustvarite funkcijo, jo lahko uporabite tudi v drugih podprocedurah.

Če je funkcija javna, jo lahko uporabite v katerem koli postopku v istem ali drugačnem modulu. Če je zasebno, ga je mogoče uporabiti samo v istem modulu.

Spodaj je funkcija, ki vrne ime delovnega zvezka.

Funkcija WorkbookName () Kot String WorkbookName = ThisWorkbook.Name Končna funkcija

Spodnji postopek pokliče funkcijo in nato prikaže ime v polju za sporočila.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Funkcijo lahko pokličete tudi iz druge funkcije.

V spodnjih kodah prva koda vrne ime delovnega zvezka, druga pa vrne ime z velikimi črkami s klicem prve funkcije.

Funkcija WorkbookName () Kot String WorkbookName = ThisWorkbook.Name Končna funkcija

Funkcija WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Končna funkcija

Klicanje uporabniško določene funkcije iz drugih delovnih zvezkov

Če imate funkcijo v delovnem zvezku, lahko to funkcijo pokličete tudi v drugih delovnih zvezkih.

To lahko storite na več načinov:

1. Ustvarjanje dodatka
2. Shranjevanje funkcije v osebnem delovnem zvezku makra
3. Sklicevanje na funkcijo iz drugega delovnega zvezka.

Ustvarjanje dodatka

Z ustvarjanjem in nameščanjem dodatka boste imeli v njem na voljo funkcijo po meri v vseh delovnih zvezkih.

Recimo, da ste ustvarili funkcijo po meri - "GetNumeric" in jo želite v vseh delovnih zvezkih. Če želite to narediti, ustvarite nov delovni zvezek in imejte kodo funkcije v modulu v tem novem delovnem zvezku.

Sledite spodnjim korakom, da ga shranite kot dodatek in ga nato namestite v Excel.

• Pojdite na zavihek Datoteka in kliknite Shrani kot.
• V pogovornem oknu Shrani kot spremenite vrsto »Shrani kot« v .xlam. Ime, ki ga dodelite datoteki, bi bilo ime vašega dodatka. V tem primeru je datoteka shranjena z imenom GetNumeric.
  • Opazili boste, da se pot datoteke, kamor se shrani, samodejno spremeni. Uporabite lahko privzetega ali ga po želji spremenite.
• Odprite nov delovni zvezek Excel in pojdite na zavihek Razvijalec.
• Kliknite možnost Excelovi dodatki.
• V pogovornem oknu z dodatki poiščite in shranite datoteko, ki ste jo shranili, in kliknite V redu.

Zdaj je dodatek aktiviran.

Zdaj lahko uporabite funkcijo po meri v vseh delovnih zvezkih.

Shranjevanje funkcije v delovnem zvezku osebnih makrov

Delovni zvezek osebnega makra je skriti delovni zvezek v vašem sistemu, ki se odpre vsakič, ko odprete aplikacijo Excel.

To je kraj, kjer lahko shranite kode makrov in nato dostopate do teh makrov iz katerega koli delovnega zvezka. To je odličen kraj za shranjevanje tistih makrov, ki jih želite pogosto uporabljati.

V Excelu privzeto ni osebnega delovnega zvezka makra. Ustvariti ga morate tako, da posnamete makro in ga shranite v delovni zvezek Osebni makro.

Podrobne korake o ustvarjanju in shranjevanju makrov v osebnem delovnem zvezku za makro najdete tukaj.

Sklicevanje na funkcijo iz drugega delovnega zvezka

Čeprav bi prvi dve metodi (ustvarjanje dodatka in uporaba osebnega delovnega zvezka makra) delovali v vseh situacijah, mora biti ta delovni zvezek odprt, če se želite sklicevati na funkcijo iz drugega delovnega zvezka.

Recimo, da imate delovni zvezek z imenom 'Delovni zvezek s formuloin ima funkcijo z imenom 'GetNumeric '.

Če želite uporabiti to funkcijo v drugem delovnem zvezku (medtem ko je Delovni zvezek s formulo je odprt), lahko uporabite spodnjo formulo:

= 'Delovni zvezek s formulo'! GetNumeric (A1)

Zgornja formula bo uporabila uporabniško definirano funkcijo v Delovni zvezek s formulo datoteko in vam da rezultat.

Upoštevajte, da ker ime delovnega zvezka vsebuje presledke, ga morate vstaviti v enojne narekovaje.

Uporaba izjave o funkciji Izhod VBA

Če želite zapustiti funkcijo, medtem ko se koda izvaja, lahko to storite s stavkom »Izhodna funkcija«.

Spodnja koda bi izvlekla prve tri številske znake iz alfanumeričnega besedilnega niza. Takoj, ko dobi tri znake, se funkcija konča in vrne rezultat.

Funkcija GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 then Exit Function If IsNumeric (Mid (CellRef, i, 1)) then J = J + 1 Rezultat = Rezultat in sredina (CellRef, i, 1) GetNumericFirstThree = Rezultat Konec Če je funkcija Naprej i Konec

Zgornja funkcija preveri število številk, ki so številske, in ko dobi 3 številske znake, zapre funkcijo v naslednji zanki.

Odpravljanje napak uporabniško določene funkcije

Med odpravljanjem napak uporabniško določene funkcije v VBA lahko uporabite nekaj tehnik:

Odpravljanje napak po meri s pomočjo sporočilnega polja

Uporabite funkcijo MsgBox za prikaz okna sporočila z določeno vrednostjo.

Prikazana vrednost lahko temelji na tem, kaj želite preizkusiti. Na primer, če želite preveriti, ali se koda izvaja ali ne, bi delovalo katero koli sporočilo, če pa želite preveriti, ali zanke delujejo ali ne, lahko prikažete določeno vrednost ali števec zanke.

Odpravljanje napak po meri z nastavitvijo prelomne točke

Nastavite točko preloma, da boste lahko stopili skozi vsako vrstico eno za drugo. Če želite nastaviti prelomno točko, izberite vrstico, kjer jo želite, in pritisnite F9 ali kliknite na sivo navpično območje, ki je levo od kodnih vrstic. Vsaka od teh metod bi vstavila prelomno točko (v sivi coni boste videli rdečo piko).

Ko nastavite prelomno točko in izvedete funkcijo, gre do črte prelomne točke in se nato ustavi. Zdaj lahko stopite skozi kodo s tipko F8. Ko enkrat pritisnete F8, se premaknete v naslednjo vrstico kode.

Odpravljanje napak po meri s pomočjo Debug.Print v kodi

Za pridobitev vrednosti navedenih spremenljivk/argumentov v neposrednem oknu lahko uporabite stavek Debug.Print.

Na primer, v spodnji kodi sem uporabil Debug.Print, da sem dobil vrednost dveh spremenljivk - "j" in "Rezultat"

Funkcija GetNumericFirstThree (CellRef As Range) As Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 then Exit Function If IsNumeric (Mid (CellRef, i, 1)) then J = J + 1 Rezultat = Rezultat in sredina (CellRef, i, 1) Debug.Print J, Rezultat GetNumericFirstThree = Rezultat Konec Če je funkcija Naprej i Končaj

Ko se ta koda izvede, se v neposrednem oknu prikaže naslednje.

Vgrajene funkcije Excel vs. Uporabniško definirana funkcija VBA

Uporaba vgrajenih funkcij Excel pred funkcijami po meri, ustvarjenimi v VBA, ima nekaj močnih prednosti.

• Vgrajene funkcije so veliko hitrejše od funkcij VBA.
• Ko ustvarite poročilo/nadzorno ploščo s funkcijami VBA in ga pošljete odjemalcu/sodelavcu, jim ne bo treba skrbeti, ali so makri omogočeni ali ne. V nekaterih primerih se stranke/stranke ustrašijo, ko v rumeni vrstici vidijo opozorilo (ki jih preprosto prosi, naj omogočijo makre).
• Z vgrajenimi funkcijami Excel vam ni treba skrbeti za razširitve datotek. Če imate v delovnem zvezku makre ali uporabniško določene funkcije, jih morate shraniti v .xlsm.

Čeprav obstaja veliko močnih razlogov za uporabo vgrajenih funkcij programa Excel, je v nekaterih primerih bolje uporabiti funkcijo, ki jo določi uporabnik.

• Če je vaša vgrajena formula velika in zapletena, je bolje uporabiti uporabniško definirano funkcijo. To postane še bolj pomembno, ko potrebujete nekoga drugega, da posodobi formule. Če imate na primer ogromno formulo, sestavljeno iz številnih različnih funkcij, je lahko tudi spreminjanje sklica na celico dolgočasno in nagnjeno k napakam. Namesto tega lahko ustvarite funkcijo po meri, ki vzame le en ali dva argumenta in opravi vse, kar je težko za dvig zaledja.
• Ko morate narediti nekaj, česar vgrajene funkcije programa Excel ne morejo narediti. Primer tega je lahko, ko želite izvleči vse številske znake iz niza. V takšnih primerih korist uporabe uporabniško definirane funkcije gar odtehta njene negativne strani.

Kje postaviti kodo VBA za uporabniško določeno funkcijo

Ko ustvarjate funkcijo po meri, morate kodo vnesti v okno kode za delovni zvezek, v katerem želite funkcijo.

Spodaj so navedeni koraki za vnos kode za funkcijo »GetNumeric« v delovni zvezek.

1. Pojdite na zavihek Razvijalec.
2. Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
3. V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
4. Pojdite na Vstavi in ​​kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
5. Kodo kopirajte in prilepite v okno modula.

Morda vam bodo všeč tudi naslednji vadnici Excel VBA:

• Delo s celicami in obsegi v Excelu VBA.
• Delo z delovnimi listi v Excelu VBA.
• Delo z delovnimi zvezki z uporabo VBA.
• Kako uporabljati zanke v Excelu VBA.
• Dogodki Excel VBA - enostaven (in popoln) vodnik
• Uporaba IF Nato drugih stavkov v VBA.
• Kako posneti makro v Excelu.
• Kako zagnati makro v Excelu.
• Kako razvrstiti podatke v Excelu z uporabo VBA (Vodnik po korakih).
• Funkcija Excel VBA InStr - pojasnjeno s primeri.