Kasutaja määratud funktsiooni (UDF) loomine Excel VBA -s (ülim juhend)

VBA abil saate luua kohandatud funktsiooni (mida nimetatakse ka kasutaja määratud funktsiooniks), mida saab töölehtedel kasutada nagu tavalisi funktsioone.

Need on abiks, kui olemasolevatest Exceli funktsioonidest ei piisa. Sellistel juhtudel saate luua oma kohandatud kasutaja määratletud funktsiooni (UDF), mis vastab teie konkreetsetele vajadustele.

Selles õpetuses käsitlen kõike kohandatud funktsioonide loomise ja kasutamise kohta VBA -s.

Kui olete huvitatud VBA lihtsast õppimisest, vaadake minu lehte Online VBA koolitus Excelis.

Mis on VBA funktsiooniprotseduur?

Funktsiooniprotseduur on VBA -kood, mis teostab arvutusi ja tagastab väärtuse (või väärtuste massiivi).

Funktsiooniprotseduuri abil saate luua funktsiooni, mida saate töölehel kasutada (nagu iga tavaline Exceli funktsioon, näiteks SUM või VLOOKUP).

Kui olete loonud funktsiooniprotseduuri VBA abil, saate seda kasutada kolmel viisil.

1. Töölehe valemina, kus ta saab argumente sisenditeks võtta ja tagastab väärtuse või väärtuste massiivi.
2. Osana oma VBA alamprogrammi koodist või muust funktsioonikoodist.
3. Tingimusvormingus.

Kuigi töölehel on juba saadaval 450+ sisseehitatud Exceli funktsiooni, võib teil olla vaja kohandatud funktsiooni, kui:

• Sisseehitatud funktsioonid ei saa teha seda, mida soovite teha. Sel juhul saate oma vajaduste põhjal luua kohandatud funktsiooni.
• Sisseehitatud funktsioonid saavad tööd teha, kuid valem on pikk ja keeruline. Sel juhul saate luua kohandatud funktsiooni, mida on lihtne lugeda ja kasutada.

Pange tähele, et VBA abil loodud kohandatud funktsioonid võivad olla oluliselt aeglasemad kui sisseehitatud funktsioonid. Seega sobivad need kõige paremini olukordadesse, kus te ei saa tulemust sisseehitatud funktsioonide abil.

Funktsioon vs. Alamprogramm VBA -s

„Alamprogramm” võimaldab teil käivitada koodikomplekti, samas kui „Funktsioon” tagastab väärtuse (või väärtuste massiivi).

Kui tuua näide, kui teil on numbrite loend (nii positiivsed kui ka negatiivsed) ja soovite tuvastada negatiivsed numbrid, saate funktsiooni ja alamprogrammi abil teha järgmist.

Alamprogramm võib loopida kõiki vahemiku lahtreid ja esile tõsta kõik lahtrid, millel on negatiivne väärtus. Sel juhul muudab alamprogramm vahemiku objekti omadusi (muutes lahtrite värvi).

Kohandatud funktsiooni korral saate seda kasutada eraldi veerus ja see võib tagastada tõe, kui lahtri väärtus on negatiivne ja vale, kui see on positiivne. Funktsiooniga ei saa objekti omadusi muuta. See tähendab, et lahtri värvi ei saa funktsiooni funktsiooniga muuta (siiski saate seda teha kohandatud funktsiooniga tingimusvormingu abil).

Kui loote VBA abil kasutaja määratud funktsiooni (UDF), saate seda funktsiooni töölehel kasutada nagu kõiki muid funktsioone. Ma käsitlen seda rohkem jaotises „Kasutaja määratud funktsiooni kasutamine Excelis”.

Lihtsa kasutaja määratud funktsiooni loomine VBA -s

Lubage mul luua VBA-s lihtne kasutaja määratud funktsioon ja näidata, kuidas see toimib.

Allolev kood loob funktsiooni, mis eraldab numbrilised osad tähtnumbrilisest stringist.

Funktsioon GetNumeric (CellRef kui string) kui pikk hämar string Pikkus täisarvuna Järgmine i GetNumeric = Tulemuse lõppfunktsioon

Kui ülaltoodud kood on moodulis, saate seda funktsiooni töövihikus kasutada.

Allpool on kirjeldatud, kuidas see funktsioon - GetNumeric - saab kasutada Excelis.

Enne kui ma teile ütlen, kuidas see funktsioon VBA -s luuakse ja kuidas see töötab, peaksite teadma mõnda asja:

• Kui loote funktsiooni VBA -s, muutub see kogu töövihikus kättesaadavaks nagu iga teine ​​tavaline funktsioon.
• Kui sisestate funktsiooni nime ja sellele järgneva võrdusmärgi, kuvab Excel teile vastavate funktsioonide loendis funktsiooni nime. Ülaltoodud näites, kui sisestasin = Get, näitas Excel mulle loendit, millel oli minu kohandatud funktsioon.

Usun, et see on hea näide, kui saate VBA abil luua Excelis lihtsasti kasutatava funktsiooni. Saate sama teha ka valemiga (nagu on näidatud selles õpetuses), kuid see muutub keeruliseks ja raskesti mõistetavaks. Selle UDF -iga peate läbima ainult ühe argumendi ja saate tulemuse.

Kasutaja määratud funktsiooni anatoomia VBA -s

Ülaltoodud jaotises andsin teile koodi ja näitasin, kuidas UDF -funktsioon töötab töölehel.

Nüüd sukeldume sügavalt ja vaatame, kuidas see funktsioon luuakse. Peate paigutama alloleva koodi VB redaktori moodulisse. Ma käsitlen seda teemat jaotises - "Kuhu kasutaja määratud funktsiooni VBA -kood panna".

Funktsioon GetNumeric (CellRef As String) kui pikk 'See funktsioon ekstraheerib numbrilise osa stringist Dim StringLength As Integer StringLength = Len (CellRef) I = 1 kuni StringLength If IsNumeric (Mid (CellRef, i, 1)) Siis Tulemus = Tulemus ja keskmine (CellRef, i, 1) Järgmine i GetNumeric = Tulemuse lõppfunktsioon

Koodi esimene rida algab sõnaga - Funktsioon.

See sõna ütleb VBA -le, et meie kood on funktsioon (mitte alamprogramm). Sõnale Function järgneb funktsiooni nimi - GetNumeric. Seda nime kasutame töölehel selle funktsiooni kasutamiseks.

• Funktsiooni nimes ei tohi olla tühikuid. Samuti ei saa te funktsiooni nimetada, kui see on lahtri viite nimega vastuolus. Näiteks ei saa te nimetada funktsiooni ABC123, kuna see viitab ka Exceli töölehe lahtrile.
• Te ei tohiks oma funktsioonile anda sama nime kui olemasolevale funktsioonile. Kui te seda teete, eelistaks Excel sisseehitatud funktsiooni.
• Kui soovite sõnu eraldada, võite kasutada alajoont. Näiteks Get_Numeric on vastuvõetav nimi.

Funktsiooni nimele järgnevad sulgudes mõned argumendid. Need on argumendid, mida meie funktsioon kasutajal vajaks. Need on täpselt nagu argumendid, mida peame Exceli sisseehitatud funktsioonidele esitama. Näiteks funktsioonis COUNTIF on kaks argumenti (vahemik ja kriteeriumid)

Sulgudes tuleb argumendid täpsustada.

Meie näites on ainult üks argument - CellRef.

Hea tava on ka täpsustada, millist argumenti funktsioon ootab. Selles näites, kuna me toidame funktsiooni lahtri viitena, saame argumendi määrata vahemiku tüübiks. Kui te andmetüüpi ei määra, peab VBA seda variandiks (mis tähendab, et saate kasutada mis tahes andmetüüpi).

Kui teil on rohkem kui üks argument, saate need määrata samas sulgudes, eraldades need komaga. Sellest õpetusest näeme hiljem, kuidas kasutaja määratud funktsioonis kasutada mitut argumenti.

Pange tähele, et funktsioon on määratud andmetüübina „String”. See ütleks VBA -le, et valemi tulemus oleks andmetüüp String.

Kuigi ma saan siin kasutada numbrilisi andmetüüpe (näiteks pikk või kahekordne), piiraks see toiming tagastatavate numbrite vahemikku. Kui mul on 20 numbripikkune string, mille pean üldisest stringist välja võtma, annaks funktsiooni pikaks või kahekordseks kuulutamine vea (kuna number jääks selle vahemikust välja). Seega olen säilitanud funktsiooni väljundandmete tüübi stringina.

Koodi teine ​​rida - roheline, mis algab apostrofiga - on kommentaar. Koodi lugedes ignoreerib VBA seda rida. Seda saate kasutada koodi kirjelduse või üksikasjade lisamiseks.

Koodi kolmas rida deklareerib muutuja „StringLength” täisarvu andmetüübiks. See on muutuja, kuhu salvestame valemiga analüüsitava stringi pikkuse väärtuse.

Neljas rida deklareerib muutuja tulemuse stringina. See on muutuja, kus me ekstraheerime numbrid tähtnumbrilisest stringist.

Viies rida määrab sisestusargumendis oleva stringi pikkuse muutujale „StringLength”. Pange tähele, et „CellRef” viitab argumendile, mille kasutaja esitab töölehel oleva valemi kasutamisel (või selle kasutamisel VBA -s - mida näeme selles juhendis hiljem).

Kuues, seitsmes ja kaheksas rida on osa järgmiseks silmusest. Tsükkel kestab nii mitu korda, kui sisendargumendis on tähemärke. Selle numbri annab funktsioon LEN ja see määratakse muutujale „StringLength”.

Seega kulgeb tsükkel vahemikus 1 kuni stringi pikkus.

Ahelas analüüsib IF -lause stringi iga märki ja kui see on numbriline, lisab see muutuja Result selle numbrilise märgi. Selleks kasutab ta VBA -s funktsiooni MID.

Koodi teine ​​viimane rida määrab funktsioonile tulemuse väärtuse. See koodirida tagab, et funktsioon tagastab väärtuse „Tulemus” tagasi lahtrisse (sealt, kust seda nimetatakse).

Koodi viimane rida on lõppfunktsioon. See on kohustuslik koodirida, mis ütleb VBA -le, et funktsioonikood lõpeb siin.

Ülaltoodud kood selgitab VBA -s loodud tüüpilise kohandatud funktsiooni erinevaid osi. Järgmistes jaotistes sukeldume nendesse elementidesse ja näeme ka erinevaid VBA funktsiooni täitmise viise Excelis.

Argumendid kasutaja määratud funktsioonis VBA -s

Ülaltoodud näidetes, kus lõime kasutaja määratud funktsiooni, et saada numbriline osa tähtnumbrilisest stringist (GetNumeric), oli funktsioon mõeldud ühe argumendi jaoks.

Selles jaotises käsitlen, kuidas luua funktsioone, mis ei võta argumente vastu neile, mis võtavad vastu mitu argumenti (kohustuslikud ja valikulised argumendid).

Funktsiooni loomine VBA -s ilma argumentideta

Exceli töölehel on meil mitu funktsiooni, millel pole argumente (nt RAND, TODAY, NOW).

Need funktsioonid ei sõltu sisendargumentidest. Näiteks tagastab funktsioon TODAY praeguse kuupäeva ja funktsioon RAND juhusliku arvu vahemikus 0 kuni 1.

Sellise sarnase funktsiooni saate luua ka VBA -s.

Allpool on kood, mis annab teile faili nime. See ei võta mingeid argumente, sest tagastamisele kuuluv tulemus ei sõltu ühestki argumendist.

Funktsioon WorkbookName () Stringina WorkbookName = ThisWorkbook.Name Lõppfunktsioon

Ülaltoodud kood määrab funktsiooni tulemuse stringitüübina (soovitud tulemus on failinimi - see on string).

See funktsioon määrab funktsioonile väärtuse „ThisWorkbook.Name”, mis tagastatakse funktsiooni kasutamisel töölehel.

Kui fail on salvestatud, tagastab see nime koos faililaiendiga, vastasel juhul annab see lihtsalt nime.

Eespool on aga üks probleem.

Kui faili nimi muutub, ei värskendata seda automaatselt. Tavaliselt värskendatakse funktsiooni iga kord, kui sisendargumendid muutuvad. Kuid kuna selles funktsioonis pole argumente, ei arvesta funktsioon uuesti (isegi kui muudate töövihiku nime, sulgege see ja avage uuesti).

Kui soovite, võite sundida ümberarvutamist, kasutades kiirklahvi - Control + Alt + F9.

Kui soovite töölehel muudatusi teha, valemit uuesti arvutada, peate sellele koodirea sisestama.

Allolev kood paneb funktsiooni uuesti arvutama iga kord, kui töölehel tehakse muudatusi (nagu ka muud sarnased töölehe funktsioonid, näiteks TODAY või RAND).

Funktsiooni töövihiku nimi () stringina. Volatile True WorkbookName = ThisWorkbook.Name End Function

Nüüd, kui muudate töövihiku nime, värskendatakse seda funktsiooni iga kord, kui töölehel tehakse muudatusi või kui avate selle töövihiku uuesti.

Funktsiooni loomine VBA -s ühe argumendiga

Ühes ülaltoodud jaotistes oleme juba näinud, kuidas luua funktsiooni, mis võtab ainult ühe argumendi (ülaltoodud funktsioon GetNumeric).

Loome veel ühe lihtsa funktsiooni, mis võtab ainult ühe argumendi.

Allpool toodud koodiga loodud funktsioon teisendab viidatud teksti suurtähtedeks. Nüüd on meil selle jaoks Excelis juba funktsioon ja see funktsioon on lihtsalt selleks, et näidata teile, kuidas see toimib. Kui peate seda tegema, on parem kasutada sisseehitatud UPPER -funktsiooni.

Funktsioon ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) lõppfunktsioon

See funktsioon kasutab VBA UCase funktsiooni CellRef muutuja väärtuse muutmiseks. Seejärel määrab see väärtuse funktsioonile ConvertToUpperCase.

Kuna see funktsioon võtab argumendi, ei pea me rakendust kasutama. Lenduv osa siin. Niipea kui argument muutub, värskendatakse funktsiooni automaatselt.

Funktsiooni loomine VBA -s mitme argumendiga

Nii nagu töölehe funktsioonid, saate ka VBA -s luua funktsioone, mis nõuavad mitut argumenti.

Allpool olev kood looks funktsiooni, mis eraldab teksti enne määratud eraldajat. See võtab kaks argumenti - lahtri viide, millel on tekstistring, ja eraldaja.

Funktsioon GetDataBeforeDelimiter (CellRef As Range, Delim As String) kui String Dim Tulemus String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter =

Kui peate kasutaja määratud funktsioonis kasutama rohkem kui ühte argumenti, saate sulgudes kõik argumendid komaga eraldada.

Pange tähele, et iga argumendi jaoks saate määrata andmetüübi. Ülaltoodud näites on „CellRef” deklareeritud vahemiku andmetüübiks ja „Delim” on deklareeritud andmetüübina String. Kui te ei määra ühtegi andmetüüpi, leiab VBA, et need on andmetüübi variandid.

Kui kasutate töölehel ülaltoodud funktsiooni, peate andma lahtri viite, mille esimeseks argumendiks on tekst ja teise argumendina kahekordse jutumärgi (de) eraldaja.

Seejärel kontrollib see eraldaja positsiooni, kasutades VBA funktsiooni INSTR. Seda positsiooni kasutatakse seejärel kõigi märkide eraldamiseks enne eraldajat (kasutades funktsiooni LEFT).

Lõpuks määrab see tulemuse funktsioonile.

See valem pole kaugeltki täiuslik. Näiteks kui sisestate eraldaja, mida tekstist ei leia, annaks see vea. Nüüd saate vigadest vabanemiseks kasutada töölehel funktsiooni IFERROR või kasutada allolevat koodi, mis tagastab kogu teksti, kui see ei leia eraldajat.

Funktsioon GetDataBeforeDelimiter (CellRef As Range, Delim As String) kui string Dim Tulemus String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Kui DelimPosition <0 Siis DelimPosition = Len (CellRef) Tulemus = Left (Left CellRef, DelimPosition) GetDataBeforeDelimiter = Tulemuse lõppfunktsioon

Saame seda funktsiooni veelgi optimeerida.

Kui sisestate teksti (millest soovite eraldusosa ees oleva osa välja võtta) otse funktsiooni, annaks see teile vea. Lase käia… proovi!

See juhtub siis, kui oleme määranud vahemiku andmetüübiks „CellRef”.

Või kui soovite, et eraldaja oleks lahtris ja kasutaksite lahtris viidet selle asemel, et seda valemis kõvasti kodeerida, ei saa te seda ülaltoodud koodiga teha. Selle põhjuseks on asjaolu, et Delim on kuulutatud stringi andmetüübiks.

Kui soovite, et funktsioonil oleks paindlikkus otsese tekstisisestuse või lahtriviidete vastuvõtmiseks kasutajalt, peate andmetüübi deklaratsiooni eemaldama. See muudaks argumendi andmetüübiks, mis võib võtta mis tahes tüüpi argumente ja neid töödelda.

Allolev kood teeks seda:

Funktsioon GetDataBeforeDelimiter (CellRef, Delim) String Dim Tulemus String Dim DelimPosition kui täisarv DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Kui DelimPosition <0 Siis DelimPosition = Len (CellRef) Tulemus = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Tulemuse lõppfunktsioon

Funktsiooni loomine VBA -s valikuliste argumentidega

Excelis on palju funktsioone, kus mõned argumendid on valikulised.

Näiteks legendaarsel funktsioonil VLOOKUP on 3 kohustuslikku argumenti ja üks valikuline argument.

Valikuline argument, nagu nimigi ütleb, on valikuline. Kui te ei määra ühte kohustuslikku argumenti, annab teie funktsioon teile vea, kuid kui te ei määra valikulist argumenti, töötab teie funktsioon.

Kuid valikulised argumendid pole kasutud. Need võimaldavad teil valida paljude võimaluste hulgast.

Näiteks kui te funktsiooni VLOOKUP neljandat argumenti ei määra, teeb VLOOKUP ligikaudse otsingu ja kui määrate viimase argumendi väärtuseks VÄÄR (või 0), teeb see täpse vaste.

Pidage meeles, et valikulised argumendid peavad alati tulema pärast kõiki nõutavaid argumente. Teil ei saa alguses olla valikulisi argumente.

Nüüd vaatame, kuidas valikuliste argumentidega VBA -s funktsiooni luua.

Funktsioon ainult valikuliste argumentidega

Minu teada pole ühtegi sisseehitatud funktsiooni, mis võtaks ainult valikulisi argumente (ma võin siin eksida, kuid ma ei suuda sellist funktsiooni välja mõelda).

Kuid me saame selle luua VBA abil.

Allpool on selle funktsiooni kood, mis annab teile praeguse kuupäeva vormingus pp-kk-aaaa, kui te ei sisesta ühtegi argumenti (st jätke see tühjaks), ja vormingus „pp mmmm, aaaa”, kui sisestate midagi argumendina (st mida iganes, et argument poleks tühi).

Funktsioon CurrDate (valikuline fmt variandina) Dim Result If IsMissing (fmt) then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Pange tähele, et ülaltoodud funktsioon kasutab „IsMissing”, et kontrollida, kas argument puudub või mitte. Funktsiooni IsMissing kasutamiseks peab teie valikuline argument olema varianti andmetüüp.

Ülaltoodud funktsioon töötab olenemata sellest, mida argumendina sisestate. Koodis kontrollime ainult seda, kas valikuline argument on esitatud või mitte.

Saate seda tugevamaks muuta, kui võtate argumentidena ainult konkreetsed väärtused ja kuvate ülejäänud juhtudel vea (nagu on näidatud allolevas koodis).

Funktsioon CurrDate (valikuline fmt variandina) Dim Result If IsMissing (fmt) Siis CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 Siis CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) Funktsioon End If End

Ülaltoodud kood loob funktsiooni, mis näitab kuupäeva vormingus „pp-kk-aaaa”, kui ühtegi argumenti ei esitata, ja „pp mmmm, aaaa” vormingus, kui argument on 1. See annab vea kõigil muudel juhtudel.

Funktsioon koos nõutavate ja valikuliste argumentidega

Oleme juba näinud koodi, mis eraldab numbrilise osa stringist.

Vaatame nüüd sarnast näidet, mis sisaldab nii nõutavaid kui ka valikulisi argumente.

Allolev kood loob funktsiooni, mis ekstraheerib tekstiosa stringist. Kui valikuline argument on TÕENE, annab see tulemuse suurtähtedega ja kui valikuline argument on VÄÄR või see jäetakse välja, siis annab see tulemuse sellisena, nagu see on.

Funktsioon GetText (CellRef kui vahemik, valikuline TextCase = vale) Stringina Dim StringPikkus täisarvuna Dim Tulemusena stringina StringLength = Len (CellRef) i = 1 kuni StringLength Kui ei (IsNumeric (Mid (CellRef, i, 1)))) Siis Tulemus = Tulemus ja keskmine (CellRef, i, 1) Järgmine i Kui TextCase = Tõene, siis Tulemus = UCase (Tulemus) GetText = Tulemuse lõppfunktsioon

Pange tähele, et ülaltoodud koodis oleme lähtestanud väärtuse „TextCase” väärtuseks Väär (vaadake esimese rea sulgusid).

Seda tehes oleme taganud, et valikuline argument algab vaikeväärtusega, mis on VÄÄR. Kui kasutaja määrab väärtuseks TRUE, tagastab funktsioon teksti suurtähtedega ja kui kasutaja määrab valikulise argumendi väärtuseks FALSE või jätab selle välja, siis tagastatud tekst on selline, nagu see on.

Funktsiooni loomine VBA -s, mille argument on massiiv

Siiani oleme näinud näiteid funktsiooni loomisest valikuliste/nõutavate argumentidega - kus need argumendid olid üks väärtus.

Samuti saate luua funktsiooni, mis võib argumendina võtta massiivi. Exceli töölehe funktsioonides on palju funktsioone, mis võtavad massiivi argumente, näiteks SUM, VLOOKUP, SUMIF, COUNTIF jne.

Allpool on kood, mis loob funktsiooni, mis annab lahtrite määratud vahemikus kõigi paarisarvude summa.

Funktsioon AddEven (CellRef kui vahemik) Dim Dim Cell kui vahemik iga CellRef lahtri jaoks, kui see on numbriline (Cell.Value) Siis If Cell.Value Mod 2 = 0 Siis Result = Result + Cell.Value End If End Kui järgmine lahter AddEven = Result End Funktsioon

Seda funktsiooni saate kasutada töölehel ja esitada lahtrivahemiku, mille argumendiks on numbrid. Funktsioon tagastab ühe väärtuse - kõigi paarisarvude summa (nagu allpool näidatud).

Ülaltoodud funktsioonis oleme ühe väärtuse asemel esitanud massiivi (A1: A10). Selle toimimiseks peate veenduma, et teie argumendi andmetüüp võtab massiivi vastu.

Ülaltoodud koodis määrasin argumendi CellRef vahemikuks (mis võib sisendiks võtta massiivi). Siin saate kasutada ka andmetüübi varianti.

Koodis on iga silmuse jaoks, mis läbib iga lahtri ja kontrollib, kas seda on mitu. Kui ei, siis ei juhtu midagi ja see liigub järgmisesse lahtrisse. Kui see on number, kontrollib see, kas see on paaris või mitte (kasutades funktsiooni MOD).

Lõpuks lisatakse kõik paarisarvud ja summa määratakse funktsioonile tagasi.

Määramatu arvu argumentidega funktsiooni loomine

Mõne funktsiooni loomisel VBA -s ei pruugi te teada, kui palju argumente kasutaja soovib esitada. Seega on vaja luua funktsioon, mis võtab vastu nii palju argumente, kui kasutada neid tulemuse tagastamiseks.

Sellise töölehe funktsiooni näide on funktsioon SUM. Saate sellele esitada mitu argumenti (näiteks see):

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

Ülaltoodud funktsioon lisab kõigi nende argumentide väärtused. Samuti pange tähele, et need võivad olla üksikud lahtrid või lahtrite massiiv.

Sellise funktsiooni saate luua VBA -s, kui viimane argument (või see võib olla ainus argument) valikuline. Samuti peaks sellele valikulisele argumendile eelnema märksõna „ParamArray”.

„ParamArray” on modifikaator, mis võimaldab teil aktsepteerida nii palju argumente kui soovite. Pange tähele, et sõna ParamArray kasutamine enne argumenti muudab argumendi valikuliseks. Siiski ei pea te siin kasutama sõna Valikuline.

Loome nüüd funktsiooni, mis võtab vastu suvalise arvu argumente ja lisab kõik numbrid määratud argumentidesse:

Funktsioon AddArguments (ParamArray arglist () Variandina) Iga arg Arglist arg Argist AddArguments = AddArguments + arg Järgmine arg Lõppfunktsioon

Ülaltoodud funktsioon võib võtta suvalise arvu argumente ja lisada need argumendid tulemuse saamiseks.

Pange tähele, et saate argumendina kasutada ainult ühte väärtust, lahtriviidet, tõeväärtust või avaldist. Massiivi ei saa argumendina esitada. Näiteks kui üks teie argumentidest on D8: D10, annab see valem teile vea.

Kui soovite kasutada mõlemat mitme lahtri argumenti, peate kasutama järgmist koodi:

Funktsioon AddArguments (ParamArray arglist () Variandina) Iga arg Arglist Argisti iga lahtri arg Arg AddArguments = AddArguments + Lahter Järgmine lahter Järgmine arg Lõppfunktsioon

Pange tähele, et see valem töötab mitme lahtri ja massiivi viitega, kuid see ei saa töödelda kõvakodeeritud väärtusi ega avaldisi. Neid tingimusi kontrollides ja ravides saate luua tugevama funktsiooni, kuid see pole siin eesmärk.

Eesmärk on näidata teile, kuidas ParamArray töötab, nii et saate lubada funktsioonis määramatu arvu argumente. Kui soovite paremat funktsiooni kui ülaltoodud koodiga loodud funktsioon, kasutage töölehel funktsiooni SUM.

Massiivi tagastava funktsiooni loomine

Siiani oleme näinud funktsioone, mis tagastavad ühe väärtuse.

VBA abil saate luua funktsiooni, mis tagastab variandi, mis võib sisaldada tervet väärtuste massiivi.

Massiivivalemid on saadaval ka Exceli töölehtede sisseehitatud funktsioonidena. Kui olete Excelis massiivivalemitega tuttav, siis teate, et need sisestatakse klahvikombinatsiooni Control + Tõstuklahv + Enter (mitte ainult sisestusklahvi). Massiivivalemite kohta saate lugeda siit. Kui te ei tea massiivivalemitest, ärge muretsege, jätkake lugemist.

Loome valemi, mis tagastab kolmest numbrist koosneva massiivi (1,2,3).

Seda teeks allolev kood.

Funktsioon ThreeNumbers () variandina Dim NumberValue (1 kuni 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Funktsioon

Ülaltoodud koodis oleme variandina määranud funktsiooni „ThreeNumbers”. See võimaldab tal hoida väärtuste massiivi.

Muutuja „NumberValue” deklareeritakse kolme elemendiga massiivina. See hoiab kolme väärtust ja määrab selle funktsioonile „ThreeNumbers”.

Seda funktsiooni saate töölehel kasutada, kui sisestate funktsiooni ja vajutate klahve Control + Shift + Enter (hoidke all klahve Control ja Shift ja seejärel vajutage sisestusklahvi).

Kui teete seda, tagastab see lahtrisse 1, kuid tegelikult sisaldab see kõiki kolme väärtust. Selle kontrollimiseks kasutage järgmist valemit:

= MAX (kolm numbrit ())

Kasutage ülaltoodud funktsiooni klahvikombinatsiooni Control + tõstuklahv + sisestusklahv. Märkate, et tulemus on nüüd 3, kuna see on massiivi suurimad väärtused, mille tagastab funktsioon Max, mis saab kolm numbrit meie kasutaja määratud funktsiooni - ThreeNumbers - tulemusena.

Sama tehnikat saate kasutada funktsiooni loomiseks, mis tagastab kuu nimede massiivi, nagu on näidatud alloleval koodil:

Funktsioonikuud () Variandina Dim MonthName (1 kuni 12) MonthName (1) = "Jaanuar" MonthName (2) = "Veebruar" MonthName (3) = "Märts" MonthName (4) = "Aprill" MonthName (5) = "Mai" MonthName (6) = "Juuni" MonthName (7) = "Juuli" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "Oktoober" MonthName (11) = "November" "MonthName (12) =" December "Months = MonthName End Function

Nüüd, kui sisestate Exceli töölehel funktsiooni = Kuud () ja kasutate klahvikombinatsiooni Control + Tõstuklahv + Enter, tagastab see kogu kuunimede massiivi. Pange tähele, et näete lahtris ainult jaanuari, kuna see on massiivi esimene väärtus. See ei tähenda, et massiiv tagastab ainult ühe väärtuse.

Et näidata teile fakti, et see tagastab kõik väärtused, tehke seda - valige valemiga lahter, minge valemiribale, valige kogu valem ja vajutage klahvi F9. See näitab teile kõiki väärtusi, mille funktsioon tagastab.

Saate seda kasutada, kasutades allpool olevat INDEX -valemit, et saada kõigi kuude nimede loend korraga.

= INDEX (kuud (), ROW ())

Kui teil on palju väärtusi, ei ole hea tava neid väärtusi ükshaaval määrata (nagu oleme teinud eespool). Selle asemel saate VBA -s kasutada massiivi funktsiooni.

Nii et sama kood, kus loome funktsiooni „Kuud”, lüheneb, nagu allpool näidatud:

Funktsioonikuud () Variandikuudena = massiiv ("jaanuar", "veebruar", "märts", "aprill", "mai", "juuni", _ "juuli", "august", "september", "oktoober" , "November", "detsember") Lõppfunktsioon

Ülaltoodud funktsioon kasutab funktsiooni Array, et määrata väärtused otse funktsioonile.

Pange tähele, et kõik ülaltoodud funktsioonid tagastavad horisontaalse väärtuste massiivi. See tähendab, et kui valite 12 horisontaalset lahtrit (oletame, et A1: L1) ja sisestate lahtrisse A1 valemi = Months (), annab see teile kõik kuude nimed.

Aga mis siis, kui soovite neid väärtusi lahtrite vertikaalses vahemikus.

Seda saate teha, kasutades töölehel valemit TRANSPOSE.

Valige lihtsalt 12 vertikaalset lahtrit (külgnevad) ja sisestage järgmine valem.

Kasutaja määratud funktsiooni ulatuse mõistmine Excelis

Funktsioonil võib olla kaks ulatust - Avalik või Privaatne.

• A Avalik ulatus tähendab, et funktsioon on saadaval kõikidele töövihiku lehtedele, samuti kõik protseduurid (alam- ja funktsioon) kõikides töövihiku moodulites. See on kasulik, kui soovite funktsiooni kutsuda alamprogrammist (kuidas seda teha, näeme järgmises osas).
• A Privaatne ulatus tähendab, et funktsioon on saadaval ainult selles moodulis, kus see on olemas. Te ei saa seda teistes moodulites kasutada. Te ei näe seda ka töölehe funktsioonide loendis. Näiteks kui teie funktsiooni nimi on „Kuud ()” ja sisestate funktsiooni Excelis (pärast = -märki), ei näita see teile funktsiooni nime. Saate seda siiski kasutada, kui sisestate valemi nime.

Kui te midagi ei määra, on funktsioon vaikimisi avalik funktsioon.

Allpool on funktsioon, mis on privaatne:

Privaatfunktsiooni töövihikuNimi () Stringina TöövihikuNimi = ThisWorkbook.Name Lõppfunktsioon

Seda funktsiooni saate kasutada samade moodulite alamprogrammides ja protseduurides, kuid teistes moodulites ei saa seda kasutada. Seda funktsiooni ei kuvata ka töölehel.

Allolev kood muudaks selle funktsiooni avalikuks. See kuvatakse ka töölehel.

Funktsioon WorkbookName () Stringina WorkbookName = ThisWorkbook.Name Lõppfunktsioon

Kasutaja määratud funktsiooni kasutamise erinevad viisid Excelis

Kui olete VBA-s kasutaja määratud funktsiooni loonud, saate seda kasutada mitmel erineval viisil.

Vaatame kõigepealt, kuidas töölehel funktsioone kasutada.

UDF -ide kasutamine töölehtedel

Oleme töölehel juba näinud näiteid VBA -s loodud funktsiooni kasutamisest.

Peate lihtsalt sisestama funktsioonide nime ja see kuvatakse intellisense'is.

Pange tähele, et funktsiooni töölehel kuvamiseks peab see olema avalik funktsioon (nagu ülalpool kirjeldatud).

Dialoogiboksi Funktsiooni sisestamine saate kasutada ka kasutaja määratud funktsiooni lisamiseks (kasutades alltoodud samme). See toimiks ainult funktsioonide puhul, mis on avalikud.

• Minge vahekaardile Andmed.
• Klõpsake suvandit „Lisa funktsioon”.
• Valige dialoogiboksis Funktsiooni lisamine kategooriaks kasutaja määratud. See suvand kuvatakse ainult siis, kui teil on funktsioon VB -redaktoris (ja funktsioon on avalik).
• Valige funktsioon kõigi avalikult määratletud funktsioonide loendist.
• Klõpsake nuppu OK.

Ülaltoodud sammud sisestaksid funktsiooni töölehele. Samuti kuvatakse dialoogiboks Funktsioonide argumendid, mis annab teile üksikasju argumentide ja tulemuse kohta.

Saate kasutada kasutaja määratud funktsiooni nagu mis tahes muud funktsiooni Excelis. See tähendab ka seda, et saate seda kasutada koos teiste sisseehitatud Exceli funktsioonidega. Näiteks. järgmine valem annaks töövihiku nime suurtähtedega:

= ÜLEMINE (töövihiku nimi ())

Kasutaja määratud funktsioonide kasutamine VBA protseduurides ja funktsioonides

Kui olete funktsiooni loonud, saate seda kasutada ka teistes alamprotseduurides.

Kui funktsioon on avalik, saab seda kasutada mis tahes protseduuris samas või erinevas moodulis. Kui see on privaatne, saab seda kasutada ainult samas moodulis.

Allpool on funktsioon, mis tagastab töövihiku nime.

Funktsioon WorkbookName () Stringina WorkbookName = ThisWorkbook.Name Lõppfunktsioon

Allpool kirjeldatud protseduur kutsub funktsiooni ja kuvab seejärel nime sõnumikastis.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Funktsioonile saab helistada ka teisest funktsioonist.

Allpool toodud koodides tagastab esimene kood töövihiku nime ja teine ​​tagastab nime suurtähtedega, kutsudes esimest funktsiooni.

Funktsioon WorkbookName () Stringina WorkbookName = ThisWorkbook.Name Lõppfunktsioon

Funktsioon TöövihikNameinUpper () TöövihikNameinUpper = UCase (TöövihikuNimi) Lõppfunktsioon

Kasutaja määratud funktsiooni kutsumine teistest töövihikutest

Kui teil on töövihikus funktsioon, saate seda funktsiooni nimetada ka teistes töövihikutes.

Selleks on mitu võimalust:

1. Lisandmooduli loomine
2. Salvestusfunktsioon isikliku makro töövihikus
3. Funktsiooni viitamine teisest töövihikust.

Lisandmooduli loomine

Lisandmooduli loomisel ja installimisel on kohandatud funktsioon saadaval kõikides töövihikutes.

Oletame, et olete loonud kohandatud funktsiooni - GetNumeric ja soovite seda kõikides töövihikutes. Selleks looge uus töövihik ja lisage funktsioonikood selle uue töövihiku moodulisse.

Nüüd järgige allolevaid samme, et see lisandmoodulina salvestada ja seejärel Excelisse installida.

• Minge vahekaardile Fail ja klõpsake nuppu Salvesta nimega.
• Muutke dialoogiboksis Salvesta nimega „Salvesta nimega” tüüp .xlam. Failile määratud nimi oleks teie lisandmooduli nimi. Selles näites salvestatakse fail nimega GetNumeric.
  • Märkate, et faili tee, kuhu see salvestatakse, muutub automaatselt. Soovi korral saate vaikeseadet kasutada või seda muuta.
• Avage uus Exceli töövihik ja minge vahekaardile Arendaja.
• Klõpsake Exceli lisandmoodulite valikut.
• Sirvige ja leidke dialoogiboksis Lisandmoodulid salvestatud fail ja klõpsake nuppu OK.

Nüüd on lisandmoodul aktiveeritud.

Nüüd saate kohandatud funktsiooni kasutada kõikides töövihikutes.

Funktsiooni salvestamine isikliku makro töövihikusse

Isiklik makro töövihik on teie süsteemis peidetud töövihik, mis avaneb iga kord, kui avate Exceli rakenduse.

See on koht, kus saate salvestada makrokoode ja pääseda neile makrodele juurde igast töövihikust. See on suurepärane koht nende makrode salvestamiseks, mida soovite sageli kasutada.

Vaikimisi pole teie Excelis isiklikku makro töövihikut. Peate selle looma, salvestades makro ja salvestades selle isikliku makro töövihikusse.

Makro loomise ja salvestamise üksikasjalikud juhised leiate isiklikust makro töövihikust siit.

Funktsiooni viitamine teisest töövihikust

Kuigi kaks esimest meetodit (lisandmooduli loomine ja isikliku makro töövihiku kasutamine) toimiksid kõikides olukordades, kuid kui soovite funktsioonile viidata teisest töövihikust, peab see töövihik olema avatud.

Oletame, et teil on töövihik nimega "Töövihik valemiga ”ja sellel on funktsioon nimega "GetNumeric ”.

Selle funktsiooni kasutamiseks teises töövihikus (samas Töövihik valemiga on avatud), saate kasutada järgmist valemit:

= 'Valemiga töövihik'! GetNumeric (A1)

Ülaltoodud valem kasutab kasutaja määratud funktsiooni Töövihik valemiga faili ja anna teile tulemus.

Pange tähele, et kuna töövihiku nimes on tühikuid, peate selle lisama jutumärkidesse.

Väljumisfunktsiooni avalduse VBA kasutamine

Kui soovite koodi töötamise ajal funktsioonist väljuda, saate seda teha avaldise „Välju funktsioonist” abil.

Allolev kood eraldab kolm esimest numbrimärki tähtnumbrilisest tekstistringist. Niipea kui see saab kolm märki, funktsioon lõpeb ja tagastab tulemuse.

Funktsioon GetNumericFirstThree (CellRef As Range) Pikk Dim Dimring Tulemus = Tulemus ja keskmine (CellRef, i, 1) GetNumericFirstThree = Tulemuse lõpp, kui järgmine i Lõppfunktsioon

Ülaltoodud funktsioon kontrollib numbriliste tähemärkide arvu ja kui see saab 3 numbrimärki, väljub see funktsioon järgmises silmus.

Kasutaja määratud funktsiooni silumine

Kasutaja määratud funktsiooni silumisel VBA-s saate kasutada mõnda tehnikat:

Kohandatud funktsiooni silumine sõnumikasti abil

Kasutage funktsiooni MsgBox, et kuvada konkreetse väärtusega sõnumikast.

Kuvatav väärtus võib põhineda testitaval. Näiteks kui soovite kontrollida, kas kood käivitatakse või mitte, toimiks mis tahes teade ja kui soovite kontrollida, kas ahelad töötavad või mitte, saate kuvada konkreetse väärtuse või silmuse loenduri.

Kohandatud funktsiooni silumine katkestuspunkti määramisega

Määrake katkestuspunkt, et saaksite iga rida ükshaaval läbi astuda. Katkestuspunkti määramiseks valige soovitud rida ja vajutage klahvi F9 või klõpsake hallil vertikaalsel alal, mis jääb koodiridadele. Ükskõik milline neist meetoditest sisestaks katkestuspunkti (hallil alal näete punast punkti).

Kui olete katkestuspunkti määranud ja funktsiooni täitnud, läheb see kuni katkestusjooneni ja seejärel peatub. Nüüd saate koodist läbi astuda, kasutades klahvi F8. F8 üks kord vajutamine liigub koodi järgmisele reale.

Kohandatud funktsiooni silumine silumise abil. Printige kood

Saate kasutada koodis käsku Debug.Print, et saada vahetuaknas määratud muutujate/argumentide väärtused.

Näiteks olen allolevas koodis kasutanud Debug.Print, et saada kahe muutuja - 'j' ja 'Result' - väärtus

Funktsioon GetNumericFirstThree (CellRef As Range) Pika hämara stringina Pikkus täisarvuna StringLength = Len (CellRef) I = 1 kuni StringLength Kui J = 3, siis väljuge funktsioonist Kui IsNumeric (Mid (CellRef, i, 1)) Siis J = J + 1 Tulemus = Tulemus ja keskmine (CellRef, i, 1) Silumine. Prindi J, Tulemus GetNumericFirstThree = Tulemus lõpp, kui järgmine i Lõppfunktsioon

Selle koodi käivitamisel kuvatakse vahetu aknas järgmist.

Exceli sisseehitatud funktsioonid vs. VBA kasutaja määratud funktsioon

Exceli sisseehitatud funktsioonide kasutamisel VBA-s loodud kohandatud funktsioonide ees on vähe eeliseid.

• Sisseehitatud funktsioonid on palju kiiremad kui VBA funktsioonid.
• Kui loote aruande/armatuurlaua VBA funktsioonide abil ja saadate selle kliendile/kolleegile, ei pea nad muretsema, kas makrod on lubatud või mitte. Mõnel juhul hirmutavad kliendid/kliendid kollast riba hoiatust nähes (mis palub neil lihtsalt makrosid lubada).
• Sisseehitatud Exceli funktsioonidega ei pea te muretsema faililaiendite pärast. Kui teil on töövihikus makrosid või kasutaja määratud funktsioone, peate selle salvestama .xlsm-faili.

Kuigi Exceli sisseehitatud funktsioonide kasutamiseks on palju kaalukaid põhjuseid, on mõnel juhul parem kasutada kasutaja määratud funktsiooni.

• Kui teie sisseehitatud valem on tohutu ja keeruline, on parem kasutada kasutaja määratud funktsiooni. See muutub veelgi asjakohasemaks, kui vajate valemite värskendamiseks kedagi teist. Näiteks kui teil on tohutu valem, mis koosneb paljudest erinevatest funktsioonidest, võib isegi viite muutmine lahtrile olla tüütu ja veaohtlik. Selle asemel saate luua kohandatud funktsiooni, mis võtab ainult ühe või kaks argumenti ja tõstab kogu taustaprogrammi.
• Kui peate tegema midagi, mida ei saa teha Exceli sisseehitatud funktsioonidega. Selle näiteks võib olla see, kui soovite stringist välja võtta kõik numbrimärgid. Sellistel juhtudel kaalub kasutaja määratud funktsiooni gar kasutamise kasu üles selle negatiivsed küljed.

Kuhu panna kasutaja määratletud funktsiooni VBA kood

Kohandatud funktsiooni loomisel peate koodi sisestama selle töövihiku koodiaknasse, kuhu funktsiooni soovite.

Allpool on toodud sammud funktsiooni „GetNumeric” koodi sisestamiseks töövihikusse.

1. Minge vahekaardile Arendaja.
2. Klõpsake suvandil Visual Basic. See avab taustaprogrammis VB redaktori.
3. Paremklõpsake VB redaktori Project Exploreri paanil töövihiku mis tahes objekti, kuhu soovite koodi sisestada. Kui te ei näe Project Explorerit, minge vahekaardile Vaade ja klõpsake nuppu Project Explorer.
4. Minge sisesta ja klõpsake moodulit. See lisab teie töövihikusse mooduliobjekti.
5. Kopeerige ja kleepige kood mooduli aknasse.

Samuti võivad teile meeldida järgmised Exceli VBA õpetused:

• Lahtrite ja vahemikega töötamine Excel VBA -s.
• Töölehtedega töötamine Excel VBA -s.
• Töövihikutega töötamine VBA abil.
• Kuidas kasutada silmuseid Excel VBA -s.
• Exceli VBA sündmused - lihtne (ja täielik) juhend
• IF ja muude avalduste kasutamine VBA -s.
• Kuidas Excelis makro salvestada.
• Kuidas Excelis makro käivitada.
• Kuidas sortida andmeid Excelis VBA abil (samm-sammuline juhend).
• Funktsioon Excel VBA InStr - selgitatud näidetega.