Felhasználó által definiált függvény (UDF) létrehozása az Excel VBA -ban (Végső útmutató)

A VBA segítségével létrehozhat egy egyéni függvényt (más néven felhasználó által definiált funkciót), amelyet ugyanúgy használhat a munkalapokon, mint a szokásos funkciókat.

Ezek akkor hasznosak, ha a meglévő Excel funkciók nem elegendők. Ilyen esetekben saját egyedi felhasználói definiált funkciót (UDF) hozhat létre, hogy megfeleljen az Ön egyedi igényeinek.

Ebben az oktatóanyagban mindent leírok az egyéni funkciók létrehozásáról és használatáról a VBA -ban.

Ha érdekli a VBA egyszerű tanulása, nézze meg az enyémet Online Excel VBA képzés.

Mi a funkcióeljárás a VBA -ban?

A függvény eljárás egy VBA kód, amely számításokat végez és értéket (vagy értéktömböt) ad vissza.

A Function (Funkció) eljárással létrehozhat egy funkciót, amelyet a munkalapon használhat (csakúgy, mint bármely hagyományos Excel függvényt, például SUM vagy VLOOKUP).

Ha létrehozott egy Function eljárást a VBA használatával, akkor háromféleképpen használhatja:

1. Képletként a munkalapon, ahol az argumentumokat bemenetként veheti fel, és értéket vagy értéktömböt ad vissza.
2. A VBA alprogram kódjának vagy egy másik funkciókódnak a részeként.
3. Feltételes formázásban.

Bár a munkalapon már 450+ beépített Excel -funkció érhető el, szükség lehet egy egyéni funkcióra, ha:

• A beépített funkciók nem tudják megtenni azt, amit szeretnének. Ebben az esetben egyedi funkciót hozhat létre az Ön igényei alapján.
• A beépített funkciók elvégzik a munkát, de a képlet hosszú és bonyolult. Ebben az esetben létrehozhat egy egyéni funkciót, amely könnyen olvasható és használható.

Vegye figyelembe, hogy a VBA használatával létrehozott egyéni függvények lényegesen lassabbak lehetnek, mint a beépített funkciók. Ezért ezek a legalkalmasabbak olyan helyzetekben, amikor a beépített funkciókkal nem lehet elérni az eredményt.

Funkció vs. Szubrutin a VBA -ban

Az „alprogram” lehetővé teszi kódkészlet végrehajtását, míg a „függvény” értéket (vagy értéktömböt) ad vissza.

Például, ha van egy listája a számokról (mind pozitív, mind negatív), és azonosítani szeretné a negatív számokat, akkor itt van egy funkció és egy alprogram.

Egy szubrutin végigmehet a tartomány minden celláján, és kiemelheti az összes cellát, amelyek negatív értékkel rendelkeznek. Ebben az esetben az alprogram végül megváltoztatja a tartományobjektum tulajdonságait (a cellák színének megváltoztatásával).

Egyéni függvény esetén külön oszlopban használhatja, és IGAZ értéket adhat vissza, ha a cella értéke negatív, és hamis, ha pozitív. Funkcióval nem módosíthatja az objektum tulajdonságait. Ez azt jelenti, hogy nem változtathatja meg a cella színét egy funkcióval (ezt azonban feltételes formázással is elvégezheti az egyéni funkcióval).

Amikor felhasználó által definiált függvényt (UDF) hoz létre a VBA használatával, ezt a funkciót ugyanúgy használhatja a munkalapon, mint bármely más funkciót. Erről bővebben a „Felhasználó által definiált függvény Excelben történő használatának különböző módjai” című részben fogok kitérni.

Egyszerű felhasználó által definiált függvény létrehozása a VBA -ban

Hadd hozzon létre egy egyszerű felhasználó által definiált függvényt a VBA-ban, és mutassam meg, hogyan működik.

Az alábbi kód létrehoz egy függvényt, amely kibontja a numerikus részeket egy alfanumerikus karakterláncból.

Függvény GetNumeric (CellRef as String) Long Dim StringLength as Integer StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) then Result = Result & Mid (CellRef, i, 1) Következő i GetNumeric = Eredményvégfüggvény

Ha a fenti kód a modulban van, akkor ezt a funkciót használhatja a munkafüzetben.

Az alábbiakban bemutatjuk, hogyan működik ez a funkció - GetNumeric - Excelben használható.

Mielőtt elmondanám, hogyan jön létre ez a funkció a VBA -ban és hogyan működik, néhány dolgot tudnia kell:

• Amikor létrehoz egy függvényt a VBA -ban, az elérhető lesz a teljes munkafüzetben, mint bármely más normál függvény.
• Amikor beírja a függvény nevét, majd az egyenlőségjelet, az Excel megjeleníti a függvény nevét a megfelelő függvények listájában. A fenti példában, amikor a = Get parancsot írtam be, az Excel megmutatott egy listát, amely rendelkezik az egyéni funkciómmal.

Úgy gondolom, hogy ez jó példa arra, hogy a VBA segítségével egyszerűen használható funkciót hozhat létre az Excelben. Ugyanezt megteheti egy képlettel is (ahogy ez az oktatóanyagban látható), de ez bonyolult és nehezen érthető lesz. Ezzel az UDF -el csak egy érvet kell átadnia, és megkapja az eredményt.

A felhasználó által definiált funkció anatómiája a VBA -ban

A fenti részben megadtam a kódot, és megmutattam, hogyan működik az UDF funkció egy munkalapon.

Most merüljünk mélyen és nézzük meg, hogyan jön létre ez a funkció. Az alábbi kódot be kell helyeznie egy modulba a VB szerkesztőben. Ezt a témát a „Hova tegyem a VBA kódot egy felhasználó által definiált funkcióhoz” részben tárgyalom.

Funkció GetNumeric (CellRef mint karakterlánc), mint hosszú 'Ez a funkció kivonja a numerikus részt a karakterláncból Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) then Result = Eredmény és közepes (CellRef, i, 1) Következő i GetNumeric = Eredményvégfüggvény

A kód első sora a Funkció szóval kezdődik.

Ez a szó azt mondja a VBA -nak, hogy a kódunk függvény (és nem alprogram). A Function szót a függvény neve követi - GetNumeric. Ezt a nevet fogjuk használni a munkalapon ennek a funkciónak a használatához.

• A függvény nevében nem lehet szóköz. Ezenkívül nem nevezhet függvényt, ha ütközik a cellahivatkozás nevével. Például nem nevezheti el az ABC123 függvényt, mivel az Excel munkalap cellájára is utal.
• A funkciónak nem szabad ugyanazt a nevet adni, mint egy meglévő funkciónak. Ha ezt teszi, az Excel előnyben részesíti a beépített funkciót.
• Aláhúzást használhat, ha szavakat szeretne elválasztani. Például a Get_Numeric név elfogadható.

A függvény nevét néhány argumentum követi zárójelben. Ezek azok az érvek, amelyekre a funkciónknak szüksége lenne a felhasználó részéről. Ezek éppen olyan érvek, mint amelyeket az Excel beépített funkcióihoz kell szolgáltatnunk. Például a COUNTIF függvényben két érv létezik (tartomány és kritérium)

A zárójelben meg kell adnia az argumentumokat.

Példánkban egyetlen érv létezik - CellRef.

Szintén jó gyakorlat annak meghatározása, hogy a funkció milyen érvet vár el. Ebben a példában, mivel cellahivatkozást adunk a függvénynek, megadhatjuk az argumentumot „Tartomány” típusként. Ha nem ad meg adattípust, a VBA egy változatnak tekinti (ami azt jelenti, hogy bármilyen adattípust használhat).

Ha több argumentuma is van, akkor azokat ugyanazon zárójelben adhatja meg - vesszővel elválasztva. Ebben az oktatóanyagban később látni fogjuk, hogyan lehet több argumentumot használni egy felhasználó által definiált függvényben.

Ne feledje, hogy a függvény „String” adattípusként van megadva. Ez azt mondaná a VBA -nak, hogy a képlet eredménye String adattípusú lesz.

Bár itt numerikus adattípust használhatok (például Hosszú vagy Dupla), ez korlátozná a visszaadható számok tartományát. Ha van egy 20 szám hosszú karakterláncom, amelyet ki kell vonnom a teljes karakterláncból, a függvény hosszú vagy kettős deklarálása hibát eredményezne (mivel a szám kívül esik a tartományán). Ezért a függvény kimeneti adattípusát String -ként tartottam.

A kód második sora - a zöld, amely aposztróffal kezdődik - egy megjegyzés. A kód olvasásakor a VBA figyelmen kívül hagyja ezt a sort. Ezzel leírást vagy részletet adhat hozzá a kódhoz.

A kód harmadik sora a „StringLength” változót deklarálja Integer adattípusként. Ez az a változó, amelyben tároljuk a képlet által elemzett karakterlánc hosszának értékét.

A negyedik sor String adattípusként deklarálja a Result változót. Ez az a változó, ahol kivonjuk a számokat az alfanumerikus karakterláncból.

Az ötödik sor hozzárendeli a bemeneti argumentumban szereplő karakterlánc hosszát a „StringLength” változóhoz. Ne feledje, hogy a „CellRef” arra az érvre vonatkozik, amelyet a felhasználó ad meg a munkalap képletének használata közben (vagy a VBA -ban történő használatakor - ezt az oktatóanyag későbbi részében látjuk).

A hatodik, hetedik és nyolcadik sor a For Next ciklus része. A ciklus annyiszor fut, amennyi karakter van a bemeneti argumentumban. Ezt a számot a LEN függvény adja meg, és a „StringLength” változóhoz van hozzárendelve.

Tehát a ciklus „1 -től Stringlength -ig” fut.

A cikluson belül az IF utasítás elemzi a karakterlánc minden karakterét, és ha numerikus, akkor hozzáadja ezt a numerikus karaktert az Eredmény változóhoz. Ehhez a VBA MID funkcióját használja.

A kód második utolsó sora hozzárendeli az eredmény értékét a függvényhez. Ez a kódsor biztosítja, hogy a függvény visszaadja az „Eredmény” értéket a cellában (onnan, ahol hívják).

A kód utolsó sora az End Function. Ez egy kötelező kódsor, amely azt mondja a VBA -nak, hogy a funkciókód itt véget ér.

A fenti kód megmagyarázza a VBA -ban létrehozott tipikus egyéni függvény különböző részeit. A következő szakaszokban mélyen elmerülünk ezekben az elemekben, és megnézzük a VBA funkció Excelben történő végrehajtásának különböző módjait is.

Érvek a felhasználó által definiált függvényben a VBA -ban

A fenti példákban, ahol egy felhasználó által definiált függvényt hoztunk létre, hogy a numerikus részt egy alfanumerikus karakterláncból (GetNumeric) szerezzük be, a függvény egyetlen argumentumot tartalmaz.

Ebben a részben azt fogom ismertetni, hogyan lehet olyan függvényeket létrehozni, amelyek nem tartalmaznak érveket azokhoz, amelyek több argumentumot tartalmaznak (kötelező és nem kötelező argumentumok).

Funkció létrehozása a VBA -ban érvek nélkül

Az Excel munkalapon számos függvényünk van, amelyek nem vesznek argumentumokat (például RAND, TODAY, NOW).

Ezek a függvények nem függnek semmilyen bemeneti argumentumtól. Például a TODAY függvény az aktuális dátumot, a RAND függvény pedig 0 és 1 közötti véletlen számot ad vissza.

Hasonló funkciót hozhat létre a VBA -ban is.

Az alábbiakban látható a kód, amely megadja a fájl nevét. Nem igényel érveket, mivel a visszaadandó eredmény nem függ semmilyen érvtől.

Funkció WorkbookName () mint karakterlánc WorkbookName = ThisWorkbook.Name End Function

A fenti kód a funkció eredményét karakterlánc adattípusként határozza meg (mivel a kívánt eredmény a fájl neve - ami egy karakterlánc).

Ez a függvény a „ThisWorkbook.Name” értéket rendeli hozzá a függvényhez, amelyet a függvény munkalapon történő használatakor ad vissza.

Ha a fájl mentésre került, akkor a fájlkiterjesztéssel együtt adja vissza a nevet, különben egyszerűen megadja a nevet.

A fentieknek azonban van egy problémája.

Ha a fájlnév megváltozik, nem frissül automatikusan. Általában egy függvény frissül, amikor a bemeneti argumentumok megváltoznak. De mivel ebben a függvényben nincsenek érvek, a függvény nem számol újra (még akkor sem, ha megváltoztatja a munkafüzet nevét, zárja be, majd nyissa meg újra).

Ha szeretné, kényszerítheti az újraszámítást a billentyűparancs - Control + Alt + F9 - használatával.

Ahhoz, hogy a képlet újraszámításra kerüljön, amikor változás történik a munkalapon, be kell írnia egy kód sort.

Az alábbi kód lehetővé teszi a függvény újraszámítását, amikor változás történik a munkalapon (csakúgy, mint más hasonló munkalap -funkciók, például a MA vagy a RAND függvény).

Funkció WorkbookName () karakterlánc -alkalmazásként. Volatile True WorkbookName = ThisWorkbook.Name End Function

Most, ha megváltoztatja a munkafüzet nevét, ez a funkció frissül, amikor bármilyen változás történik a munkalapon, vagy amikor újra megnyitja ezt a munkafüzetet.

Funkció létrehozása a VBA -ban egy érvvel

A fenti szakaszok egyikében már láttuk, hogyan lehet olyan függvényt létrehozni, amely csak egy argumentumot tartalmaz (a fent leírt GetNumeric függvény).

Hozzunk létre egy másik egyszerű függvényt, amely csak egy érvet tartalmaz.

Az alábbi kóddal létrehozott függvény átalakítja a hivatkozott szöveget nagybetűvé. Most már van egy funkciónk az Excelben, és ez a funkció csak arra szolgál, hogy megmutassa, hogyan működik. Ha ezt meg kell tennie, akkor jobb, ha használja a beépített UPPER funkciót.

Funkció ConvertToUpperCase (CellRef as Range) ConvertToUpperCase = UCase (CellRef) End Function

Ez a függvény a VBA UCase függvényét használja a CellRef változó értékének megváltoztatására. Ezután hozzárendeli az értéket a ConvertToUpperCase függvényhez.

Mivel ez a függvény érvelést igényel, nincs szükségünk az Application.Volatile részre. Amint az argumentum megváltozik, a funkció automatikusan frissül.

Funkció létrehozása a VBA -ban több érveléssel

A munkalap függvényeihez hasonlóan a VBA -ban is létrehozhat olyan függvényeket, amelyek több érvet is tartalmaznak.

Az alábbi kód létrehoz egy függvényt, amely kibontja a szöveget a megadott határoló előtt. Két érvre van szükség - a szöveges karakterláncot tartalmazó cellahivatkozásra és a határolóra.

Funkció GetDataBeforeDelimiter (CellRef As Range, Delim As String) karakterlánc halvány eredményeként String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Bal (CellRef, DelimPosition) GetDataBeforeDelimiter

Ha egy felhasználó által definiált függvényben több argumentumot kell használnia, akkor a zárójelben lévő összes argumentum vesszővel elválasztható.

Ne feledje, hogy minden argumentumhoz adattípust adhat meg. A fenti példában a „CellRef” tartomány adattípusként, a „Delim” pedig String adattípusként lett deklarálva. Ha nem ad meg semmilyen adattípust, a VBA ezeket egy adattípus -változatnak tekinti.

Amikor a fenti függvényt használja a munkalapon, meg kell adnia a cellahivatkozást, amelynek első argumentuma a szöveg, a második argumentumként pedig kettős idézőjelben lévő elválasztó karakter (ek).

Ezután a VBA INSTR függvényével ellenőrzi a határoló helyzetét. Ezt a pozíciót használjuk az összes karakter kibontására a határoló előtt (a LEFT funkció használatával).

Végül hozzárendeli az eredményt a függvényhez.

Ez a képlet messze nem tökéletes. Ha például megad egy elválasztót, amely nem található meg a szövegben, az hibát okoz. Most már használhatja a munkalap IFERROR funkcióját, hogy megszabaduljon a hibáktól, vagy használhatja az alábbi kódot, amely visszaadja a teljes szöveget, ha nem találja a határolót.

Funkció GetDataBeforeDelimiter (CellRef As Range, Delim As String) karakterlánc halvány eredményeként String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ha DelimPosition <0 Akkor DelimPosition = Len (CellRef) Eredmény = Left ( CellRef, DelimPosition) GetDataBeforeDelimiter = Eredményvégfüggvény

Ezt a funkciót tovább optimalizálhatjuk.

Ha közvetlenül a függvénybe írja be a szöveget (amelyből ki akarja vonni az elválasztó előtti részt), az hibát jelez. Hajrá… próbáld ki!

Ez történik, mivel a „CellRef” -et tartomány adattípusként adtuk meg.

Vagy ha azt szeretné, hogy a határoló egy cellában legyen, és a cellahivatkozást használja a képletben történő kemény kódolás helyett, akkor ezt nem teheti meg a fenti kóddal. Ez azért van, mert a Delim -et karakterlánc -adattípusnak nyilvánították.

Ha azt szeretné, hogy a függvény rugalmasan fogadja el a közvetlen szövegbevitelt vagy cellahivatkozásokat a felhasználótól, akkor el kell távolítania az adattípus deklarációt. Ez azt eredményezné, hogy az argumentum adattípusváltozatként jelenik meg, amely bármilyen típusú érvet felvehet és feldolgozhat.

Az alábbi kód ezt tenné:

Funkció GetDataBeforeDelimiter (CellRef, Delim) String Dim eredményként String Dim DelimPosition mint Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Ha DelimPosition <0 Akkor DelimPosition = Len (CellRef) Eredmény = Bal (CellRef, DelimP GetDataBeforeDelimiter = Eredményvégfüggvény

Funkció létrehozása VBA -ban opcionális érvekkel

Az Excelben sok olyan funkció létezik, ahol néhány argumentum opcionális.

Például a legendás VLOOKUP függvény 3 kötelező argumentumot és egy opcionális argumentumot tartalmaz.

Egy opcionális argumentum, ahogy a neve is sugallja, nem kötelező megadni. Ha nem adja meg a kötelező argumentumok egyikét, akkor a függvény hibát fog adni, de ha nem adja meg az opcionális argumentumot, akkor a funkció működni fog.

De az opcionális érvek nem haszontalanok. Lehetővé teszik, hogy számos lehetőség közül válasszon.

Például a VLOOKUP függvényben, ha nem adja meg a negyedik argumentumot, a VLOOKUP hozzávetőleges keresést végez, és ha az utolsó argumentumot HAMIS (vagy 0) értékként adja meg, akkor pontos egyezést végez.

Ne feledje, hogy az opcionális argumentumoknak mindig az összes szükséges argumentum után kell szerepelniük. Az elején nem lehetnek opcionális érvek.

Most nézzük meg, hogyan hozhatunk létre függvényt a VBA -ban opcionális argumentumokkal.

Funkció csak opcionális érvvel

Amennyire én tudom, nincs olyan beépített függvény, amely csak opcionális argumentumokat vesz fel (itt tévedhetek, de ilyen funkció nem jut eszembe).

De létrehozhatunk egyet a VBA -val.

Az alábbiakban annak a funkciónak a kódja látható, amely megadja az aktuális dátumot dd-mm-yyyy formátumban, ha nem ad meg argumentumokat (azaz hagyja üresen), és „dd mmmm, yyyy” formátumban, ha bármit megad. argumentumként (azaz bármit, hogy az érv ne legyen üres).

Funkció CurrDate (opcionális fmt változatként) Dim Result If IsMissing (fmt) then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Ne feledje, hogy a fenti függvény az „IsMissing” használatával ellenőrzi, hogy az argumentum hiányzik -e vagy sem. Az IsMissing függvény használatához az opcionális argumentumnak a változat adattípusának kell lennie.

A fenti függvény működik, függetlenül attól, hogy mit ad meg argumentumként. A kódban csak azt ellenőrizzük, hogy az opcionális argumentum szerepel -e vagy sem.

Erősebbé teheti ezt, ha csak meghatározott értékeket vesz argumentumként, és a többi esetben hibát mutat (amint az az alábbi kódban látható).

Funkció CurrDate (opcionális fmt változatként) Dim Result If IsMissing (fmt) then CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 then CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) End If End függvény

A fenti kód létrehoz egy függvényt, amely megjeleníti a dátumot „dd-mm-yyyy” formátumban, ha nincs argumentum, és „dd mmmm, yyyy” formátumban, ha az argumentum 1. Minden más esetben hibát ad.

Funkció kötelező és opcionális érvekkel

Láthattunk már olyan kódot, amely kibontja a numerikus részt egy karakterláncból.

Most nézzünk meg egy hasonló példát, amely mind a kötelező, mind az opcionális érveket tartalmazza.

Az alábbi kód létrehoz egy függvényt, amely kibontja a szöveges részt egy karakterláncból. Ha az opcionális argumentum IGAZ, akkor az eredményt nagybetűvel adja meg, és ha az opcionális argumentum HAMIS vagy kihagyott, akkor az eredményt úgy adja meg, ahogy van.

Függvény GetText (CellRef as Range, Optional TextCase = False) Mint String Dim StringLength Mint Integer Dim Result String Eredmény = Eredmény és közepes (CellRef, i, 1) Következő i Ha TextCase = Igaz, akkor Eredmény = UCase (Eredmény) GetText = Eredményvégfüggvény

Ne feledje, hogy a fenti kódban a „TextCase” értékét hamis értékre inicializáltuk (nézze meg az első sor zárójelét).

Ezzel biztosítottuk, hogy az opcionális argumentum az alapértelmezett értékkel kezdődjön, ami FALSE. Ha a felhasználó IGAZ értékként adja meg az értéket, a függvény nagybetűvel adja vissza a szöveget, és ha a felhasználó az opcionális argumentumot HAMISnak adja meg, vagy kihagyja, akkor a visszaadott szöveg változatlan.

Funkció létrehozása VBA -ban, tömbként érvként

Eddig láttunk példákat egy függvény létrehozására opcionális/kötelező argumentumokkal - ahol ezek az érvek egyetlen értéket jelentettek.

Létrehozhat egy függvényt is, amely argumentumként tömböt vehet fel. Az Excel munkalap funkcióiban sok olyan függvény létezik, amelyek tömb argumentumokat vesznek fel, például SUM, VLOOKUP, SUMIF, COUNTIF stb.

Az alábbiakban az a kód található, amely létrehoz egy függvényt, amely megadja a cellák meghatározott tartományában lévő összes páros szám összegét.

Funkció AddEven (CellRef mint tartomány) A cella halványítása tartományként a CellRef minden cellájához, ha IsNumeric (Cell.Value), majd If Cell.Value Mod 2 = 0 Akkor Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Funkció

Ezt a funkciót használhatja a munkalapon, és megadhatja a cellák tartományát, amelyek argumentumai a számok. A függvény egyetlen értéket adna vissza - az összes páros szám összegét (amint az alább látható).

A fenti függvényben egyetlen érték helyett tömböt adtunk meg (A1: A10). Ahhoz, hogy ez működjön, meg kell győződnie arról, hogy az argumentum adattípusa elfogad egy tömböt.

A fenti kódban a CellRef argumentumot adtam meg tartományként (amely tömböt vehet be bemenetként). Itt is használhatja a változat adattípust.

A kódban van egy minden ciklus, amely minden cellán átmegy, és ellenőrzi, hogy nem. Ha nem, akkor semmi sem történik, és a következő cellába kerül. Ha ez egy szám, akkor a MOD funkció használatával ellenőrzi, hogy páros -e vagy sem.

Végül az összes páros szám összeadódik, és az összeg vissza van rendelve a függvényhez.

Határozatlan érvekkel rendelkező függvény létrehozása

A VBA egyes funkcióinak létrehozása során előfordulhat, hogy nem tudja, hogy a felhasználó hány argumentumot szeretne megadni. Tehát szükség van egy olyan függvény létrehozására, amely képes elfogadni annyi argumentumot, és ezeket felhasználva adja vissza az eredményt.

Ilyen munkalap -funkció például a SUM függvény. Több érvet is megadhat (például ezt):

= ÖSSZES (A1, A2: A4, B1: B20)

A fenti függvény hozzáadja az értékeket ezekhez az argumentumokhoz. Azt is vegye figyelembe, hogy ezek lehetnek egyetlen cella vagy cellatömb.

Létrehozhat egy ilyen függvényt a VBA -ban úgy, hogy az utolsó argumentumot (vagy ez lehet az egyetlen argumentumot) választhatónak tekinti. Ezenkívül ezt az opcionális érvet a „ParamArray” kulcsszónak kell megelőznie.

A „ParamArray” egy módosító, amely lehetővé teszi, hogy annyi érvet fogadjon el, amennyit csak akar. Ne feledje, hogy a ParamArray szó használata az argumentum előtt választhatóvá teszi az argumentumot. Itt azonban nem szükséges az Opcionális szót használni.

Most hozzunk létre egy függvényt, amely tetszőleges számú argumentumot képes elfogadni, és hozzáadja a megadott argumentumok összes számát:

Funkció AddArguments (ParamArray arglist () Variantként) Minden argumentumhoz Arglistában AddArguments = AddArguments + arg Következő arg End Function

A fenti függvény tetszőleges számú argumentumot felvehet, és hozzáadhatja ezeket az érveket az eredmény megadásához.

Ne feledje, hogy csak egyetlen értéket, cellahivatkozást, logikai értéket vagy kifejezést használhat argumentumként. Nem adhat meg tömböt argumentumként. Például, ha az egyik érve D8: D10, akkor ez a képlet hibát jelez.

Ha mindkét többcellás argumentumot használni szeretné, akkor használja az alábbi kódot:

Funkció AddArguments (ParamArray arglist () Variantként) Minden arg Arglistában Minden cellához arg AddArguments = AddArguments + Cell Next Cell Next arg End Function

Vegye figyelembe, hogy ez a képlet több cellával és tömbhivatkozással működik, azonban nem tudja feldolgozni a kódolt értékeket vagy kifejezéseket. Erősebb funkciót hozhat létre ezen feltételek ellenőrzésével és kezelésével, de itt nem ez a cél.

A cél az, hogy megmutassuk Önnek a ParamArray működését, így korlátlan számú érvet engedélyezhet a függvényben. Ha jobb funkciót szeretne, mint a fenti kód által létrehozott, használja a munkalap SUM függvényét.

Egy tömböt visszaadó függvény létrehozása

Eddig olyan funkciókat láttunk, amelyek egyetlen értéket adnak vissza.

A VBA segítségével létrehozhat egy függvényt, amely egy változatot ad vissza, amely teljes értéktömböt tartalmazhat.

A tömbképletek beépített függvényként is elérhetők az Excel munkalapokon. Ha ismeri az Excel tömbképleteit, akkor tudja, hogy ezeket a Control + Shift + Enter billentyűkombinációval írja be (az Enter helyett). A tömbképletekről itt olvashat bővebben. Ha nem ismeri a tömbképleteket, ne aggódjon, olvassa tovább.

Hozzon létre egy képletet, amely három számból álló tömböt ad vissza (1,2,3).

Az alábbi kód ezt tenné.

Funkció ThreeNumbers () mint variáns Dim DimensionValue (1 - 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Funkció

A fenti kódban a „ThreeNumbers” függvényt adtuk meg változatként. Ez lehetővé teszi, hogy értékrendet tároljon.

A „NumberValue” változó 3 elemből álló tömbként van deklarálva. Tartja a három értéket, és hozzárendeli a „ThreeNumbers” függvényhez.

Ezt a funkciót a munkalapon úgy használhatja, hogy belép a funkcióba, és megnyomja a Control + Shift + Enter billentyűt (tartsa lenyomva a Control és a Shift billentyűket, majd nyomja meg az Enter billentyűt).

Ha ezt megteszi, akkor 1 -et ad vissza a cellában, de a valóságban mindhárom értéket tartalmazza. Ennek ellenőrzéséhez használja az alábbi képletet:

= MAX (három szám ())

Használja a fenti funkciót a Control + Shift + Enter billentyűkombinációval. Észre fogja venni, hogy az eredmény most 3, mivel ez a legnagyobb érték a Max függvény által visszaadott tömbben, amely a három számot a felhasználó által definiált függvény - ThreeNumbers - eredményeként kapja meg.

Ugyanezzel a technikával hozhat létre olyan függvényt, amely a hónapnevek tömbjét adja vissza, az alábbi kód szerint:

Funkció hónapok () Variantként Dim MonthName (1-12) MonthName (1) = "January" MonthName (2) = "Február" MonthName (3) = "March" MonthName (4) = "April" MonthName (5) = "Május" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November" "MonthName (12) =" December "Months = MonthName End Function

Most, amikor megadja a = Hónapok () függvényt az Excel munkalapon, és a Control + Shift + Enter billentyűkombinációt használja, a hónapnevek teljes tömbjét adja vissza. Ne feledje, hogy csak a januárt látja a cellában, mivel ez az első érték a tömbben. Ez nem jelenti azt, hogy a tömb csak egy értéket ad vissza.

Ha meg szeretné mutatni azt a tényt, hogy az összes értéket visszaadja, tegye ezt - válassza ki a képletet tartalmazó cellát, lépjen a képletsávba, válassza ki a teljes képletet, és nyomja meg az F9 billentyűt. Ez megmutatja a függvény által visszaadott összes értéket.

Ezt használhatja az alábbi INDEX képlet használatával, hogy egy menetben megkapja az összes hónap nevét.

= INDEX (hónap (), ROW ())

Most, ha sok értéke van, nem jó gyakorlat ezeket az értékeket egyenként hozzárendelni (ahogy fentebb tettük). Ehelyett használhatja a tömb funkciót a VBA -ban.

Tehát ugyanaz a kód, ahol a „Hónapok” funkciót hozzuk létre, rövidebb lesz, mint az alábbiakban látható:

Funkcióhónapok () Változatos hónapokként = tömbök ("január", "február", "március", "április", "május", "június", _ "július", "augusztus", "szeptember", "október" , "November", "December") End Function

A fenti függvény a Tömb funkciót használja az értékek közvetlen hozzárendeléséhez a függvényhez.

Ne feledje, hogy a fent létrehozott összes függvény vízszintes értéktömböt ad vissza. Ez azt jelenti, hogy ha 12 vízszintes cellát választ ki (mondjuk A1: L1), és írja be a = Hónapok () képletet az A1 cellába, akkor az összes hónapnevet megadja.

De mi van akkor, ha ezeket az értékeket függőleges cellatartományba szeretné helyezni?

Ezt a munkalap TRANSPOSE képletével teheti meg.

Egyszerűen válassza ki a 12 függőleges cellát (szomszédos), és írja be az alábbi képletet.

A felhasználó által definiált függvény hatókörének megértése az Excel programban

Egy függvénynek két hatóköre lehet - Nyilvános vagy Magán.

• A Nyilvános hatály azt jelenti, hogy a funkció elérhető a munkafüzet összes lapjára, valamint az összes eljárás (al- és funkció) a munkafüzet minden moduljában. Ez akkor hasznos, ha egy alprogramból szeretne függvényt hívni (ezt a következő részben látjuk).
• A Privát hatókör azt jelenti, hogy a funkció csak abban a modulban érhető el, amelyben létezik. Más modulokban nem használható. A munkalap funkcióinak listájában sem fogja látni. Például, ha a függvény neve ‘Hónapok ()’, és Excelbe írja be a függvényt (a = jel után), akkor nem jelenik meg a függvény neve. Ennek ellenére továbbra is használhatja, ha beírja a képlet nevét.

Ha nem ad meg semmit, akkor a funkció alapértelmezés szerint Nyilvános függvény.

Az alábbiakban egy függvény, amely egy privát funkció:

Privát funkció WorkbookName () Mint karakterlánc WorkbookName = ThisWorkbook.Name End Function

Ezt a funkciót ugyanabban a modulban használhatja az alprogramokban és az eljárásokban, de más modulokban nem. Ez a funkció szintén nem jelenik meg a munkalapon.

Az alábbi kód nyilvánossá tenné ezt a funkciót. A munkalapon is megjelenik.

Funkció WorkbookName () mint karakterlánc WorkbookName = ThisWorkbook.Name End Function

A felhasználó által definiált funkció használatának különböző módjai az Excelben

Miután létrehozott egy felhasználó által definiált függvényt a VBA-ban, sokféleképpen használhatja.

Először nézzük meg, hogyan kell használni a munkalap funkcióit.

UDF -ek használata munkalapokon

Láttunk már példákat a VBA -ban létrehozott függvény használatára a munkalapon.

Mindössze annyit kell tennie, hogy megadja a függvény nevét, és megjelenik az intellisense -ben.

Ne feledje, hogy ahhoz, hogy a függvény megjelenhessen a munkalapon, nyilvános függvénynek kell lennie (amint azt a fenti szakasz ismerteti).

A Funkció beszúrása párbeszédpanelen a felhasználó által definiált függvényt is beszúrhatja (az alábbi lépések végrehajtásával). Ez csak a nyilvános funkcióknál működik.

• Lépjen az Adatok fülre.
• Kattintson a „Funkció beszúrása” lehetőségre.
• A Funkció beszúrása párbeszédpanelen válassza a Felhasználó által meghatározott kategóriát. Ez az opció csak akkor jelenik meg, ha van egy funkciója a VB szerkesztőben (és a funkció nyilvános).
• Válassza ki a funkciót a nyilvános felhasználó által definiált funkciók listájából.
• Kattintson az OK gombra.

A fenti lépések beillesztenék a függvényt a munkalapba. Megjelenik egy Funkció érvek párbeszédpanel is, amely részletesen ismerteti az érveket és az eredményt.

A felhasználó által definiált függvényt ugyanúgy használhatja, mint az Excel más funkcióit. Ez azt is jelenti, hogy más beépített Excel funkciókkal is használható. Például. az alábbi képlet megadná a munkafüzet nevét nagybetűvel:

= FELSŐ (WorkbookName ())

Felhasználó által definiált függvények használata a VBA eljárásokban és funkciókban

Ha létrehozott egy függvényt, akkor más al-eljárásokban is használhatja.

Ha a függvény nyilvános, akkor bármely eljárásban használható ugyanabban vagy más modulban. Ha privát, akkor csak ugyanabban a modulban használható.

Az alábbiakban egy függvény adja vissza a munkafüzet nevét.

Funkció WorkbookName () mint karakterlánc WorkbookName = ThisWorkbook.Name End Function

Az alábbi eljárás hívja meg a függvényt, majd jelenítse meg a nevet egy üzenetmezőben.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Funkciót más funkcióból is hívhat.

Az alábbi kódokban az első kód a munkafüzet nevét adja vissza, a második pedig a nevet nagybetűvel az első függvény meghívásával.

Funkció WorkbookName () mint karakterlánc WorkbookName = ThisWorkbook.Name End Function

Funkció WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) End Function

Felhasználó által definiált funkció hívása más munkafüzetekből

Ha van funkciója a munkafüzetben, akkor más munkafüzetekben is meghívhatja ezt a funkciót.

Ennek több módja is van:

1. Bővítmény létrehozása
2. Mentési funkció a Személyes makró munkafüzetben
3. A függvény hivatkozása egy másik munkafüzetből.

Bővítmény létrehozása

Egy bővítmény létrehozásával és telepítésével az egyéni funkció minden munkafüzetben elérhető lesz.

Tegyük fel, hogy létrehozott egy egyéni függvényt - a „GetNumeric” funkciót, és azt szeretné az összes munkafüzetben. Ehhez hozzon létre egy új munkafüzetet, és a funkciókódot az új munkafüzet egyik moduljában.

Most kövesse az alábbi lépéseket, és mentse bővítményként, majd telepítse az Excelbe.

• Lépjen a Fájl fülre, és kattintson a Mentés másként gombra.
• A Mentés másként párbeszédpanelen módosítsa a „Mentés másként” típust .xlam formátumra. A fájlhoz rendelt név a bővítmény neve lesz. Ebben a példában a fájl GetNumeric néven kerül mentésre.
  • Észre fogja venni, hogy a fájl elérési útja automatikusan megváltozik. Használhatja az alapértelmezettet, vagy módosíthatja, ha akarja.
• Nyisson meg egy új Excel munkafüzetet, és lépjen a Fejlesztő fülre.
• Kattintson az Excel-bővítmények lehetőségre.
• A Bővítmények párbeszédpanelen keresse meg és keresse meg a mentett fájlt, majd kattintson az OK gombra.

Most a bővítmény aktiválva lett.

Most már használhatja az egyéni funkciót az összes munkafüzetben.

A funkció mentése a személyes makró munkafüzetbe

A személyes makró munkafüzet egy rejtett munkafüzet a rendszerben, amely az Excel alkalmazás megnyitásakor megnyílik.

Ez egy olyan hely, ahol makrókódokat tárolhat, majd ezeket a makrókat bármely munkafüzetből elérheti. Remek hely a gyakran használni kívánt makrók tárolására.

Alapértelmezés szerint nincs személyes makró munkafüzet az Excelben. Létre kell hoznia egy makró rögzítésével és a Személyes makró munkafüzetbe mentésével.

A makrók létrehozásának és mentésének részletes lépéseit itt találja a személyes makró munkafüzetben.

A függvény hivatkozása egy másik munkafüzetből

Míg az első két módszer (egy kiegészítő létrehozása és a személyes makró munkafüzet használata) minden helyzetben működne, ha egy másik munkafüzetből kíván hivatkozni a funkcióra, akkor a munkafüzetnek nyitva kell lennie.

Tegyük fel, hogy van egy munkafüzete a következő névvel:Munkafüzet képlettel ”, és funkciója „GetNumeric ”.

Ennek a funkciónak a használata egy másik munkafüzetben (míg a Munkafüzet képlettel nyitva van), az alábbi képletet használhatja:

= 'Munkafüzet képlettel'! GetNumeric (A1)

A fenti képlet a felhasználó által definiált függvényt használja Munkafüzet képlettel fájlt, és adja meg az eredményt.

Ne feledje, hogy mivel a munkafüzet nevében szóközök vannak, ezt egyetlen idézőjelbe kell foglalni.

Az Exit Function Statement VBA használata

Ha a kód futása közben ki szeretne lépni egy funkcióból, akkor ezt az „Exit Function” utasítással teheti meg.

Az alábbi kód kivonja az első három numerikus karaktert egy alfanumerikus szövegből. Amint megkapja a három karaktert, a funkció véget ér, és visszaadja az eredményt.

Funkció GetNumericFirstThree (CellRef mint tartomány) Hosszú halvány karakterlánc Hosszúság, mint egész karakterláncLength = Len (CellRef) I = 1 -ig StringLength Ha J = 3, akkor lépjen ki a funkcióból, ha IsNumeric (Közép (CellRef, i, 1)) Akkor J = J + 1 Eredmény = Eredmény és közép (CellRef, i, 1) GetNumericFirstThree = Eredmény vége, ha a következő i vége

A fenti függvény ellenőrzi a numerikus karakterek számát, és ha 3 numerikus karaktert kap, kilép a funkcióból a következő ciklusban.

Felhasználó által definiált funkció hibakeresése

Néhány technikát használhat a felhasználó által definiált funkció hibakeresése során a VBA-ban:

Egyéni funkció hibakeresése az Üzenetdoboz használatával

Az MsgBox funkcióval megjeleníthet egy adott értékű üzenetdobozt.

A megjelenített érték azon alapulhat, amit tesztelni szeretne. Például, ha ellenőrizni szeretné, hogy a kód végrehajtásra kerül -e vagy sem, bármelyik üzenet működne, és ha ellenőrizni szeretné, hogy a ciklusok működnek -e vagy sem, megjeleníthet egy adott értéket vagy a ciklusszámlálót.

Egyéni funkció hibakeresése a töréspont beállításával

Állítson be egy töréspontot, hogy egyenként végig tudjon lépni minden soron. Töréspont beállításához válassza ki a kívánt vonalat, és nyomja meg az F9 billentyűt, vagy kattintson a szürke függőleges területre, amely a kódvonalakhoz tartozik. Ezen módszerek bármelyike ​​töréspontot illesztene be (piros pontot lát a szürke területen).

Miután beállította a töréspontot és végrehajtotta a funkciót, az a töréspont vonaláig tart, majd leáll. Most az F8 billentyűvel léphet át a kódon. Az F8 egyszeri megnyomása a kód következő sorára lép.

Egyéni funkció hibakeresése a Debug használatával. Nyomtassa ki a kódot

A Debug.Print utasítással a kódban lekérheti a megadott változók/argumentumok értékeit a közvetlen ablakban.

Például az alábbi kódban a Debug.Print programot használtam két változó - „j” és „Result” - értékének lekérésére

Funkció GetNumericFirstThree (CellRef mint tartomány) Hosszú halvány karakterlánc Hosszúság, mint egész karakterláncLength = Len (CellRef) I = 1 -ig StringLength Ha J = 3, akkor lépjen ki a funkcióból, ha IsNumeric (Közép (CellRef, i, 1)) Akkor J = J + 1 Eredmény = Eredmény és közepes (CellRef, i, 1) Hibakeresés. Nyomtatás J, Eredmény GetNumericFirstThree = Eredmény vége, ha a következő i Vége funkció

Amikor ezt a kódot végrehajtja, a következőt jeleníti meg a közvetlen ablakban.

Excel beépített funkciók vs. VBA felhasználó által definiált funkció

Az Excel beépített funkcióinak kevés előnye van a VBA-ban létrehozott egyéni funkciókkal szemben.

• A beépített funkciók sokkal gyorsabbak, mint a VBA funkciók.
• Amikor jelentést/irányítópultot hoz létre a VBA funkciók használatával, és elküldi egy ügyfélnek/kollégának, nem kell aggódnia azon, hogy a makrók engedélyezve vannak -e. Bizonyos esetekben az ügyfelek/ügyfelek megijednek attól, hogy figyelmeztetést látnak a sárga sávon (amely egyszerűen felkéri őket a makrók engedélyezésére).
• A beépített Excel funkciókkal nem kell aggódnia a fájlkiterjesztések miatt. Ha makrók vagy felhasználó által definiált funkciók vannak a munkafüzetben, akkor azt .xlsm fájlba kell mentenie.

Bár számos komoly oka van az Excel beépített funkcióinak használatára, néhány esetben jobb, ha egy felhasználó által definiált funkciót használ.

• Jobb a felhasználó által definiált függvény használata, ha a beépített képlet hatalmas és bonyolult. Ez még relevánsabbá válik, ha valaki másra van szüksége a képletek frissítéséhez. Például, ha van egy hatalmas képlete, amely sok különböző funkcióból áll, még a cellahivatkozás megváltoztatása is unalmas és hibalehetőséget okozhat. Ehelyett létrehozhat egy egyéni függvényt, amely csak egy vagy két argumentumot vesz igénybe, és minden nehéz feladatot megemel.
• Ha valamit el kell végeznie, amit az Excel beépített funkciói nem képesek megtenni. Erre példa lehet, ha ki akarja vonni az összes numerikus karaktert egy karakterláncból. Ilyen esetekben a felhasználó által definiált függvény gar használatának előnye meghaladja annak negatívumait.

Hová tegye a VBA kódot egy felhasználó által definiált funkcióhoz

Egyéni függvény létrehozásakor be kell helyeznie a kódot annak a munkafüzetnek a kódablakába, amelyben a funkciót használni szeretné.

Az alábbiakban bemutatjuk a „GetNumeric” funkció kódjának a munkafüzetbe történő beillesztésének lépéseit.

1. Lépjen a Fejlesztő fülre.
2. Kattintson a Visual Basic lehetőségre. Ez megnyitja a VB szerkesztőt a háttérben.
3. A VB szerkesztő Project Explorer ablaktáblájában kattintson jobb gombbal a munkafüzet bármely olyan objektumára, amelybe be szeretné illeszteni a kódot. Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
4. Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ezzel beszúr egy modulobjektumot a munkafüzetébe.
5. Másolja ki és illessze be a kódot a modul ablakába.

A következő Excel VBA oktatóanyagok is tetszhetnek:

• Munka cellákkal és tartományokkal az Excel VBA -ban.
• Munkalapok használata Excel VBA -ban.
• Munkafüzetek használata VBA használatával.
• A hurkok használata az Excel VBA -ban.
• Excel VBA események - Egyszerű (és teljes) útmutató
• Az IF majd más állítások használata a VBA -ban.
• Makró rögzítése Excelben.
• Makró futtatása Excelben.
• Az adatok rendezése Excelben a VBA használatával (lépésről lépésre).
• Excel VBA InStr függvény - példákkal magyarázva.