Vo VBA, an Array je jedna premenná, ktorá môže obsahovať viacero hodnôt. Myslite na pole ako na rozsah buniek: každá bunka môže uložiť hodnotu. Polia môžu byť jednorozmerné (myslíme na jeden stĺpec), dvojrozmerné (myslíme na viac riadkov a stĺpcov) alebo viacrozmerné. K hodnotám poľa je možné získať prístup podľa ich polohy (indexového čísla) v poli.
Rýchly list poľa VBA
Polia
PopisKód VBAVytvoriťDim ar (1 až 3) ako variantarr (1) = „jeden“
arr (2) = „dva“
arr (3) = „tri“Vytvoriť z ExceluDim ar (1 až 3) ako variant
Dim bunka ako rozsah, aj ako celé číslo
i = LViazané (arr)
Pre každú bunku v rozsahu („A1: A3“)
i = i + 1
arr (i) = hodnota bunky
Ďalšia bunkaPrečítajte si všetky položkyDim i as Long
For i = LBound (arr) To UBound (arr)
MsgBox arr (i)
Ďalej iVymazaťVymazať arrPole na reťazecDim sName As String
sName = Pripojte sa (arr, „:“)Zväčšiť veľkosťZachovať aretáciu ReDim (0 až 100)Nastaviť hodnotuarr (1) = 22
Rýchle príklady poľa VBA
Pozrime sa na úplný príklad, než sa ponoríme do špecifík:
| 12345678910 | Sub ArrayPríklad ()Dim strNames (1 až 4) ako reťazecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)Koniec pod |
Tu sme vytvorili jednorozmerné pole reťazcov: strNames s veľkosťou štyri (môže obsahovať štyri hodnoty) a priradili sme štyri hodnoty. Nakoniec zobrazíme tretiu hodnotu v poli správ.
V tomto prípade je výhoda použitia poľa malá: namiesto štyroch sa vyžaduje iba jedna deklarácia premennej.
Pozrime sa však na príklad, ktorý ukáže skutočnú silu poľa:
| 12345678 | Sub ArrayExample2 ()Dim strNames (1 až 60 000) ako reťazecDim i As LongPre i = 1 až 60 000strNames (i) = Bunky (i, 1). HodnotaĎalej iKoniec pod |
Tu sme vytvorili pole, ktoré môže obsahovať 60 000 hodnôt, a pole sme rýchlo vyplnili zo stĺpca A pracovného hárka.
Výhody poľa? - Rýchlosť!
Môžete si myslieť na polia podobné pracovným listom programu Excel:
- Každá bunka (alebo položka v poli) môže obsahovať svoju vlastnú hodnotu
- Ku každej bunke (alebo položke v poli) je možné pristupovať podľa polohy riadka a stĺpca.
- Pracovný list Pr. bunky (1,4) .hodnota = „Riadok 1, Stĺpec 4“
- Pole Ex. arrVar (1,4) = „Riadok 1, stĺpec 4“
Prečo sa teda trápiť s poliami? Prečo jednoducho nečítate a nepíšete hodnoty priamo do buniek v programe Excel? Jedno slovo: Rýchlosť!
Čítanie / zápis do buniek programu Excel je pomalý proces. Práca s poliami je oveľa rýchlejšia!
Vytvoriť / Vyhlásiť pole (Dim)
Poznámka: Polia môžu mať viacero „dimenzií“. Aby to nebolo jednoduché, začneme tým, že budeme pracovať iba s jednorozmernými poľami. Neskôr v tutoriále vám predstavíme viacrozmerné polia.
Statické pole
Statické polia sú polia, ktoré nemôžu meniť veľkosť. A naopak, Dynamické polia môže zmeniť veľkosť. Sú vyhlásené trochu inak. Najprv sa pozrime na statické polia.
Poznámka: Ak sa veľkosť vášho poľa nezmení, použite statické pole.
Deklarácia statickej premennej poľa je veľmi podobná deklarácii bežnej premennej, ibaže musíte definovať veľkosť poľa. Existuje niekoľko rôznych spôsobov, ako nastaviť veľkosť poľa.
Počiatočnú a koncovú pozíciu poľa môžete explicitne deklarovať:
| 123456789101112 | Sub StaticArray1 ()'Vytvorí pole s pozíciami 1,2,3,4Dim arrDemo1 (1 až 4) ako reťazec'Vytvorí pole s pozíciami 4,5,6,7Dim arrDemo2 (4 až 7) ako dlhý'Vytvorí pole s pozíciami 0,1,2,3Dim arrDemo3 (0 to 3) As LongKoniec pod |
Alebo môžete zadať iba veľkosť poľa:
| 123456 | Sub StaticArray2 ()'Vytvorí pole s pozíciami 0,1,2,3Dim arrDemo1 (3) As StringKoniec pod |
Dôležité! Všimnite si, že v predvolenom nastavení začínajú polia na pozícii 0. Takže Dim arrDemo1 (3) vytvorí pole s pozíciami 0,1,2,3.
Môžete vyhlásiť Možnosť Základ 1 v hornej časti modulu, aby sa pole namiesto toho začalo na pozícii 1:
| 12345678 | Možnosť Základ 1Sub StaticArray3 ()'Vytvorí pole s pozíciami 1,2,3Dim arrDemo1 (3) As StringKoniec pod |
Zistil som však, že je oveľa jednoduchšie (a menej mätúce) iba explicitne deklarovať počiatočnú a koncovú polohu polí.
Ste unavení z hľadania príkladov kódu VBA? Skúste AutoMacro!
Dynamické pole
Dynamické polia sú polia, ktorých veľkosť je možné zmeniť (alebo ktorých veľkosť nie je potrebné definovať).
Dynamické pole je možné deklarovať dvoma spôsobmi.
Variantné polia
Prvý spôsob deklarovania dynamického poľa je nastavenie poľa na typ Varianta.
| 1 | Dim arrVar () ako variant |
S Variant Array, nemusíte definovať veľkosť poľa. Veľkosť sa automaticky upraví. Nezabudnite, že pole začína polohou 0 (pokiaľ do hornej časti modulu nepridáte Option Base 1)
| 12345678910111213 | Pole pod variantom ()Dim arrVar () ako variant„Definujte hodnoty (veľkosť = 0,1,2,3)arrVar = pole (1, 2, 3, 4)„Zmeniť hodnoty (veľkosť = 0,1,2,3,4)arrVar = pole („1a“, „2a“, „3a“, „4a“, „5a“)„Výstupná pozícia 4 („ 5a “)MsgBox arrVar (4)Koniec pod |
Nevariantné dynamické polia
Pri nevariantných poliach musíte definovať veľkosť poľa pred priradením hodnôt k poľu. Proces vytvárania poľa je však mierne odlišný:
| 1234567 | Sub DynamicArray1 ()Dim arrDemo1 () ako reťazec'Zmení veľkosť poľa s pozíciami 1,2,3,4ReDim arrDemo1 (1 až 4)Koniec pod |
Najprv deklarujete pole, podobné statickému poľu, ibaže vynecháte veľkosť poľa:
| 1 | Dim arrDemo1 () ako reťazec |
Teraz, keď chcete nastaviť veľkosť poľa, použijete ReDim príkaz na veľkosť poľa:
| 12 | 'Zmení veľkosť poľa s pozíciami 1,2,3,4ReDim arrDemo1 (1 až 4) |
ReDim zmení veľkosť poľa. Rozdiel medzi ReDim a ReDim Preserve si prečítajte nižšie.
ReDim vs. ReDim Preserve
Keď použijete ReDim príkazom vymažete z poľa všetky existujúce hodnoty. Namiesto toho môžete použiť Zachovať ReDim na zachovanie hodnôt poľa:
| 12 | "Zmení veľkosť poľa s pozíciami 1,2,3,4 (zachovanie existujúcich hodnôt)ReDim Zachovať arrDemo1 (1 až 4) |
Zjednodušená deklarácia polí
Po prečítaní všetkého vyššie sa môžete cítiť zahltení. Aby to nebolo jednoduché, po zvyšok článku budeme väčšinou pracovať so statickými poľami.
Nastavte hodnoty poľa
Nastavenie hodnôt poľa je veľmi jednoduché.
Pri statickom poli musíte definovať každú pozíciu poľa, jednu po druhej:
| 12345678 | Sub ArrayPríklad ()Dim strNames (1 až 4) ako reťazecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Koniec pod |
Pomocou poľa variantov môžete definovať celé pole jedným riadkom (praktické iba pre malé polia):
| 123456 | Sub ArrayExample_1Line ()Dim strNames () ako variantstrNames = Pole („Shelly“, „Steve“, „Neema“, „Jose“)Koniec pod |
Ak sa pokúsite definovať hodnotu pre neexistujúce umiestnenie poľa, zobrazí sa chyba dolného indexu mimo rozsah:
| 1 | strNames (5) = "Shannon" |
V nižšie uvedenej časti „Priradenie rozsahu k poli“ vám ukážeme, ako použiť slučku na rýchle priradenie veľkého počtu hodnôt k poliam.
Získajte hodnotu poľa
Hodnoty polí môžete načítať rovnakým spôsobom. V nižšie uvedenom príklade napíšeme hodnoty polí do buniek:
| 1234 | Rozsah („A1“). Hodnota = strNames (1)Rozsah ("A2"). Hodnota = strNames (2)Rozsah ("A3"). Hodnota = strNames (3)Rozsah ("A4"). Hodnota = strNames (4) |
Programovanie VBA | Generátor kódu pracuje za vás!
Priraďte rozsah k poľu
Na priradenie rozsahu k poli môžete použiť slučku:
| 12345678 | Sub RangeToArray ()Dim strNames (1 až 60 000) ako reťazecDim i As LongPre i = 1 až 60 000strNames (i) = Bunky (i, 1). HodnotaĎalej iKoniec pod |
Tým sa bude prechádzať bunkami A1: A60000 a priradia sa hodnoty buniek k poľu.
Výstupné pole na rozsah
Alebo môžete použiť slučku na priradenie poľa k rozsahu:
| 123 | Pre i = 1 až 60 000Bunky (i, 1). Hodnota = strNames (i)Ďalej i |
Bude to naopak: priradiť hodnoty poľa k bunkám A1: A60000
2D / viacrozmerné polia
Doteraz sme pracovali výlučne s jednorozmernými (1D) poľami. Polia však môžu mať až 32 rozmerov.
Predstavte si pole 1D ako jeden riadok alebo stĺpec buniek programu Excel, 2D pole ako celý pracovný hárok programu Excel s viacerými riadkami a stĺpcami a pole 3D je ako celý zošit obsahujúci viacero hárkov, z ktorých každý obsahuje viacero riadkov a stĺpcov (vy mohol by tiež uvažovať o 3D poli ako o Rubikovej kocke).
Príklady viacrozmerného poľa
Teraz si ukážme príklady práce s poliami rôznych rozmerov.
Programovanie VBA | Generátor kódu pracuje za vás!
Príklad 1D poľa
Tento postup kombinuje predchádzajúce príklady polí do jedného postupu a ukazuje, ako by ste pole mohli v praxi používať.
| 1234567891011121314 | Sub ArrayEx_1d ()Dim strNames (1 až 60 000) ako reťazecDim i As Long„Priraďte hodnoty k poľuPre i = 1 až 60 000strNames (i) = Bunky (i, 1). HodnotaĎalej i„Hodnoty výstupného poľa na rozsahPre i = 1 až 60 000Tabuľky („Výstup“). Bunky (i, 1). Hodnota = strNames (i)Ďalej iKoniec pod |
Príklad 2D poľa
Tento postup obsahuje príklad 2D poľa:
| 123456789101112131415161718 | Sub ArrayEx_2d ()Dim strNames (1 až 60 000, 1 až 10) ako reťazecDim i As Long, j As Long„Priraďte hodnoty k poľuPre i = 1 až 60 000Pre j = 1 až 10strNames (i, j) = Bunky (i, j). HodnotaĎalej jĎalej i„Hodnoty výstupného poľa na rozsahPre i = 1 až 60 000Pre j = 1 až 10Tabuľky („Výstup“). Bunky (i, j). Hodnota = strNames (i, j)Ďalej jĎalej iKoniec pod |
Príklad 3D poľa
Tento postup obsahuje príklad 3D poľa na prácu s viacerými listami:
| 12345678910111213141516171819202122 | Sub ArrayEx_3d ()Dim strNames (1 až 60 000, 1 až 10, 1 až 3) ako reťazecDim i As Long, j As Long, k As Long„Priraďte hodnoty k poľuPre k = 1 až 3Pre i = 1 až 60 000Pre j = 1 až 10strNames (i, j, k) = Tabuľky ("List" & k). Bunky (i, j). HodnotaĎalej jĎalej iĎalej k„Hodnoty výstupného poľa na rozsahPre k = 1 až 3Pre i = 1 až 60 000Pre j = 1 až 10Tabuľky ("Výstup" & k). Bunky (i, j). Hodnota = strNames (i, j, k)Ďalej jĎalej iĎalej kKoniec pod |
Dĺžka / veľkosť poľa
Doteraz sme vám predstavili rôzne typy polí a naučili vás, ako polia deklarovať a získať/nastaviť hodnoty polí. Ďalej sa zameriame na ďalšie potrebné témy pre prácu s poľami.
Programovanie VBA | Generátor kódu pracuje za vás!
Funkcie UBound a LBound
Prvým krokom k získaniu dĺžky / veľkosti poľa je použitie funkcií UBound a LBound na získanie hornej a dolnej hranice poľa:
| 123456 | Sub UBoundLBound ()Dim strNames (1 až 4) ako reťazecMsgBox UBound (strNames)MsgBox LBound (strNames)Koniec pod |
Odčítaním týchto dvoch (a pridaním 1) získate dĺžku:
| 1 | GetArrLength = UBound (strNames) - LBound (strNames) + 1 |
Funkcia dĺžky poľa
Tu je funkcia na získanie dĺžky jednorozmerného poľa:
| 1234567 | Verejná funkcia GetArrLength (ako variant) tak dlhoIf IsEmpty (a) ThenGetArrLength = 0InakGetArrLength = UBound (a) - LBound (a) + 1Koniec AkKoncová funkcia |
Potrebujete vypočítať veľkosť 2D poľa? Pozrite sa na náš návod: Vypočítajte veľkosť poľa.
Smyčka cez pole
Existujú dva spôsoby, ako prechádzať poľom. Prvá slučka prechádza celými číslami zodpovedajúcimi číselným polohám poľa. Ak poznáte veľkosť poľa, môžete ho priamo zadať:
| 12345678910111213 | Vedľajšie polePriklad_Smyčka1 ()Dim strNames (1 až 4) ako reťazecDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pre i = 1 až 4MsgBox strNames (i)Ďalej iKoniec pod |
Ak však nepoznáte veľkosť poľa (ak je pole dynamické), môžete použiť funkcie LBound a UBound z predchádzajúcej časti:
| 12345678910111213 | Vedľajšie polePriklad_Smyčka2 ()Dim strNames (1 až 4) ako reťazecDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pre i = LBound (strNames) Do UBound (strNames)MsgBox strNames (i)Ďalej iKoniec pod |
Pre každú slučku poľa
Druhá metóda je pre každú slučku. Toto sa opakuje cez každú položku v poli:
| 12345678910111213 | Vedľajšie polePriklad_Smyčka3 ()Dim strNames (1 až 4) ako reťazecTlmená položkastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Pre každú položku v strNamesPoložka MsgBoxNasledujúca položkaKoniec pod |
Slučka For Every Array Loop bude okrem jednorozmerných polí fungovať aj s viacrozmernými poľami.
Programovanie VBA | Generátor kódu pracuje za vás!
Smyčka cez 2D pole
Môžete tiež použiť funkcie UBound a LBound na prechádzanie viacrozmerným poľom. V tomto prípade sa budeme opakovať v 2D poli. Všimnite si, že funkcie UBound a LBound vám umožňujú určiť, ktorý rozmer poľa má nájsť hornú a dolnú hranicu (1 pre prvú dimenziu, 2 pre druhú dimenziu).
| 1234567891011121314151617181920 | Vedľajšie polePriklad_Smyčka4 ()Dim strNames (1 až 4, 1 až 2) ako reťazecDim i As Long, j As LongstrNames (1, 1) = "Shelly"strNames (2, 1) = "Steve"strNames (3, 1) = "Neema"strNames (4, 1) = "Jose"strNames (1, 2) = "Shelby"strNames (2, 2) = "Steven"strNames (3, 2) = "Nemo"strNames (4, 2) = "Jesse"Pre j = LBound (strNames, 2) až UBound (strNames, 2)Pre i = LBound (strNames, 1) Do UBound (strNames, 1)MsgBox strNames (i, j)Ďalej iĎalej jKoniec pod |
Ďalšie úlohy poľa
Vymazať pole
Ak chcete vymazať celé pole, použite príkaz Vymazať:
| 1 | Vymazať strNames |
Príklad použitia:
| 12345678910 | Sub ArrayPríklad ()Dim strNames (1 až 4) ako reťazecstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Vymazať strNamesKoniec pod |
Alternatívne môžete tiež zmeniť veľkosť poľa a vymazať časť poľa:
| 1 | ReDim strNames (1 až 2) |
Tým sa zmení veľkosť poľa na veľkosť 2, pričom sa odstránia polohy 3 a 4.
Gróf Array
Počet pozícií v každej dimenzii poľa môžete spočítať pomocou funkcií UBound a LBound (diskutované vyššie).
Môžete tiež vypočítať počet zadaných položiek (alebo položiek, ktoré spĺňajú určité kritériá) opakovaním v poli.
Tento príklad bude prechádzať poľom objektov a počítať počet prázdnych reťazcov nájdených v poli:
| 123456789101112131415 | Sub ArrayLoopandCount ()Dim strNames (1 až 4) ako reťazecDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Pre i = LBound (strNames) Do UBound (strNames)Ak strNames (i) "" Potomn = n + 1Koniec AkĎalej iMsgBox n & "nájdené prázdne hodnoty."Koniec pod |
Programovanie VBA | Generátor kódu pracuje za vás!
Odstráňte duplikáty
V určitom okamihu budete chcieť odstrániť duplikáty z poľa. Na to bohužiaľ VBA nemá vstavanú funkciu. Napísali sme však funkciu na odstránenie duplikátov z poľa (zahrnutie do tohto tutoriálu je príliš dlhé, ale navštívte odkaz a dozviete sa viac).
Filter
Funkcia filtra VBA vám umožňuje filtrovať pole. Urobí to tak, že vytvorí nové pole iba s filtrovanými hodnotami. Nasleduje rýchly príklad, ale prečítajte si článok, kde nájdete ďalšie príklady rôznych potrieb.
| 1234567891011121314 | Sub Filter_Match ()„Definujte poleDim strNames As VariantstrNames = Pole („Steve Smith“, „Shannon Smith“, „Ryan Johnson“)„Pole filtraDim strSubNames ako variantstrSubNames = Filter (strNames, "Smith")„Gróf filtrované poleMsgBox „Nájdené“ a UBound (strSubNames) - LBound (strSubNames) + 1 & „mená“.Koniec pod |
Funkcia IsArray
Pomocou funkcie IsArray môžete otestovať, či je premenná poľom:
| 123456789101112 | Sub IsArrayEx ()'Vytvorí pole s pozíciami 1,2,3Dim arrDemo1 (3) As String'Vytvorí pravidelnú reťazcovú premennúDim str Ako reťazecMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Koniec pod |
Pripojte sa k Array
Pomocou funkcie Pripojiť sa môžete rýchlo „spojiť“ s celým poľom:
| 123456789101112 | Sub Array_Join ()Dim strNames (1 až 4) ako reťazecDim joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Pripojiť sa (strNames, ",")MsgBox joinNamesKoniec pod |
Programovanie VBA | Generátor kódu pracuje za vás!
Rozdeľte reťazec do poľa
Funkcia rozdelenia VBA rozdelí reťazec textu do poľa obsahujúceho hodnoty z pôvodného reťazca. Pozrime sa na príklad:
| 123456789 | Sub Array_Split ()Dim Names () ako reťazecDim sa pripojil k menám ako reťazecjoinNames = "Shelly, Steve, Nema, Jose"Mená = Rozdeliť (joinNames, ",")Názvy MsgBox (1)Koniec pod |
Tu sme rozdelili tento reťazec textu „Shelly, Steve, Nema, Jose“ na pole (veľkosť 4) pomocou oddeľovača čiarok (, ”).
Const Array
Pole nemôže byť deklarovaný ako konštanta vo VBA. Môžete to však obísť vytvorením funkcie, ktorá sa použije ako pole:
| 123456789 | „Definujte ConstantArrayFunkcia ConstantArray ()ConstantArray = Array (4, 12, 21, 100, 5)Koncová funkcia„Obnovte hodnotu ConstantArrayHodnoty čiastkového získavania ()MsgBox ConstantArray (3)Koniec pod |
Pole kopírovania
Neexistuje žiadny vstavaný spôsob kopírovania poľa pomocou VBA. Namiesto toho budete musieť použiť slučku na priradenie hodnôt z jedného poľa do druhého.
| 12345678910111213141516171819 | Podradená kópia poľa ()Dim Arr1 (1 až 100) tak dlhoDim Arr2 (1 až 100) tak dlhoDim i As Long„Vytvoriť pole 1Pre i = 1 až 100Arr1 (i) = iĎalej i„CopyArray1 to Array2Pre i = 1 až 100Arr2 (i) = Arr1 (i)Ďalej iMsgBox Arr2 (74)Koniec pod |
Transponovať
Neexistuje žiadna vstavaná funkcia VBA, ktorá by vám umožnila transponovať pole. Napísali sme však funkciu na transpozíciu 2D poľa. Ak sa chcete dozvedieť viac, prečítajte si článok.
Programovanie VBA | Generátor kódu pracuje za vás!
Funkcia návratového poľa
Bežnou otázkou pre vývojárov VBA je, ako vytvoriť funkciu, ktorá vracia pole. Myslím si, že väčšina problémov je vyriešená použitím variantných polí. Napísali sme článok na tému: Návratové pole funkcií VBA.
Použitie polí v Access VBA
Väčšina vyššie uvedených príkladov poľa v programe Access VBA funguje úplne rovnako ako v programe Excel VBA. Jeden zásadný rozdiel je v tom, že keď chcete naplniť pole pomocou údajov programu Access, budete musieť prejsť objektom RecordSet a nie objektom Range.
| 1234567891011121314151617181920212223 | Sub RangeToArrayAccess ()Pri chybe Pokračovať ďalejDim strNames () ako reťazecDim i As LongDim iCount ako dlhýDim dbs ako databázaDim first as RecordsetNastaviť dbs = CurrentDbNastaviť rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvým.Pohyb.Pohyb Po prvéiCount = .RecordCountReDim strNames (1 až iCount)Pre i = 1 do iCountstrNames (i) = rst.Fields ("ClientName").MoveNextĎalej iUkončiť snajskôr. ZatvorteNastaviť prvý = ničNastaviť dbs = ničKoniec pod |
| Array Návody | |
|---|---|
| Array Mega-Guide | Áno |
| Získajte veľkosť poľa | |
| Vymazať pole | |
| Pole filtra | |
| Transponovať pole | |
| Funkcia návratového poľa | |
| Odstráňte duplikáty | |
