Ve VBA, an Pole je jedna proměnná, která může obsahovat více hodnot. Představte si pole jako řadu buněk: každá buňka může uložit hodnotu. Pole mohou být jednorozměrná (přemýšlejte o jednom sloupci), dvourozměrná (přemýšlejte o více řádcích a sloupcích) nebo vícerozměrná. K hodnotám pole lze přistupovat podle jejich pozice (indexového čísla) v poli.
Rychlý list pole VBA
Pole
PopisKód VBAVytvořitDim arr (1 To 3) As Variantarr (1) = „jeden“
arr (2) = „dva“
arr (3) = „tři“Vytvořit z ExceluDim arr (1 To 3) As Variant
Dim buňka jako rozsah, i jako celé číslo
i = LBound (arr)
Pro každou buňku v dosahu („A1: A3“)
i = i + 1
arr (i) = hodnota buňky
Další buňkaPřečtěte si všechny položkyDim i jako Long
For i = LBound (arr) To UBound (arr)
MsgBox arr (i)
Příště jáVymazatVymazat arrPole na řetězecDim sName As String
sName = Připojte se (arr, „:“)Zvětšit velikostZachování aretace ReDim (0 až 100)Nastavit hodnotuarr (1) = 22
Rychlé příklady pole VBA
Podívejme se na úplný příklad, než se ponoříme do specifik:
| 12345678910 | Dílčí polePříklad ()Dim strNames (1 až 4) jako řetězecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)End Sub |
Zde jsme vytvořili jednorozměrné pole řetězců: strNames o velikosti čtyři (může obsahovat čtyři hodnoty) a přiřadili čtyři hodnoty. Poslední zobrazíme 3. hodnotu ve schránce zpráv.
V tomto případě je výhoda použití pole malá: místo čtyř je vyžadována pouze jedna deklarace proměnné.
Podívejme se však na příklad, který ukáže skutečnou sílu pole:
| 12345678 | Dílčí polePříklad2 ()Dim strNames (1 až 60 000) jako řetězecDim i As LongPro i = 1 až 60 000strNames (i) = Buňky (i, 1). HodnotaPříště jáEnd Sub |
Zde jsme vytvořili pole, které pojme 60 000 hodnot, a rychle jsme pole naplnili ze sloupce A listu.
Výhody pole? - Rychlost!
Možná vás napadnou pole podobná pracovním listům Excelu:
- Každá buňka (nebo položka v poli) může obsahovat svou vlastní hodnotu
- Ke každé buňce (nebo položce v poli) lze přistupovat podle polohy řádku a sloupce.
- Pracovní list Př. buňky (1,4) .hodnota = „řádek 1, sloupec 4“
- Array Ex. arrVar (1,4) = „Řádek 1, Sloupec 4“
Proč se tedy trápit s poli? Proč nečíst a zapisovat hodnoty přímo do buněk v Excelu? Jedno slovo: Rychlost!
Čtení / zápis do buněk aplikace Excel je pomalý proces. Práce s poli je mnohem rychlejší!
Vytvořit / Deklarovat pole (Dim)
Poznámka: Pole mohou mít více „rozměrů“. Abychom měli věci jednoduché, začneme tím, že budeme pracovat pouze s jednorozměrnými poli. Později v tutoriálu vás seznámíme s vícerozměrnými poli.
Statické pole
Statická pole jsou pole, která nemohou měnit velikost. Naopak, Dynamická pole lze změnit velikost. Jsou deklarovány mírně odlišně. Nejprve se podívejme na statická pole.
Poznámka: Pokud se velikost pole nezmění, použijte statické pole.
Deklarace proměnné statického pole je velmi podobná deklaraci běžné proměnné, kromě toho, že musíte definovat velikost pole. Existuje několik různých způsobů, jak nastavit velikost pole.
Počáteční a koncovou pozici pole můžete explicitně deklarovat:
| 123456789101112 | Sub StaticArray1 ()'Vytvoří pole s pozicemi 1,2,3,4Dim arrDemo1 (1 až 4) jako řetězec'Vytvoří pole s pozicemi 4,5,6,7Dim arrDemo2 (4 To 7) As Long'Vytvoří pole s pozicemi 0,1,2,3Dim arrDemo3 (0 To 3) As LongEnd Sub |
Nebo můžete zadat pouze velikost pole:
| 123456 | Sub StaticArray2 ()'Vytvoří pole s pozicemi 0,1,2,3Dim arrDemo1 (3) jako řetězecEnd Sub |
Důležité! Všimněte si, že ve výchozím nastavení pole začínají na pozici 0. Takže Dim arrDemo1 (3) vytvoří pole s pozicemi 0,1,2,3.
Můžete prohlásit Možnost Základna 1 v horní části modulu, takže pole začíná na pozici 1:
| 12345678 | Možnost Základna 1Sub StaticArray3 ()'Vytvoří pole s pozicemi 1,2,3Dim arrDemo1 (3) jako řetězecEnd Sub |
Zjistil jsem však, že je mnohem jednodušší (a méně matoucí) pouze explicitně deklarovat počáteční a koncovou polohu polí.
Už vás nebaví hledat příklady kódu VBA? Zkuste AutoMacro!
Dynamické pole
Dynamická pole jsou pole, jejichž velikost lze změnit (nebo jejichž velikost není třeba definovat).
Dynamické pole lze deklarovat dvěma způsoby.
Pole variant
První způsob, jak deklarovat dynamické pole, je nastavit pole na typ Varianta.
| 1 | Dim arrVar () jako varianta |
S Varianta Array, nemusíte definovat velikost pole. Velikost se automaticky upraví. Pamatujte, že pole začíná pozicí 0 (pokud nepřidáte Option Base 1 do horní části modulu)
| 12345678910111213 | Sub VariantArray ()Dim arrVar () jako varianta'Definujte hodnoty (velikost = 0,1,2,3)arrVar = pole (1, 2, 3, 4)'Změnit hodnoty (velikost = 0,1,2,3,4)arrVar = pole („1a“, „2a“, „3a“, „4a“, „5a“)'Výstupní poloha 4 ("5a")MsgBox arrVar (4)End Sub |
Nevariantní dynamická pole
U polí jiných než variant musíte před přiřazením hodnot k poli definovat velikost pole. Proces vytváření pole se však mírně liší:
| 1234567 | Sub DynamicArray1 ()Dim arrDemo1 () jako řetězec'Změní velikost pole s pozicemi 1,2,3,4.'ReDim arrDemo1 (1 až 4)End Sub |
Nejprve deklarujete pole, podobné statickému poli, kromě toho, že vynecháte velikost pole:
| 1 | Dim arrDemo1 () jako řetězec |
Nyní, když chcete nastavit velikost pole, použijte ReDim příkaz pro velikost pole:
| 12 | 'Změní velikost pole s pozicemi 1,2,3,4.'ReDim arrDemo1 (1 až 4) |
ReDim změní velikost pole. Níže si přečtěte rozdíl mezi ReDim a ReDim Preserve.
ReDim vs. ReDim Preserve
Když použijete ReDim příkaz vymažete všechny existující hodnoty z pole. Místo toho můžete použít Zachovat ReDim zachovat hodnoty pole:
| 12 | 'Změní velikost pole s pozicemi 1,2,3,4 (zachování stávajících hodnot)ReDim Zachovat arrDemo1 (1 až 4) |
Zjednodušená deklarace polí
Po přečtení všeho výše se můžete cítit zahlceni. Aby to nebylo jednoduché, budeme po zbytek článku většinou pracovat se statickými poli.
Nastavte hodnoty pole
Nastavení hodnot pole je velmi snadné.
U statického pole musíte definovat každou pozici pole, jednu po druhé:
| 12345678 | Dílčí polePříklad ()Dim strNames (1 až 4) jako řetězecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"End Sub |
Pomocí Variant Array můžete definovat celé pole jedním řádkem (praktické pouze pro malá pole):
| 123456 | Dílčí poleExample_1Line ()Dim strNames () jako variantastrNames = Pole („Shelly“, „Steve“, „Neema“, „Jose“)End Sub |
Pokud se pokusíte definovat hodnotu pro pole, které neexistuje, zobrazí se chyba Subscript Out of Range:
| 1 | strNames (5) = "Shannon" |
V níže uvedené části „Přiřadit rozsah poli“ si ukážeme, jak pomocí smyčky rychle přiřadit poli velký počet hodnot.
Získejte hodnotu pole
Hodnoty pole můžete načíst stejným způsobem. V níže uvedeném příkladu zapíšeme hodnoty polí do buněk:
| 1234 | Rozsah ("A1"). Hodnota = strNames (1)Rozsah ("A2"). Hodnota = strNames (2)Rozsah ("A3"). Hodnota = strNames (3)Rozsah ("A4"). Hodnota = strNames (4) |
Programování VBA | Generátor kódu pracuje pro vás!
Přiřaďte rozsah poli
K přiřazení rozsahu k poli můžete použít smyčku:
| 12345678 | Sub RangeToArray ()Dim strNames (1 až 60 000) jako řetězecDim i As LongPro i = 1 až 60 000strNames (i) = Buňky (i, 1). HodnotaPříště jáEnd Sub |
Tím se bude procházet buňkami A1: A60000 a přiřazovat hodnoty buněk k poli.
Výstupní pole na rozsah
Nebo můžete použít smyčku k přiřazení pole k rozsahu:
| 123 | Pro i = 1 až 60 000Buňky (i, 1). Hodnota = strNames (i)Příště já |
Bude to naopak: přiřadit hodnoty pole buňkám A1: A60000
2D / vícerozměrná pole
Doposud jsme pracovali výhradně s jednorozměrnými (1D) poli. Pole však mohou mít až 32 dimenzí.
Představte si 1D pole jako jeden řádek nebo sloupec buněk aplikace Excel, 2D pole jako celý list aplikace Excel s více řádky a sloupci a 3D pole je jako celý sešit obsahující více listů, z nichž každý obsahuje více řádků a sloupců (vy mohl by také uvažovat o 3D poli jako o Rubikově kostce).
Příklady vícerozměrných polí
Nyní si předveďme příklady práce s poli různých dimenzí.
Programování VBA | Generátor kódu pracuje pro vás!
Příklad 1D pole
Tento postup kombinuje předchozí příklady polí do jedné procedury a ukazuje, jak můžete pole v praxi používat.
| 1234567891011121314 | Dílčí poleEx_1d ()Dim strNames (1 až 60 000) jako řetězecDim i As Long„Přiřaďte hodnoty poliPro i = 1 až 60 000strNames (i) = Buňky (i, 1). HodnotaPříště já„Hodnoty výstupního pole na rozsahPro i = 1 až 60 000Listy („Výstup“). Buňky (i, 1). Hodnota = strNames (i)Příště jáEnd Sub |
Příklad 2D pole
Tento postup obsahuje příklad 2D pole:
| 123456789101112131415161718 | Dílčí poleEx_2d ()Dim strNames (1 až 60 000, 1 až 10) jako řetězecDim i As Long, j As Long„Přiřaďte hodnoty poliPro i = 1 až 60 000Pro j = 1 až 10strNames (i, j) = Buňky (i, j). HodnotaDalší jPříště já„Hodnoty výstupního pole na rozsahPro i = 1 až 60 000Pro j = 1 až 10Listy („Výstup“). Buňky (i, j). Hodnota = strNames (i, j)Další jPříště jáEnd Sub |
Příklad 3D pole
Tento postup obsahuje příklad 3D pole pro práci s více listy:
| 12345678910111213141516171819202122 | Dílčí poleEx_3d ()Dim strNames (1 až 60 000, 1 až 10, 1 až 3) jako řetězecDim i As Long, j As Long, k As Long„Přiřaďte hodnoty poliPro k = 1 až 3Pro i = 1 až 60 000Pro j = 1 až 10strNames (i, j, k) = Sheets ("Sheet" & k). Cells (i, j) .ValueDalší jPříště jáDalší k„Hodnoty výstupního pole na rozsahPro k = 1 až 3Pro i = 1 až 60 000Pro j = 1 až 10Listy ("Výstup" & k). Buňky (i, j). Hodnota = strNames (i, j, k)Další jPříště jáDalší kEnd Sub |
Délka / velikost pole
Zatím jsme vám představili různé typy polí a naučili vás, jak pole deklarovat a získat/nastavit hodnoty polí. Dále se zaměříme na další nezbytná témata pro práci s poli.
Programování VBA | Generátor kódu pracuje pro vás!
Funkce UBound a LBound
Prvním krokem k získání délky / velikosti pole je použití funkcí UBound a LBound k získání horní a dolní hranice pole:
| 123456 | Sub UBoundLBound ()Dim strNames (1 až 4) jako řetězecMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub |
Odečtením obou (a přidáním 1) získáte délku:
| 1 | GetArrLength = UBound (strNames) - LBound (strNames) + 1 |
Funkce délky pole
Zde je funkce pro získání délky jednorozměrného pole:
| 1234567 | Veřejná funkce GetArrLength (jako varianta) jako dlouháIf IsEmpty (a) ThenGetArrLength = 0JinýGetArrLength = UBound (a) - LBound (a) + 1Konec IfKoncová funkce |
Potřebujete vypočítat velikost 2D pole? Podívejte se na náš návod: Vypočítejte velikost pole.
Smyčka skrz pole
Pole lze procházet dvěma způsoby. První prochází celými čísly odpovídajícími pozicím čísel v poli. Pokud znáte velikost pole, můžete ji zadat přímo:
| 12345678910111213 | Dílčí polePříklad_Smyčka1 ()Dim strNames (1 až 4) jako řetězecDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pro i = 1 až 4MsgBox strNames (i)Příště jáEnd Sub |
Pokud však neznáte velikost pole (pokud je pole dynamické), můžete použít funkce LBound a UBound z předchozí části:
| 12345678910111213 | Dílčí poleExample_Loop2 ()Dim strNames (1 až 4) jako řetězecDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pro i = LBound (strNames) To UBound (strNames)MsgBox strNames (i)Příště jáEnd Sub |
Pro každou smyčku pole
Druhá metoda je pro každou smyčku. Toto prochází každou položkou v poli:
| 12345678910111213 | Dílčí polePříklad_Smyčka3 ()Dim strNames (1 až 4) jako řetězecDim ItemstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pro každou položku v strNamesPoložka MsgBoxDalší položkaEnd Sub |
Smyčka For Each Array Loop bude kromě jednorozměrných polí fungovat také s vícerozměrnými poli.
Programování VBA | Generátor kódu pracuje pro vás!
Smyčka skrz 2D pole
Pomocí funkcí UBound a LBound můžete také procházet vícerozměrným polem. V tomto případě budeme procházet 2D polem. Všimněte si, že funkce UBound a LBound vám umožňují určit, který rozměr pole má najít horní a dolní hranici (1 pro první dimenzi, 2 pro druhou dimenzi).
| 1234567891011121314151617181920 | Dílčí poleExample_Loop4 ()Dim strNames (1 až 4, 1 až 2) jako řetězecDim i As Long, j As LongstrNames (1, 1) = "Shelly"strNames (2, 1) = "Steve"strNames (3, 1) = "Neema"strNames (4, 1) = "Jose"strNames (1, 2) = "Shelby"strNames (2, 2) = "Steven"strNames (3, 2) = "Nemo"strNames (4, 2) = "Jesse"Pro j = LBound (strNames, 2) Do UBound (strNames, 2)Pro i = LBound (strNames, 1) Do UBound (strNames, 1)MsgBox strNames (i, j)Příště jáDalší jEnd Sub |
Další úkoly pole
Vymazat pole
Chcete -li vymazat celé pole, použijte příkaz Erase:
| 1 | Vymazat strNames |
Příklad použití:
| 12345678910 | Dílčí polePříklad ()Dim strNames (1 až 4) jako řetězecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Vymazat strNamesEnd Sub |
Alternativně můžete také změnit velikost pole a vymazat část pole:
| 1 | ReDim strNames (1 až 2) |
Tím se změní velikost pole na velikost 2, přičemž se odstraní pozice 3 a 4.
Hrabě Array
Počet pozic v každé dimenzi pole můžete spočítat pomocí funkcí UBound a LBound (popsáno výše).
Můžete také spočítat počet zadaných položek (nebo položek, které splňují určitá kritéria) opakováním v poli.
Tento příklad bude procházet řadou objektů a počítat počet neprázdných řetězců nalezených v poli:
| 123456789101112131415 | Sub ArrayLoopandCount ()Dim strNames (1 až 4) jako řetězecDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Pro i = LBound (strNames) To UBound (strNames)Pokud strNames (i) "" Pakn = n + 1Konec IfPříště jáMsgBox n & "nalezeny neprázdné hodnoty."End Sub |
Programování VBA | Generátor kódu pracuje pro vás!
Odebrat duplikáty
V určitém okamžiku možná budete chtít odebrat duplikáty z pole. VBA k tomu bohužel nemá vestavěnou funkci. Napsali jsme však funkci pro odstranění duplikátů z pole (zahrnutí do tohoto tutoriálu je příliš dlouhé, ale další informace získáte po kliknutí na odkaz).
Filtr
Funkce filtru VBA vám umožňuje filtrovat pole. Dělá to tak, že vytvoří nové pole pouze s filtrovanými hodnotami. Níže je rychlý příklad, ale přečtěte si článek, kde najdete další příklady pro různé potřeby.
| 1234567891011121314 | Sub Filter_Match ()"Definujte pole."Dim strNames As VariantstrNames = Array („Steve Smith“, „Shannon Smith“, „Ryan Johnson“)„Pole filtrůDim strSubNames jako variantastrSubNames = Filtr (strNames, "Smith")„Hrabě Filtrované poleMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "names."End Sub |
Funkce IsArray
Pomocí funkce IsArray můžete vyzkoušet, zda je proměnná matice:
| 123456789101112 | Sub IsArrayEx ()'Vytvoří pole s pozicemi 1,2,3Dim arrDemo1 (3) jako řetězec'Vytvoří pravidelnou proměnnou řetězceDim str jako řetězecMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub |
Připojte se k Array
Pomocí funkce Připojit můžete rychle „připojit“ celé pole:
| 123456789101112 | Sub Array_Join ()Dim strNames (1 až 4) jako řetězecDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Připojit se (strNames, ",")MsgBox joinNamesEnd Sub |
Programování VBA | Generátor kódu pracuje pro vás!
Rozdělte řetězec do pole
Funkce rozdělení VBA rozdělí řetězec textu do pole obsahujícího hodnoty z původního řetězce. Podívejme se na příklad:
| 123456789 | Sub Array_Split ()Dim Jména () jako řetězecDim připojilNázvy jako řetězecjoinNames = "Shelly, Steve, Nema, Jose"Jména = Rozdělit (joinNames, ",")Názvy MsgBox (1)End Sub |
Zde jsme rozdělili tento řetězec textu „Shelly, Steve, Nema, Jose“ do pole (velikost 4) pomocí oddělovače čárky (, ”).
Const Array
Pole nemůže být deklarován jako konstanta ve VBA. Můžete to však obejít vytvořením funkce, kterou použijete jako pole:
| 123456789 | "Definujte ConstantArray."Funkce ConstantArray ()ConstantArray = Pole (4, 12, 21, 100, 5)Koncová funkce`` Obnovte hodnotu ConstantArraySub RetrieveValues ()MsgBox ConstantArray (3)End Sub |
Kopírovat pole
Neexistuje žádný vestavěný způsob kopírování pole pomocí VBA. Místo toho budete muset použít smyčku k přiřazení hodnot z jednoho pole do druhého.
| 12345678910111213141516171819 | Dílčí kopie Pole ()Dim Arr1 (1 až 100) jako dlouhýDim Arr2 (1 až 100) jako dlouhýDim i As Long'Vytvořit pole1Pro i = 1 až 100Arr1 (i) = iPříště já'CopyArray1 to Array2Pro i = 1 až 100Arr2 (i) = Arr1 (i)Příště jáMsgBox Arr2 (74)End Sub |
Přemístit
Neexistuje žádná vestavěná funkce VBA, která by vám umožnila transponovat pole. Napsali jsme však funkci pro transpozici 2D pole. Přečtěte si článek a dozvíte se více.
Programování VBA | Generátor kódu pracuje pro vás!
Funkce Zpětné pole
Častou otázkou vývojářů VBA je, jak vytvořit funkci, která vrací pole. Myslím, že většina obtíží je vyřešena použitím variantních polí. Napsali jsme článek na téma: Zpětné pole funkcí VBA.
Použití polí v Access VBA
Většina výše uvedených příkladů Array funguje v Access VBA úplně stejně jako v Excel VBA. Jeden zásadní rozdíl je v tom, že když chcete naplnit pole pomocí dat aplikace Access, budete muset procházet objektem RecordSet spíše než objektem Range.
| 1234567891011121314151617181920212223 | Dílčí rozsahToArrayAccess ()Při chybě Pokračovat DalšíDim strNames () jako řetězecDim i As LongDim iCount jako dlouhýDim dbs jako databázeDim první jako sada záznamůNastavit dbs = CurrentDbNastavit rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S první.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 do iCount)Pro i = 1 do iCountstrNames (i) = rst.Fields ("ClientName").MoveNextPříště jáKonec sprvní ZavřítNastavit první = nicNastavit dbs = nicEnd Sub |
| Array Návody | |
|---|---|
| Array Mega-Guide | Ano |
| Získejte velikost pole | |
| Vymazat pole | |
| Pole filtru | |
| Transponovat pole | |
| Funkce Zpětné pole | |
| Odebrat duplikáty | |
