Funkcia UDF Array s argumentmi v programe Excel
Ako naznačuje názov, naučíme sa vytvárať v Exceli používateľom definovanú funkciu, ktorá vracia pole. Už sme sa naučili, ako vytvoriť funkciu definovanú používateľom vo VBA. Bez toho, aby sme strácali čas, začnime s návodom.
Čo je to funkcia poľa?
Funkcie poľa sú funkcie, ktoré pri použití vracajú pole. Tieto funkcie sa používajú s kombináciami klávesov CTRL+SHIFT+ENTER, a preto ako funkciu a vzorce CSE uprednostňujeme volanie funkcie poľa alebo vzorcov.
Funkcia poľa Excel je často vzorcom viacbunkových polí. Jedným z príkladov je funkcia TRANSPOSE.
Vytvorenie funkcie poľa UDF vo VBA
Scenár je teda taký, že chcem vrátiť prvé 3 párne čísla pomocou funkcie ThreeEven ().
Kód bude vyzerať takto.
Funkcia ThreeEven () As Integer () 'definuje pole Dim čísla (2) Ako celé číslo' Priradí hodnoty číslam polí (0) = 0 čísel (1) = 2 čísla (2) = 4 'návratové hodnoty ThreeEven = čísla Koniec Funkcia
Použime túto funkciu na pracovnom liste.
Vidíte, že najskôr vyberieme tri bunky (horizontálne, pre vertikálne musíme použiť dvojrozmerné pole. Máme to pokryté nižšie.). Potom začneme písať náš vzorec. Potom stlačíme CTRL+SHIFT+ENTER. Toto vyplní vybraté bunky hodnotami poľa.
Poznámka:
- V praxi nebudete vedieť, koľko buniek budete potrebovať. V takom prípade vždy vyberte viac buniek, ako je očakávaná dĺžka poľa. Funkcia zaplní bunky poľom a v ďalších bunkách sa zobrazí chyba #N/A.
- Táto funkcia poľa štandardne vracia hodnoty v horizontálnom poli. Ak sa pokúsite vybrať zvislé bunky, všetky bunky budú zobrazovať iba prvú hodnotu poľa.
Ako to funguje?
Ak chcete vytvoriť funkciu poľa, musíte postupovať podľa tejto syntaxe.
Funkcia functionName (premenné) Ako returnType () dim resultArray (dĺžka) ako dataType 'Priradenie hodnôt tu matici functionName = resultArray End Function
Deklarácia funkcie musí byť taká, ako je definované vyššie. Toto vyhlásilo, že ide o funkciu poľa.
Pri použití na pracovnom hárku musíte použiť kombináciu klávesov CTRL+SHIFT+ENTER. V opačnom prípade vráti iba prvú hodnotu poľa.
Funkcia poľa VBA na vrátenie vertikálneho poľa
Aby vaša funkcia poľa UDF fungovala vertikálne, nemusíte veľa robiť. Stačí deklarovať polia ako dvojrozmerné pole. Potom v prvej dimenzii pridajte hodnoty a druhú dimenziu nechajte prázdnu. Takto to robíte:
Funkcia ThreeEven () Ako celé číslo () 'definovať matné čísla polí (2,0) ako celé číslo' Priradiť hodnoty k číslam poľa (0,0) = 0 čísel (1,0) = 2 čísla (2,0) = 4 ' návratové hodnoty ThreeEven = čísla Ukončiť funkciu
Takto to použijete v pracovnom liste.
Funkcia UDF Array s argumentmi v programe Excel
Vo vyššie uvedených príkladoch sme jednoducho vytlačili súčet statických hodnôt na list. Povedzme, že chceme, aby naša funkcia akceptovala argument rozsahu, vykonala na ňom niektoré operácie a vrátila výsledné pole.
Príklad Pridajte „-done“ ku každej hodnote v rozsahu
Teraz viem, že sa to dá ľahko vykonať, ale len aby som vám ukázal, ako môžete na riešenie problémov používať užívateľsky definované funkcie poľa VBA.
Takže tu chcem funkciu poľa, ktorá berie argument ako argument a pridáva "-done" ku každej hodnote v rozsahu. To sa dá ľahko vykonať pomocou funkcie zreťazenia, ale tu použijeme funkciu poľa.
Funkcia CONCATDone (rozsah ako rozsah) Ako variant () Dim resulArr () Ako variant „Vytvoriť zbierku Dim col ako nová kolekcia“ Pridávanie hodnôt do zbierky Pri chybe Pokračovať ďalej pre každú v In rng col.Add v Next On Error GoTo 0 ' dokončenie operácie s každou hodnotou a ich pridanie do poľa Array ReDim resulArr (kol. počet - 1, 0) pre i = 0 do stĺpcového súčtu - 1 výsledok CONCATDone = funkcia ukončenia resulArr
Vysvetlenie:
Vyššie uvedená funkcia akceptuje rozsah ako argument a ku každej hodnote v rozsahu pridá „-done“.
Môžete vidieť, že sme tu použili zbierku VBA na uchovanie hodnôt poľa a potom sme urobili každú operáciu s každou hodnotou a pridali sme ich späť do dvojrozmerného poľa.
Takže áno, chlapci, takto môžete vytvoriť vlastnú funkciu poľa VBA, ktorá dokáže vrátiť pole. Dúfam, že to bolo dostatočne vysvetľujúce. Ak máte akékoľvek otázky týkajúce sa tohto článku, zadajte ich do sekcie komentárov nižšie.
Kliknutím na odkaz nižšie si stiahnete pracovný súbor:
Vytvorte funkciu VBA na návrat poľaPolia v programe Excel Formul | Zistite, aké polia sú v programe Excel.
Ako vytvoriť funkciu definovanú používateľom prostredníctvom VBA | Naučte sa vytvárať v programe Excel funkcie definované používateľom
Použitie funkcie definovanej používateľom (UDF) z iného zošita pomocou jazyka VBA v programe Microsoft Excel | Používateľom definovanú funkciu použite v inom zošite programu Excel
Vráťte chybové hodnoty z funkcií definovaných používateľom pomocou jazyka VBA v programe Microsoft Excel | Zistite, ako môžete vrátiť chybové hodnoty z funkcie definovanej používateľom
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel