Käyttäjän määrittämän toiminnon (UDF) luominen Excel VBA: ssa (Ultimate Guide)

VBA: n avulla voit luoda mukautetun toiminnon (jota kutsutaan myös käyttäjän määrittämäksi funktioksi), jota voidaan käyttää laskentataulukoissa aivan kuten tavallisia toimintoja.

Näistä on hyötyä, kun olemassa olevat Excel -toiminnot eivät riitä. Tällaisissa tapauksissa voit luoda omia käyttäjän määrittämiä toimintoja (UDF), jotka vastaavat erityistarpeitasi.

Tässä opetusohjelmassa käsitän kaiken mukautettujen toimintojen luomisesta ja käytöstä VBA: ssa.

Jos olet kiinnostunut VBA: n oppimisesta helpolla tavalla, tutustu minun Online Excel VBA -koulutus.

Mikä on toimintamenettely VBA: ssa?

Toimintamenettely on VBA -koodi, joka suorittaa laskutoimituksia ja palauttaa arvon (tai arvoryhmän).

Function -toiminnolla voit luoda funktion, jota voit käyttää laskentataulukossa (aivan kuten mitä tahansa tavallista Excel -toimintoa, kuten SUM tai VLOOKUP).

Kun olet luonut toimintamenettelyn VBA: n avulla, voit käyttää sitä kolmella tavalla:

1. Laskentataulukon kaava, jossa se voi ottaa argumentteja syötteinä ja palauttaa arvon tai arvotaulukon.
2. Osana VBA -aliohjelmakoodiasi tai muuta toimintokoodia.
3. Ehdollisessa muotoilussa.

Vaikka laskentataulukossa on jo 450+ sisäänrakennettua Excel -toimintoa, saatat tarvita mukautetun toiminnon, jos:

• Sisäänrakennetut toiminnot eivät voi tehdä sitä, mitä haluat tehdä. Tässä tapauksessa voit luoda mukautetun toiminnon tarpeidesi mukaan.
• Sisäänrakennetut toiminnot voivat saada työn aikaan, mutta kaava on pitkä ja monimutkainen. Tässä tapauksessa voit luoda mukautetun toiminnon, jota on helppo lukea ja käyttää.

Huomaa, että VBA: lla luodut mukautetut toiminnot voivat olla huomattavasti hitaampia kuin sisäänrakennetut toiminnot. Siksi nämä sopivat parhaiten tilanteisiin, joissa et voi saada tulosta sisäänrakennettujen toimintojen avulla.

Toiminto vs. Aliohjelma VBA: ssa

Aliohjelman avulla voit suorittaa koodijoukon, kun taas funktio palauttaa arvon (tai arvoryhmän).

Esimerkki: jos sinulla on luettelo numeroista (sekä positiivisia että negatiivisia) ja haluat tunnistaa negatiiviset luvut, voit tehdä funktion ja aliohjelman seuraavasti.

Aliohjelma voi silmukoida alueen jokaisen solun läpi ja korostaa kaikki solut, joilla on negatiivinen arvo. Tässä tapauksessa aliohjelma muuttaa alueobjektin ominaisuuksia (muuttamalla solujen väriä).

Mukautetun funktion avulla voit käyttää sitä erillisessä sarakkeessa ja se voi palauttaa arvon TOSI, jos solun arvo on negatiivinen ja EPÄTOSI, jos se on positiivinen. Funktion avulla et voi muuttaa objektin ominaisuuksia. Tämä tarkoittaa, että et voi muuttaa solun väriä itse funktiolla (voit kuitenkin tehdä sen käyttämällä ehdollista muotoilua mukautetulla toiminnolla).

Kun luot käyttäjän määrittämän funktion (UDF) VBA: n avulla, voit käyttää tätä toimintoa laskentataulukossa aivan kuten mitä tahansa muuta toimintoa. Käsittelen tästä lisää osiossa ”Käyttäjän määrittämän toiminnon erilaiset käyttötavat Excelissä”.

Yksinkertaisen käyttäjän määrittämän toiminnon luominen VBA: ssa

Haluan luoda yksinkertaisen käyttäjän määrittämän toiminnon VBA: han ja näyttää kuinka se toimii.

Alla oleva koodi luo toiminnon, joka poimii numeeriset osat aakkosnumeerisesta merkkijonosta.

Funktio GetNumeric (CellRef As String) Long Dim StringPituus kokonaislukuna StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) Sitten Result = Result & Mid (CellRef, i, 1) Seuraava i GetNumeric = Tuloksen lopetusfunktio

Kun yllä oleva koodi on moduulissa, voit käyttää tätä toimintoa työkirjassa.

Alla on, miten tämä toiminto - GetNumeric - voidaan käyttää Excelissä.

Ennen kuin kerron sinulle, miten tämä toiminto luodaan VBA: ssa ja miten se toimii, sinun on tiedettävä muutama asia:

• Kun luot funktion VBA: ssa, se tulee saataville koko työkirjassa aivan kuten mikä tahansa muu tavallinen toiminto.
• Kun kirjoitat funktion nimen ja sen jälkeen yhtäsuuruusmerkin, Excel näyttää toiminnon nimen vastaavien toimintojen luettelossa. Yllä olevassa esimerkissä, kun kirjoitin = Hae, Excel näytti minulle luettelon, jossa oli oma toiminto.

Uskon, että tämä on hyvä esimerkki, kun voit luoda VBA: n avulla helppokäyttöisen toiminnon Excelissä. Voit tehdä saman myös kaavalla (kuten tässä opetusohjelmassa on esitetty), mutta siitä tulee monimutkaista ja vaikeasti ymmärrettävää. Tämän UDF: n avulla sinun on vain läpäistävä yksi argumentti ja saat tuloksen.

Käyttäjän määrittämän toiminnon anatomia VBA: ssa

Yllä olevassa osassa annoin sinulle koodin ja näytin sinulle, miten UDF -toiminto toimii laskentataulukossa.

Katsotaanpa nyt syvälle ja katsotaan, miten tämä toiminto luodaan. Sinun on sijoitettava alla oleva koodi VB Editorin moduuliin. Käsittelen tätä aihetta osiossa - 'Minne sijoittaa käyttäjän määrittämän toiminnon VBA -koodi'.

Funktio GetNumeric (CellRef As String) kuten Long 'Tämä toiminto poimii numeerisen osan merkkijonosta Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 to StringLength If IsNumeric (Mid (CellRef, i, 1)) Sitten Tulos = Tulos ja keskitaso (CellRef, i, 1) Seuraava i GetNumeric = Tuloksen lopetusfunktio

Koodin ensimmäinen rivi alkaa sanalla - Function.

Tämä sana kertoo VBA: lle, että koodimme on funktio (eikä aliohjelma). Sanaa Function seuraa funktion nimi - GetNumeric. Tätä nimeä käytämme laskentataulukossa tämän toiminnon käyttämiseksi.

• Funktion nimessä ei saa olla välilyöntejä. Et voi myöskään nimetä funktiota, jos se on ristiriidassa soluviittauksen nimen kanssa. Et esimerkiksi voi antaa toiminnolle nimeä ABC123, koska se viittaa myös Excel -laskentataulukon soluun.
• Älä anna toiminnollesi samaa nimeä kuin olemassa oleva toiminto. Jos teet tämän, Excel suosii sisäänrakennettua toimintoa.
• Voit käyttää alaviivaa, jos haluat erottaa sanat. Esimerkiksi Get_Numeric on hyväksyttävä nimi.

Funktion nimen perässä on joitakin argumentteja suluissa. Nämä ovat argumentteja, joita toimintomme tarvitsee käyttäjältä. Nämä ovat aivan kuin argumentit, jotka meidän on toimitettava Excelin sisäänrakennettuihin toimintoihin. Esimerkiksi COUNTIF -funktiossa on kaksi argumenttia (alue ja ehdot)

Sulkeissa on määritettävä argumentit.

Esimerkissämme on vain yksi argumentti - CellRef.

On myös hyvä määritellä, millaisia ​​argumentteja toiminto odottaa. Tässä esimerkissä, koska annamme funktiolle soluviittauksen, voimme määrittää argumentin "Range" -tyypiksi. Jos et määritä tietotyyppiä, VBA pitää sitä muunnelmana (mikä tarkoittaa, että voit käyttää mitä tahansa tietotyyppiä).

Jos sinulla on useampi kuin yksi argumentti, voit määrittää ne samassa sulkeessa pilkulla erotettuna. Näemme myöhemmin tässä opetusohjelmassa kuinka käyttää useita argumentteja käyttäjän määrittämässä funktiossa.

Huomaa, että toiminto on määritetty merkkijonotyypiksi. Tämä kertoisi VBA: lle, että kaavan tulos olisi merkkijonotyyppi.

Vaikka voin käyttää tässä numeerista tietotyyppiä (kuten pitkä tai kaksinkertainen), se rajoittaisi palauttamiensa numeroiden määrää. Jos minulla on 20 numeroa pitkä merkkijono, joka minun on poimittava koko merkkijonosta, funktion ilmoittaminen pitkäksi tai kaksinkertaiseksi antaisi virheen (koska numero olisi sen alueen ulkopuolella). Siksi olen pitänyt toiminnon ulostulotyypin merkkijonona.

Koodin toinen rivi - vihreä, joka alkaa heittomerkillä - on kommentti. Koodia lukiessaan VBA jättää tämän rivin huomiotta. Voit käyttää tätä koodin kuvauksen tai yksityiskohdan lisäämiseen.

Koodin kolmas rivi ilmoittaa muuttujan "StringLength" kokonaislukutietotyypiksi. Tämä on muuttuja, johon tallennamme kaavan analysoiman merkkijonon pituuden arvon.

Neljäs rivi ilmoittaa muuttujan Tuloksen merkkijonon tietotyypiksi. Tämä on muuttuja, jossa poimimme numerot aakkosnumeerisesta merkkijonosta.

Viides rivi määrittää syöttöargumentin merkkijonon pituuden "StringLength" -muuttujalle. Huomaa, että "CellRef" viittaa argumenttiin, jonka käyttäjä antaa käyttäessään laskentataulukon kaavaa (tai käyttämällä sitä VBA: ssa - jonka näemme myöhemmin tässä opetusohjelmassa).

Kuudes, seitsemäs ja kahdeksas rivi ovat osa Seuraava -silmukkaa. Silmukka kestää niin monta kertaa kuin monta merkkiä on syöttöargumentissa. Tämä numero annetaan LEN -funktiolla ja se on määritetty StringLength -muuttujalle.

Silmukka kulkee siis "yhdestä merkkijonopituuteen".

Silmukan sisällä IF -käsky analysoi merkkijonon jokaisen merkin ja jos se on numeerinen, se lisää kyseisen numeerisen merkin tulosmuuttujaan. Se käyttää VBA: n MID -toimintoa tähän.

Koodin toinen viimeinen rivi määrittää tuloksen arvon funktiolle. Tämä koodirivi varmistaa, että funktio palauttaa Result -arvon takaisin soluun (mistä sitä kutsutaan).

Koodin viimeinen rivi on End Function. Tämä on pakollinen koodirivi, joka kertoo VBA: lle, että toimintokoodi päättyy tähän.

Yllä oleva koodi selittää VBA: ssa luodun tyypillisen mukautetun toiminnon eri osat. Seuraavissa osioissa syvennymme näihin elementteihin ja näemme myös eri tapoja suorittaa VBA -toiminto Excelissä.

Argumentit käyttäjän määrittämässä toiminnossa VBA: ssa

Yllä olevissa esimerkeissä, joissa loimme käyttäjän määrittämän funktion numeerisen osan saamiseksi aakkosnumeerisesta merkkijonosta (GetNumeric), toiminto on suunniteltu yhdeksi argumentiksi.

Tässä osassa käsittelen, miten luodaan funktioita, jotka eivät ota argumentteja useiden argumenttien (pakolliset ja valinnaiset) argumentteihin.

Toiminnon luominen VBA: ssa ilman väitteitä

Excel -laskentataulukossa on useita toimintoja, jotka eivät ota argumentteja (kuten RAND, TODAY, NOW).

Nämä toiminnot eivät ole riippuvaisia ​​mistään syöttöargumenteista. Esimerkiksi TODAY -funktio palauttaa nykyisen päivämäärän ja RAND -funktio palauttaa satunnaisluvun välillä 0 ja 1.

Voit luoda samanlaisen toiminnon myös VBA: ssa.

Alla on koodi, joka antaa sinulle tiedoston nimen. Se ei vaadi mitään argumentteja, koska palautettava tulos ei ole riippuvainen mistään argumentista.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Yllä oleva koodi määrittää toiminnon tuloksen merkkijonotyypiksi (haluamamme tulos on tiedostonimi - joka on merkkijono).

Tämä toiminto määrittää funktiolle arvon ThisWorkbook.Name, joka palautetaan, kun funktiota käytetään laskentataulukossa.

Jos tiedosto on tallennettu, se palauttaa nimen tiedostopäätteen kanssa, muuten se antaa nimen.

Edellä on kuitenkin yksi ongelma.

Jos tiedostonimi muuttuu, se ei päivity automaattisesti. Normaalisti toiminto päivittyy aina, kun syöttöargumentit muuttuvat. Mutta koska tässä funktiossa ei ole argumentteja, funktio ei laske uudelleen (vaikka muutat työkirjan nimen, sulje se ja avaa se sitten uudelleen).

Halutessasi voit pakottaa uudelleenlaskennan käyttämällä pikanäppäintä - Control + Alt + F9.

Jos haluat, että kaava lasketaan uudelleen aina, kun laskentataulukko muuttuu, sinun on syötettävä sille koodirivi.

Alla oleva koodi saa funktion laskemaan uudelleen aina, kun laskentataulukko muuttuu (aivan kuten muut vastaavat laskentataulukon toiminnot, kuten TODAY tai RAND -toiminto).

Function WorkbookName () merkkijonosovelluksena. Volatile True WorkbookName = ThisWorkbook.Name End Function

Jos muutat työkirjan nimeä, tämä toiminto päivittyy aina, kun laskentataulukossa tapahtuu muutoksia tai kun avaat tämän työkirjan uudelleen.

Toiminnon luominen VBA: ssa yhdellä argumentilla

Yhdessä yllä olevista osista olemme jo nähneet, kuinka luodaan funktio, joka vaatii vain yhden argumentin (GetNumeric -toiminto yllä).

Luodaan toinen yksinkertainen funktio, joka vaatii vain yhden argumentin.

Alla olevalla koodilla luotu toiminto muuttaisi viitatun tekstin isoiksi kirjaimiksi. Nyt meillä on jo toiminto sille Excelissä, ja tämä toiminto on vain näyttääksesi kuinka se toimii. Jos sinun on tehtävä tämä, on parempi käyttää sisäänrakennettua UPPER -toimintoa.

Toiminto ConvertToUpperCase (CellRef as Range) ConvertToUpperCase = UCase (CellRef) End Function

Tämä toiminto käyttää VBA: n UCase -toimintoa CellRef -muuttujan arvon muuttamiseen. Sitten se määrittää arvon funktiolle ConvertToUpperCase.

Koska tämä toiminto ottaa argumentin, meidän ei tarvitse käyttää sovellusta. Haihtuva osa tässä. Heti kun argumentti muuttuu, toiminto päivittyy automaattisesti.

Toiminnon luominen VBA: ssa, jossa on useita argumentteja

Aivan kuten laskentataulukkotoiminnot, voit luoda VBA: han toimintoja, jotka vaativat useita argumentteja.

Alla oleva koodi luo funktion, joka poimii tekstin ennen määritettyä erotinta. Se vaatii kaksi argumenttia - soluviittauksen, jossa on tekstijono, ja erottimen.

Funktio GetDataBeforeDelimiter (CellRef As Range, Delim As String) merkkijonona Dim Tulos merkkijonona Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBefore Endelimeter =

Kun sinun on käytettävä useampaa kuin yhtä argumenttia käyttäjän määrittämässä funktiossa, suluissa olevat argumentit voidaan erottaa toisistaan ​​pilkulla.

Huomaa, että jokaiselle argumentille voit määrittää tietotyypin. Yllä olevassa esimerkissä "CellRef" on ilmoitettu alueen tietotyypiksi ja "Delim" on ilmoitettu merkkijonon tietotyypiksi. Jos et määritä tietotyyppiä, VBA pitää näitä tietotyypin muunnoksena.

Kun käytät yllä olevaa funktiota laskentataulukossa, sinun on annettava soluviittaus, jonka ensimmäinen argumentti on teksti ja toisena argumenttina erotinmerkki (lainausmerkit).

Sen jälkeen se tarkistaa erottimen aseman VBA: n INSTR -toiminnolla. Tätä kohtaa käytetään sitten erottamaan kaikki merkit ennen erotinta (LEFT -funktion avulla).

Lopuksi se määrittää tuloksen funktiolle.

Tämä kaava on kaukana täydellisestä. Jos esimerkiksi kirjoitat erottimen, jota ei löydy tekstistä, se antaa virheen. Nyt voit käyttää virheitä laskentataulukon IFERROR -toiminnolla tai käyttää alla olevaa koodia, joka palauttaa koko tekstin, kun se ei löydä erotinta.

Funktio GetDataBeforeDelimiter (CellRef As Range, Delim As String) merkkijonona Dim Tulos merkkijonona Dim DelimPosition kokonaislukuna DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Jos DelimPosition <0 Sitten DelimPosition = Len (CellRef) Tulos = Vasen ( CellRef, DelimPosition) GetDataBeforeDelimiter = Tuloksen lopetusfunktio

Voimme optimoida tämän toiminnon edelleen.

Jos kirjoitat tekstin (josta haluat erottaa erottimen edessä olevan osan) suoraan funktioon, se antaa sinulle virheen. Mene eteenpäin… kokeile!

Tämä tapahtuu, kun olemme määrittäneet CellRef -alueen tietotyypiksi.

Tai jos haluat, että erotin on solussa ja käytät soluviittausta sen kovan koodauksen sijaan kaavassa, et voi tehdä sitä yllä olevan koodin avulla. Tämä johtuu siitä, että Delim on ilmoitettu merkkijonon tietotyypiksi.

Jos haluat toiminnon joustavan hyväksyvän suoran tekstinsyötön tai soluviittaukset käyttäjältä, sinun on poistettava tietotyypin ilmoitus. Tämä tekisi argumentista tietotyypin muunnelman, joka voi ottaa minkä tahansa argumentin ja käsitellä sen.

Alla oleva koodi toimisi näin:

Toiminto GetDataBeforeDelimiter (CellRef, Delim) merkkijonon himmeänä tuloksena merkkijonona himmeänä DelimPosition kokonaislukuna DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Jos DelimPosition <0 Sitten DelimPosition = Len (CellRef) Tulos = Left (CellRef, DelimP) GetDataBeforeDelimiter = Tuloksen lopetusfunktio

Toiminnon luominen VBA: ssa valinnaisilla argumenteilla

Excelissä on monia toimintoja, joissa jotkut argumentit ovat valinnaisia.

Esimerkiksi legendaarisessa VLOOKUP -funktiossa on 3 pakollista argumenttia ja yksi valinnainen argumentti.

Valinnainen argumentti, kuten nimestä voi päätellä, on valinnainen määritettäessä. Jos et määritä yhtä pakollista argumenttia, funktiosi antaa sinulle virheen, mutta jos et määritä valinnaista argumenttia, toiminto toimii.

Mutta valinnaiset argumentit eivät ole hyödyttömiä. Niiden avulla voit valita useista vaihtoehdoista.

Esimerkiksi VLOOKUP -funktiossa, jos et määritä neljättä argumenttia, VLOOKUP tekee likimääräisen haun ja jos määrität viimeisen argumentin EPÄTOSI (tai 0), se vastaa tarkasti.

Muista, että valinnaisten argumenttien on aina oltava kaikkien vaadittujen argumenttien jälkeen. Alussa ei voi olla valinnaisia ​​argumentteja.

Katsotaan nyt, miten luodaan toiminto VBA: ssa valinnaisilla argumentteilla.

Toiminto vain valinnaisella argumentilla

Sikäli kuin tiedän, ei ole sisäänrakennettua toimintoa, joka ottaa vain valinnaisia ​​argumentteja (voin olla väärässä, mutta en voi ajatella mitään sellaista toimintoa).

Mutta voimme luoda sellaisen VBA: n avulla.

Alla on sen toiminnon koodi, joka antaa sinulle nykyisen päivämäärän muodossa pp-kk-vvvv, jos et anna mitään argumenttia (eli jätä se tyhjäksi), ja muodossa „pp mmmm, yyyy”, jos syötät jotain argumenttina (eli mitä tahansa, jotta argumentti ei ole tyhjä).

Toiminto CurrDate (valinnainen fmt vaihtoehtona) Dim Result If IsMissing (fmt) then CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Huomaa, että yllä oleva toiminto tarkistaa IsMissing -toiminnon avulla, puuttuuko argumentti vai ei. Jos haluat käyttää IsMissing -toimintoa, valinnaisen argumenttisi on oltava varianttityyppiä.

Yllä oleva toiminto toimii riippumatta siitä, mitä kirjoitat argumentiksi. Koodissa tarkistamme vain, onko valinnainen argumentti toimitettu vai ei.

Voit tehdä tästä vahvemman ottamalla argumentteina vain tietyt arvot ja näyttämällä muissa tapauksissa virheen (kuten alla olevassa koodissa näytetään).

Toiminto CurrDate (valinnainen fmt vaihtoehtona) Dim Tulos If IsMissing (fmt) Sitten CurrDate = Format (Date, "dd-mm-yyyy") ElseIf fmt = 1 then CurrDate = Format (Date, "dd mmmm, yyyy") Else CurrDate = CVErr (xlErrValue) End If End -toiminto

Yllä oleva koodi luo funktion, joka näyttää päivämäärän muodossa "pp-kk-vvvv", jos argumenttia ei anneta, ja muodossa "pp kk-vvvv", kun argumentti on 1. Se antaa virheen kaikissa muissa tapauksissa.

Toiminto pakollisilla ja valinnaisilla argumenteilla

Olemme jo nähneet koodin, joka poimii numeerisen osan merkkijonosta.

Katsotaanpa nyt samanlaista esimerkkiä, joka sisältää sekä pakollisia että valinnaisia ​​argumentteja.

Alla oleva koodi luo toiminnon, joka poimii tekstiosan merkkijonosta. Jos valinnainen argumentti on TOSI, se antaa tuloksen isoilla kirjaimilla ja jos valinnainen argumentti on EPÄTOSI tai se jätetään pois, se antaa tuloksen sellaisenaan.

Funktio GetText (CellRef As Range, Optional TextCase = False) merkkijonona Dim StringLength kokonaislukuna Dim Tuloksena merkkijonona StringLength = Len (CellRef) I = 1 StringLength Jos ei (IsNumeric (Mid (CellRef, i, 1))) Sitten Tulos = Tulos ja puoliväli (CellRef, i, 1) Seuraava i Jos TextCase = Tosi Sitten Tulos = UCase (tulos) GetText = Tuloksen lopetusfunktio

Huomaa, että yllä olevassa koodissa olemme alustaneet "TextCase" -arvon arvoksi False (katso ensimmäisen rivin sulkeita).

Näin olemme varmistaneet, että valinnainen argumentti alkaa oletusarvolla, joka on EPÄTOSI. Jos käyttäjä määrittää arvon TOSI, funktio palauttaa tekstin isoilla kirjaimilla ja jos käyttäjä määrittää valinnaisen argumentin EPÄTOSIAksi tai jättää sen pois, palautettu teksti on sellaisenaan.

Toiminnon luominen VBA: ssa, jossa Array on argumentti

Toistaiseksi olemme nähneet esimerkkejä funktion luomisesta valinnaisilla/pakollisilla argumenteilla - joissa nämä argumentit olivat yksi arvo.

Voit myös luoda funktion, joka voi käyttää taulukkoa argumenttina. Excel -laskentataulukkotoiminnoissa on monia funktioita, jotka käyttävät matriisi -argumentteja, kuten SUM, VLOOKUP, SUMIF, COUNTIF jne.

Alla on koodi, joka luo funktion, joka antaa kaikkien parillisten numeroiden summan määritetyllä solualueella.

Toiminto AddEven (CellRef as Range) Dim Dim Cell as Range for each Cell In CellRef If IsNumeric (Cell.Value) Sitten If Cell.Value Mod 2 = 0 then Result = Result + Cell.Value End Jos End If Next Cell AddEven = Result End Toiminto

Voit käyttää tätä toimintoa laskentataulukossa ja antaa solualueen, jonka argumentit ovat numerot. Funktio palauttaa yhden arvon - kaikkien parillisten numeroiden summan (kuten alla on esitetty).

Yllä olevassa funktiossa yhden arvon sijasta olemme toimittaneet taulukon (A1: A10). Jotta tämä toimisi, sinun on varmistettava, että argumentin tietotyyppi hyväksyy taulukon.

Määritin yllä olevassa koodissa CellRef -argumentin alueeksi (joka voi ottaa taulukon syötteeksi). Voit myös käyttää muunnelman tietotyyppiä täällä.

Koodissa on jokaiselle silmukalle, joka kulkee jokaisen solun läpi ja tarkistaa, onko se useita. Jos ei, mitään ei tapahdu ja se siirtyy seuraavaan soluun. Jos se on numero, se tarkistaa, onko se parillinen vai ei (käyttämällä MOD -toimintoa).

Lopuksi kaikki parilliset luvut lisätään ja summa palautetaan funktiolle.

Funktion luominen, jossa on rajaton määrä argumentteja

Kun luot joitakin toimintoja VBA: ssa, et ehkä tiedä tarkkaa argumenttien määrää, jotka käyttäjä haluaa antaa. Tarve on siis luoda funktio, joka voi hyväksyä niin monta argumenttia, että käyttää niitä ja palauttaa tulos.

Esimerkki tällaisesta laskentataulukkotoiminnosta on SUM -funktio. Voit esittää sille useita argumentteja (kuten tämän):

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

Yllä oleva funktio lisäisi arvot kaikkiin näihin argumentteihin. Huomaa myös, että nämä voivat olla yksittäinen solu tai soluryhmä.

Voit luoda tällaisen funktion VBA: ssa pitämällä viimeisen argumentin (tai se voi olla ainoa argumentti) valinnaisena. Tämän valinnaisen argumentin edessä tulee myös olla avainsana "ParamArray".

"ParamArray" on muunnin, jonka avulla voit hyväksyä niin monta argumenttia kuin haluat. Huomaa, että sanan ParamArray käyttäminen ennen argumenttia tekee argumentista valinnaisen. Sinun ei kuitenkaan tarvitse käyttää sanaa Valinnainen täällä.

Luodaan nyt funktio, joka hyväksyy mielivaltaisen määrän argumentteja ja lisää kaikki määrätyt argumentit:

Funktio AddArguments (ParamArray arglist () Varianttina) Kullekin argumentille Arglistissa AddArguments = AddArguments + arg Next arg End Function

Yllä oleva funktio voi ottaa minkä tahansa määrän argumentteja ja lisätä nämä argumentit antaakseen tuloksen.

Huomaa, että voit käyttää argumenttina vain yhtä arvoa, soluviittausta, booleania tai lauseketta. Et voi antaa taulukkoa argumenttina. Jos esimerkiksi yksi argumentteistasi on D8: D10, tämä kaava antaa sinulle virheen.

Jos haluat käyttää molempia monisoluisia argumentteja, käytä alla olevaa koodia:

Funktio AddArguments (ParamArray arglist () Varianttina) Jokaiselle arg Arglistille Jokaisen solun arg AddArguments = AddArguments + solu Seuraava solu Seuraava arg Lopetusfunktio

Huomaa, että tämä kaava toimii useiden solujen ja matriisiviittausten kanssa, mutta se ei voi käsitellä kovakoodattuja arvoja tai lausekkeita. Voit luoda tehokkaamman toiminnon tarkistamalla ja käsittelemällä nämä olosuhteet, mutta se ei ole tarkoitus tässä.

Tarkoituksena on näyttää sinulle, miten ParamArray toimii, jotta voit sallia määrittelemättömän määrän argumentteja funktiossa. Jos haluat paremman funktion kuin yllä olevan koodin luoma toiminto, käytä laskentataulukon SUM -funktiota.

Funktion luominen, joka palauttaa taulukon

Toistaiseksi olemme nähneet toimintoja, jotka palauttavat yhden arvon.

VBA: n avulla voit luoda funktion, joka palauttaa muunnelman, joka voi sisältää koko joukon arvoja.

Taulukkokaavat ovat saatavana myös sisäänrakennettuina funktioina Excel -laskentataulukoissa. Jos tunnet Excelin taulukkokaavat, tiedät, että ne syötetään näppäimillä Control + Vaihto + Enter (vain Enter -näppäimen sijaan). Voit lukea lisää taulukkokaavoista täältä. Jos et tiedä taulukkokaavoista, älä huoli, jatka lukemista.

Luodaan kaava, joka palauttaa kolmen numeron matriisin (1,2,3).

Alla oleva koodi tekisi tämän.

Funktio ThreeNumbers () Varianttina Dim NumberValue (1 to 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Function

Yllä olevassa koodissa olemme määrittäneet "ThreeNumbers" -funktion muunnelmana. Tämä mahdollistaa sen, että se voi pitää joukon arvoja.

Muuttuja "NumberValue" ilmoitetaan taulukkona, jossa on 3 elementtiä. Siinä on kolme arvoa ja se liitetään kolmeen numeroon.

Voit käyttää tätä toimintoa laskentataulukossa syöttämällä funktion ja painamalla Control + Shift + Enter -näppäintä (pitämällä Control- ja Shift -näppäimiä painettuna ja painamalla sitten Enter -näppäintä).

Kun teet tämän, se palauttaa solun 1, mutta todellisuudessa se sisältää kaikki kolme arvoa. Voit tarkistaa tämän käyttämällä seuraavaa kaavaa:

= MAX (kolme numeroa ())

Käytä yllä olevaa toimintoa Control + Shift + Enter. Huomaat, että tulos on nyt 3, koska se on suurimmat arvot Max -funktion palauttamassa taulukossa, joka saa kolme numeroa käyttäjän määrittämän funktion - ThreeNumbers - tuloksena.

Voit käyttää samaa tekniikkaa luodaksesi funktion, joka palauttaa kuukausien nimiryhmän alla olevan koodin mukaisesti:

Funktio Months () Varianttina Dim MonthName (1-12) MonthName (1) = "January" MonthName (2) = "February" MonthName (3) = "March" MonthName (4) = "April" MonthName (5) = "Toukokuu" 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

Nyt kun kirjoitat funktion = Kuukaudet () Excel -laskentataulukkoon ja käytät Control + Vaihto + Enter, se palauttaa koko kuukausien nimiryhmän. Huomaa, että näet solussa vain tammikuun, koska se on taulukon ensimmäinen arvo. Tämä ei tarkoita, että taulukko palauttaa vain yhden arvon.

Jos haluat näyttää, että se palauttaa kaikki arvot, tee tämä - valitse solu, jolla on kaava, siirry kaavapalkkiin, valitse koko kaava ja paina F9. Tämä näyttää kaikki funktion palauttamat arvot.

Voit käyttää tätä käyttämällä alla olevaa INDEX -kaavaa saadaksesi luettelon kaikista kuukausien nimistä kerralla.

= INDEX (kuukautta (), ROW ())

Jos sinulla on paljon arvoja, ei ole hyvä käytäntö antaa näitä arvoja yksitellen (kuten olemme tehneet edellä). Sen sijaan voit käyttää Array -toimintoa VBA: ssa.

Joten sama koodi, jossa luomme kuukausitoiminnon, lyhenee alla esitetyllä tavalla:

Toimintokuukaudet () Variant Months = Array ("tammikuu", "helmikuu", "maaliskuu", "huhtikuu", "toukokuu", "kesäkuu", "" heinäkuu "," elokuu "," syyskuu "," lokakuu " , "Marraskuu", "joulukuu") Lopetustoiminto

Yllä oleva toiminto käyttää Array -toimintoa arvojen määrittämiseen suoraan toiminnolle.

Huomaa, että kaikki yllä luodut funktiot palauttavat vaakasuuntaisen arvoryhmän. Tämä tarkoittaa, että jos valitset 12 vaakasuoraa solua (esimerkiksi A1: L1) ja kirjoitat soluun A1 kaavan = Kuukaudet (), se antaa sinulle kaikki kuukausien nimet.

Mutta entä jos haluat nämä arvot pystysuoraan solualueeseen.

Voit tehdä tämän käyttämällä laskentataulukon TRANSPOSE -kaavaa.

Valitse vain 12 pystysuoraa solua (vierekkäiset) ja kirjoita alla oleva kaava.

Käyttäjän määrittämän toiminnon laajuuden ymmärtäminen Excelissä

Toiminnolla voi olla kaksi ulottuvuutta - Julkinen tai Yksityinen.

• A Julkinen soveltamisala tarkoittaa, että toiminto on käytettävissä kaikille työkirjan arkkeille sekä kaikille toimenpiteille (ali ja toiminto) kaikissa työkirjan moduuleissa. Tämä on hyödyllistä, kun haluat kutsua funktion aliohjelmasta (näemme, miten tämä tehdään seuraavassa osassa).
• A Yksityinen soveltamisala tarkoittaa, että toiminto on käytettävissä vain siinä moduulissa, jossa se on olemassa. Et voi käyttää sitä muissa moduuleissa. Et myöskään näe sitä laskentataulukon toimintojen luettelossa. Jos funktion nimi on esimerkiksi kuukaudet () ja kirjoitat funktion Excelissä (= -merkin jälkeen), se ei näytä sinulle toiminnon nimeä. Voit kuitenkin käyttää sitä, jos kirjoitat kaavan nimen.

Jos et määritä mitään, toiminto on oletusarvoisesti julkinen.

Alla on yksityinen toiminto:

Private Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Voit käyttää tätä toimintoa aliohjelmissa ja samojen moduulien menettelyissä, mutta et voi käyttää sitä muissa moduuleissa. Tämä toiminto ei myöskään näy laskentataulukossa.

Alla oleva koodi tekisi tämän toiminnon julkiseksi. Se näkyy myös laskentataulukossa.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Eri tapoja käyttää käyttäjän määrittämää toimintoa Excelissä

Kun olet luonut käyttäjän määrittämän toiminnon VBA: ssa, voit käyttää sitä monella eri tavalla.

Katsotaan ensin, miten laskentataulukon toimintoja käytetään.

UDF -tiedostojen käyttäminen laskentataulukoissa

Olemme jo nähneet esimerkkejä laskentataulukon VBA: ssa luodun funktion käyttämisestä.

Sinun tarvitsee vain kirjoittaa toimintojen nimi, ja se näkyy intellisenseessa.

Huomaa, että jotta funktio voidaan näyttää laskentataulukossa, sen on oltava julkinen toiminto (kuten yllä olevassa osassa selitetään).

Voit myös lisätä käyttäjän määrittämän toiminnon Lisää toiminto -valintaikkunan avulla (alla olevien ohjeiden mukaisesti). Tämä toimii vain julkisille toiminnoille.

• Siirry Data -välilehdelle.
• Napsauta "Lisää toiminto" -vaihtoehtoa.
• Valitse Lisää toiminto -valintaikkunassa luokkaan Käyttäjän määrittämä. Tämä vaihtoehto näkyy vain, jos sinulla on toiminto VB Editorissa (ja toiminto on julkinen).
• Valitse toiminto kaikkien julkisten käyttäjien määrittämien toimintojen luettelosta.
• Napsauta Ok -painiketta.

Yllä olevat vaiheet lisäisivät toiminnon laskentataulukkoon. Se näyttää myös Funktion argumentit -valintaikkunan, jossa on yksityiskohtaisia ​​tietoja argumentteista ja tuloksesta.

Voit käyttää käyttäjän määrittämää toimintoa aivan kuten mitä tahansa muuta Excelin toimintoa. Tämä tarkoittaa myös sitä, että voit käyttää sitä muiden sisäänrakennettujen Excel -toimintojen kanssa. Esimerkiksi. alla oleva kaava antaisi työkirjan nimen isoilla kirjaimilla:

= YLÄPÄINEN (Työkirjanimi ())

Käyttäjän määrittämien toimintojen käyttäminen VBA -menettelyissä ja -toiminnoissa

Kun olet luonut toiminnon, voit käyttää sitä myös muissa alitoiminnoissa.

Jos toiminto on julkinen, sitä voidaan käyttää missä tahansa saman tai eri moduulin toiminnassa. Jos se on yksityinen, sitä voidaan käyttää vain samassa moduulissa.

Alla on funktio, joka palauttaa työkirjan nimen.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Alla oleva menettely kutsuu toiminnon ja näyttää sitten nimen viestiruudussa.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Voit myös kutsua funktion toisesta toiminnosta.

Alla olevissa koodeissa ensimmäinen koodi palauttaa työkirjan nimen ja toinen palauttaa nimen isoilla kirjaimilla kutsumalla ensimmäisen funktion.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name End Function

Toiminto TyökirjaNameinUpper () TyökirjaNameinUpper = UCase (Työkirjanimi) Pääte

Käyttäjän määrittämän toiminnon kutsuminen muista työkirjoista

Jos sinulla on toiminto työkirjassa, voit kutsua tätä toimintoa myös muissa työkirjoissa.

Tähän on useita tapoja:

1. Lisäosan luominen
2. Tallennustoiminto henkilökohtaisen makron työkirjassa
3. Toiminnon viittaaminen toisesta työkirjasta.

Lisäosan luominen

Kun luot ja asennat apuohjelman, mukautettu toiminto on käytettävissä kaikissa työkirjoissa.

Oletetaan, että olet luonut mukautetun toiminnon - GetNumeric ja haluat sen kaikkiin työkirjoihin. Voit tehdä tämän luomalla uuden työkirjan ja pitämällä toimintokoodin uuden työkirjan moduulissa.

Tallenna se apuohjelmana ja asenna se sitten Exceliin noudattamalla alla olevia ohjeita.

• Siirry Tiedosto -välilehteen ja napsauta Tallenna nimellä.
• Muuta Tallenna nimellä -valintaikkunassa Tallenna nimellä -tyypiksi .xlam. Tiedostolle antamasi nimi on apuohjelmasi nimi. Tässä esimerkissä tiedosto tallennetaan nimellä GetNumeric.
  • Huomaat, että tiedoston polku, johon se tallennetaan, muuttuu automaattisesti. Voit käyttää oletusarvoa tai muuttaa sitä, jos haluat.
• Avaa uusi Excel -työkirja ja siirry kehittäjä -välilehdelle.
• Napsauta Excel-apuohjelmat -vaihtoehtoa.
• Selaa ja etsi tallennettu tiedosto Lisäosat-valintaikkunassa ja napsauta OK.

Nyt apuohjelma on aktivoitu.

Nyt voit käyttää mukautettua toimintoa kaikissa työkirjoissa.

Toiminnon tallentaminen henkilökohtaiseen makro -työkirjaan

Personal Macro Workbook on piilotettu työkirja järjestelmässäsi, joka avautuu aina, kun avaat Excel -sovelluksen.

Se on paikka, jossa voit tallentaa makrokoodeja ja käyttää näitä makroja mistä tahansa työkirjasta. Se on loistava paikka tallentaa ne makrot, joita haluat käyttää usein.

Oletuksena Excelissä ei ole henkilökohtaista makrotyökirjaa. Sinun on luotava se tallentamalla makro ja tallentamalla se henkilökohtaiseen makro -työkirjaan.

Löydät yksityiskohtaiset vaiheet makron luomiseen ja tallentamiseen henkilökohtaisesta makro -työkirjasta täältä.

Toiminnon viittaaminen toisesta työkirjasta

Kaksi ensimmäistä menetelmää (apuohjelman luominen ja henkilökohtaisen makrotyökirjan käyttö) toimivat kaikissa tilanteissa, mutta jos haluat viitata funktioon toisesta työkirjasta, kyseisen työkirjan on oltava auki.

Oletetaan, että sinulla on työkirja, jonka nimi on "Työkirja kaavalla ”, ja sillä on toiminto nimeltä "GetNumeric ”.

Tämän toiminnon käyttäminen toisessa työkirjassa (kun Työkirja kaavalla on auki), voit käyttää alla olevaa kaavaa:

= 'Työkirja kaavalla'! GetNumeric (A1)

Yllä oleva kaava käyttää käyttäjän määrittämää funktiota Työkirja kaavalla tiedosto ja anna tulos.

Huomaa, että koska työkirjan nimessä on välilyöntejä, sinun on liitettävä se lainausmerkkeihin.

Exit Function Statement VBA: n käyttäminen

Jos haluat poistua toiminnosta koodin ollessa käynnissä, voit tehdä sen käyttämällä Exit Function -lauseketta.

Alla oleva koodi poimisi kolme ensimmäistä numeerista merkkiä aakkosnumeerisesta tekstimerkkijonosta. Heti kun se saa kolme merkkiä, toiminto päättyy ja palauttaa tuloksen.

Funktio GetNumericFirstThree (CellRef As Range) Niin pitkä himmeä merkkijono Pituus kuin kokonaisluku StringLength = Len (CellRef) I = 1 StringLength Jos J = 3 Sitten Lopeta toiminto jos IsNumeric (Mid (CellRef, i, 1)) Sitten J = J + 1 Tulos = Tulos ja keskitaso (CellRef, i, 1) GetNumericFirstThree = Tulos päättyy, jos seuraava i Lopeta

Yllä oleva toiminto tarkistaa numeeristen merkkien määrän, ja kun se saa 3 numeerista merkkiä, se poistuu toiminnosta seuraavassa silmukassa.

Käyttäjän määrittämän toiminnon virheenkorjaus

On olemassa muutamia tekniikoita, joita voit käyttää käyttäjän määrittämän toiminnon virheenkorjauksessa VBA: ssa:

Mukautetun toiminnon virheenkorjaus viestiruudun avulla

Käytä MsgBox -toimintoa näyttääksesi tietyn arvon viestiruudun.

Näytettävä arvo voi perustua siihen, mitä haluat testata. Jos esimerkiksi haluat tarkistaa, suoritetaanko koodi vai ei, mikä tahansa viesti toimisi, ja jos haluat tarkistaa, toimivatko silmukat vai eivät, voit näyttää tietyn arvon tai silmukkalaskurin.

Mukautetun toiminnon virheenkorjaus asettamalla katkaisupiste

Aseta katkaisukohta, jotta voit kulkea jokaisen rivin läpi yksi kerrallaan. Jos haluat asettaa katkaisupisteen, valitse haluamasi viiva ja paina F9 -näppäintä tai napsauta harmaata pystysuoraa aluetta, joka jää koodirivien viereen. Mikä tahansa näistä menetelmistä lisäisi katkaisupisteen (näet punaisen pisteen harmaalla alueella).

Kun olet asettanut katkaisupisteen ja suoritat toiminnon, se kulkee katkaisupisteviivaan asti ja pysähtyy sitten. Nyt voit selata koodia F8 -näppäimellä. Painamalla F8 kerran siirryt koodin seuraavalle riville.

Mukautetun toiminnon virheenkorjaus käyttämällä virheenkorjausta Tulosta koodi

Voit käyttää Debug.Print -lauseketta koodissasi saadaksesi määritettyjen muuttujien/argumenttien arvot välittömässä ikkunassa.

Esimerkiksi alla olevassa koodissa olen käyttänyt Debug.Print -toimintoa saadaksesi kahden muuttujan arvon - "j" ja "Result"

Funktio GetNumericFirstThree (CellRef As Range) Niin pitkä himmeä merkkijono Pituus kuin kokonaisluku StringLength = Len (CellRef) I = 1 StringLength Jos J = 3 Sitten Lopeta toiminto jos IsNumeric (Mid (CellRef, i, 1)) Sitten J = J + 1 Tulos = Tulos ja keskitaso (CellRef, i, 1) Virheenkorjaus. Tulosta J, Tulos GetNumericFirstThree = Tulos päättyy, jos seuraava i Lopeta

Kun tämä koodi suoritetaan, se näyttää seuraavan ikkunan.

Excelin sisäänrakennetut toiminnot vs. VBA -käyttäjän määrittämä toiminto

Excelin sisäänrakennettujen toimintojen käytöstä VBA: ssa luotuihin mukautettuihin toimintoihin verrattuna on vain muutamia vahvoja etuja.

• Sisäänrakennetut toiminnot ovat paljon nopeampia kuin VBA -toiminnot.
• Kun luot raportin/koontinäytön VBA -toimintojen avulla ja lähetät sen asiakkaalle/kollegalle, heidän ei tarvitse huolehtia siitä, ovatko makrot käytössä vai eivät. Joissakin tapauksissa asiakkaat/asiakkaat pelkäävät, kun he näkevät varoituksen keltaisessa palkissa (joka pyytää heitä ottamaan makroja käyttöön).
• Sisäänrakennettujen Excel -toimintojen ansiosta sinun ei tarvitse huolehtia tiedostojen laajennuksista. Jos työkirjassa on makroja tai käyttäjän määrittämiä toimintoja, se on tallennettava .xlsm-tiedostoon.

Vaikka Excelin sisäänrakennettujen toimintojen käyttämiseen on monia painavia syitä, joissakin tapauksissa sinun on parempi käyttää käyttäjän määrittämää toimintoa.

• On parempi käyttää käyttäjän määrittämää toimintoa, jos sisäänrakennettu kaava on valtava ja monimutkainen. Tämä muuttuu entistä tärkeämmäksi, kun tarvitset jonkun muun päivittämään kaavat. Jos sinulla on esimerkiksi valtava kaava, joka koostuu monista eri toiminnoista, jopa viittauksen muuttaminen soluun voi olla työlästä ja virhealtista. Sen sijaan voit luoda mukautetun toiminnon, joka kestää vain yhden tai kaksi argumenttia ja joka nostaa taustan raskaasti.
• Kun sinun on tehtävä jotain, mitä Excelin sisäänrakennetut toiminnot eivät voi tehdä. Esimerkki tästä voi olla, kun haluat poimia kaikki numeeriset merkit merkkijonosta. Tällaisissa tapauksissa käyttäjän määrittämän funktion gar käyttö on suurempi kuin sen negatiiviset puolet.

Minne sijoittaa VBA-koodi käyttäjän määrittämälle toiminnolle

Kun luot mukautettua toimintoa, sinun on asetettava koodi sen työkirjan koodi -ikkunaan, johon haluat toiminnon.

Alla on vaiheet, joilla voit lisätä GetNumeric -toiminnon koodin työkirjaan.

1. Siirry Kehittäjä -välilehdelle.
2. Napsauta Visual Basic -vaihtoehtoa. Tämä avaa VB -editorin taustaohjelmaan.
3. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia VB Editorin Project Explorer -ruudussa, johon haluat lisätä koodin. Jos et näe Project Exploreria, siirry Näytä -välilehteen ja napsauta Project Explorer.
4. Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää moduuliobjektin työkirjaasi.
5. Kopioi ja liitä koodi moduuli -ikkunaan.

Saatat pitää myös seuraavista Excel VBA -opetusohjelmista:

• Solujen ja alueiden käyttö Excel VBA: ssa.
• Työskentely laskentataulukoiden kanssa Excel VBA: ssa.
• Työkirjojen käyttäminen VBA: n avulla.
• Silmukoiden käyttäminen Excel VBA: ssa.
• Excel VBA -tapahtumat - helppo (ja täydellinen) opas
• IF IF: n muiden lausuntojen käyttäminen VBA: ssa.
• Makron tallentaminen Exceliin.
• Makron suorittaminen Excelissä.
• Tietojen lajitteleminen Excelissä VBA: n avulla (vaiheittainen opas).
• Excel VBA InStr -toiminto - selitetty esimerkeillä.