A gyűjtemény olyan objektum, amely számos hasonló elemet tartalmaz. Ezek könnyen hozzáférhetők és manipulálhatók, még akkor is, ha a gyűjteményben nagyszámú elem található.
Már vannak beépített gyűjtemények az Excel VBA segítségével. Példa erre a Sheets gyűjtemény. A munkafüzet minden munkalapjához tartozik egy elem a Táblázatok gyűjteményben.
A beépített gyűjtemények sokkal több tulajdonsággal és módszerrel állnak az Ön rendelkezésére, de ezek nem állnak rendelkezésre az Ön által létrehozott gyűjteményekben.
Például a gyűjtemény segítségével információkat szerezhet egy adott munkalapról. Láthatja például a munkalap nevét, és azt is, hogy látható -e vagy sem. A Minden egyes ciklus használatával megismételheti a gyűjtemény minden munkalapját.
| 1234567 | Alvizsgálati munkalapok ()Dim Sh mint munkalapMinden Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleKövetkező ShEnd Sub |
A gyűjtemény egy adott munkalapját is megcímzheti az index értékével vagy a munkalap tényleges nevével:
| 12 | MsgBox Sheets (1). NévMsgBox Sheets ("Sheet1"). Név |
A munkalapok hozzáadásával vagy törlésével a Táblázatok gyűjteménye növekszik vagy csökken.
Vegye figyelembe, hogy a VBA gyűjteményeknél az indexszám 1 -gyel kezdődik, nem 0 -val
Gyűjtemények a tömbök ellen
A tömbök és gyűjtemények funkcióikban hasonlóak, mivel mindkettő olyan módszertan, amely lehetővé teszi nagy mennyiségű adat tárolását, amelyekre ezután könnyen hivatkozni lehet kód segítségével. Működésükben azonban számos különbség van:
- A tömbök többdimenziósak, míg a gyűjtemények csak egyetlen dimenziót tartalmaznak. Egy tömböt több dimenzióval méretezhet, pl.
| 1 | Dim MyArray (10, 2) karakterláncként |
Ez 10 sorból álló tömböt hoz létre 2 oszloppal, majdnem egy munkalaphoz hasonlóan. A gyűjtemény gyakorlatilag egyetlen oszlop. A tömb akkor hasznos, ha számos olyan adatot kell tárolnia, amelyek egymáshoz kapcsolódnak, pl. név és cím. A név a tömb első dimenziójában, a cím pedig a második dimenzióban lenne.
- A tömb feltöltésekor külön kódsorra van szükség ahhoz, hogy értéket adjon a tömb minden eleméhez. Ha kétdimenziós tömbje lenne, akkor valójában 2 sornyi kódra lenne szüksége - egy sor az első oszlopra, egy sor pedig a második oszlopra. A Gyűjtemény objektummal egyszerűen a Hozzáadás metódust használja, hogy az új elem csak a gyűjteménybe kerüljön, és az index értéke automatikusan illeszkedjen a megfelelőhez.
- Ha törölnie kell egy adatot, akkor az bonyolultabb a tömbben. Beállíthat egy elem értékét üres értékre, de maga az elem még mindig létezik a tömbön belül. Ha a Következő ciklus segítségével ismétli a tömböt, a ciklus üres értéket ad vissza, amely kódolást igényel annak biztosítására, hogy az üres értéket figyelmen kívül hagyja. Egy gyűjteményben a Hozzáadás vagy Eltávolítás módszert használja, és az összes indexelésről és átméretezésről automatikusan gondoskodik. Az eltávolított elem teljesen eltűnik. A tömbök hasznosak rögzített méretű adatok esetén, de a gyűjtemények jobbak ott, ahol az adatmennyiség változhat.
- A gyűjtemények csak olvashatóak, míg a tömbértékek a VBA használatával módosíthatók. Gyűjtemény esetén először el kell távolítania a módosítani kívánt értéket, majd hozzá kell adnia az új módosított értéket.
- Egy tömbben csak egyetlen adattípust használhat az elemekhez, amelyeket a tömb méretezésekor állít be. A tömbben azonban egyéni adattípusokat használhat, amelyeket saját maga tervezett. Egy nagyon bonyolult tömbstruktúrája lehet egy egyéni adattípussal, amely viszont több egyéni adattípussal rendelkezik alatta. Egy gyűjteményben felhasználási adattípusokat adhat hozzá az egyes elemekhez. Lehet számszerű értéke, dátuma vagy karakterlánca - a gyűjtési objektum bármilyen adattípust felvesz. Ha egy karakterlánc értékét próbálta elhelyezni egy tömbben, amelyet numerikusnak méreteztek, az hibaüzenetet jelenít meg.
- A gyűjtemények általában könnyebben használhatók, mint a tömbök. Kódolási szempontból, amikor gyűjteményobjektumot hoz létre, annak csak két módja van (Hozzáadás és eltávolítás) és két tulajdonsága (Számlálás és Tétel), így az objektum korántsem bonyolult programozni.
- A gyűjtemények kulcsokat használhatnak az adatok megkereséséhez. A tömbök nem rendelkeznek ezzel a funkcióval, és hurokkódot igényelnek a tömbön való iteráláshoz, hogy megtalálják az adott értékeket.
- A tömb méretét az első létrehozáskor kell meghatározni. Elképzelni kell, hogy mennyi adatot fog tárolni. Ha növelni kell a tömb méretét, használhatja a „ReDim” -t az átméretezéshez, de a „Megőrzés” kulcsszót kell használnia, ha nem szeretné elveszíteni a tömbben már tárolt adatokat. A gyűjtemény méretét nem kell meghatározni. Csak növekszik és automatikusan zsugorodik, amikor elemeket ad hozzá vagy távolít el.
Gyűjtemény tárgya
A hatókör tekintetében a gyűjtőobjektum csak a munkafüzet nyitva tartása alatt érhető el. A munkafüzet mentésekor nem kerül mentésre. Ha a munkafüzetet újra megnyitja, akkor a gyűjteményt újra kell létrehozni VBA-kód használatával.
Ha azt szeretné, hogy gyűjteménye a kódmodul összes kódja számára elérhető legyen, akkor a gyűjtőobjektumot a modul ablakának tetején található Nyilatkozat szakaszban kell deklarálnia
Ez biztosítja, hogy a modulban található összes kód hozzáférjen a gyűjteményhez. Ha azt szeretné, hogy a munkafüzet bármely modulja hozzáférjen a gyűjteményhez, akkor határozza meg azt globális objektumként
| 1 | Globális MyCollection új gyűjteményként |
Gyűjtemény létrehozása, elemek hozzáadása és elérése
Egy egyszerű gyűjteményobjektum hozható létre a VBA -ban a következő kód használatával:
| 123456 | Sub CreateCollection ()Dim MyCollection mint új kollekcióMyCollection.Add "Item1"MyCollection.Add "Item2"Saját gyűjtemény. Adja hozzá a „3. tételt”End Sub |
A kód méretez egy új objektumot, amelyet „MyCollection” -nak neveznek, majd a következő kódsorok a Hozzáadás módszerrel adnak hozzá 3 új értéket.
Ezt követően kód segítségével iterálhat a gyűjteményben az értékek eléréséhez
| 123 | A MyCollection minden egyes eleméhezMsgBox elemKövetkező tétel |
A For Next Loop segítségével is megismételheti gyűjteményét:
| 123 | N = 1 esetén a Saját gyűjteményhezMsgBox MyCollection (n)Következő n |
A kód a Count tulajdonság használatával megkapja a gyűjtemény méretét, majd ezt használja az 1 érték indításával az egyes elemek indexeléséhez
A For Every Loop gyorsabb, mint a For Next hurok, de csak egy irányban működik (alacsony index és magas). A For Next Loop előnye, hogy más irányt is használhat (a magas indextől az alacsonyig), és a Step módszerrel is módosíthatja a lépést. Ez akkor hasznos, ha több elemet szeretne törölni, mivel a törlést a gyűjtemény végétől az elejéig kell futtatnia, mivel az index a törlések során megváltozik.
A gyűjtemény Hozzáadás metódusa 3 választható paraméterrel rendelkezik - Kulcs, Előtte és Utána
Az „Előtte” és az „Utána” paraméterek segítségével meghatározhatja az új elem pozícióját a gyűjteményben lévő többihez képest
Ehhez adja meg azt az indexszámot, amelyhez viszonyítani szeretné az új elemet.
| 123456 | Sub CreateCollection ()Dim MyCollection mint új kollekcióMyCollection.Add "Item1"MyCollection.Ad "Item2",, 1Saját gyűjtemény. Adja hozzá a „3. tételt”End Sub |
Ebben a példában a „2. tétel” hozzá van adva ahhoz, hogy a gyűjtemény első indexelt eleme (azaz „1. tétel”) elé kerüljön. Amikor végiggondolja ezt a gyűjteményt, először megjelenik a „2. tétel”, majd az „1. tétel” és a „3. tétel”.
Amikor megadja az „Előtte” vagy az „Utána” paramétert, az index értéke automatikusan módosul a gyűjteményen belül úgy, hogy a „2. tétel” 1 -es indexértékűvé válik, és az „1. tétel” 2 -es indexértékre kerül.
A „Kulcs” paraméterrel referenciaértéket is hozzáadhat, amellyel azonosíthatja a gyűjteményt. Vegye figyelembe, hogy a kulcsértéknek karakterláncnak kell lennie, és egyedinek kell lennie a gyűjteményen belül.
| 1234567 | Sub CreateCollection ()Dim MyCollection mint új kollekcióMyCollection.Add "Item1"Saját gyűjtemény. Add "Item2", "MyKey"Saját gyűjtemény. Adja hozzá a „3. tételt”MsgBox MyCollection ("MyKey")End Sub |
A „Item2” a „MyKey” „Key” értékét kapta, így arra az elemre hivatkozhat a „MyKey” érték használatával az indexszám helyett (2)
Ne feledje, hogy a „Kulcs” értéknek karakterlánc értéknek kell lennie. Nem lehet más adattípus. Ne feledje, hogy a gyűjtemény csak olvasható, és a kulcsértéket a beállítás után nem frissítheti. Ezenkívül nem ellenőrizheti, hogy létezik -e kulcsérték a gyűjtemény egy adott eleméhez, vagy nem tekintheti meg a kulcsértéket, ami némi hátrányt jelent.
A „Kulcs” paraméter további előnye, hogy olvashatóbbá teszi a kódot, különösen akkor, ha azt átadja egy kollégának, hogy támogassa, és nem kell végigjárnia a teljes gyűjteményt az érték megtalálásához. Képzelje el, ha 10 000 elemből álló gyűjteménye lenne, milyen nehéz lenne egy adott elemre hivatkozni!
Elem eltávolítása a gyűjteményből
Az „Eltávolítás” módszerrel törölhet elemeket a gyűjteményből.
| 1 | MyCollection.Remove (2) |
Sajnos nem könnyű, ha a gyűjtemény nagyszámú elemet tartalmaz a törlendő elem indexének kidolgozásához. Itt hasznos a „Kulcs” paraméter a gyűjtemény létrehozásakor
| 1 | MyCollection.Remove („MyKey”) |
Amikor egy elemet eltávolítanak a gyűjteményből, az indexértékek automatikusan visszaállnak a gyűjteménybe. Itt a „Kulcs” paraméter olyan hasznos, ha egyszerre több elemet töröl. Például törölheti a 105 -ös cikkindexet, és a 106 -os cikk -index azonnal 105 -ös indexszé válik, és minden, ami ezen elem felett van, az index értéke lefelé mozdul. Ha a Key paramétert használja, nem kell aggódnia, hogy melyik indexértéket kell eltávolítani.
Az összes gyűjteményelem törléséhez és új gyűjtemény létrehozásához használja ismét a Dim utasítást, amely üres gyűjteményt hoz létre.
| 1 | Dim MyCollection mint új kollekció |
A tényleges gyűjtőobjektum teljes eltávolításához állítsa az objektumot semmire
| 1 | Set MyCollection = Semmi |
Ez akkor hasznos, ha a gyűjteményt már nem írja elő a kód. Ha a gyűjteményobjektumot semmire állítja, akkor minden hivatkozás megszűnik, és felszabadul a használt memória. Ez jelentős hatással lehet a kód végrehajtásának sebességére, ha egy nagy objektum a memóriában ül, amelyre már nincs szükség.
Számolja meg a gyűjtemény elemeinek számát
A „Szám” tulajdonság segítségével könnyen megtudhatja a gyűjteményben lévő tételek számát
| 1 | MsgBox MyCollection.Count |
Ezt a tulajdonságot akkor használná, ha a Következő hurok segítségével ismételgetné a gyűjteményt, mivel ez biztosítja az indexszám felső korlátját.
Egy adott érték tesztgyűjteménye
Gyűjteményben ismétlődve kereshet egy adott értéket egy elemhez a For Every Loop használatával
| 123456789101112 | Sub SearchCollection ()Dim MyCollection mint új kollekcióMyCollection.Add "Item1"MyCollection.Add "Item2"Saját gyűjtemény. Adja hozzá a „3. tételt”A MyCollection minden egyes eleméhezHa Elem = "Elem2" AkkorMsgBox elem és "talált"Vége HaKövetkezőEnd Sub |
A kód egy kis gyűjteményt hoz létre, majd ismétlődik rajta, és keres egy „item2” nevű elemet. Ha megtalálta, megjelenik egy üzenetmező, amely szerint megtalálta az adott elemet
Ennek a módszertannak az egyik hátránya, hogy nem érheti el az indexértéket vagy a kulcsértéket
Ha helyette For Next Loop -ot használ, akkor a Next Next számlálóval szerezheti be az indexértéket, bár még mindig nem kapja meg a „Key” értéket
| 123456789101112 | Sub SearchCollection ()Dim MyCollection mint új kollekcióMyCollection.Add "Item1"MyCollection.Add "Item2"Saját gyűjtemény. Adja hozzá a „3. tételt”N = 1 esetén a Saját gyűjteményhezHa MyCollection.Item (n) = "Item2" AkkorMsgBox MyCollection.Item (n) & "found in index position" & nVége HaKövetkező nEnd Sub |
A Következő számláló (n) megadja az index pozícióját
Gyűjtemény rendezése
A gyűjtemény rendezéséhez nincs beépített funkcionalitás, de bizonyos „a dobozon kívül” gondolkodás segítségével kód írható a rendezéshez, az Excel munkalapok rendezési funkciójával. Ez a kód egy üres „SortSheet” nevű munkalapot használ a tényleges rendezéshez.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Alválogatási gyűjtemény ()Dim MyCollection mint új kollekcióHosszú tompított számláló„Gyűjteményt készíthet véletlen sorrendű elemekkelSaját gyűjtemény. Adja hozzá az "5. tételt"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"Saját gyűjtemény. Adja hozzá a „3. tételt”„Rögzítse a gyűjteményben lévő tételek számát későbbi használatraSzámláló = MyCollection.Count„Ismételje meg a gyűjteményt, és másolja át az egyes elemeket a„ SortSheet ”(A oszlop) egymást követő cellájábaN = 1 esetén a Saját gyűjteményhezTáblázatok ("SortSheet"). Cellák (n, 1) = Saját gyűjtemény (n)Következő n„Aktiválja a rendezési lapot, és az Excel rendezési rutin segítségével rendezze az adatokat növekvő sorrendbeTáblázatok ("SortSheet"). AktiválásTartomány ("A1: A" & MyCollection.Count). Válassza kiActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Tartomány (_"A1: A5"), SortOn: = xlSortOnValues, Sorrend: = xlNövekvő, DataOption: = _xlSortNormalAz ActiveWorkbook.Worksheets segítségével ("SortSheet").SetRange tartomány ("A1: A5").Header = xlGuess.MatchCase = Hamis.Orientation = xlTopToBottom.SortMethod = xlPinYin.AlkalmazVége ezzel„Törölje a gyűjtemény összes elemét - vegye figyelembe, hogy ez a For Next Loop fordított sorrendben futN = Saját gyűjtemény. Számlálás az 1. lépéshezMyCollection.Remove (n)Következő n„Másolja vissza a cellaértékeket az üres gyűjtőobjektumba a ciklus tárolt értékének (számlálója) segítségévelN = 1 esetén SzámlálóMyCollection.Add Sheets ("SortSheet"). Cellák (n, 1). ÉrtékKövetkező n„Ismételje meg a gyűjteményt, hogy bizonyítsa a tételek sorrendjétA MyCollection minden egyes eleméhezMsgBox elemKövetkező tétel„Törölje a munkalapot (fajtalapot) - ha szükséges, törölje azt isTáblázatok ("SortSheet"). Tartomány (cellák (1, 1), cellák (számláló, 1)). TisztaEnd Sub |
Ez a kód először gyűjteményt hoz létre az elemekkel véletlen sorrendben. Ezután átmásolja őket a munkalap első oszlopába (SortSheet).
A Code ezután az Excel rendezési funkcióval rendezi az oszlopban lévő adatokat növekvő sorrendbe. A kód módosítható is, hogy csökkenő sorrendbe rendeződjön.
A gyűjtemény ezután kiürül az adatokból a For Next Loop használatával. Ne feledje, hogy a lépés lehetőséget úgy használja, hogy a gyűjtés végétől a kezdetig törlődjön. Ennek oka az, hogy a törlés során az indexértékek visszaállnak, ha az elejétől törlődik, akkor nem törlődik helyesen (a 2. index 1 -es index lesz)
Végül egy másik For Next Loop használatával a cikk értékei visszakerülnek az üres gyűjteménybe
Egy további For Every Loop bizonyítja, hogy a gyűjtemény most jó növekvő sorrendben van.
Sajnos ez nem foglalkozik az eredetileg megadott kulcsértékekkel, mivel a kulcsértékek nem olvashatók
Gyűjtemény átadása egy al / funkciónak
A gyűjtemény ugyanúgy továbbítható egy al- vagy függvényhez, mint bármely más paraméter
| 1 | Funkció MyFunction (ByRef MyCollection as Collection) |
Fontos, hogy a gyűjteményt a „ByRef” segítségével adja át. Ez azt jelenti, hogy az eredeti gyűjteményt használják. Ha a gyűjteményt a „ByVal” segítségével továbbítják, akkor ez létrehozza a gyűjtemény másolatát, amely sajnálatos következményekkel járhat
Ha a másolat a „ByVal” használatával jön létre, akkor minden, ami megváltoztatja a gyűjteményt a funkción belül, csak a másolaton történik, és nem az eredetin. Például, ha a függvényen belül új elem kerül a gyűjteménybe, ez nem jelenik meg az eredeti gyűjteményben, ami hibát okoz a kódban.
Gyűjtemény visszaküldése egy függvényből
A függvényből származó gyűjteményt ugyanúgy küldheti vissza, mint bármely objektumot. A Set kulcsszót kell használnia
| 12345 | Sub ReturnFromFunction ()Dim MyCollection gyűjteménykéntSet MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Ez a kód létrehoz egy alrutinot, amely létrehozza a „MyCollection” nevű objektumot, majd a „Set” kulcsszó segítségével hatékonyan meghívja a függvényt a gyűjtemény feltöltéséhez. Miután ez megtörtént, megjelenik egy üzenetmező, amely a 2 elem számát mutatja
| 1234567 | Funkció PopulateCollection () gyűjteménykéntDim MyCollection mint új kollekcióMyCollection.Add "Item1"MyCollection.Add "Item2"Set PopulateCollection = Saját gyűjteményVége funkció |
A PopulateCollection függvény új gyűjteményobjektumot hoz létre, és 2 elemmel tölti fel. Ezután továbbítja ezt az objektumot az eredeti alrutinban létrehozott gyűjteményobjektumnak.
Gyűjtemény tömbvé alakítása
Érdemes a gyűjteményt tömbré alakítani. Érdemes az adatokat olyan helyen tárolni, ahol azok módosíthatók és manipulálhatók. Ez a kód egy kis gyűjteményt hoz létre, majd egy tömbbe továbbítja
Vegye figyelembe, hogy a gyűjteményindex 1 -től kezdődik, míg a tömbindex 0 -tól. Míg a gyűjtemény 3 elemből áll, a tömböt csak 2 -re kell méretezni, mert van 0 elem
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection mint új kollekcióDim MyArray (2) karakterlánckéntMyCollection.Add "Item1"MyCollection.Add "Item2"Saját gyűjtemény. Adja hozzá a „3. tételt”N = 1 esetén a Saját gyűjteményhezMyArray (n - 1) = Saját gyűjtemény (n)Következő nN = 0 és 2 közöttMsgBox MyArray (n)Következő nEnd Sub |
Egy tömb gyűjteményké alakítása
Érdemes egy tömböt gyűjteményké alakítani. Előfordulhat például, hogy gyorsabban és elegánsabban szeretne hozzáférni az adatokhoz, például kód használatával tömb elemhez.
Ne feledje, hogy ez csak a tömb egyetlen dimenziójára vonatkozik, mert a gyűjteménynek csak egy dimenziója van
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection mint új kollekcióDim MyArray (2) karakterlánckéntMyArray (0) = "item1"MyArray (1) = "Elem2"MyArray (2) = "3. tétel"N = 0 és 2 közöttMyCollection. Add MyArray (n)Következő nA MyCollection minden egyes eleméhezMsgBox elemKövetkező tételEnd Sub |
Ha többdimenziós tömböt szeretne használni, akkor a tömb értékeit a tömb minden egyes sorában összefűzheti a tömb dimenziói közötti elválasztó karakter használatával, így a gyűjteményérték olvasásakor programszerűen használhatja a határoló karaktert szétválasztani az értékeket.
Azt is áthelyezheti az adatokat a gyűjteménybe, hogy az első dimenzió értéke hozzáadásra kerül (1. index), majd a következő dimenzióérték (2. index) stb.
Ha a tömbnek mondjuk 4 dimenziója lenne, a gyűjtemény minden negyedik értéke új értékkészlet lenne.
Hozzáadhat tömbértékeket is, amelyeket kulcsként használhat (feltéve, hogy azok egyediek), ami megkönnyíti az egyes adatok megkeresését.
