Objekt ArrayList je podobný objektu Collection, ale má mnohem více metod a vlastností, a tedy mnohem větší flexibilitu z hlediska programování.
Objekt Collection má pouze dvě metody (Add, Remove) a dvě vlastnosti (Count, Item), zatímco Array List has many more. Objekt Collection je také jen pro čtení. Jakmile byly přidány hodnoty, nelze indexovanou hodnotu změnit, zatímco v seznamu polí je možné úpravy.
Mnoho metod seznamu polí používá parametry. Na rozdíl od mnoha standardních metod VBA není žádný z těchto parametrů volitelný. Některé metody a vlastnosti také nemusí vždy používat velká písmena, pokud jsou zadány stejným způsobem jako v aplikaci Excel VBA. Stále však fungují.
Objekt ArrayList se rozbalí a zmenší podle toho, kolik položek obsahuje. Před použitím jako pole nemusí být dimenzováno.
Seznam polí je jednorozměrný (stejný jako objekt Collection) a výchozí datový typ je Variant, což znamená, že bude přijímat jakýkoli typ dat, ať už číselný, textový nebo datový.
Seznam polí v mnoha ohledech řeší řadu nedostatků objektu Collection. Určitě je mnohem flexibilnější v tom, co dokáže.
Objekt Array List není součástí standardní knihovny VBA. Můžete jej použít v kódu Excel VBA pomocí pozdní nebo časné vazby
| 1234 | Sub LateBindingExample ()Dim MyList jako objektNastavit MyList = CreateObject ("System.Collections.ArrayList")End Sub |
| 123 | Sub EarlyBindingExample ()Dim MyList jako nový ArrayListEnd Sub |
Abyste mohli použít příklad rané vazby, musíte ve VBA nejprve zadat odkaz na soubor „mscorlib.tlb“
To provedete výběrem „Nástroje | Reference ‘z okna editoru Visual Basic (VBE). Zobrazí se vyskakovací okno se všemi dostupnými referencemi. Přejděte dolů na „mscorlib.dll“ a zaškrtněte políčko vedle něj. Klikněte na OK a tato knihovna je nyní součástí vašeho projektu:
Jednou z velkých nevýhod objektu seznamu Array je, že nemá „Intellisense“. Normálně tam, kde používáte objekt ve VBA, například rozsah, se zobrazí vyskakovací seznam všech dostupných vlastností a metod. Nezískáte to u objektu Seznam polí a někdy to vyžaduje pečlivou kontrolu, abyste se ujistili, že jste metodu nebo vlastnost zadali správně.
Pokud také stisknete F2 v okně VBE a budete hledat „arraylist“, nic se nezobrazí, což vývojáři příliš nepomůže.
Váš kód poběží podstatně rychleji s včasnou vazbou, protože je celý kompilován předem. Při pozdní vazbě musí být objekt kompilován při spuštění kódu
Distribuce aplikace Excel obsahující seznam polí
Jak již bylo uvedeno, objekt ArrayList není součástí aplikace Excel VBA. To znamená, že každý z vašich kolegů, kterým distribuujete aplikaci, musí mít přístup k souboru „mscorlib.tlb“
Tento soubor se obvykle nachází v:
C: \ Windows \ Microsoft.NET \ Framework \ v4.0.30319
Možná by stálo za to napsat nějaký kód (pomocí metody Dir), abyste zkontrolovali, zda tento soubor existuje, když uživatel načte aplikaci, takže pokud nenajde, dojde k „měkkému přistání“. Pokud není k dispozici a kód se spustí, dojde k chybám.
Uživatel také musí mít nainstalovanou správnou verzi .Net Framework. I když má uživatel novější verzi, musí být nainstalován V3.5, jinak vaše aplikace nebude fungovat
Rozsah objektu seznamu polí
Pokud jde o rozsah, objekt seznamu polí je k dispozici pouze tehdy, když je sešit otevřený. Při uložení sešitu se neuloží. Pokud je sešit znovu otevřen, je třeba objekt Seznam polí znovu vytvořit pomocí kódu VBA.
Pokud chcete, aby byl váš Array List k dispozici pro veškerý kód v modulu kódu, pak musíte deklarovat objekt Array List v sekci Declare v horní části okna modulu
Tím zajistíte, že celý váš kód v tomto modulu bude mít přístup k seznamu polí. Pokud chcete, aby jakýkoli modul v sešitu měl přístup k objektu Seznam polí, definujte jej jako globální objekt
| 1 | Global MyCollection As New ArrayList |
Naplnění a čtení ze seznamu polí
Nejzákladnější akcí, kterou chcete provést, je vytvořit seznam polí, vložit do něj nějaká data a poté dokázat, že data lze číst. Všechny příklady kódu v tomto článku předpokládají, že používáte časnou vazbu, a přidali „mscorlib.tlb“ do odkazů VBA, jak je popsáno výše
| 123456789101112 | Sub ArrayListExample ()„Vytvořit nový objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamuMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Opakujte seznam polí, abyste dokázali hodnotyPro N = 0 do MyList.Count - 1MsgBox MyList (N)Další N.End Sub |
Tento příklad vytvoří nový objekt ArrayList, naplní jej 3 položkami a iteruje seznamem zobrazujícím každou položku.
Index ArrayList začíná na 0, ne na 1, takže od hodnoty Count musíte odečíst 1
Ke čtení hodnot můžete také použít smyčku „Pro … Každý“:
| 123456789101112 | Sub ArrayListExample ()„Vytvořit nový objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamuMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Opakujte seznam polí, abyste dokázali hodnotyZa každý I In MyListMsgBox IPříště jáEnd Sub |
Úpravy a změna položek v seznamu polí
Hlavní výhodou seznamu polí oproti kolekci je, že položky v seznamu lze v kódu upravovat a měnit. Objekt Collection je pouze pro čtení, zatímco objekt Array List je pro čtení / zápis
| 123456789101112131415 | Sub ArrayListExample ()„Vytvořit nový objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamuMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Změňte položku 1 z„ Položky 2 “na„ Změněno “MyList (1) = "Změněno"„Opakujte seznam polí, abyste dokázali, že změna fungovalaZa každý I In MyList„Zobrazit název položkyMsgBox IPříště jáEnd Sub |
V tomto případě je druhá položka „Item2“ změněna na hodnotu „Changed“ (pamatujte, že index začíná na 0). Když je iterace spuštěna na konci kódu, zobrazí se nová hodnota
Přidání pole hodnot do seznamu polí
Do seznamu polí můžete zadat hodnoty pomocí pole obsahujícího seznam těchto hodnot nebo odkazy na hodnoty buněk v listu
| 123456789101112131415161718 | Sub AddArrayExample ()„Vytvořit objekt seznamu ArrayDim MyList jako nový ArrayList‘Iterujte hodnoty pole a přidejte je do seznamu políPro každý v v poli („A1“, „A2“, „A3“)„Přidejte každou hodnotu pole do seznamuMyList.Add vdalší‘Iterujte hodnoty pole pomocí odkazů na list a přidejte je do seznamu políPro každý v v poli (rozsah („A5“). Hodnota, rozsah („A6“). Hodnota)MyList.Add vdalší„Opakujte seznam polí, abyste dokázali hodnotyPro N = 0 do MyList.Count - 1„Zobrazit položku seznamuMsgBox MyList.Item (N)Další N.End Sub |
Čtení / načítání rozsahu položek ze seznamu polí
Pomocí metody GetRange v seznamu polí můžete určit vztek po sobě jdoucích položek, které mají být načteny. Dva požadované parametry jsou počáteční pozice indexu a počet položek, které mají být načteny. Kód naplní druhý objekt seznamu polí podskupinou položek, které lze pak číst samostatně.
| 123456789101112131415161718 | Dílčí rozsah čteníExample ()„Definujte objektyDim MyList jako nový ArrayList, MyList1 jako objekt„Přidejte položky do objektu„ MyList “MyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item6"MyList.Add "Item4"MyList.Add "Item7"„Zachyťte 4 položky v seznamu„ MyList “od pozice indexu 2Nastavit MyList1 = MyList.GetRange (2, 4)„Opakujte objekt„ MyList1 “a zobrazte podskupinu položekZa každý I v MyList1„Zobrazit název položkyMsgBox IPříště jáEnd Sub |
Hledání položek v seznamu polí
Pomocí metody „Obsahuje“ můžete vyzkoušet, zda je pojmenovaná položka ve vašem seznamu. Tím se vrátí True nebo False
| 1 | MsgBox MyList.Contains ("Item2") |
Skutečnou pozici indexu můžete také zjistit pomocí metody „IndexOf“. Musíte zadat počáteční index pro vyhledávání (obvykle 0). Návratová hodnota je index první instance nalezené položky. Potom můžete pomocí smyčky změnit počáteční bod na další hodnotu indexu a najít další instance, pokud existuje několik duplicitních hodnot.
Pokud není hodnota nalezena, je vrácena hodnota -1
Tento příklad ukazuje použití položky „Obsahuje“, položka nenalezena a opakování seznamu polí k nalezení polohy všech duplicitních položek:
| 1234567891011121314151617181920212223242526 | Sub SearchListExample ()„Definujte seznam polí a proměnnéDim MyList jako nový ArrayList, Sp jako celé číslo, Pos jako celé číslo„Přidejte nové položky včetně duplikátuMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"„Test, zda je v seznamu uvedena položka„ Item2 “ - vrátí hodnotu TrueMsgBox MyList.Contains ("Item2")„Získejte index neexistující hodnoty -vrátí -1MsgBox MyList.IndexOf ("Item", 0)„Nastavte počáteční pozici pro vyhledávání na nuluSp = 0„Opakujte seznam, abyste získali všechny pozice„ položky 1 “Dělat„Získejte indexovou pozici další‘ položky1 ’na základě pozice v proměnné‘ Sp ’Pos = MyList.IndexOf ("Item1", Sp)„Pokud nejsou nalezeny žádné další instance položky„ Item1 “, ukončete smyčkuPokud je Pos = -1, pak Exit Do„Zobrazte další nalezenou instanci a pozici indexuMsgBox MyList (Pos) & "at index" & Pos„Přidejte 1 k poslední nalezené hodnotě indexu - toto se nyní stane novou počáteční pozicí pro další hledáníSp = Poz + 1SmyčkaEnd Sub |
Použitý vyhledávací text rozlišuje velká a malá písmena a zástupné znaky nejsou přijímány.
Vkládání a odebírání položek
Pokud si nepřejete přidat své položky na konec seznamu, můžete je vložit na konkrétní pozici indexu, aby byla nová položka uprostřed seznamu. Čísla indexů se automaticky upraví pro následující položky.
| 123456789101112131415 | Dílčí vložka Příklad ()„Definujte objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamu políMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"„Vložte‘ Item6 ’na pozici indexu 2MyList.Insert 2, "Item6"„Opakováním položek v seznamu polí zobrazte nové pořadí a pozici indexuPro N = 0 do MyList.Count - 1MsgBox MyList (N) & "Index" & N.Další N.End Sub |
V tomto příkladu je ‘Item6’ přidán do seznamu na pozici indexu 2, takže ‘item3’, který byl na pozici indexu 2, se nyní přesune na pozici indexu 3
Jednotlivé položky lze odebrat pomocí metody „Odebrat“.
| 1 | MyList.Remove "Item" |
Pokud název položky není nalezen, nevznikne žádná chyba. Všechna následující čísla indexů budou změněna tak, aby vyhovovala odstranění.
Pokud znáte pozici indexu položky, můžete použít metodu ‘RemoveAt’, např.
| 1 | MyList.RemoveAt 2 |
Všimněte si, že pokud je daná pozice indexu větší než počet položek v seznamu polí, pak bude vrácena chyba.
Rozsah hodnot můžete ze seznamu odebrat pomocí metody ‘RemoveRange’. Parametry jsou počáteční index a poté počet položek, které je třeba odstranit, např.
| 1 | MyList.RemoveRange 3, 2 |
Všimněte si, že v kódu se zobrazí chyba, pokud je počet položek posunutý od počáteční hodnoty větší než počet položek v seznamu polí.
V metodách „RemoveAt“ i „RemoveRange“ by bylo vhodné nějaký kód zkontrolovat, zda zadaná čísla indexů nejsou větší než celkový počet položek v seznamu polí, aby se zachytily všechny možné chyby. Vlastnost „Počet“ poskytne celkový počet položek v seznamu polí.
| 12345678910111213141516171819202122232425 | Sub RemoveExample ()„Definujte objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamu políMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"MyList.Add "Item1"MyList.Add "Item4"MyList.Add "Item5"„Vložte‘ Item6 ’na pozici indexu 2MyList.Insert 2, "Item6""Odebrat" Item2 "MyList.Remove "Item2"„Odebrat‘ položku ‘ - neexistuje v seznamu polí, ale nechybíMyList.Remove "Item"„Odeberte položku na pozici indexu 2MyList.RemoveAt 2„Odstraňte 2 po sobě jdoucí položky začínající na pozici indexu 2MyList.RemoveRange 3, 2„Opakujte seznam polí, abyste ukázali, co zbylo a v jaké pozici indexu se nyní nacházíPro N = 0 do MyList.Count - 1MsgBox MyList (N) & "Index" & N.Další N.End Sub |
Všimněte si toho, že pokud používáte „RemoveAt“ k odebrání položky na konkrétní pozici, jakmile se tato položka odstraní, změní se všechny následující pozice indexu. Pokud máte vícenásobné odebrání pomocí pozice indexu, je dobré začít s nejvyšším číslem indexu a postupovat zpět dolů na pozici nula, abyste vždy odstranili správnou položku. Tímto způsobem nebudete mít problém
Třídění seznamu polí
Další velkou výhodou oproti kolekci je, že můžete položky řadit vzestupně nebo sestupně.
Objekt Array List je jediným objektem v aplikaci Excel VBA s metodou třídění. Metoda řazení je velmi rychlá a to může být důležitým faktorem při používání seznamu polí.
V objektu kolekce bylo pro třídění všech položek nutné určité myšlení „po vybalení z krabice“, ale se seznamem polí je to velmi jednoduché.
Metoda „Sort“ seřadí vzestupně a metoda „Reverse“ seřadí sestupně.
| 12345678910111213141516171819202122 | Sub ArrayListExample ()„Vytvořit objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky v netříděném pořadíMyList.Add "Item1"MyList.Add "Item3"MyList.Add "Item2"„Seřaďte položky vzestupněMyList.Sort„Opakujte položky, abyste zobrazili vzestupné pořadíZa každý I In MyList„Zobrazit název položkyMsgBox IPříště já„Seřaďte položky sestupněMyList.Reverse„Opakujte položky a zobrazte sestupné pořadíZa každý I In MyList„Zobrazit název položkyMsgBox IPříště jáEnd Sub |
Klonování seznamu polí
Seznam polí má možnost vytvořit klon nebo kopii sebe sama. To je užitečné, pokud uživatel provede změny položek pomocí front -endu a kódu VBA, ale musíte jako zálohu ponechat kopii položek v původním stavu.
To by uživateli mohlo poskytnout funkci „Zpět“. Možná provedli změny a chtějí se vrátit zpět k původnímu seznamu.
| 123456789101112131415 | Sub CloneExample ()„Definujte dva objekty - seznam polí a objektDim MyList jako nový ArrayList, MyList1 jako objekt„Naplňte první objekt položkamiMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Zkopírujte Mylist do MyList1Nastavit MyList1 = MyList.Clone„Opakujte MyList1, abyste dokázali klonováníZa každý I v MyList1„Zobrazit název položkyMsgBox IPříště jáEnd Sub |
„MyList1“ nyní obsahuje všechny položky z „MyList“ ve stejném pořadí
Kopírování pole seznamu do konvenčního objektu pole VBA
K zkopírování seznamu polí do normálního pole VBA můžete použít jednoduchou metodu:
| 123456789101112131415 | Dílčí polePříklad ()„Vytvořte objekt seznamu polí a standardní objekt poleDim MyList jako nový ArrayList, NewArray jako varianta„Naplňte seznam polí položkamiMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Zkopírujte seznam polí do nového poleNewArray = MyList.ToArray„Opakujte nové pole - počet seznamů polí poskytuje maximální indexPro N = 0 do MyList.Count - 1„Zobrazit název položkyMsgBox NewArray (N)Další N.End Sub |
Kopírování pole seznamu do rozsahu listu
Seznam polí můžete zkopírovat na konkrétní list a odkaz na buňku, aniž byste museli iterovat seznam polí. Stačí zadat pouze odkaz na první buňku
| 123456789101112131415 | Dílčí rozsah Příklad ()„Vytvořit nový objekt seznamu políDim MyList jako nový ArrayList„Přidejte položky do seznamuMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Vymažte cílový listListy („List1“). UsedRange.Clear„Zkopírujte položky přes řádekListy („List1“). Rozsah („A1“). Změnit velikost (1, MyList.Count). Hodnota = MyList.toArray„Zkopírujte položky do sloupceListy („List1“). Rozsah („A5“). Změnit velikost (MyList.Count, 1). Hodnota = _WorksheetFunction.Transpose (MyList.toArray)End Sub |
Vyprázdněte všechny položky ze seznamu polí
Existuje jednoduchá funkce (Vymazat) pro úplné vymazání seznamu polí
| 1234567891011121314 | Dílčí ClearListExample ()„Vytvořit objekt seznamu políDim MyList jako nový ArrayList„Přidejte nové položkyMyList.Add "Item1"MyList.Add "Item2"MyList.Add "Item3"„Zobrazit počet položekMsgBox MyList.Count„Vymažte všechny položkyMyList.Clear„Ukažte počet položek, abyste dokázali, že jasný fungovalMsgBox MyList.CountEnd Sub |
Tento příklad vytvoří položky v seznamu polí a poté vymaže seznam polí. Okna zpráv prokazují počet položek v seznamu polí před a za nimi.
Shrnutí metod seznamu polí pro Excel VBA
| Úkol | Parametry | Příklady |
| Přidat / upravit položku | Hodnota | MyList.Add „Item1“ |
| MyList (4) = „Item2“ | ||
| Klonujte seznam polí | Žádný | Dim MyList jako objekt |
| Nastavit MyList2 = MyList.Clone | ||
| Kopírovat do pole | Žádný | Dim MyArray jako varianta |
| MyArray = MyList.ToArray | ||
| Kopírovat do rozsahu listu (řádek) | Žádný | Listy („List1“). Rozsah („A1“). Změnit velikost (1, MyList.Count). Hodnota = MyList.ToArray |
| Kopírovat do rozsahu listu (sloupec) | Žádný | Listy („List1“). Rozsah („A3“). Změnit velikost (MyList.Count, 1). Hodnota = WorksheetFunction.Transpose (MyList.ToArray) |
| Vytvořit | „System.Collections.ArrayList“ | Dim MyList jako objekt |
| Nastavit MyList = CreateObject („System.Collections.ArrayList“) | ||
| Prohlásit | N/A | Dim MyList jako objekt |
| Najděte / zkontrolujte, zda položka existuje | Položka k nalezení | MyList.Contains („Item2“) |
| Najděte polohu položky v seznamu ArrayList | 1. Položka k nalezení. | Dim IndexNo As Long |
| 2. Pozice, ze které chcete začít hledat. | IndexNo = MyList.IndexOf („Item3“, 0) | |
| IndexNo = MyList.IndexOf („Item5“, 3) | ||
| Získejte počet položek | Žádný | MsgBox MyList.Count |
| Vložit položku | 1. Index - pozice pro vložení. | MyList.Insert 0, „Item5“ |
| 2 Hodnota - objekt nebo hodnota, kterou chcete vložit. | MyList.Insert 4, „Item7“ | |
| Přečíst položku | Index - dlouhé celé číslo | MsgBox MyList.Item (0) |
| MsgBox MyList.Item (4) | ||
| Přečtená položka přidána jako poslední | Index - dlouhé celé číslo | MsgBox MyList.Item (list.Count - 1) |
| Nejprve si přečtěte položku | Index - dlouhé celé číslo | MsgBox MyList.Item (0) |
| Přečíst všechny položky (pro každou) | N/A | Dim element As Variant |
| Pro každý prvek v MyList | ||
| Prvek MsgBox | ||
| Další prvek | ||
| Přečíst všechny položky (pro) | Index - dlouhé celé číslo | Dim i As Long |
| Pro i = 0 do MyList.Count - 1 | ||
| MsgBox i | ||
| Příště já | ||
| Odebrat všechny položky | Žádný | MyList.Clear |
| Odeberte položku na místě | Index pozice, kde je položka | MyList.RemoveAt 5 |
| Odebrat položku podle názvu | Položka, kterou chcete odebrat z ArrayList | MyList.Remove “Item3” |
| Odeberte řadu položek | 1. Index - počáteční pozice. | MyList.RemoveRange 4,3 |
| 2. Počítat - počet položek k odebrání. | ||
| Seřadit sestupně | Žádný | MyList.Reverse |
| Seřadit vzestupně | Ne | MyList.Sort |
