Kolekcija ir objekts, kurā glabājas vairāki līdzīgi priekšmeti. Tiem var viegli piekļūt un ar tiem manipulēt, pat ja kolekcijā ir liels skaits priekšmetu.
Ar Excel VBA jau ir iebūvētas kolekcijas. Piemērs ir Sheets kolekcija. Katrai darbgrāmatas darblapai ir vienums izklājlapu kolekcijā.
Iebūvētajām kolekcijām ir pieejams daudz vairāk īpašību un metožu, taču tās nav pieejamas jūsu izveidotajās kolekcijās.
Piemēram, jūs varat izmantot kolekciju, lai iegūtu informāciju par konkrētu darblapu. Piemēram, jūs varat redzēt darblapas nosaukumu un arī to, vai tā ir redzama vai nē. Izmantojot cilpu Katrai cilpai, varat atkārtot katru kolekcijas darblapu.
| 1234567 | Apakštesta darba lapas ()Dim Sh kā darblapaPar katru Sh In SheetsMsgBox Sh.NameMsgBox Sh.VisibleNākamais ŠBeigu apakš |
Varat arī adresēt konkrētu kolekcijas darblapu, izmantojot indeksa vērtību vai darblapas faktisko nosaukumu:
| 12 | MsgBox Sheets (1). NosaukumsMsgBox Sheets ("Sheet1"). Nosaukums |
Pievienojot vai dzēšot darblapas, izklājlapu kolekcija palielinās vai samazinās.
Ņemiet vērā, ka ar VBA kolekcijām indeksa numurs sākas ar 1, nevis ar 0
Kolekcijas pret masīviem
Masīvu un kolekciju funkcijas ir līdzīgas, jo tās abas ir metodes, kas ļauj uzglabāt lielu datu apjomu, uz kuru pēc tam var viegli atsaukties, izmantojot kodu. Tomēr tiem ir vairākas atšķirības to darbībā:
- Masīvi ir daudzdimensionāli, turpretī kolekcijas ir tikai viena dimensija. Jūs varat izmērīt masīvu ar vairākām dimensijām, piem.
| 1 | Dim MyArray (10, 2) kā virkne |
Tādējādi tiek izveidots 10 rindu masīvs ar 2 kolonnām, gandrīz kā darblapa. Kolekcija faktiski ir viena kolonna. Masīvs ir noderīgs, ja jums ir jāsaglabā vairāki datu elementi, kas attiecas viens uz otru, piem. vārds un adrese. Nosaukums būtu masīva pirmajā dimensijā un adrese otrajā dimensijā.
- Aizpildot masīvu, jums ir nepieciešama atsevišķa koda rinda, lai katrā masīva elementā ievietotu vērtību. Ja jums būtu divdimensiju masīvs, jums faktiski būtu vajadzīgas 2 koda rindas - viena rinda, lai adresētu pirmo kolonnu, un viena rinda, lai adresētu otro kolonnu. Izmantojot kolekcijas objektu, jūs vienkārši izmantojat pievienošanas metodi, lai jaunais vienums tikko tiktu pievienots kolekcijai un indeksa vērtība tiktu automātiski pielāgota atbilstoši.
- Ja jums ir jāizdzēš datu vienums, tad masīvā tas ir sarežģītāk. Jūs varat iestatīt elementa vērtības uz tukšu vērtību, bet pats elements joprojām pastāv masīvā. Ja jūs izmantojat cilni Nākotne, lai atkārtotu masīvu, cilpa atgriež tukšu vērtību, kurai būs nepieciešama kodēšana, lai pārliecinātos, ka tukšā vērtība tiek ignorēta. Kolekcijā tiek izmantotas metodes Pievienot vai Noņemt, un par visu indeksēšanu un izmēru maiņu tiek automātiski atbildēts. Noņemtais vienums pilnībā pazūd. Masīvi ir noderīgi noteikta lieluma datiem, bet kolekcijas ir labākas vietās, kur datu daudzums var mainīties.
- Kolekcijas ir tikai lasāmas, turpretī masīva vērtības var mainīt, izmantojot VBA. Izmantojot kolekciju, vispirms jānoņem maināmā vērtība un pēc tam jāpievieno jaunā mainītā vērtība.
- Masīvā elementiem varat izmantot tikai vienu datu tipu, kas tiek iestatīts, veicot masīva dimensiju. Tomēr masīvā varat izmantot pielāgotus datu veidus, kurus esat izstrādājis pats. Jums varētu būt ļoti sarežģīta masīva struktūra, izmantojot pielāgotu datu tipu, kam savukārt ir vairāki pielāgoti datu tipi. Kolekcijā katram vienumam varat pievienot lietošanas datu veidus. Jums var būt skaitliska vērtība, datums vai virkne - savākšanas objekts ņems jebkāda veida datus. Ja jūs mēģinātu ievietot virknes vērtību masīvā, kas tika klasificēts kā skaitlisks, tas parādītu kļūdas ziņojumu.
- Kolekcijas parasti ir vieglāk izmantot nekā masīvus. Kodēšanas ziņā, veidojot kolekcijas objektu, tam ir tikai divas metodes (pievienot un noņemt) un divi rekvizīti (skaits un vienums), tāpēc objekts nekādā ziņā nav sarežģīts programmēšanā.
- Kolekcijas var izmantot atslēgas, lai atrastu datus. Masīviem nav šīs funkcijas, un, lai atrastu noteiktas vērtības, masīvam ir jāatkārto kodēšanas kods.
- Masīva lielums ir jānosaka, kad tas pirmo reizi tiek izveidots. Jums ir jābūt priekšstatam par to, cik daudz datu tas glabās. Ja jums ir jāpalielina masīva izmērs, varat izmantot “ReDim”, lai to mainītu, bet jums ir jāizmanto atslēgvārds “Saglabāt”, ja nevēlaties zaudēt masīvā jau esošos datus. Kolekcijas lielums nav jānosaka. Tas vienkārši aug un sarūk automātiski, pievienojot vai noņemot vienumus.
Kolekcijas objekta darbības joma
Apjoma ziņā kolekcijas objekts ir pieejams tikai tad, kad darbgrāmata ir atvērta. Saglabājot darbgrāmatu, tā netiek saglabāta. Ja darbgrāmata tiek atkārtoti atvērta, kolekcija ir jāizveido atkārtoti, izmantojot VBA kodu.
Ja vēlaties, lai jūsu kolekcija būtu pieejama visiem kodiem jūsu koda modulī, kolekcijas objekts ir jādeklarē moduļa loga augšdaļā esošajā sadaļā Deklarēt
Tas nodrošinās, ka viss jūsu kods šajā modulī var piekļūt kolekcijai. Ja vēlaties, lai kāds darbgrāmatas modulis piekļūtu kolekcijai, definējiet to kā globālu objektu
| 1 | Globālā MyCollection kā jauna kolekcija |
Kolekcijas izveide, vienumu pievienošana un piekļūšana vienumiem
Vienkāršu kolekcijas objektu var izveidot VBA, izmantojot šādu kodu:
| 123456 | Sub CreateCollection ()Dim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2"Mana kolekcija. Pievienot vienumu3Beigu apakš |
Kods izmēra jaunu objektu ar nosaukumu “Mana kolekcija” un pēc tam sekojošās koda rindas izmanto metodi Pievienot, lai pievienotu 3 jaunas vērtības.
Pēc tam varat izmantot kodu, lai atkārtotu kolekciju, lai piekļūtu vērtībām
| 123 | Par katru vienumu MyCollectionMsgBox vienumsNākamais vienums |
Varat arī atkārtot savu kolekciju, izmantojot For Next Loop:
| 123 | Ja n = 1 uz MyCollection.CountMsgBox MyCollection (n)Nākamais n |
Kods iegūst kolekcijas lielumu, izmantojot rekvizītu Count, un pēc tam izmanto šo sākuma vērtību 1, lai indeksētu katru vienumu
Katra cilpa ir ātrāka par cilni Nākamā, taču tā darbojas tikai vienā virzienā (no zema indeksa līdz augstam). For Next Loop priekšrocība ir tā, ka jūs varat izmantot citu virzienu (no augsta indeksa līdz zemam), un jūs varat arī izmantot Step metodi, lai mainītu pieaugumu. Tas ir noderīgi, ja vēlaties dzēst vairākus vienumus, jo jums būs jāveic dzēšana no kolekcijas beigām līdz sākumam, jo, mainoties dzēšanai, indekss mainīsies.
Kolekcijas pievienošanas metodei ir 3 izvēles parametri - atslēga, pirms un pēc
Varat izmantot parametrus “Pirms” un “Pēc”, lai definētu jaunā vienuma pozīciju attiecībā pret citiem kolekcijā jau esošajiem.
Tas tiek darīts, norādot indeksa numuru, kuram vēlaties pievienot savu jauno vienumu.
| 123456 | Sub CreateCollection ()Dim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2",, 1Mana kolekcija. Pievienot vienumu3Beigu apakš |
Šajā piemērā ir norādīts, ka “Item2” jāpievieno pirms kolekcijas pirmā indeksētā vienuma (kas ir “Item1”). Atkārtojot šo kolekciju, vispirms tiks parādīts vienums2, kam seko vienums1 un vienums3
Norādot parametru “Pirms” vai “Pēc”, indeksa vērtība kolekcijā tiek automātiski pielāgota tā, lai “2. vienums” kļūtu par indeksa vērtību 1 un “Vienums 1” tiktu pārvietots uz indeksa vērtību 2
Varat arī izmantot parametru “Atslēga”, lai pievienotu atsauces vērtību, ko varat izmantot kolekcijas vienuma identificēšanai. Ņemiet vērā, ka atslēgas vērtībai ir jābūt virknei, un tai jābūt unikālai kolekcijā.
| 1234567 | Sub CreateCollection ()Dim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2", "MyKey"Mana kolekcija. Pievienot vienumu3MsgBox MyCollection ("MyKey")Beigu apakš |
Vienumam2 ir piešķirta atslēgas vērtība “MyKey”, lai jūs varētu atsaukties uz šo vienumu, izmantojot indeksa numura vietā vērtību “MyKey” (2)
Ņemiet vērā, ka atslēgas vērtībai ir jābūt virknes vērtībai. Tas nevar būt cita veida dati. Ņemiet vērā, ka kolekcija ir tikai lasāma un jūs nevarat atjaunināt atslēgas vērtību, tiklīdz tā ir iestatīta. Tāpat jūs nevarat pārbaudīt, vai kādai kolekcijas vienībai pastāv atslēgas vērtība, vai apskatīt atslēgas vērtību, kas ir neliels trūkums.
Parametram “Atslēga” ir papildu priekšrocība, jo tas padara jūsu kodu lasāmāku, it īpaši, ja tas tiek nodots kolēģim, lai to atbalstītu, un jums nav jāatkārto visa kolekcija, lai atrastu šo vērtību. Iedomājieties, ja jums būtu 10 000 priekšmetu kolekcija, cik grūti būtu atsaukties uz vienu konkrētu vienumu!
Vienuma noņemšana no kolekcijas
Lai dzēstu vienumus no savas kolekcijas, varat izmantot metodi “Noņemt”.
| 1 | Mana kolekcija. Noņemt (2) |
Diemžēl nav viegli, ja kolekcijā ir liels vienumu skaits, lai izstrādātu dzēšamā vienuma indeksu. Šeit ir noderīgs parametrs “Atslēga”, kad kolekcija tiek veidota
| 1 | MyCollection.Remove (“MyKey”) |
Kad vienums tiek noņemts no kolekcijas, indeksa vērtības tiek automātiski atiestatītas līdz kolekcijai. Šeit parametrs “Atslēga” ir tik noderīgs, ja vienlaikus dzēšat vairākus vienumus. Piemēram, jūs varat izdzēst vienumu indeksu 105, un uzreiz vienuma rādītājs 106 kļūst par indeksu 105, un visa virs šī vienuma indeksa vērtība tiek pārvietota uz leju. Ja izmantojat parametru Key, nav jāuztraucas par to, kura indeksa vērtība ir jānoņem.
Lai dzēstu visus kolekcijas vienumus un izveidotu jaunu kolekciju, vēlreiz izmantojiet priekšrakstu Dim, kas izveido tukšu kolekciju.
| 1 | Dim MyCollection kā jauna kolekcija |
Lai pilnībā noņemtu faktisko kolekcijas objektu, varat iestatīt objektu uz neko
| 1 | Set MyCollection = Nekas |
Tas ir noderīgi, ja jūsu kods vairs neprasa kolekciju. Kolekcijas objekta iestatīšana uz neko nenoņem visas atsauces uz to un atbrīvo izmantoto atmiņu. Tas var būtiski ietekmēt jūsu koda izpildes ātrumu, ja atmiņā sēž liels objekts, kas vairs nav nepieciešams.
Saskaitiet kolekcijas priekšmetu skaitu
Izmantojot rekvizītu “Skaits”, varat viegli uzzināt kolekcijas priekšmetu skaitu
| 1 | MsgBox MyCollection.Count |
Jūs izmantotu šo rekvizītu, ja kolekcijas atkārtošanai izmantotu funkciju “Nākamā cilpa”, jo tā nodrošinās indeksa numura augšējo robežu.
Konkrētas vērtības testa kolekcija
Jūs varat atkārtot kolekciju, lai meklētu vienumam noteiktu vērtību, izmantojot katrai cilpai
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2"Mana kolekcija. Pievienot vienumu3Par katru vienumu MyCollectionJa vienums = "Vienums2" TadMsgBox vienums un "Atrasts"Beigas JaNākamaisBeigu apakš |
Kods izveido nelielu kolekciju un pēc tam to atkārto, meklējot vienumu ar nosaukumu “item2”. Ja tas tiek atrasts, tiek parādīts ziņojuma lodziņš, ka tas ir atradis konkrēto vienumu
Viens no šīs metodikas trūkumiem ir tas, ka jūs nevarat piekļūt indeksa vērtībai vai atslēgas vērtībai
Ja tā vietā izmantojat cilpu “Nākamā cilpa”, varat izmantot skaitītāju “Nākamais”, lai iegūtu indeksa vērtību, lai gan jūs joprojām nevarat iegūt vērtību “Atslēga”
| 123456789101112 | Sub SearchCollection ()Dim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2"Mana kolekcija. Pievienot vienumu3Ja n = 1 uz MyCollection.CountJa MyCollection.Item (n) = "Item2" TadMsgBox MyCollection.Item (n) & "found on index position" & nBeigas JaNākamais nBeigu apakš |
Nākamais skaitītājs (n) nodrošinās indeksa pozīciju
Kolekcijas kārtošana
Kolekcijas kārtošanai nav iebūvētas funkcionalitātes, taču, izmantojot domāšanas veidu, var rakstīt kodu, lai veiktu kārtošanu, izmantojot Excel darblapu šķirošanas funkciju. Šis kods izmanto tukšu darblapu ar nosaukumu “SortSheet”, lai veiktu faktisko šķirošanu.
| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152 | Apakššķirot ()Dim MyCollection kā jauna kolekcijaDim skaitītājs tik ilgi“Veidojiet kolekciju ar nejaušas secības vienumiemMana kolekcija. Pievienot vienumu5MyCollection.Add "Item2"MyCollection.Pievienot "Item4"Mana kolekcija. Pievienot vienumu1Mana kolekcija. Pievienot vienumu3“Uzņemiet kolekcijā esošo priekšmetu skaitu turpmākai lietošanaiSkaitītājs = MyCollection.Count“Atkārtojiet kolekciju, kopējot katru vienumu“ SortSheet ”secīgā šūnā (A sleja)Ja n = 1 uz MyCollection.CountIzklājlapas ("SortSheet"). Šūnas (n, 1) = Mana kolekcija (n)Nākamais n“Aktivizējiet šķirnes lapu un izmantojiet Excel kārtošanas kārtību, lai sakārtotu datus augošā secībāIzklājlapas ("SortSheet"). AktivizētDiapazons ("A1: A" un MyCollection.Count). AtlasietActiveWorkbook.Worksheets ("SortSheet"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("SortSheet"). Kārtot.SortFields.Add2 atslēga: = Diapazons (_"A1: A5"), SortOn: = xlSortOnValues, secība: = xlAscending, DataOption: = _xlSortNormalAr ActiveWorkbook.Worksheets ("SortSheet"). Kārtot.SetRange diapazons ("A1: A5").Galva = xlGuess.MatchCase = Nepareizi.Orientation = xlTopToBottom.SortMethod = xlPinYin.PiesakiesBeigt ar“Dzēsiet visus kolekcijas vienumus - ņemiet vērā, ka šī sadaļa“ Nākamā cilpa ”darbojas apgrieztā secībāJa n = Mana kolekcija. Saskaitiet līdz 1. solim -1Mana kolekcija. Noņemt (n)Nākamais n“Kopējiet šūnu vērtības atpakaļ tukšajā savākšanas objektā, izmantojot cilpas saglabāto vērtību (skaitītāju)Par n = 1 uz skaitītājuMyCollection.Add Sheets ("SortSheet"). Šūnas (n, 1). VērtībaNākamais n“Atkārtojiet kolekciju, lai pierādītu kārtību, kādā preces tagad atrodasPar katru vienumu MyCollectionMsgBox vienumsNākamais vienums“Notīriet darblapu (šķirnes lapu) - ja nepieciešams, izdzēsiet arī toIzklājlapas ("SortSheet"). Diapazons (šūnas (1, 1), šūnas (skaitītājs, 1)). SkaidrsBeigu apakš |
Šis kods vispirms izveido kolekciju ar vienumiem, kas pievienoti nejaušā secībā. Pēc tam tā tos kopē darblapas pirmajā kolonnā (SortSheet).
Pēc tam kods izmanto Excel kārtošanas funkciju, lai slejā esošos datus sakārtotu augošā secībā. Kodu var arī mainīt, lai sakārtotu dilstošā secībā.
Pēc tam kolekcija tiek iztukšota no datiem, izmantojot For Next Loop. Ņemiet vērā, ka soļa opcija tiek izmantota, lai tā tiktu notīrīta no kolekcijas beigām līdz sākumam. Tas notiek tāpēc, ka, dzēšot, indeksa vērtības tiek atiestatītas; ja tas tiktu notīrīts no sākuma, tas netiktu notīrīts pareizi (2. indekss kļūtu par indeksu 1)
Visbeidzot, izmantojot citu For Next Loop, vienumu vērtības tiek nodotas atpakaļ tukšajā kolekcijā
Vēl viena par katru cilpu pierāda, ka kolekcija tagad ir labā augošā secībā.
Diemžēl tas neattiecas uz galvenajām vērtībām, kas varētu būt ievadītas sākotnēji, jo tās nevar nolasīt
Kolekcijas nodošana apakš / funkcijai
Kolekciju var nodot apakšvienībai vai funkcijai tāpat kā jebkuru citu parametru
| 1 | Funkcija MyFunction (ByRef MyCollection kā kolekcija) |
Ir svarīgi nodot kolekciju, izmantojot “ByRef”. Tas nozīmē, ka tiek izmantota oriģinālā kolekcija. Ja kolekcija tiek nodota, izmantojot “ByVal”, tiek izveidota kolekcijas kopija, kurai var būt nepatīkamas sekas
Ja kopija tiek izveidota, izmantojot “ByVal”, viss, kas maina kolekciju funkcijas ietvaros, notiek tikai uz kopijas, nevis uz oriģināla. Piemēram, ja funkcijas ietvaros kolekcijai tiek pievienots jauns vienums, tas netiks parādīts sākotnējā kolekcijā, kas radīs kļūdu jūsu kodā.
Kolekcijas atgriešana no funkcijas
Jūs varat atgriezt kolekciju no funkcijas tāpat kā jebkura objekta atgriešanu. Jums ir jāizmanto atslēgvārds
| 12345 | Sub ReturnFromFunction ()Dim MyCollection kā kolekcijaIestatīt MyCollection = PopulateCollectionMsgBox MyCollection.CountBeigu apakš |
Šis kods izveido apakšprogrammu, kas izveido objektu ar nosaukumu “Mana kolekcija” un pēc tam izmanto atslēgvārdu “Iestatīt”, lai efektīvi izsauktu funkciju, lai aizpildītu šo kolekciju. Kad tas ir izdarīts, tiek parādīts ziņojumu lodziņš, kurā parādīts 2 vienumu skaits
| 1234567 | Funkcija PopulateCollection () kā kolekcijaDim MyCollection kā jauna kolekcijaMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2"Iestatīt PopulateCollection = Mana kolekcijaBeigu funkcija |
Funkcija PopulateCollection izveido jaunu kolekcijas objektu un aizpilda to ar 2 vienumiem. Pēc tam tas nodod šo objektu atpakaļ kolekcijas objektam, kas izveidots sākotnējā apakšprogrammā.
Kolekcijas pārvēršana masīvā
Iespējams, vēlēsities pārvērst savu kolekciju masīvā. Iespējams, vēlēsities saglabāt datus tur, kur tos varēs mainīt un ar tiem manipulēt. Šis kods izveido nelielu kolekciju un pēc tam pārsūta to masīvā
Ņemiet vērā, ka kolekcijas indekss sākas ar 1, bet masīva indekss sākas ar 0. Kamēr kolekcijā ir 3 vienumi, masīvam ir jābūt tikai izmēriem līdz 2, jo ir elements 0
| 1234567891011121314151617 | Sub ConvertCollectionToArray ()Dim MyCollection kā jauna kolekcijaDim MyArray (2) kā virkneMana kolekcija. Pievienot vienumu1MyCollection.Add "Item2"Mana kolekcija. Pievienot vienumu3Ja n = 1 uz MyCollection.CountMyArray (n - 1) = Mana kolekcija (n)Nākamais nJa n = 0 līdz 2MsgBox MyArray (n)Nākamais nBeigu apakš |
Masīva pārveidošana par kolekciju
Iespējams, vēlēsities masīvu pārvērst kolekcijā. Piemēram, iespējams, vēlēsities piekļūt datiem ātrāk un elegantāk, izmantojot kodu, lai iegūtu masīva elementu.
Paturiet prātā, ka tas darbosies tikai vienai masīva dimensijai, jo kolekcijai ir tikai viena dimensija
| 123456789101112131415 | Sub ConvertArrayIntoCollection ()Dim MyCollection kā jauna kolekcijaDim MyArray (2) kā virkneMyArray (0) = "item1"MyArray (1) = "Vienums2"MyArray (2) = "3. vienums"Ja n = 0 līdz 2MyCollection. Pievienot MyArray (n)Nākamais nPar katru vienumu MyCollectionMsgBox vienumsNākamais vienumsBeigu apakš |
Ja vēlaties izmantot daudzdimensiju masīvu, varat sasaistīt masīva vērtības katrai masīva rindai, izmantojot atdalītāja rakstzīmi starp masīva izmēriem, lai, lasot kolekcijas vērtību, varētu programmiski izmantot atdalītāja rakstzīmi, lai atdaliet vērtības.
Jūs varētu arī pārvietot datus kolekcijā, pamatojoties uz to, ka tiek pievienota pirmās dimensijas vērtība (1. indekss) un pēc tam tiek pievienota nākamās kategorijas vērtība (2. indekss) utt.
Ja masīvam būtu, teiksim, 4 dimensijas, katra ceturtā kolekcijas vērtība būtu jauna vērtību kopa.
Varat arī pievienot masīva vērtības, ko izmantot kā atslēgas (ar nosacījumu, ka tās ir unikālas), kas pievienotu vienkāršu veidu, kā atrast konkrētus datus.
