Kolekcija yra objektas, kuriame yra daug panašių daiktų. Juos galima lengvai pasiekti ir jais manipuliuoti, net jei kolekcijoje yra daug daiktų.
Jau yra integruotų kolekcijų su „Excel VBA“. Pavyzdys yra „Sheets“ kolekcija. Kiekviename darbaknygės darbalapyje yra elementas Skaičiuoklių kolekcijoje.
Integruotos kolekcijos turi daug daugiau savybių ir metodų, tačiau jų nėra jūsų sukurtose kolekcijose.
Pavyzdžiui, galite naudoti kolekciją, kad gautumėte informacijos apie tam tikrą darbalapį. Pavyzdžiui, galite pamatyti darbalapio pavadinimą ir tai, ar jis matomas, ar ne. Naudodami ciklą „Už kiekvieną“ galite kartoti kiekvieną kolekcijos darbalapį.
| 1234567 | Antrinis bandomasis darbalapis ()Dim Sh kaip darbalapisUž kiekvieną „Sh In Sheets“MsgBox Sh.Name„MsgBox Sh.Visible“Kitas ŠPabaiga Sub |
Taip pat galite kreiptis į konkretų kolekcijos darbalapį naudodami indekso reikšmę arba faktinį darbalapio pavadinimą:
| 12 | „MsgBox Sheets“ (1). Pavadinimas„MsgBox Sheets“ („Sheet1“). Pavadinimas |
Kai darbalapiai pridedami arba ištrinami, „Sheets“ kolekcija auga arba mažėja.
Atminkite, kad naudojant VBA kolekcijas indekso numeris prasideda 1, o ne 0
Kolekcijos prieš masyvus
Masyvai ir rinkiniai savo funkcijomis yra panašūs, nes jie abu yra metodika, leidžianti saugoti daug duomenų, kuriuos vėliau galima lengvai nurodyti naudojant kodą. Tačiau jų veikimo būdas turi daug skirtumų:
- Masyvai yra daugialypiai, o kolekcijos yra tik vienas aspektas. Masyvo matmenis galite nustatyti keliais matmenimis, pvz.
| 1 | Dim MyArray (10, 2) kaip eilutė |
Taip sukuriamas 10 eilučių masyvas su 2 stulpeliais, beveik kaip darbalapis. Kolekcija iš tikrųjų yra vienas stulpelis. Masyvas yra naudingas, jei reikia saugoti daugybę duomenų elementų, kurie yra susiję vienas su kitu, pvz. vardas ir adresas. Pavadinimas būtų pirmame masyvo matmenyje, o adresas - antrame.
- Kai pildote masyvą, jums reikia atskiros kodo eilutės, kad kiekvienam masyvo elementui būtų pateikta reikšmė. Jei turėtumėte dvimatį masyvą, jums iš tikrųjų reikės 2 kodo eilučių - vienos eilutės, skirtos pirmajam stulpeliui, ir vienos eilutės, skirtos kitam stulpeliui. Naudodami „Collection“ objektą, jūs tiesiog naudojate metodą „Add“, kad naujas elementas būtų ką tik įtrauktas į kolekciją, o indekso vertė automatiškai būtų koreguojama, kad tiktų.
- Jei reikia ištrinti duomenų elementą, tai masyve yra sudėtingiau. Galite nustatyti tuščias elemento reikšmes, tačiau pats elementas vis dar egzistuoja masyve. Jei naudojate „For Next“ kilpą, norėdami kartoti masyvą, ciklas grąžins tuščią reikšmę, kurią reikės koduoti, kad būtų užtikrinta, jog tuščia reikšmė nepaisoma. Kolekcijoje naudojate pridėjimo arba pašalinimo metodus, o visas indeksavimas ir dydžio keitimas automatiškai pasirūpina. Pašalintas elementas visiškai išnyksta. Masyvai yra naudingi fiksuoto dydžio duomenims, tačiau rinkiniai yra geresni ten, kur duomenų kiekis gali keistis.
- Kolekcijos yra tik skaitomos, o masyvo reikšmes galima keisti naudojant VBA. Turėdami kolekciją, pirmiausia turėsite pašalinti vertę, kurią norite pakeisti, ir tada pridėti naują pakeistą vertę.
- Masyve elementams galite naudoti tik vieną duomenų tipą, kuris nustatomas matuojant masyvą. Tačiau masyve galite naudoti pasirinktinius duomenų tipus, kuriuos sukūrėte patys. Galite turėti labai sudėtingą masyvo struktūrą, naudodami pasirinktinį duomenų tipą, kuris savo ruožtu turi keletą tinkintų duomenų tipų. Kolekcijoje galite pridėti kiekvieno elemento duomenų tipų naudojimą. Galite turėti skaitinę reikšmę, datą ar eilutę - surinkimo objektas imsis bet kokio tipo duomenų. Jei bandytumėte įterpti eilutės reikšmę į masyvą, kurio matmenys yra skaitmeniniai, būtų rodomas klaidos pranešimas.
- Kolekcijas paprastai lengviau naudoti nei masyvus. Kodavimo požiūriu, kai sukuriate kolekcijos objektą, jis turi tik du metodus (pridėti ir pašalinti) ir dvi ypatybes (skaičiuoti ir elementas), todėl objektas jokiu būdu nėra sudėtingas programuoti.
- Kolekcijos gali naudoti raktus duomenims rasti. Masyvai neturi šios funkcijos ir reikalauja, kad ciklas kartotųsi masyvu, kad rastų konkrečias vertes.
- Masyvo dydį reikia apibrėžti pirmą kartą jį sukuriant. Turite įsivaizduoti, kiek duomenų jis ketina saugoti. Jei norite padidinti masyvo dydį, galite naudoti „ReDim“, kad pakeistumėte jo dydį, tačiau turite naudoti raktinį žodį „Išsaugoti“, jei nenorite prarasti masyve jau esančių duomenų. Kolekcijos dydžio apibrėžti nereikia. Jis tiesiog auga ir susitraukia, kai elementai pridedami ar pašalinami.
Kolekcijos objekto apimtis
Kalbant apie apimtį, surinkimo objektas pasiekiamas tik kol darbo knyga yra atidaryta. Išsaugojus darbo knygą jis neišsaugomas. Jei darbaknygė atidaroma iš naujo, rinkinį reikia iš naujo sukurti naudojant VBA kodą.
Jei norite, kad jūsų kolekcija būtų prieinama visiems jūsų kodo modulio kodams, turite deklaruoti kolekcijos objektą modulio lango viršuje esančioje skiltyje Deklaruoti
Tai užtikrins, kad visas jūsų modulio kodas gali pasiekti kolekciją. Jei norite, kad bet kuris jūsų darbaknygės modulis galėtų pasiekti kolekciją, apibrėžkite ją kaip visuotinį objektą
| 1 | Pasaulinė „MyCollection“ kaip nauja kolekcija |
Kolekcijos kūrimas, elementų pridėjimas ir prieiga prie elementų
Paprastą surinkimo objektą galima sukurti VBA naudojant šį kodą:
| 123456 | Sub CreateCollection ()„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Pabaiga Sub |
Kodas matuoja naują objektą, pavadintą „MyCollection“, o toliau pateiktos kodo eilutės naudoja pridėjimo metodą, kad pridėtų 3 naujas reikšmes.
Tada galite naudoti kodą, norėdami pakartoti savo kolekciją, kad pasiektumėte vertes
| 123 | Už kiekvieną „MyCollection“ elementą„MsgBox“ elementasKitas elementas |
Taip pat galite kartoti savo kolekciją naudodami „For Next Loop“:
| 123 | Jei n = 1 į „Mano kolekcija“„MsgBox“ mano kolekcija (n)Kitas n |
Kodas gauna kolekcijos dydį naudodamas ypatybę Skaičiuoti ir tada naudoja šią pradinę reikšmę 1, kad indeksuotų kiekvieną elementą
„For Every Loop“ yra greitesnis už „For Next“ ciklą, tačiau jis veikia tik viena kryptimi (nuo mažo iki aukšto indekso). „For Next Loop“ pranašumas yra tas, kad galite naudoti kitą kryptį (nuo aukšto iki mažo indekso), taip pat galite naudoti žingsnio metodą, kad pakeistumėte žingsnį. Tai naudinga, kai norite ištrinti kelis elementus, nes turėsite paleisti ištrynimą nuo kolekcijos pabaigos iki pradžios, nes indeksas pasikeis, kai bus ištrinti duomenys.
Kolekcijos pridėjimo metodas turi 3 pasirenkamus parametrus - raktas, prieš ir po
Galite naudoti parametrus „Prieš“ ir „Po“, kad nustatytumėte naujo elemento padėtį, palyginti su kitais kolekcijoje jau esančiais elementais.
Tai daroma nurodant indekso numerį, su kuriuo norite palyginti naują elementą.
| 123456 | Sub CreateCollection ()„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Add "Item3"Pabaiga Sub |
Šiame pavyzdyje nurodytas elementas „2“, kuris turi būti pridėtas prieš pirmąjį indeksuotą kolekcijos elementą (kuris yra „1 elementas“). Kai kartojate šią kolekciją, pirmiausia pasirodys „Item2“, po to - „Item1“ ir „Item3“
Kai nurodote parametrą „Prieš“ arba „Po“, indekso vertė automatiškai koreguojama kolekcijoje, kad „2 elementas“ taptų 1 indekso reikšme, o „1 elementas“ - 2 rodyklės verte
Taip pat galite naudoti parametrą „Raktas“, kad pridėtumėte nuorodos vertę, kurią galite naudoti rinkinio elementui identifikuoti. Atminkite, kad rakto vertė turi būti eilutė ir turi būti unikali kolekcijoje.
| 1234567 | Sub CreateCollection ()„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Add "Item3"„MsgBox MyCollection“ („MyKey“)Pabaiga Sub |
„Item2“ buvo suteikta „MyKey“ reikšmė „Key“, kad galėtumėte nurodyti tą elementą naudodami „MyKey“ vertę, o ne indekso numerį (2)
Atminkite, kad „rakto“ reikšmė turi būti eilutės reikšmė. Tai negali būti kito tipo duomenys. Atminkite, kad kolekcija yra tik skaitoma ir jūs negalite atnaujinti rakto vertės, kai tik ji bus nustatyta. Be to, negalite patikrinti, ar konkrečioje kolekcijos prekėje yra pagrindinė vertė, arba peržiūrėti pagrindinės vertės, kuri yra nedidelis trūkumas.
Parametras „Raktas“ turi papildomą pranašumą, nes jis daro kodą lengviau skaitomą, ypač jei jis perduodamas kolegai palaikyti, ir jums nereikia kartoti visos kolekcijos, kad rastumėte šią vertę. Įsivaizduokite, jei turėtumėte 10 000 daiktų kolekciją, kaip sunku būtų nurodyti vieną konkretų daiktą!
Daikto pašalinimas iš kolekcijos
Jei norite ištrinti elementus iš savo kolekcijos, galite naudoti „Pašalinti“ metodą.
| 1 | Mano kolekcija. Pašalinti (2) |
Deja, nėra lengva, jei kolekcijoje yra daug elementų, kad būtų galima nustatyti norimo ištrinti elemento indeksą. Čia kuriant kolekciją praverčia parametras „raktas“
| 1 | „MyCollection.Remove“ („MyKey“) |
Kai elementas pašalinamas iš kolekcijos, indekso vertės automatiškai nustatomos iš naujo. Čia parametras „Raktas“ yra toks naudingas, kai vienu metu ištrinate kelis elementus. Pvz., Galite ištrinti elementų indeksą 105, o akimirksniu elementų indeksas 106 tampa indeksu 105, o viskas, kas yra virš šio elemento, jo indekso vertė perkeliama žemyn. Jei naudojate „Key“ parametrą, nereikia jaudintis, kurią indekso vertę reikia pašalinti.
Norėdami ištrinti visus kolekcijos elementus ir sukurti naują kolekciją, dar kartą naudokite „Dim“ teiginį, kuris sukuria tuščią kolekciją.
| 1 | „Dim MyCollection“ kaip nauja kolekcija |
Jei norite visiškai pašalinti faktinį surinkimo objektą, galite nustatyti, kad objektas būtų nieko
| 1 | Nustatykite „MyCollection“ = nieko |
Tai naudinga, jei rinkti nebereikalauja jūsų kodas. Nustačius surinkimo objektą nieko, pašalinamos visos nuorodos į jį ir atlaisvinama jo naudojama atmintis. Tai gali turėti reikšmingos įtakos jūsų kodo vykdymo greičiui, jei atmintyje yra didelis objektas, kurio nebereikia.
Suskaičiuokite kolekcijos elementų skaičių
Naudodami ypatybę „Skaičiuoti“, galite lengvai sužinoti savo kolekcijos elementų skaičių
| 1 | „MsgBox MyCollection.Count“ |
Šią ypatybę naudotumėte, jei kolekcijai kartoti naudojote „For Next Loop“, nes ji suteiks jums viršutinę indekso numerio ribą.
Konkrečios vertės bandymų rinkinys
Galite kartoti kolekciją, kad ieškotumėte konkrečios elemento vertės naudodami „Kiekvienai kilpai“
| 123456789101112 | Antrinė paieškos kolekcija ()„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Už kiekvieną elementą „MyCollection“Jei prekė = "2 prekė", tada„MsgBox“ elementas ir „Rasta“Pabaiga JeiKitasPabaiga Sub |
Kodas sukuria nedidelę kolekciją, o po to kartojasi ieškodamas elemento, pavadinto „item2“. Jei jis bus rastas, jis parodys pranešimų laukelį, kuriame rastas konkretus elementas
Vienas iš šios metodikos trūkumų yra tai, kad negalite pasiekti indekso vertės arba pagrindinės vertės
Jei vietoj to naudojate „For Next Loop“, galite naudoti „For Next“ skaitiklį, kad gautumėte indekso vertę, nors vis tiek negalite gauti „Key“ vertės
| 123456789101112 | Antrinė paieškos kolekcija ()„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Jei n = 1 į „Mano kolekcija“Jei MyCollection.Item (n) = "Item2" Tada„MsgBox MyCollection.Item“ (n) & „rasta indekso pozicijoje“ & nPabaiga JeiKitas nPabaiga Sub |
Skaitiklis „Kitas“ (n) pateiks indekso poziciją
Kolekcijos rūšiavimas
Nėra integruotos kolekcijos rūšiavimo funkcijos, tačiau naudojant tam tikrą „iš dėžutės“ mąstymą galima parašyti kodą rūšiuoti, naudojant „Excel“ darbalapio rūšiavimo funkciją. Šis kodas naudoja tuščią darbalapį, pavadintą „SortSheet“, kad būtų galima rūšiuoti.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Antrinis rūšiavimas ()„Dim MyCollection“ kaip nauja kolekcijaDim skaitiklis kaip ilgai„Sukurkite kolekciją naudodami atsitiktinio užsakymo elementusMyCollection.Add "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Add "Item3"„Užfiksuokite kolekcijoje esančių daiktų skaičių, kad galėtumėte juos naudoti ateityjeSkaitliukas = MyCollection.Count„Pakartokite kolekciją, nukopijuodami kiekvieną elementą į eilutę„ SortSheet “(A stulpelis)Jei n = 1 į „Mano kolekcija“Skaičiuoklės ("SortSheet"). Ląstelės (n, 1) = MyCollection (n)Kitas n„Suaktyvinkite rūšiavimo lapą ir naudokite„ Excel “rūšiavimo tvarką, kad surūšiuotumėte duomenis didėjančia tvarkaSkaičiuoklės („SortSheet“). SuaktyvinkiteDiapazonas („A1: A“ ir „MyCollection.Count“). PasirinkiteActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Rūšiuoti.SortFields.Add2 raktas: = Diapazonas (_"A1: A5"), SortOn: = xlSortOnValues, Tvarka: = xlAugantis, duomenų pasirinkimas: = _xlSortNormalSu „ActiveWorkbook.Worksheets“ („SortSheet“). Rūšiuoti.SetRange diapazonas ("A1: A5").Header = xlGuess.MatchCase = Netiesa.Orientation = xlTopToBottom.SortMethod = xlPinYin.TaikytiPabaiga su„Ištrinkite visus kolekcijos elementus - atkreipkite dėmesį, kad„ For Next Loop “vykdoma atvirkštine tvarkaJei n = „MyCollection“. Skaičiuokite iki 1 veiksmo -1Mano kolekcija. Pašalinti (n)Kitas n„Nukopijuokite langelio vertes atgal į tuščią surinkimo objektą, naudodami„ ciklo “išsaugotą vertę (skaitiklis)Jei n = 1 skaitikliuiMyCollection.Add Sheets („SortSheet“). Ląstelės (n, 1) .VertybėKitas n„Pakartokite kolekciją, kad įrodytumėte, kokia tvarka daiktai dabar yraUž kiekvieną „MyCollection“ elementą„MsgBox“ elementasKitas elementas„Išvalykite darbalapį (rūšiavimo lapą) - jei reikia, taip pat ištrinkiteSkaičiuoklės („SortSheet“). Diapazonas (langeliai (1, 1), langeliai (skaitiklis, 1)). AiškuPabaiga Sub |
Šis kodas pirmiausia sukuria kolekciją, kurioje elementai pridedami atsitiktine tvarka. Tada jis nukopijuoja juos į pirmą darbalapio stulpelį (SortSheet).
Tada kodas naudoja „Excel“ rūšiavimo funkciją stulpelio duomenims rūšiuoti didėjančia tvarka. Kodas taip pat gali būti pakeistas, kad būtų rūšiuojama mažėjančia tvarka.
Tada rinkinys ištuštinamas iš duomenų naudojant „For Next Loop“. Atminkite, kad veiksmo parinktis naudojama taip, kad ji būtų išvalyta nuo surinkimo pabaigos iki pradžios. Taip yra todėl, kad kai jis išvalomas, indekso vertės iš naujo nustatomos; jei jis bus išvalytas nuo pat pradžių, jis nebus išvalytas teisingai (2 indeksas taps 1 indeksu)
Galiausiai, naudojant kitą „For Next Loop“, prekės vertės perkeliamos atgal į tuščią kolekciją
Kitas už kiekvieną kilpą įrodo, kad kolekcija dabar yra didėjančia tvarka.
Deja, tai neapima jokių pagrindinių reikšmių, kurios galėjo būti įvestos iš pradžių, nes pagrindinių reikšmių negalima nuskaityti
Kolekcijos perdavimas padaliniui / funkcijai
Kolekcija gali būti perduodama daliai arba funkcijai taip pat, kaip ir bet kuris kitas parametras
| 1 | Funkcija „MyFunction“ („ByRef MyCollection as Collection“) |
Svarbu perduoti kolekciją naudojant „ByRef“. Tai reiškia, kad naudojama originali kolekcija. Jei kolekcija perduodama naudojant „ByVal“, sukuriama kolekcijos kopija, kuri gali turėti liūdnų padarinių
Jei kopija sukurta naudojant „ByVal“, viskas, kas keičia kolekciją funkcijoje, vyksta tik ant kopijos, o ne ant originalo. Pvz., Jei šioje funkcijoje prie kolekcijos pridedamas naujas elementas, tai nebus rodoma pradinėje kolekcijoje, o tai sukels jūsų kodo klaidą.
Kolekcijos grąžinimas iš funkcijos
Galite grąžinti kolekciją iš funkcijos taip pat, kaip grąžindami bet kurį objektą. Turite naudoti raktinį žodį Set
| 12345 | Sub ReturnFromFunction ()„Dim MyCollection“ kaip kolekcijaNustatyti „MyCollection“ = „PopulateCollection“„MsgBox MyCollection.Count“Pabaiga Sub |
Šis kodas sukuria papildomą tvarką, kuri sukuria objektą, pavadintą „Mano kolekcija“, ir tada naudoja raktinį žodį „Nustatyti“, kad efektyviai iškviestų šią kolekciją užpildančią funkciją. Kai tai bus padaryta, jis parodys pranešimų laukelį, kuriame bus rodomas 2 elementų skaičius
| 1234567 | Funkcija PopulateCollection () kaip kolekcija„Dim MyCollection“ kaip nauja kolekcijaMyCollection.Add "Item1"MyCollection.Add "Item2"Nustatykite PopulateCollection = MyCollectionPabaigos funkcija |
Funkcija PopulateCollection sukuria naują kolekcijos objektą ir užpildo jį 2 elementais. Tada jis grąžina šį objektą surinkimo objektui, sukurtam pagal pradinę papildomą tvarką.
Kolekcijos konvertavimas į masyvą
Galbūt norėsite savo kolekciją paversti masyvu. Galbūt norite saugoti duomenis ten, kur juos galima keisti ir manipuliuoti. Šis kodas sukuria nedidelę kolekciją ir perkelia ją į masyvą
Atkreipkite dėmesį, kad surinkimo indeksas prasideda nuo 1, o masyvo indeksas - nuo 0. Nors kolekcijoje yra 3 elementai, masyvas turi būti matuojamas tik iki 2, nes yra elementas 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()„Dim MyCollection“ kaip nauja kolekcijaDim MyArray (2) kaip eilutėMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Add "Item3"Jei n = 1 į „Mano kolekcija“MyArray (n - 1) = MyCollection (n)Kitas nJei n = 0 iki 2„MsgBox MyArray“ (n)Kitas nPabaiga Sub |
Masyvo pavertimas kolekcija
Galbūt norėsite masyvą paversti kolekcija. Pavyzdžiui, galbūt norėsite greičiau ir elegantiškiau pasiekti duomenis, naudodami kodą masyvo elementui gauti.
Atminkite, kad tai veiks tik vienam masyvo matmeniui, nes kolekcija turi tik vieną dimensiją
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()„Dim MyCollection“ kaip nauja kolekcijaDim MyArray (2) kaip eilutė„MyArray“ (0) = "elementas1"„MyArray“ (1) = "2 elementas"„MyArray“ (2) = "3 elementas"Jei n = 0 iki 2MyCollection. Pridėti MyArray (n)Kitas nUž kiekvieną „MyCollection“ elementą„MsgBox“ elementasKitas elementasPabaiga Sub |
Jei norite naudoti kelių matmenų masyvą, galite sujungti masyvo vertes kiekvienai masyvo eilutei, naudodami masyvo matmenų skyriklį, kad skaitydami surinkimo vertę galėtumėte programiškai naudoti skiriamąjį ženklą atskirti vertybes.
Taip pat galite perkelti duomenis į rinkinį, remdamiesi tuo, kad pridedama pirmojo matmens vertė (1 indeksas), o po to pridedama kitos dimensijos vertė (2 indeksas) ir pan.
Jei masyvas turėtų, tarkime, 4 matmenis, kas ketvirta kolekcijos reikšmė būtų naujas reikšmių rinkinys.
Taip pat galite pridėti masyvo reikšmes, kurios bus naudojamos kaip raktai (su sąlyga, kad jos yra unikalios), kad būtų lengviau rasti konkrečius duomenis.
