Izradite VBA funkciju za vraćanje niza

Kao što naslov govori, naučit ćemo kako stvoriti korisnički definiranu funkciju u Excelu koja vraća niz. Već smo naučili kako stvoriti korisnički definiranu funkciju u VBA-i. Dakle, bez gubljenja vremena, počnimo s vodičem.

Što je funkcija niza?

Funkcije niza su funkcije koje vraćaju niz kada se koriste. Ove se funkcije koriste s kombinacijama tipki CTRL+SHIFT+ENTER i zato više volimo pozivanje funkcije niza ni formule kao CSE funkcije i formule.

Excelova funkcija niza često je formula s više stanica. Jedan primjer je funkcija TRANSPOSE.

Stvaranje funkcije polja UDF u VBA

Dakle, scenarij je da samo želim vratiti prva 3 parna broja pomoću funkcije ThreeEven ().

Kod će izgledati ovako.

Funkcija ThreeEven () Kao Integer () 'definirajte niz Dim brojeve (2) As Integer' Dodijelite vrijednosti brojevima niza (0) = 0 brojeva (1) = 2 broja (2) = 4 'povratne vrijednosti ThreeEven = brojevi Kraj Funkcija 

Upotrijebimo ovu funkciju na radnom listu.

To možete vidjeti, prvo odabiremo tri ćelije (vodoravno, za okomito moramo koristiti dvodimenzionalni niz. U nastavku ga pokrivamo.) Zatim počinjemo pisati formulu. Zatim pritisnemo CTRL+SHIFT+ENTER. Ovo ispunjava odabrane ćelije vrijednostima niza.

Bilješka:

• U praksi nećete znati koliko će vam stanica trebati. U tom slučaju uvijek odaberite više ćelija od očekivane duljine polja. Funkcija će ispuniti ćelije nizom, a dodatne će stanice pokazati pogrešku #N/A.
• Prema zadanim postavkama, ova funkcija niza vraća vrijednosti u vodoravnom nizu. Ako pokušate odabrati okomite ćelije, sve će stanice prikazati samo prvu vrijednost niza.

Kako radi?

Da biste stvorili funkciju niza, morate slijediti ovu sintaksu.

Naziv funkcije functionName (varijable) Kao returnType () priguši resultArray (dužina) kao dataType 'Ovdje dodijelite vrijednosti nizu functionName = resultArray Kraj Funkcija 

Deklaracija funkcije mora biti kako je gore definirano. Time je deklarirano da je to funkcija niza.
Dok ga koristite na radnom listu, morate koristiti kombinaciju tipki CTRL+SHIFT+ENTER. U suprotnom će vratiti samo prvu vrijednost niza.

Funkcija VBA niza za vraćanje okomitog niza

Da bi vaša funkcija UDF niza radila okomito, ne morate učiniti mnogo. Samo deklarirajte nizove kao dvodimenzionalni niz. Zatim u prvu dimenziju dodajte vrijednosti, a drugu dimenziju ostavite praznom. Ovako to radite:

Funkcija ThreeEven () Kao cijeli broj () 'definirajte niz Dim brojeve (2,0) Kao cijeli broj' Dodijelite vrijednosti brojevima niza (0,0) = 0 brojeva (1,0) = 2 broja (2,0) = 4 ' povratne vrijednosti ThreeEven = brojevi Kraj funkcija 

Ovako ga koristite na radnom listu.

UDF funkcija niza s argumentima u Excelu

U gornjim primjerima jednostavno smo ispisali statičke vrijednosti zbroja na listu. Recimo da želimo da naša funkcija prihvati argument raspona, izvede neke operacije nad njima i vrati rezultirajući niz.

Primjer Dodajte "-done" svakoj vrijednosti u rasponu

Znam da se to može učiniti jednostavno, ali samo da vam pokažem kako možete koristiti korisnički definirane funkcije polja VBA za rješavanje problema.

Dakle, ovdje želim funkciju niza koja uzima raspon kao argument i dodaje "-done" svakoj vrijednosti u rasponu. To se može lako učiniti pomoću funkcije povezivanja, ali ovdje ćemo koristiti funkciju niza.

Funkcija CONCATDone (rng kao raspon) kao varijanta () priguši rezultatArr () kao varijanta 'Stvori zbirku Zatamni kolonu kao novu zbirku' Dodavanje vrijednosti u zbirku Pogreška Nastavi Sljedeće Za svako v U rng kolo.Dodaj v Sljedeće Pogreška Idi na 0 ' dovršavanje operacije za svaku vrijednost i njihovo dodavanje u niz ReDim resultArr (col.Count - 1, 0) Za i = 0 Za col.Count - 1 resultArr (i, 0) = col (i + 1) & "-done" Dalje CONCATDone = Funkcija završetka resultArr 

Obrazloženje:

Gornja funkcija prihvatit će raspon kao argument te će svakoj vrijednosti u rasponu dodati "-done".

Možete vidjeti da smo ovdje koristili VBA zbirku za držanje vrijednosti niza, a zatim smo obavili operaciju na svakoj vrijednosti i dodali ih natrag na dvodimenzionalni niz.

Pa da, dečki, ovako možete stvoriti prilagođenu funkciju VBA polja koja može vratiti niz. Nadam se da je bilo dovoljno objašnjenje. Ako imate bilo kakvih pitanja u vezi s ovim člankom, postavite ih u odjeljak komentara ispod.

Kliknite donju vezu za preuzimanje radne datoteke:

Izradite VBA funkciju za vraćanje niza

Nizovi u Excelu Formul | Saznajte koji su nizovi u Excelu.

Kako stvoriti korisnički definiranu funkciju putem VBA | Saznajte kako stvoriti korisnički definirane funkcije u Excelu

Korištenje korisnički definirane funkcije (UDF) iz druge radne knjige pomoću VBA u programu Microsoft Excel | Upotrijebite korisnički definiranu funkciju u drugoj radnoj knjizi programa Excel

Vraćanje vrijednosti pogrešaka iz korisnički definiranih funkcija pomoću VBA u Microsoft Excelu | Saznajte kako možete vratiti vrijednosti pogreške iz korisnički definirane funkcije

Popularni članci:

50 Excel prečaca za povećanje vaše produktivnosti

Funkcija VLOOKUP u Excelu

COUNTIF u Excelu 2016

Kako koristiti funkciju SUMIF u Excelu