Vytvořte funkci VBA pro návrat pole

Jak naznačuje název, naučíme se, jak v Excelu vytvořit uživatelem definovanou funkci, která vrací pole. Už jsme se naučili, jak vytvořit uživatelsky definovanou funkci ve VBA. Takže bez ztráty času začněme s výukovým programem.

Co je funkce pole?

Funkce pole jsou funkce, které při použití vrací pole. Tyto funkce se používají s kombinacemi kláves CTRL+SHIFT+ENTER, a proto dáváme přednost volání funkce pole nebo vzorců jako funkce a vzorců CSE.

Funkce pole excelu je často maticové vzorce s více buňkami. Jedním příkladem je funkce TRANSPOSE.

Vytvoření funkce pole UDF ve VBA

Scénář je tedy takový, že chci vrátit první 3 sudá čísla pomocí funkce ThreeEven ().

Kód bude vypadat takto.

Funkce ThreeEven () As Integer () 'definuje pole Dim čísla (2) Jako Integer' Přiřazuje hodnoty číslům pole (0) = 0 čísel (1) = 2 čísla (2) = 4 'návratové hodnoty ThreeEven = čísla Koncová funkce 

Tuto funkci použijeme na listu.

Vidíte, že nejprve vybereme tři buňky (vodorovně, pro svislé musíme použít dvourozměrné pole. Máme to pokryté níže.). Poté začneme psát náš vzorec. Poté stiskneme CTRL+SHIFT+ENTER. Tím se vybrané buňky vyplní hodnotami pole.

Poznámka:

• V praxi nebudete vědět, kolik buněk budete potřebovat. V takovém případě vždy vyberte více buněk, než je očekávaná délka pole. Funkce zaplní buňky polem a další buňky zobrazí chybu #N/A.
• Ve výchozím nastavení tato funkce pole vrací hodnoty v horizontálním poli. Pokud se pokusíte vybrat svislé buňky, všechny buňky zobrazí pouze první hodnotu pole.

Jak to funguje?

Chcete -li vytvořit funkci pole, musíte dodržovat tuto syntaxi.

Funkce functionName (proměnné) Jako returnType () dim resultArray (délka) jako dataType 'Přiřazení hodnot k poli zde functionName = resultArray End Function 

Deklarace funkce musí být definována výše. Toto prohlásilo, že se jedná o funkci pole.
Při používání na listu musíte použít kombinaci kláves CTRL+SHIFT+ENTER. V opačném případě vrátí pouze první hodnotu pole.

Funkce VBA Array pro návrat vertikálního pole

Aby vaše funkce pole UDF fungovala svisle, nemusíte mnoho dělat. Stačí deklarovat pole jako dvourozměrné pole. Poté v první dimenzi přidejte hodnoty a druhou dimenzi nechte prázdnou. Takto to uděláte:

Funkce ThreeEven () As Integer () 'definuje pole Dim number (2,0) As Integer' Assign values ​​to array numbers (0,0) = 0 numbers (1,0) = 2 numbers (2,0) = 4 ' návratové hodnoty ThreeEven = čísla Koncová funkce 

Takto to použijete na listu.

Funkce pole UDF s argumenty v aplikaci Excel

Ve výše uvedených příkladech jsme jednoduše vytiskli součet statických hodnot na list. Řekněme, že chceme, aby naše funkce přijala argument rozsahu, provedla na něm některé operace a vrátila výsledné pole.

Příklad Ke každé hodnotě v rozsahu přidejte „-done“

Nyní vím, že to lze provést snadno, ale jen abych vám ukázal, jak můžete k řešení problémů používat uživatelsky definované funkce pole VBA.

Takže tady chci funkci pole, která bere rozsah jako argument a přidává "-done" ke každé hodnotě v rozsahu. To lze snadno provést pomocí funkce zřetězení, ale zde použijeme funkci pole.

Funkce CONCATDone (rozsah jako rozsah) jako varianta () Dim resulArr () jako varianta 'Vytvořit kolekci Dim col jako nová kolekce' Přidávání hodnot do kolekce Při chybě Pokračovat dále pro každou v In rng col.Add v Další při chybě GoTo 0 ' dokončení operace s každou hodnotou a jejich přidání do Array ReDim resulArr (col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr (i, 0) = col (i + 1) & "-done" Další CONCATDone = resulArr Koncová funkce 

Vysvětlení:

Výše uvedená funkce přijme rozsah jako argument a ke každé hodnotě v rozsahu přidá „-done“.

Můžete vidět, že jsme zde použili kolekci VBA k uchování hodnot pole a poté jsme provedli naši operaci s každou hodnotou a přidali je zpět do dvourozměrného pole.

Takže jo, lidi, takto můžete vytvořit vlastní funkci pole VBA, která může vrátit pole. Doufám, že to bylo dostatečně vysvětlující. Máte -li jakékoli dotazy týkající se tohoto článku, vložte je do sekce komentářů níže.

Pracovní soubor stáhnete kliknutím na odkaz níže:

Vytvořte funkci VBA pro návrat pole

Pole v aplikaci Excel Formul | Zjistěte, jaká pole jsou v aplikaci Excel.

Jak vytvořit funkci definovanou uživatelem prostřednictvím VBA | Zjistěte, jak v aplikaci Excel vytvářet funkce definované uživatelem

Použití funkce definované uživatelem (UDF) z jiného sešitu pomocí jazyka VBA v aplikaci Microsoft Excel | Použijte funkci definovanou uživatelem v jiném sešitu aplikace Excel

Vraťte chybové hodnoty z funkcí definovaných uživatelem pomocí jazyka VBA v aplikaci Microsoft Excel | Zjistěte, jak můžete vrátit chybové hodnoty z funkce definované uživatelem

Oblíbené články:

50 zkratek aplikace Excel pro zvýšení produktivity

Funkce VLOOKUP v Excelu

COUNTIF v aplikaci Excel 2016

Jak používat funkci SUMIF v aplikaci Excel