Kokoelma on esine, joka sisältää useita vastaavia esineitä. Näihin pääsee helposti käsiksi ja niitä voidaan käsitellä, vaikka kokoelmassa olisi suuri määrä esineitä.
Excel VBA: lla on jo valmiita kokoelmia. Esimerkki on Sheets -kokoelma. Jokaisessa työkirjan laskentataulukossa on kohde Sheets -kokoelmassa.
Sisäänrakennetuilla kokoelmilla on paljon enemmän ominaisuuksia ja menetelmiä, mutta ne eivät ole käytettävissä luomissasi kokoelmissa.
Voit esimerkiksi käyttää kokoelmaa saadaksesi tietoja tietystä laskentataulukosta. Näet esimerkiksi laskentataulukon nimen ja sen, onko se näkyvissä vai ei. Käyttämällä jokaiselle silmukalle voit iteroida kokoelman jokaisen laskentataulukon.
| 1234567 | Alatestityöarkit ()Dim Sh kuten laskentataulukkoJokaiselle arkilleMsgBox Sh.NameMsgBox Sh.VisibleSeuraava ShEnd Sub |
Voit myös osoittaa tietyn kokoelman laskentataulukon käyttämällä indeksin arvoa tai laskentataulukon varsinaista nimeä:
| 12 | MsgBox Sheets (1) NimiMsgBox Sheets ("Sheet1"). Nimi |
Kun laskentataulukoita lisätään tai poistetaan, Sheets -kokoelma kasvaa tai pienenee.
Huomaa, että VBA -kokoelmissa hakemistonumero alkaa 1 eikä 0
Kokoelmat ja taulukot
Taulukot ja kokoelmat ovat toiminnoiltaan samankaltaisia, koska ne ovat molemmat menetelmiä, joiden avulla voidaan tallentaa suuri määrä dataa, johon voidaan sitten viitata helposti koodilla. Niillä on kuitenkin useita eroja niiden toiminnassa:
- Taulukot ovat moniulotteisia, kun taas kokoelmat ovat vain yksi ulottuvuus. Voit mitoittaa taulukon, jossa on useita ulottuvuuksia, esim.
| 1 | Dim MyArray (10, 2) merkkijonona |
Tämä luo 10 rivin taulukon, jossa on 2 saraketta, melkein kuin laskentataulukko. Kokoelma on käytännössä yksi sarake. Matriisi on hyödyllinen, jos sinun on tallennettava useita toisiinsa liittyviä tietoja, esim. nimi ja osoite. Nimi olisi taulukon ensimmäisessä ulottuvuudessa ja osoite toisessa ulottuvuudessa.
- Kun täytät matriisi, tarvitset erillisen koodirivin arvon lisäämiseksi jokaiseen taulukon elementtiin. Jos sinulla olisi kaksiulotteinen taulukko, tarvitset itse asiassa 2 riviä koodia - yhden rivin ensimmäisen sarakkeen ja toisen rivin toisen sarakkeen osoittamiseksi. Kokoelma -objektin kanssa käytät vain Lisää -menetelmää, jotta uusi kohde lisätään kokoelmaan ja indeksin arvo säädetään automaattisesti sopivaksi.
- Jos sinun on poistettava tieto, se on monimutkaisempi taulukossa. Voit asettaa elementin arvot tyhjäksi, mutta itse elementti on edelleen matriisissa. Jos käytät For Next -silmukkaa taulukon toistamiseen, silmukka palauttaa tyhjän arvon, joka tarvitsee koodausta varmistaakseen, että tyhjä arvo jätetään huomiotta. Kokoelmassa käytät Lisää tai Poista-menetelmiä, ja kaikki indeksointi ja koon muuttaminen hoidetaan automaattisesti. Poistettu kohde katoaa kokonaan. Taulukot ovat hyödyllisiä tietyn kokoisille tiedoille, mutta kokoelmat ovat parempia siellä, missä tietomäärä voi muuttua.
- Kokoelmat ovat vain luku -tiheitä, kun taas taulukon arvoja voidaan muuttaa VBA: n avulla. Kokoelman kanssa sinun on ensin poistettava muutettava arvo ja lisättävä sitten uusi muutettu arvo.
- Taulukossa voit käyttää vain yhtä tietotyyppiä elementeille, joka asetetaan taulukon mitoituksen yhteydessä. Voit kuitenkin käyttää taulukossa mukautettuja tietotyyppejä, jotka olet itse suunnitellut. Sinulla voi olla hyvin monimutkainen taulukkorakenne käyttämällä mukautettua tietotyyppiä, jonka alla on useita muokattuja tietotyyppejä. Kokoelmaan voit lisätä käytön tietotyyppejä jokaiselle kohteelle. Sinulla voi olla numeerinen arvo, päivämäärä tai merkkijono - kokoelmaobjekti ottaa minkä tahansa tietotyypin. Jos yritit laittaa merkkijonon arvon taulukkoon, joka on mitoitettu numeeriseksi, se tuottaa virheilmoituksen.
- Kokoelmat ovat yleensä helpompia käyttää kuin taulukot. Koodauksen kannalta, kun luot kokoelmaobjektin, sillä on vain kaksi tapaa (Lisää ja poista) ja kaksi ominaisuutta (Laske ja Kohde), joten objekti ei ole mitenkään monimutkainen ohjelmoitavaksi.
- Kokoelmat voivat etsiä tietoja avaimilla. Taulukoilla ei ole tätä toimintoa ja ne tarvitsevat silmukkamerkkiä iteroimaan taulukon läpi tiettyjen arvojen löytämiseksi.
- Taulukon koko on määritettävä, kun se luodaan ensimmäisen kerran. Sinulla on oltava käsitys siitä, kuinka paljon tietoja se aikoo tallentaa. Jos haluat lisätä taulukon kokoa, voit muuttaa sen kokoa käyttämällä ReDim-koodia, mutta sinun on käytettävä avainsanaa Säilytä, jos et halua kadottaa taulukossa jo olevia tietoja. Kokoelman kokoa ei tarvitse määrittää. Se vain kasvaa ja kutistuu automaattisesti, kun kohteita lisätään tai poistetaan.
Keräilykohteen laajuus
Kokoelman kannalta kokoelmaobjekti on käytettävissä vain työkirjan ollessa auki. Sitä ei tallenneta, kun työkirja tallennetaan. Jos työkirja avataan uudelleen, kokoelma on luotava uudelleen käyttämällä VBA-koodia.
Jos haluat kokoelmasi olevan kaikkien koodimoduulin koodin käytettävissä, sinun on ilmoitettava kokoelmaobjekti moduuli -ikkunan yläosan Ilmoita -osiosta
Näin varmistat, että koko moduulisi koodi voi käyttää kokoelmaa. Jos haluat minkä tahansa työkirjasi moduulin käyttää kokoelmaa, määritä se globaaliksi objektiksi
| 1 | Globaali MyCollection uutena kokoelmana |
Kokoelman luominen, kohteiden lisääminen ja kohteiden käyttäminen
Yksinkertainen kokoelmaobjekti voidaan luoda VBA: ssa käyttämällä seuraavaa koodia:
| 123456 | Sub CreateCollection ()Dim MyCollection uutena kokoelmanaMyCollection.Lisää "Item1"MyCollection.Add "Item2"Oma kokoelma.Lisää "Item3"End Sub |
Koodi mittaa uuden objektin nimeltä "MyCollection" ja sitten seuraavat koodirivit lisäävät 3 uutta arvoa Add -menetelmällä.
Voit sitten käyttää koodia iteraatioon kokoelmassasi päästäksesi arvoihin
| 123 | Jokaiselle MyCollectionin kohteelleMsgBox -kohdeSeuraava kohde |
Voit myös toistaa kokoelmasi For For For Loop -sovelluksella:
| 123 | Jos n = 1 MyCollection.CountMsgBox MyCollection (n)Seuraava n |
Koodi saa kokoelman koon käyttämällä Count -ominaisuutta ja käyttää sitten tätä alkuarvoa 1 kunkin kohteen indeksointiin
Jokaiselle silmukalle on nopeampi kuin Seuraava -silmukka, mutta se toimii vain yhteen suuntaan (matala indeksi korkeaan). For Next Loopin etuna on se, että voit käyttää eri suuntaa (korkea indeksi matalaan) ja voit myös käyttää Step -menetelmää muuttaaksesi lisäystä. Tästä on hyötyä, kun haluat poistaa useita kohteita, koska sinun on suoritettava poisto kokoelman lopusta alkuun, koska indeksi muuttuu poistojen tapahtuessa.
Kokoelman Lisää -menetelmällä on kolme valinnaista parametria - Avain, Ennen ja Jälkeen
Voit käyttää parametreja "Ennen" ja "Jälkeen" määritelläksesi uuden kohteen sijainnin suhteessa muihin jo kokoelmassa oleviin
Tämä tehdään määrittämällä indeksinumero, johon haluat uuden kohteen liittyvän.
| 123456 | Sub CreateCollection ()Dim MyCollection uutena kokoelmanaOma kokoelma.Lisää "Item1"MyCollection.Add "Item2",, 1Oma kokoelma.Lisää "Item3"End Sub |
Tässä esimerkissä kohde 2 on määritetty lisättäväksi ennen kokoelman ensimmäistä indeksoitua kohdetta (nimike 1). Kun selaat tätä kokoelmaa, näkyviin tulee ensin "Item2", jota seuraa "Item1" ja "Item3"
Kun määrität "Ennen" tai "Jälkeen" -parametrin, indeksin arvoa säädetään automaattisesti kokoelmassa siten, että "Kohta2" muuttuu indeksiarvoksi 1 ja "Kohde1" siirretään indeksiarvoon 2
Voit myös käyttää Avain -parametria lisätäksesi viitearvon, jolla voit tunnistaa kokoelman. Huomaa, että avaimen arvon on oltava merkkijono ja sen on oltava yksilöllinen kokoelmassa.
| 1234567 | Sub CreateCollection ()Dim MyCollection uutena kokoelmanaMyCollection.Lisää "Item1"MyCollection.Add "Item2", "MyKey"Oma kokoelma.Lisää "Item3"MsgBox MyCollection ("MyKey")End Sub |
"Item2" on saanut "Key" -arvon "MyKey", jotta voit viitata siihen käyttämällä MyKey -arvoa indeksinumeron sijasta (2)
Huomaa, että Avaimen arvon on oltava merkkijono. Se ei voi olla muuta tietotyyppiä. Huomaa, että kokoelma on vain luku, etkä voi päivittää avaimen arvoa, kun se on asetettu. Et myöskään voi tarkistaa, onko tietylle kokoelman kohteelle olemassa avainarvo, tai tarkastella avainarvoa, joka on hieman haittapuoli.
Avain -parametrin etuna on se, että se tekee koodistasi luettavamman, varsinkin jos se luovutetaan kollegallesi tukemaan, eikä sinun tarvitse toistaa koko kokoelmaa löytääksesi arvo. Kuvittele, jos sinulla olisi 10 000 esineen kokoelma, kuinka vaikeaa olisi viitata yhteen tiettyyn kohteeseen!
Kohteen poistaminen kokoelmasta
Voit poistaa kohteita kokoelmastasi Poista -menetelmällä.
| 1 | MyCollection.Remove (2) |
Valitettavasti ei ole helppoa, jos kokoelmassa on suuri määrä kohteita poistettavan kohteen indeksin määrittämiseksi. Tässä on avainparametri hyödyllinen kokoelman luomisen yhteydessä
| 1 | MyCollection.Remove (“MyKey”) |
Kun kohde poistetaan kokoelmasta, indeksiarvot nollataan automaattisesti kokoelman läpi. Tässä on avainparametri niin hyödyllinen, kun poistat useita kohteita kerralla. Voit esimerkiksi poistaa nimikeindeksin 105 ja kohdeindeksistä 106 tulee heti indeksi 105, ja kaiken tämän kohteen yläpuolella oleva indeksin arvo siirretään alaspäin. Jos käytät avainparametria, sinun ei tarvitse huolehtia siitä, mikä indeksin arvo on poistettava.
Voit poistaa kaikki kokoelman kohteet ja luoda uuden kokoelman käyttämällä Dim -lauseketta uudelleen, joka luo tyhjän kokoelman.
| 1 | Dim MyCollection uutena kokoelmana |
Jos haluat poistaa kokoelmaobjektin kokonaan, voit asettaa objektin tyhjäksi
| 1 | Aseta oma keräys = ei mitään |
Tästä on hyötyä, jos koodisi ei enää vaadi keräystä. Kokoelma -objektin asettaminen tyhjäksi poistaa kaikki viittaukset siihen ja vapauttaa sen käyttämän muistin. Tällä voi olla merkittäviä vaikutuksia koodin suorittamisen nopeuteen, jos muistissa on suuri objekti, jota ei enää tarvita.
Laske kokoelman kohteiden määrä
Voit helposti selvittää kokoelmasi kohteiden lukumäärän käyttämällä Count -ominaisuutta
| 1 | MsgBox MyCollection.Count |
Käytät tätä ominaisuutta, jos käytit For Next Loop -sarjaa kokoelman toistamiseen, koska se antaa sinulle indeksinumeron ylärajan.
Testikokoelma tietylle arvolle
Voit toistaa kokoelman ja etsiä kohteelle tietyn arvon käyttämällä jokaista silmukkaa
| 123456789101112 | Alahakukokoelma ()Dim MyCollection uutena kokoelmanaMyCollection.Lisää "Item1"MyCollection.Add "Item2"Oma kokoelma.Lisää "Item3"Jokaiselle MyCollectionin kohteelleIf Item = "Item2" SittenMsgBox -kohde ja "Löytyi"Loppu JosSeuraavaEnd Sub |
Koodi luo pienen kokoelman ja toistaa sen läpi etsiessään nimikettä ”item2”. Jos se löytyy, se näyttää viestiruudun, että se on löytänyt tietyn kohteen
Yksi tämän menetelmän haitoista on, että et voi käyttää indeksi- tai avainarvoa
Jos käytät sen sijaan For For Next Loopia, voit hakea Indeksiarvon For Next -laskurilla, vaikka et vieläkään voi saada Key -arvoa
| 123456789101112 | Alahakukokoelma ()Dim MyCollection uutena kokoelmanaMyCollection.Lisää "Item1"MyCollection.Add "Item2"Oma kokoelma.Lisää "Item3"Jos n = 1 MyCollection.CountJos MyCollection.Item (n) = "Item2" SittenMsgBox MyCollection.Item (n) & "found in index position" & nLoppu JosSeuraava nEnd Sub |
Seuraavaksi -laskuri (n) antaa indeksin sijainnin
Kokoelman lajittelu
Kokoelman lajitteluun ei ole sisäänrakennettuja toimintoja, mutta käyttämällä jotakin "out of the box" -ajattelua, koodi voidaan kirjoittaa lajittelua varten käyttämällä Excelin laskentataulukon lajittelutoimintoa. Tämä koodi käyttää tyhjää laskentataulukkoa nimeltä "SortSheet" varsinaisen lajittelun suorittamiseksi.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | AlalajitteluKokoelma ()Dim MyCollection uutena kokoelmanaHimmennyslaskuri yhtä pitkä"Rakenna kokoelma satunnaisjärjestyksessäOma kokoelma.Lisää "Item5"MyCollection.Add "Item2"Oma kokoelma.Lisää "Item4"Oma kokoelma.Lisää "Item1"Oma kokoelma.Lisää "Item3""Kerää kokoelman kohteiden määrä tulevaa käyttöä vartenLaskuri = MyCollection.Count"Toista kokoelma kopioimalla jokainen kohde" SortSheet "-sarakkeen peräkkäiseen soluun (sarake A)Jos n = 1 MyCollection.CountArkit ("SortSheet"). Solut (n, 1) = Oma kokoelma (n)Seuraava nAktivoi lajittelutaulukko ja käytä Excel -lajittelurutiinia tietojen lajittelemiseen nousevaan järjestykseenSheets ("SortSheet"). AktivoiAlue ("A1: A" & MyCollection.Count) .ValitseActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.Add2 Key: = Alue (_"A1: A5"), SortOn: = xlSortOnValues, Järjestys: = xlAscending, DataOption: = _xlSortNormalActiveWorkbook.Worksheets ("SortSheet").SetRange Range ("A1: A5").Header = xlGuess.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.KäytäLopeta"Poista kaikki kokoelman kohteet - huomaa, että tämä For Next Loop suoritetaan päinvastaisessa järjestyksessäJos n = Oma kokoelma. Laske vaiheeseen 1MyCollection.Remove (n)Seuraava n"Kopioi solujen arvot takaisin tyhjään kokoelmaobjektiin käyttämällä silmukan tallennettua arvoa (laskuria)Jos n = 1 laskuriinMyCollection.Add Sheets ("SortSheet"). Solut (n, 1) .ArvoSeuraava n'Selaa kokoelmaa todistaaksesi, missä järjestyksessä tuotteet ovat nytJokaiselle MyCollectionin kohteelleMsgBox -kohdeSeuraava kohdeTyhjennä laskentataulukko (lajittelutaulukko) - poista se tarvittaessa myösArkit ("SortSheet"). Alue (Solut (1, 1), Solut (Laskuri, 1)). KirkasEnd Sub |
Tämä koodi luo ensin kokoelman, jossa kohteet lisätään satunnaisessa järjestyksessä. Sitten se kopioi ne laskentataulukon (SortSheet) ensimmäiseen sarakkeeseen.
Koodi käyttää sitten Excel -lajittelutoimintoa sarakkeen tietojen lajittelemiseen nousevaan järjestykseen. Koodia voidaan myös muokata laskevaan järjestykseen.
Kokoelma tyhjennetään sitten tiedoista For Next Loop -sovelluksella. Huomaa, että askelvaihtoehtoa käytetään siten, että se tyhjenee kokoelman lopusta alusta. Tämä johtuu siitä, että kun se tyhjenee, indeksiarvot nollataan, jos se tyhjennetään alusta alkaen, se ei tyhjene oikein (indeksi 2 muuttuu indeksiksi 1)
Lopuksi toisen For Next Loop -sovelluksen avulla tuotearvot siirretään takaisin tyhjään kokoelmaan
Vielä yksi jokaiselle silmukalle osoittaa, että kokoelma on nyt hyvässä nousevassa järjestyksessä.
Valitettavasti tämä ei käsittele alun perin syötettyjä avainarvoja, koska avainarvoja ei voi lukea
Kokoelman siirtäminen ala- / toimintoon
Kokoelma voidaan välittää ali- tai funktiolle samalla tavalla kuin mikä tahansa muu parametri
| 1 | Toiminto MyFunction (ByRef MyCollection as Collection) |
On tärkeää välittää kokoelma ByRefin avulla. Tämä tarkoittaa, että käytetään alkuperäistä kokoelmaa. Jos kokoelma välitetään ByValilla, tämä luo kokoelmasta kopion, jolla voi olla valitettavia vaikutuksia
Jos kopio luodaan ByVal -ohjelmalla, kaikki toiminnon kokoelmaa muuttavat asiat tapahtuvat vain kopiossa eikä alkuperäisessä. Jos funktion sisällä esimerkiksi lisätään kokoelmaan uusi kohde, se ei näy alkuperäisessä kokoelmassa, mikä luo virheen koodissasi.
Kokoelman palauttaminen toiminnosta
Voit palauttaa kokoelman toiminnosta samalla tavalla kuin minkä tahansa objektin palauttamisen. Sinun on käytettävä Set -avainsanaa
| 12345 | Sub ReturnFromFunction ()Dim MyCollection kokoelmanaAseta MyCollection = PopulateCollectionMsgBox MyCollection.CountEnd Sub |
Tämä koodi luo alirutiinin, joka luo "MyCollection" -objektin ja käyttää sitten "Set" -avainsanaa kutsuakseen toiminnon tehokkaasti kokoelman täyttämiseen. Kun tämä on tehty, se näyttää viestiruudun, jossa näkyy 2 kohteen määrä
| 1234567 | Toiminto PopulateCollection () kokoelmanaDim MyCollection uutena kokoelmanaMyCollection.Lisää "Item1"MyCollection.Add "Item2"Aseta PopulateCollection = Oma kokoelmaLopeta toiminto |
Funktio PopulateCollection luo uuden kokoelmaobjektin ja täyttää sen kahdella kohteella. Sitten se siirtää tämän objektin takaisin alkuperäisessä alirutiinissa luotulle kokoelmaobjektille.
Kokoelman muuntaminen taulukkoksi
Voit halutessasi muuntaa kokoelmasi taulukkoksi. Voit halutessasi tallentaa tiedot sinne, missä niitä voidaan muuttaa ja käsitellä. Tämä koodi luo pienen kokoelman ja siirtää sen sitten taulukkoon
Huomaa, että kokoelmaindeksi alkaa yhdestä, kun taas taulukkoindeksi alkaa nollasta. Kokoelmassa on 3 kohdetta, mutta taulukko on mitoitettava vain 2: ksi, koska siinä on elementti 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection uutena kokoelmanaDim MyArray (2) merkkijononaMyCollection.Lisää "Item1"MyCollection.Add "Item2"Oma kokoelma.Lisää "Item3"Jos n = 1 MyCollection.CountMyArray (n - 1) = Oma kokoelma (n)Seuraava nJos n = 0 - 2MsgBox MyArray (n)Seuraava nEnd Sub |
Taulukon muuttaminen kokoelmaksi
Voit halutessasi muuntaa taulukon kokoelmaksi. Voit esimerkiksi haluta käyttää tietoja nopeammin ja tyylikkäämmin kuin käyttämällä koodia saadaksesi taulukkoelementin.
Muista, että tämä toimii vain taulukon yhdelle ulottuvuudelle, koska kokoelmalla on vain yksi ulottuvuus
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection uutena kokoelmanaDim MyArray (2) merkkijononaMyArray (0) = "item1"MyArray (1) = "Tuote2"MyArray (2) = "Tuote3"Jos n = 0 - 2Lisää MyArray (n)Seuraava nJokaiselle MyCollection -kohteelleMsgBox -kohdeSeuraava kohdeEnd Sub |
Jos haluat käyttää moniulotteista taulukkoa, voit yhdistää taulukon arvot yhteen taulukon jokaiselle riville käyttämällä erotinmerkkiä taulukon ulottuvuuksien välillä, jotta voit lukea ohjelmallisesti erotusmerkkiä lukiessasi kokoelman arvoa erota arvot.
Voit myös siirtää tiedot kokoelmaan sillä perusteella, että ensimmäisen ulottuvuuden arvo lisätään (indeksi 1) ja sitten seuraava ulottuvuuden arvo (indeksi 2) ja niin edelleen.
Jos matriisissa olisi esimerkiksi neljä ulottuvuutta, kokoelman joka neljäs arvo olisi uusi arvojoukko.
Voit myös lisätä taulukkoarvoja avaimiksi (edellyttäen, että ne ovat yksilöllisiä), mikä lisäisi helpon tavan löytää tiettyjä tietoja.
