Kogu on objekt, mis sisaldab mitmeid sarnaseid esemeid. Neile on lihtne juurde pääseda ja neid manipuleerida, isegi kui kollektsioonis on palju esemeid.
Excel VBA-ga on juba sisseehitatud kogusid. Näiteks on kollektsioon Sheets. Töövihiku iga töölehe jaoks on kogumikus Arvutustabelid üksus.
Sisseehitatud kogudel on teile palju rohkem omadusi ja meetodeid, kuid need pole teie loodud kogudes saadaval.
Näiteks saate kogu kasutada konkreetse töölehe kohta teabe hankimiseks. Näiteks näete töölehe nime ja ka seda, kas see on nähtav või mitte. Kasutades iga silmuse jaoks, saate kogu kogu töölehte itereerida.
| 1234567 | Sub TestWorksheets ()Dim Sh kui töölehtIga Sh In Sheets jaoksMsgBox Sh.NameMsgBox Sh.VisibleJärgmine ShEnd Sub |
Samuti saate adresseerida kollektsiooni teatud töölehte, kasutades indeksi väärtust või töölehe tegelikku nime:
| 12 | MsgBox Sheets (1). NimiMsgBox Sheets ("Sheet1"). Nimi |
Töölehtede lisamisel või kustutamisel kasvab arvutustabelite kogu või kahaneb.
Pange tähele, et VBA kogude puhul algab indeksi number 1 -ga, mitte 0 -ga
Kollektsioonid versus massiivid
Massiivid ja kogud on oma funktsioonide poolest sarnased, kuna mõlemad on metoodikad, mis võimaldavad salvestada suurt hulka andmeid, millele saab seejärel koodi abil hõlpsasti viidata. Neil on aga toimimisviisis mitmeid erinevusi:
- Massiivid on mitmemõõtmelised, samas kui kogud on ainult üks mõõde. Massiivi saab mõõta mitme mõõtmega, nt.
| 1 | Dim MyArray (10, 2) stringina |
See loob 10 veerust koosneva kahe veeruga massiivi, peaaegu nagu tööleht. Kogu on tegelikult üks veerg. Massiiv on kasulik, kui peate salvestama mitmeid üksteisega seotud andmeid, nt. nimi ja aadress. Nimi oleks massiivi esimeses dimensioonis ja aadress teises dimensioonis.
- Massiivi täitmisel vajate massiivi igale elemendile väärtuse lisamiseks eraldi koodirida. Kui teil oleks kahemõõtmeline massiiv, oleks teil tegelikult vaja 2 koodirida - üks rida esimese veeru ja üks rida teise veeru jaoks. Objekti Kogumine puhul kasutate lihtsalt lisamismeetodit, nii et uus üksus lisatakse lihtsalt kollektsiooni ja indeksi väärtus kohandatakse automaatselt vastavalt sellele.
- Kui teil on vaja andmeüksus kustutada, on see massiivis keerulisem. Saate määrata elemendi väärtused tühjaks, kuid element ise on massiivis endiselt olemas. Kui kasutate massiivi kordamiseks tsüklit Järgmine, tagastab tsükkel tühja väärtuse, mis vajab kodeerimist, et veenduda tühja väärtuse ignoreerimises. Kogus kasutate lisamise või eemaldamise meetodeid ning kogu indekseerimise ja suuruse muutmise eest hoolitsetakse automaatselt. Eemaldatud üksus kaob täielikult. Massiivid on kasulikud kindla suurusega andmete jaoks, kuid kogud on paremad seal, kus andmete hulk võib muutuda.
- Kogud on kirjutuskaitstud, samas kui massiivi väärtusi saab muuta VBA abil. Koguga peate esmalt eemaldama muudetava väärtuse ja seejärel lisama uue muudetud väärtuse.
- Massiivis saate elementide jaoks kasutada ainult ühte andmetüüpi, mis määratakse massiivi mõõtmete määramisel. Massiivis saate aga kasutada kohandatud andmetüüpe, mille olete ise kujundanud. Teil võib olla väga keeruline massiivi struktuur, kasutades kohandatud andmetüüpi, mille all on omakorda mitu kohandatud andmetüüpi. Kogusse saate lisada iga üksuse jaoks kasutatavate andmetüüpide andmeid. Teil võib olla numbriline väärtus, kuupäev või string - kogumisobjekt võtab mis tahes andmetüübi. Kui proovisite sisestada stringi väärtuse massiivi, mille mõõtmed olid numbrilised, tekitaks see veateate.
- Kogusid on üldiselt lihtsam kasutada kui massiive. Kodeerimise mõttes on kogumisobjekti loomisel sellel ainult kaks meetodit (lisamine ja eemaldamine) ja kaks atribuuti (loend ja üksus), seega pole objekt programmeerimine sugugi keeruline.
- Kogud saavad andmete leidmiseks kasutada võtmeid. Massiividel seda funktsiooni pole ja konkreetsete väärtuste leidmiseks on vaja massiivi kordamiseks korduskoodi.
- Massiivi suurus tuleb määratleda selle esmakordsel loomisel. Teil peab olema ettekujutus sellest, kui palju andmeid see salvestab. Kui teil on vaja massiivi suurust suurendada, saate selle suuruse muutmiseks kasutada „ReDim”, kuid kui te ei soovi massiivis juba säilinud andmeid kaotada, peate kasutama märksõna „Säilita”. Kogu suurust ei ole vaja määratleda. See lihtsalt kasvab ja kahaneb automaatselt, kui esemeid lisatakse või eemaldatakse.
Kogumisobjekti ulatus
Ulatuselt on kogumisobjekt saadaval ainult siis, kui töövihik on avatud. Töövihiku salvestamisel seda ei salvestata. Kui töövihik avatakse uuesti, tuleb kogu VBA-koodi abil uuesti luua.
Kui soovite, et teie kogu oleks saadaval kõigile teie koodimooduli koodidele, peate kogumisobjekti deklareerima mooduli akna ülaosas asuvas jaotises Deklareeri
See tagab, et kogu selle mooduli kood pääseb kogule juurde. Kui soovite, et mõni teie töövihiku moodul pääseks kollektsioonile juurde, määrake see globaalseks objektiks
| 1 | Globaalne MyCollection kui uus kollektsioon |
Kogu loomine, üksuste lisamine ja üksustele juurdepääs
VBA -s saab luua lihtsa kogumisobjekti, kasutades järgmist koodi:
| 123456 | Sub CreateCollection ()Dim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lisage "Item3"End Sub |
Kood mõõdab uue objekti nimega MyCollection ja seejärel järgnevad koodiridad lisamismeetodi abil lisavad 3 uut väärtust.
Seejärel saate väärtuste juurde pääsemiseks koodi abil oma kogu läbi itereerida
| 123 | Iga üksuse kohta minu kollektsioonisMsgBoxi üksusJärgmine kaup |
Samuti saate oma kogu läbi iteratsiooni kasutada järgmise lingi jaoks:
| 123 | N = 1 jaoks Minu kogusse. LoendMsgBox MyCollection (n)Järgmine n |
Kood saab kollektsiooni suuruse, kasutades atribuuti Count, ja kasutab seejärel iga üksuse indekseerimiseks seda väärtust 1
Iga silmuse jaoks on kiirem kui järgmiseks silmus, kuid see töötab ainult ühes suunas (madal indeks kuni kõrge). Järgmise silmuse eeliseks on see, et saate kasutada erinevat suunda (kõrge indeks kuni madal) ja sammu muutmiseks saate kasutada ka sammu meetodit. See on kasulik, kui soovite kustutada mitu üksust, kuna peate kustutamise alustama kogu lõpust alguseni, kuna indeks muutub kustutamise ajal.
Kogumiku lisamismeetodil on 3 valikulist parameetrit - võti, enne ja pärast
Saate kasutada parameetreid „Enne” ja „Pärast”, et määrata oma uue üksuse asukoht teiste kogusse kuuluvate suhtes
Seda tehakse, määrates indeksi numbri, millega soovite oma uut üksust seostada.
| 123456 | Sub CreateCollection ()Dim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2",, 1MyCollection.Lisage "Item3"End Sub |
Selles näites on määratud element „Item2”, mis tuleb lisada enne kollektsiooni esimest indekseeritud üksust (mis on „Item1”). Kui te seda kogu kordate, kuvatakse kõigepealt kirje2, millele järgnevad kirje1 ja üksus3
Kui määrate parameetri „Enne” või „Pärast”, korrigeeritakse indeksi väärtust automaatselt kogu nii, et üksusest 2 saab indeksi väärtus 1 ja üksusest 1 teisaldatakse indeksi väärtus 2
Samuti saate parameetri „Võti” abil lisada võrdlusväärtuse, mida saate kasutada kogumiseüksuse tuvastamiseks. Pange tähele, et võtmeväärtus peab olema string ja kogu peab olema kordumatu.
| 1234567 | Sub CreateCollection ()Dim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2", "MyKey"MyCollection.Lisage "Item3"MsgBox MyCollection ("MyKey")End Sub |
Üksusele 2 on antud võtmeväärtus „MyKey”, nii et saate sellele üksusele viidata, kasutades indeksinumbri asemel väärtust „MyKey” (2)
Pange tähele, et „võtme” väärtus peab olema stringi väärtus. See ei saa olla muud tüüpi andmetüüp. Pange tähele, et kogu on kirjutuskaitstud ja võtme väärtust ei saa pärast selle seadistamist värskendada. Samuti ei saa te kontrollida, kas kollektsiooni konkreetse üksuse jaoks on olemas võtmeväärtus, ega vaadata võtmeväärtust, mis on väike puudus.
Parameetri „Võti” eeliseks on teie koodi loetavamaks muutmine, eriti kui see antakse üle kolleegile, et seda toetada, ja te ei pea selle väärtuse leidmiseks kogu kogu kordama. Kujutage ette, kui teil oleks 10 000 esemest koosnev kogu, kui raske oleks ühele konkreetsele esemele viidata!
Üksuse eemaldamine kogust
Saate kogust üksuste kustutamiseks kasutada meetodit „Eemalda”.
| 1 | MyCollection. Eemalda (2) |
Kahjuks ei ole lihtne, kui kogu sisaldab palju üksusi kustutatava üksuse indeksi koostamiseks. Siin tuleb kollektsiooni loomisel kasuks parameeter „Võti”
| 1 | MyCollection.Remove (“MyKey”) |
Kui üksus kogust eemaldatakse, lähtestatakse indeksi väärtused kogu kogu ulatuses automaatselt. Siin on parameeter „Võti” nii kasulik, kui kustutate korraga mitu üksust. Näiteks võite kustutada üksuste indeksi 105 ja koheselt muutub üksuste indeks 106 indeksiks 105 ning kõik selle üksuse kohal olevad väärtused liiguvad allapoole. Kui kasutate parameetrit Key, pole vaja muretseda, milline indeksi väärtus tuleb eemaldada.
Kõigi koguüksuste kustutamiseks ja uue kogu loomiseks kasutate uuesti Dim -lauset, mis loob tühja kogu.
| 1 | Dim MyCollection uue kollektsioonina |
Tegeliku kogumisobjekti täielikuks eemaldamiseks saate seada objekti tühjaks
| 1 | Set MyCollection = Ei midagi |
See on kasulik, kui teie kood ei nõua kogumist enam. Kogumisobjekti tühjaks määramine eemaldab kõik viited sellele ja vabastab mälu, mida see kasutas. Sellel võib olla oluline mõju teie koodi täitmise kiirusele, kui mälus on suur objekt, mida enam ei nõuta.
Loendage kogude esemete arv
Atribuudi „Loendamine” abil saate hõlpsasti teada oma kollektsiooni üksuste arvu
| 1 | MsgBox MyCollection.Count |
Kasutate seda atribuuti, kui kasutasite kogu järgmiseks kordamiseks kordusringi, sest see annab teile indeksi numbri ülemise piiri.
Konkreetse väärtuse testikogumik
Saate iga kogumi jaoks üksuse jaoks konkreetse väärtuse otsimiseks kogumist läbi käia
| 123456789101112 | Alamotsingu kogu ()Dim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lisage "Item3"Iga üksuse kohta minu kollektsioonisKui üksus = "Ese2" SiisMsgBoxi üksus ja "leitud"Lõpp KuiEdasiEnd Sub |
Kood loob väikese kogu ja seejärel kordab seda, otsides üksust nimega „item2”. Kui see leitakse, kuvatakse sõnumikast, et konkreetne üksus on leitud
Selle metoodika üks puudusi on see, et te ei pääse juurde indeksi väärtusele ega võtmeväärtusele
Kui kasutate selle asemel järgmise lingi jaoks, saate indeksi väärtuse saamiseks kasutada loendurit Järgmine, kuigi te ei saa ikkagi võtme väärtust
| 123456789101112 | Alamotsingu kogu ()Dim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lisage "Item3"N = 1 jaoks Minu kogusse. LoendKui MyCollection.Item (n) = "Item2" SiisMsgBox MyCollection.Item (n) & "found in index position" & nLõpp KuiJärgmine nEnd Sub |
Järgmiseks loendur (n) annab indeksi positsiooni
Kogu sorteerimine
Kollektsiooni sortimiseks pole sisseehitatud funktsioone, kuid kasutades mõningast „karbist välja” mõtet, saab koodi sorteerimiseks kirjutada, kasutades Exceli töölehtede sortimisfunktsiooni. See kood kasutab tegeliku sortimise tegemiseks tühja töölehte nimega „SortSheet“.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Alam sorteerimine ()Dim MyCollection uue kollektsiooninaPimendusloendur nii kaua„Ehitage kollektsioon juhusliku järjekorra üksustegaMyCollection.Lisage "Item5"MyCollection.Add "Item2"MyCollection.Add "Item4"MyCollection.Add "Item1"MyCollection.Lisage "Item3"„Jäädvustage kogudes olevate esemete arv edaspidiseks kasutamiseksLoendur = MyCollection.Count„Korda kogu, kopeerides iga üksuse järjestuslahtrisse„ SortSheet ”(veerg A)N = 1 jaoks Minu kogusse. LoendLehed ("SortSheet"). Lahtrid (n, 1) = Minu kogu (n)Järgmine n„Aktiveerige sortimisleht ja kasutage Exceli sortimisrutiini, et sortida andmed kasvavas järjekorrasArvutustabelid ("SortSheet"). AktiveerigeVahemik ("A1: A" ja MyCollection.Count). ValigeActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 võti: = Vahemik (_"A1: A5"), SortOn: = xlSortOnValues, järjekord: = xlAscending, DataOption: = _xlSortNormalActiveWorkbook.Worksheets ("SortSheet"). Sorteeri.SetRange vahemik ("A1: A5").Peal = xlGuess.MatchCase = Vale.Orientation = xlTopToBottom.SortMethod = xlPinYin.KandidaLõpeta„Kustutage kõik kollektsiooni üksused - pange tähele, et see järgmise silmuse jaoks töötab vastupidises järjekorrasKui n = MyCollection. Loendage 1. toiminguni -1Minu kogu. Eemalda (n)Järgmine n„Kopeerige lahtri väärtused tagasi tühja kogumisobjekti, kasutades silmuse salvestatud väärtust (loendurit)Kui n = 1 loendurileMyCollection.Add Sheets ("SortSheet"). Lahtrid (n, 1) .VäärtusJärgmine n„Korda kogu, et tõestada esemete järjekordaIga üksuse kohta minu kollektsioonisMsgBoxi üksusJärgmine kaup„Tühjendage tööleht (sordileht) - vajadusel kustutage ka seeArvutustabelid ("SortSheet"). Vahemik (lahtrid (1, 1), lahtrid (loendur, 1)). SelgeEnd Sub |
See kood loob esmalt kollektsiooni, kuhu lisatakse üksused juhuslikus järjekorras. Seejärel kopeerib see need töölehe (SortSheet) esimesse veergu.
Seejärel kasutab kood veerus olevate andmete sortimiseks kasvavas järjekorras Exceli sortimisfunktsiooni. Koodi saab muuta ka kahanevas järjekorras.
Seejärel tühjendatakse kogu kogumist andmetest, kasutades järgmist lingi. Pange tähele, et sammuvalikut kasutatakse nii, et see kustutatakse kogumise lõpust alguseni. Selle põhjuseks on asjaolu, et kui see tühjendatakse, lähtestatakse indeksi väärtused; kui see kustutatakse algusest peale, ei kustuta see õigesti (indeks 2 muutub indeksiks 1)
Lõpuks teisaldatakse üksuse For Next Loop abil üksuse väärtused tagasi tühja kogusse
Veel üks iga silmuse jaoks tõestab, et kogu on nüüd heas kasvavas järjekorras.
Kahjuks ei käsitle see algselt sisestatud võtmeväärtusi, kuna võtmeväärtusi ei saa lugeda
Kollektsiooni edastamine alamfunktsioonile
Kogu saab edastada alam- või funktsioonile samamoodi nagu iga teine parameeter
| 1 | Funktsioon MyFunction (ByRef MyCollection kui kollektsioon) |
Oluline on kollektsioon edasi anda, kasutades funktsiooni „ByRef”. See tähendab, et kasutatakse originaalkollektsiooni. Kui kogu edastatakse „ByVal” abil, loob see kollektsioonist koopia, millel võivad olla kahetsusväärsed tagajärjed
Kui koopia luuakse rakenduse „ByVal” abil, toimub kõik, mis muudab funktsioonis kogu, ainult koopial, mitte originaalil. Näiteks kui funktsiooni piires lisatakse kollektsiooni uus üksus, ei kuvata seda algses kogus, mis tekitab teie koodis vea.
Kogu tagastamine funktsioonilt
Funktsiooni kogu saate tagastada samamoodi nagu mis tahes objekti tagastamise. Peate kasutama märksõna Määra
| 12345 | Sub ReturnFromFunction ()Dim MyCollection kollektsiooninaMäära MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
See kood loob alamrutiini, mis loob objekti nimega „MyCollection” ja kasutab seejärel märksõna „Set” funktsiooni tõhusaks kutsumiseks selle kogumi täitmiseks. Kui see on tehtud, kuvatakse sõnumikast, kus kuvatakse kahe üksuse arv
| 1234567 | Funktsioon PopulateCollection () kogumikunaDim MyCollection uue kollektsiooninaMyCollection.Add "Item1"MyCollection.Add "Item2"Set PopulateCollection = Minu koguLõppfunktsioon |
Funktsioon PopulateCollection loob uue kogumisobjekti ja täidab selle kahe üksusega. Seejärel edastab see objekti tagasi kogumisobjektile, mis on loodud algses alamrutiinis.
Kogu teisendamine massiiviks
Võimalik, et soovite oma kollektsiooni massiiviks teisendada. Võimalik, et soovite salvestada andmed sinna, kus neid saab muuta ja manipuleerida. See kood loob väikese kogu ja kannab selle seejärel massiivi
Pange tähele, et kogumisindeks algab 1 -st, massiiviindeks aga 0. Kui kogul on 3 üksust, tuleb massiiv mõõta ainult 2 -ni, kuna seal on element 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection uue kollektsiooninaDim MyArray (2) stringinaMyCollection.Add "Item1"MyCollection.Add "Item2"MyCollection.Lisage "Item3"N = 1 jaoks Minu kogusse. LoendMyArray (n - 1) = Minu kogu (n)Järgmine nN = 0 kuni 2MsgBox MyArray (n)Järgmine nEnd Sub |
Massiivi muutmine kogumikuks
Võib -olla soovite massiivi kogumikuks teisendada. Näiteks võite soovida andmetele juurde pääseda kiiremini ja elegantsemalt, kasutades massiivi elemendi saamiseks koodi.
Pidage meeles, et see toimib ainult massiivi ühe mõõtme puhul, kuna kogul on ainult üks mõõde
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection uue kollektsiooninaDim MyArray (2) stringinaMyArray (0) = "item1"MyArray (1) = "Üksus2"MyArray (2) = "Üksus3"Kui n = 0 kuni 2MyCollection. Lisa MyArray (n)Järgmine nIga üksuse kohta minu kollektsioonisMsgBoxi üksusJärgmine kaupEnd Sub |
Kui soovite kasutada mitmemõõtmelist massiivi, saate massiivi väärtused massiivi iga rea jaoks kokku ühendada, kasutades massiivi mõõtmete vahel eraldajat, nii et koguväärtuse lugemisel saaksite programmiliselt kasutada eraldusmärki eraldage väärtused.
Samuti võite andmed kogusse teisaldada selle alusel, et lisatakse esimese mõõtme väärtus (indeks 1) ja seejärel lisatakse järgmine dimensiooni väärtus (indeks 2) jne.
Kui massiivil oleks näiteks 4 dimensiooni, oleks kogu neljas väärtus uus väärtuste kogum.
Võid lisada võtmetena ka massiiviväärtusi (tingimusel, et need on ainulaadsed), mis annaks lihtsa viisi konkreetsete andmete leidmiseks.
