Luo VBA -toiminto palautusryhmään

Kuten otsikko viittaa, opimme luomaan Excelissä käyttäjän määrittämän funktion, joka palauttaa taulukon. Olemme jo oppineet luomaan käyttäjän määrittämän toiminnon VBA: ssa. Joten tuhlaamatta aikaa, aloitetaan opetusohjelman kanssa.

Mikä on Array -toiminto?

Matriisitoiminnot ovat toimintoja, jotka palauttavat taulukon, kun niitä käytetään. Näitä toimintoja käytetään CTRL+VAIHTO+ENTER -näppäinyhdistelmissä, ja siksi mieluummin kutsumme matriisitoiminnon tai kaavat CSE -funktioksi ja kaavoiksi.

Excel -matriisitoiminto on usein monisoluinen matriisikaava. Yksi esimerkki on TRANSPOSE -toiminto.

UDF -matriisitoiminnon luominen VBA: ssa

Joten skenaario on, että haluan vain palauttaa kolme ensimmäistä parillista numeroa funktion ThreeEven () avulla.

Koodi näyttää tältä.

Funktio ThreeEven () kokonaislukuna () 'määritä taulukko Dim -numerot (2) kokonaislukuna' Määritä arvot taulukon numeroille (0) = 0 numeroa (1) = 2 numeroa (2) = 4 'palauta arvot ThreeEven = numerot Lopeta toiminto 

Käytämme tätä toimintoa laskentataulukossa.

Voit nähdä, että valitsemme ensin kolme solua (vaakasuoraan, pystysuoraan meidän on käytettävä kaksiulotteista matriisia. Meillä on se alla.). Sitten aletaan kirjoittaa kaavaa. Sitten painetaan CTRL+VAIHTO+ENTER. Tämä täyttää valitut solut taulukon arvoilla.

Huomautus:

• Käytännössä et tiedä kuinka monta solua tarvitset. Valitse tällöin aina enemmän soluja kuin odotettu matriisin pituus. Toiminto täyttää solut taulukolla ja ylimääräiset solut näyttävät virheen #N/A.
• Tämä matriisitoiminto palauttaa oletuksena vaakasuuntaisen taulukon arvot. Jos yrität valita pystysuoria soluja, kaikissa soluissa näkyy vain taulukon ensimmäinen arvo.

Kuinka se toimii?

Jos haluat luoda taulukkofunktion, sinun on noudatettava tätä syntaksia.

FunktiofunktioNimi (muuttujat) ReturnType () himmeä tulosArray (pituus) datanaTyyppi 'Määritä arvot taulukolle tässä functionName = resultArray End Function 

Toimintoilmoituksen on oltava edellä määritellyn mukainen. Tämä ilmoitti olevansa matriisifunktio.
Kun käytät sitä laskentataulukossa, sinun on käytettävä näppäinyhdistelmää CTRL+VAIHTO+ENTER. Muussa tapauksessa se palauttaa vain taulukon ensimmäisen arvon.

VBA Array -toiminto palauttaa pystysuuntaisen taulukon

Jotta UDF -matriisitoiminto toimisi pystysuunnassa, sinun ei tarvitse tehdä paljon. Ilmoita vain taulukot kaksiulotteiseksi matriisiksi. Lisää sitten ensimmäiseen ulottuvuuteen arvot ja jätä toinen ulottuvuus tyhjäksi. Näin teet sen:

Funktio ThreeEven () kokonaislukuna () 'määritä taulukko Dim -numerot (2,0) kokonaislukuna' Määritä arvot taulukon numeroille (0,0) = 0 numeroa (1,0) = 2 numeroa (2,0) = 4 ' palauttaa arvot ThreeEven = numerot End Function 

Näin käytät sitä laskentataulukossa.

UDF -matriisitoiminto argumenttien kanssa Excelissä

Yllä olevissa esimerkeissä tulostimme yksinkertaisesti summan staattiset arvot arkkiin. Oletetaan, että haluamme, että funktiomme hyväksyy alue -argumentin, suorittaa joitakin toimintoja ja palauttaa tuloksena olevan taulukon.

Esimerkki Lisää "-done" jokaiseen alueen arvoon

Nyt tiedän, että tämä voidaan tehdä helposti, mutta vain näyttääkseni, kuinka voit käyttää käyttäjän määrittämiä VBA-matriisitoimintoja ongelmien ratkaisemiseen.

Joten tässä haluan array-funktion, joka ottaa alueen argumentiksi ja lisää "-done" jokaiseen alueen arvoon. Tämä voidaan tehdä helposti käyttämällä ketjutustoimintoa, mutta käytämme tässä taulukkotoimintoa.

Toiminto CONCATDone (rng As Range) Vaihtoehtona () Dim resulArr () Varianttina 'Luo kokoelma Dim col As New Collection' Arvojen lisääminen kokoelmaan Virhe Jatka seuraavaksi jokaiselle v In rng col.Ad v v Next On Error GoTo 0 ' jokaisen arvon suorittaminen loppuun ja lisääminen taulukkoon ReDim resulArr (col.Count - 1, 0) For i = 0 to col.Count - 1 resulArr (i, 0) = col (i + 1) & "-done" Next CONCATDone = resulArr Lopetustoiminto 

Selitys:

Yllä oleva toiminto hyväksyy alueen argumenttina ja lisää "-done" jokaiseen alueen arvoon.

Näet, että olemme käyttäneet täällä VBA-kokoelmaa taulukon arvojen säilyttämiseen ja sitten olemme tehneet operaatiomme jokaiselle arvolle ja lisänneet ne takaisin kaksiulotteiseen taulukkoon.

Joten kyllä ​​kaverit, tällä tavalla voit luoda mukautetun VBA -matriisitoiminnon, joka voi palauttaa taulukon. Toivottavasti se oli tarpeeksi selittävä. Jos sinulla on kysyttävää tästä artikkelista, kirjoita se alla olevaan kommenttiosaan.

Lataa työtiedosto napsauttamalla alla olevaa linkkiä:

Luo VBA -funktio palautusryhmään

Taulukot Excel Formulissa | Opi mitä taulukot ovat Excelissä.

Käyttäjän määrittämän toiminnon luominen VBA: n kautta | Opi luomaan käyttäjän määrittämiä toimintoja Excelissä

Käyttäjän määrittämän toiminnon (UDF) käyttäminen toisesta työkirjasta käyttämällä VBA: ta Microsoft Excelissä | Käytä käyttäjän määrittämää toimintoa toisessa Excelin työkirjassa

Palauta virhearvot käyttäjän määrittämistä funktioista VBA: n avulla Microsoft Excelissä | Opi kuinka voit palauttaa virhearvot käyttäjän määrittämästä funktiosta

Suosittuja artikkeleita:

50 Excel -pikanäppäintä tuottavuuden lisäämiseksi

VLOOKUP -toiminto Excelissä

COUNTIF Excel 2016: ssa

SUMIF -toiminnon käyttäminen Excelissä