Sukurkite VBA funkciją grąžinti masyvą

Kaip rodo pavadinimas, išmoksime, kaip „Excel“ sukurti vartotojo apibrėžtą funkciją, kuri grąžina masyvą. Mes jau išmokome sukurti vartotojo apibrėžtą funkciją VBA. Taigi negaišdami laiko pradėkime nuo pamokos.

Kas yra masyvo funkcija?

Masyvo funkcijos yra funkcijos, kurios grąžina masyvą, kai jis naudojamas. Šios funkcijos naudojamos su CTRL+SHIFT+ENTER klavišų kombinacijomis, todėl mes renkamės masyvo funkciją arba formules kaip TPP funkciją ir formules.

„Excel“ masyvo funkcija dažnai yra daugialąsčių masyvų formulės. Vienas iš pavyzdžių yra funkcija TRANSPOSE.

UDF masyvo funkcijos kūrimas VBA

Taigi, scenarijus yra toks, kad aš tiesiog noriu grąžinti pirmuosius 3 lyginius skaičius naudodami funkciją ThreeEven ().

Kodas atrodys taip.

Funkcija ThreeEven () kaip sveikasis skaičius () 'apibrėžia masyvą Dim skaičiai (2) kaip sveikasis skaičius' Priskirkite reikšmes masyvo skaičiams (0) = 0 skaičių (1) = 2 skaičių (2) = 4 'grąžinkite reikšmes ThreeEven = skaičiai Pabaigos funkcija 

Naudokime šią funkciją darbalapyje.

Tai matote, pirmiausia pasirenkame tris langelius (horizontaliai, vertikaliai turime naudoti dvimatį masyvą. Mes jį aptariame žemiau). Tada mes pradedame rašyti savo formulę. Tada paspauskite CTRL+SHIFT+ENTER. Tai užpildo pasirinktas ląsteles masyvo reikšmėmis.

Pastaba:

• Praktiškai jūs nežinote, kiek ląstelių jums reikės. Tokiu atveju visada pasirinkite daugiau langelių nei tikėtasi masyvo ilgio. Funkcija užpildys langelius masyvu, o papildomos ląstelės parodys klaidą #N/A.
• Pagal numatytuosius nustatymus ši masyvo funkcija pateikia vertes horizontaliame masyve. Jei bandysite pasirinkti vertikalias ląsteles, visos ląstelės parodys tik pirmąją masyvo reikšmę.

Kaip tai veikia?

Norėdami sukurti masyvo funkciją, turite laikytis šios sintaksės.

Funkcija functionName (kintamieji) Kaip returnType () silpnas rezultatasArray (ilgis) kaip dataType 'Priskirkite reikšmes masyvui čia 

Funkcijos deklaracija turi būti tokia, kaip nurodyta aukščiau. Tai pareiškė, kad tai yra masyvo funkcija.
Naudodami jį darbalapyje, turite naudoti klavišų kombinaciją CTRL+SHIFT+ENTER. Priešingu atveju grąžinama tik pirmoji masyvo vertė.

VBA masyvo funkcija, skirta vertikaliai masyvui grąžinti

Kad jūsų UDF masyvo funkcija veiktų vertikaliai, jums nereikia daug nuveikti. Tiesiog paskelbkite masyvus kaip dvimatį masyvą. Tada pirmame matmenyje pridėkite reikšmes ir palikite kitą dimensiją tuščią. Štai kaip tai darote:

Funkcija ThreeEven () Kaip sveikasis skaičius () 'apibrėžia masyvą Dim skaičiai (2,0) Kaip sveikasis skaičius' Priskirkite reikšmes masyvo skaičiams (0,0) = 0 skaičių (1,0) = 2 skaičių (2,0) = 4 ' grąžinti reikšmes ThreeEven = skaičiai Pabaigos funkcija 

Taip jį naudojate darbalapyje.

UDF masyvo funkcija su argumentais „Excel“

Anksčiau pateiktuose pavyzdžiuose paprasčiausiai ant lapo atspausdinome statines sumines vertes. Tarkime, norime, kad mūsų funkcija priimtų diapazono argumentą, atliktų tam tikras operacijas ir grąžintų gautą masyvą.

Pavyzdys Prie kiekvienos diapazono vertės pridėkite „-done“

Dabar aš žinau, kad tai galima padaryti lengvai, bet tik norėdamas parodyti, kaip galite naudoti vartotojo apibrėžtas VBA masyvo funkcijas problemoms spręsti.

Taigi, čia aš noriu masyvo funkcijos, kuri kaip diapazoną laiko argumentu ir prideda „-done“ prie kiekvienos diapazono vertės. Tai galima padaryti lengvai naudojant sujungimo funkciją, tačiau čia naudosime masyvo funkciją.

Funkcija CONCATDone (rng kaip diapazonas) Kaip variantas () Dim resulArr () Kaip variantas „Sukurti kolekciją Dim col As As New Collection“ Vertių pridėjimas prie klaidos Tęsti toliau kiekvienam v In rng col. Add v Next On Error GoTo 0 “ kiekvienos vertės operacijos užbaigimas ir įtraukimas į „Array ReDim“ CONCATDone = resulArr pabaigos funkcija 

Paaiškinimas:

Aukščiau pateikta funkcija priims diapazoną kaip argumentą ir prie kiekvienos diapazono vertės pridės „-done“.

Matote, kad čia naudojome VBA kolekciją masyvo vertėms laikyti, tada atlikome savo operaciją su kiekviena verte ir pridėjome jas prie dvimatės masyvo.

Taigi taip, vaikinai, taip galite sukurti pasirinktinę VBA masyvo funkciją, kuri gali grąžinti masyvą. Tikiuosi, kad tai buvo pakankamai aiškinama. Jei turite klausimų apie šį straipsnį, pateikite jį žemiau esančiame komentarų skyriuje.

Norėdami atsisiųsti darbo failą, spustelėkite toliau pateiktą nuorodą:

Sukurkite VBA funkciją grąžinti masyvą

Masyvai „Excel Formul“ | Sužinokite, kokie masyvai yra „Excel“.

Kaip sukurti vartotojo apibrėžtą funkciją per VBA | Sužinokite, kaip „Excel“ sukurti vartotojo apibrėžtas funkcijas

Naudotojo apibrėžtos funkcijos (UDF) naudojimas iš kitos darbaknygės naudojant VBA programoje „Microsoft Excel“ | Naudokite vartotojo apibrėžtą funkciją kitoje „Excel“ darbaknygėje

Pateikite klaidų reikšmes iš vartotojo apibrėžtų funkcijų naudodami VBA programoje „Microsoft Excel“ | Sužinokite, kaip galite grąžinti klaidų vertes iš vartotojo nustatytos funkcijos

Populiarūs straipsniai:

50 „Excel“ nuorodų, skirtų produktyvumui padidinti

„VLOOKUP“ funkcija „Excel“

COUNTIF „Excel 2016“

Kaip naudoti „SUMIF“ funkciją „Excel“