VBA tömbök

A VBA -ban egy Sor egyetlen változó, amely több értéket is tartalmazhat. Gondoljon egy tömbre, mint egy cellatartomány: minden cella tárolhat egy értéket. A tömbök lehetnek egydimenziósak (gondoljunk egy oszlopra), kétdimenziósak (gondoljunk több sorra és oszlopra) vagy többdimenziósak. A tömbértékek a tömbön belüli pozíciójuk (indexszám) alapján érhetők el.

VBA tömb gyorslap

Tömbök

LeírásVBA kódTeremtDim arr (1 - 3) Variantként
arr (1) = „egy”
arr (2) = „kettő”
arr (3) = „három”Hozzon létre ExcelbőlDim arr (1 - 3) Variantként
Homályos cella mint tartomány, és mint egész
i = LBound (arr)
A tartomány minden cellájához („A1: A3”)
i = i + 1
arr (i) = cella.érték
Következő cellaOlvassa el az összes elemetDim i as Long
I = LBound (arr) - UBound (arr)
MsgBox arr (i)
Következő iTörliTörlés arrTömb karakterlánchozDim sName As String
sName = Csatlakozás (arr, “:”)Méret növeléseReDim Preserve arr (0 és 100 között)Érték beállításaarr (1) = 22

VBA tömb gyors példák

Nézzünk egy teljes példát, mielőtt belemerülünk a konkrétumokba:

12345678910

AltömbPélda ()Dim strNames (1-4) karakterlánckéntstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)End Sub

Itt hoztuk létre az egydimenziós karakterlánc-tömböt: a strNames négyes méretű (négy értéket képes tárolni), és hozzárendeltük a négy értéket. Végül a 3. értéket jelenítjük meg egy üzenetmezőben.

Ebben az esetben a tömb használatának haszna csekély: négy helyett csak egy változó deklaráció szükséges.

Nézzünk azonban egy példát, amely megmutatja egy tömb valódi erejét:

12345678

AltömbPélda2 ()Dim strNames (1 -től 60000 -ig) karakterlánckéntDim i As LongI = 1 és 60000 közöttstrNames (i) = Cella (i, 1). ÉrtékKövetkező iEnd Sub

Itt létrehoztunk egy tömböt, amely 60 000 értéket tartalmazhat, és gyorsan feltöltöttük a tömböt egy munkalap A oszlopából.

Tömb előnyei? - Sebesség!

Gondolhat az Excel -munkalapokhoz hasonló tömbökre:

• Minden cella (vagy egy tömb eleme) tartalmazhat saját értéket
• Minden cella (vagy tömb eleme) a sor- és oszloppozíció alapján érhető el.
  • Feladatlap Péld. cellák (1,4). érték = „1. sor, 4. oszlop”
  • Tömb Ex. arrVar (1,4) = „1. sor, 4. oszlop”

Akkor miért bajlódik az Arrays -szal? Miért nem olvashat és írhat értékeket közvetlenül az Excel celláiba? Egy szó: Sebesség!

Az Excel cellákba való olvasás / írás lassú folyamat. A tömbökkel való munka sokkal gyorsabb!

Tömb létrehozása / deklarálása (Dim)

Megjegyzés: A tömböknek több „mérete” is lehet. Az egyszerűség kedvéért először csak egydimenziós tömbökkel dolgozunk. Az oktatóanyag későbbi részében bemutatunk többdimenziós tömböket.

Statikus tömb

Statikus tömbök olyan tömbök, amelyek nem tudják megváltoztatni a méretüket. Fordítva, Dinamikus tömbök megváltoztathatja a méretét. Kicsit másképp vannak bejelentve. Először nézzük a statikus tömböket.

Megjegyzés: Ha a tömb mérete nem változik, használjon statikus tömböt.

A statikus tömbváltozó deklarálása nagyon hasonló a rendszeres változó deklarálásához, kivéve, ha meg kell határoznia a tömb méretét. A tömb méretének beállítására többféle módszer létezik.

Kifejezetten deklarálhatja a tömb kezdő és véghelyzetét:

123456789101112

Sub StaticArray1 ()'1,2,3,4 pozíciójú tömböt hoz létreDim arrDemo1 (1 -től 4 -ig) karakterláncként'4,5,6,7 pozíciójú tömböt hoz létreDim arrDemo2 (4 -től 7 -ig), mint hosszú'0,1,2,3 pozíciójú tömböt hoz létreDim arrDemo3 (0 -tól 3 -ig)End Sub

Vagy csak a tömb méretét adhatja meg:

123456

Sub StaticArray2 ()'0,1,2,3 pozíciójú tömböt hoz létreDim arrDemo1 (3) karakterlánckéntEnd Sub

Fontos! Vegye figyelembe, hogy alapértelmezés szerint a tömbök a 0. pozícióból indulnak. Tehát Dim arrDemo1 (3) létrehoz egy tömböt 0,1,2,3 pozícióval.

Nyilatkozhat Opcióalap 1 a modul tetején, hogy a tömb az 1 -es pozícióból induljon:

12345678

Opcióalap 1Sub StaticArray3 ()'1,2,3 -as pozíciójú tömböt hoz létreDim arrDemo1 (3) karakterlánckéntEnd Sub

Azonban úgy találom, hogy sokkal könnyebb (és kevésbé zavaró) csak kifejezetten deklarálni a tömbök kezdő és véghelyzetét.

Unod már a VBA kódpéldák keresését? Próbálja ki az AutoMacro -t!

Dinamikus tömb

Dinamikus tömbök olyan tömbök, amelyek mérete megváltoztatható (vagy amelyek méretét nem kell meghatározni).

A dinamikus tömb deklarálásának két módja van.

Változatos tömbök

A dinamikus tömb deklarálásának első módja a tömb típusának beállítása Változat.

1	Dim arrVar () mint változat

Val,-vel Változat Array, nem kell megadni a tömb méretét. A méret automatikusan beáll. Ne feledje, hogy a tömb 0 pozícióval kezdődik (kivéve, ha hozzáadja az 1. opciót a modul tetejéhez)

12345678910111213

Sub VariantArray ()Dim arrVar () mint változat'Értékek definiálása (méret = 0,1,2,3)arrVar = tömb (1, 2, 3, 4)'Értékek módosítása (méret = 0,1,2,3,4)arrVar = tömb ("1a", "2a", "3a", "4a", "5a")'4. kimeneti pozíció ("5a")MsgBox arrVar (4)End Sub

Nem változatos dinamikus tömbök

A nem változatos tömböknél meg kell határozni a tömb méretét, mielőtt értékeket rendel hozzá a tömbhöz. A tömb létrehozásának folyamata azonban kissé eltér:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () Karakterláncként'Átméretezi a tömböt az 1,2,3,4 pozíciókkalReDim arrDemo1 (1 -től 4 -ig)End Sub

Először deklarálja a tömböt, hasonlóan a statikus tömbhöz, kivéve, ha kihagyja a tömb méretét:

1	Dim arrDemo1 () Karakterláncként

Most, amikor be szeretné állítani a tömb méretét, használja a ReDim parancs a tömb méretezéséhez:

12	'Átméretezi a tömböt az 1,2,3,4 pozíciókkalReDim arrDemo1 (1 -től 4 -ig)

A ReDim átméretezi a tömböt. Olvassa el alább a ReDim és a ReDim Preserve közötti különbséget.

ReDim vs. ReDim Preserve

Amikor a ReDim parancs törli az összes létező értéket a tömbből. Ehelyett használhatja ReDim Preserve tömbértékek megőrzése:

12	'A tömb átméretezése az 1,2,3,4 pozíciókkal (meglévő értékek megőrzése)ReDim Preserve arrDemo1 (1-4)

A tömbök deklarálása egyszerűsítve

Lehet, hogy túlterheltnek érzi magát, miután elolvasta a fentieket. Az egyszerűség kedvéért a cikk többi részében többnyire statikus tömbökkel dolgozunk.

Állítsa be a tömbértékeket

A tömbértékek beállítása nagyon egyszerű.

Statikus tömb esetén egyenként meg kell határoznia a tömb minden pozícióját:

12345678

AltömbPélda ()Dim strNames (1-4) karakterlánckéntstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"End Sub

A Variant Array segítségével egy sorral definiálhatja a teljes tömböt (csak kis tömböknél praktikus):

123456

AltömbPélda_1sor ()Dim strNames () VáltozatkéntstrNames = Tömb ("Shelly", "Steve", "Neema", "Jose")End Sub

Ha nem létező tömbhely értékét próbálja definiálni, akkor egy Előfizetési tartományon kívüli hibaüzenetet kap:

1	strNames (5) = "Shannon"

Az alábbi „Tartomány hozzárendelése tömbhöz” szakaszban bemutatjuk, hogyan használhat ciklusokat nagyszámú érték gyors hozzárendelésére a tömbökhöz.

Szerezzen tömbértéket

A tömbértékeket ugyanúgy lekérheti. Az alábbi példában tömbértékeket írunk a cellákba:

1234	Tartomány ("A1"). Érték = strNames (1)Tartomány ("A2"). Érték = strNames (2)Tartomány ("A3"). Érték = strNames (3)Tartomány ("A4"). Érték = strNames (4)

VBA programozás | A kódgenerátor működik az Ön számára!

Tartomány hozzárendelése tömbhöz

Tartomány hozzárendeléséhez tömbhöz használhat hurkot:

12345678

Sub RangeToArray ()Dim strNames (1 -től 60000 -ig) karakterlánckéntDim i As LongI = 1 és 60000 közöttstrNames (i) = Cella (i, 1). ÉrtékKövetkező iEnd Sub

Ez végigmegy az A1: A60000 cellán, és hozzárendeli a cellaértékeket a tömbhöz.

Kimeneti tömb tartományba

Vagy használhat hurkot egy tömb hozzárendeléséhez egy tartományhoz:

123	I = 1 és 60000 közöttCella (i, 1). Érték = strNames (i)Következő i

Ez fordítva történik: rendeljen tömbértékeket az A1: A60000 cellákhoz

2D / többdimenziós tömbök

Eddig kizárólag egydimenziós (1D) tömbökkel dolgoztunk. A tömböknek azonban legfeljebb 32 dimenziójuk lehet.

Gondoljon az 1D tömbre, mint az Excel cellák egyetlen sora vagy oszlopa, a 2D tömbre, mint egy teljes Excel munkalap több sorra és oszlopra, és a 3D tömb olyan, mint egy teljes munkafüzet, amely több lapot tartalmaz, amelyek mindegyike több sort és oszlopot tartalmaz ( a 3D tömbre is úgy gondolhat, mint egy Rubik -kocka).

Többdimenziós tömb példák

Most mutassunk példákat a különböző méretű tömbökkel való munkavégzésre.

VBA programozás | A kódgenerátor működik az Ön számára!

1D tömb példa

Ez az eljárás egyesíti a korábbi tömbpéldákat egy eljárásban, bemutatva, hogyan használhatja a tömböket a gyakorlatban.

1234567891011121314

AltömbEx_1d ()Dim strNames (1 -től 60000 -ig) karakterlánckéntDim i As Long'Értékek hozzárendelése a tömbhözI = 1 és 60000 közöttstrNames (i) = Cella (i, 1). ÉrtékKövetkező i'Kimeneti tömbértékek tartománybaI = 1 és 60000 közöttTáblázatok ("kimenet"). Cellák (i, 1). Érték = strNames (i)Következő iEnd Sub

2D tömb példa

Ez az eljárás egy példát tartalmaz a 2D tömbre:

123456789101112131415161718

AltömbEx_2d ()Dim strNames (1 -től 60000 -ig, 1 -től 10 -ig) karakterlánckéntDim i As Long, j A Long'Értékek hozzárendelése a tömbhözI = 1 és 60000 közöttHa j = 1-10strNames (i, j) = Cella (i, j). ÉrtékKövetkező jKövetkező i'Kimeneti tömbértékek tartománybaI = 1 és 60000 közöttHa j = 1-10Táblázatok ("kimenet"). Cellák (i, j). Érték = strNames (i, j)Következő jKövetkező iEnd Sub

3D tömb példa

Ez az eljárás egy példát tartalmaz egy 3D tömbre, amely több lappal dolgozik:

12345678910111213141516171819202122

AltömbEx_3d ()Dim strNames (1 -től 60000 -ig, 1 -től 10 -ig, 1 -től 3 -ig) karakterlánckéntDim i Olyan hosszú, j Olyan hosszú, k Olyan hosszú'Értékek hozzárendelése a tömbhözK = 1 -től 3 -igI = 1 és 60000 közöttHa j = 1-10strNames (i, j, k) = Sheets ("Sheet" & k). Cellák (i, j). ÉrtékKövetkező jKövetkező iKövetkező k'Kimeneti tömbértékek tartománybaK = 1 -től 3 -igI = 1 és 60000 közöttHa j = 1-10Táblázatok ("Output" & k). Cellák (i, j). Érték = strNames (i, j, k)Következő jKövetkező iKövetkező kEnd Sub

Tömb hossza / mérete

Eddig bemutattuk a tömbök különböző típusait, és megtanítottuk a tömbök deklarálására és tömbértékek lekérésére/beállítására. Ezután a tömbökkel való munkához szükséges egyéb témákra összpontosítunk.

VBA programozás | A kódgenerátor működik az Ön számára!

UBound és LBound funkciók

A tömb hosszának / méretének meghatározásához az első lépés az UBound és az LBound függvények használata a tömb felső és alsó határainak meghatározásához:

123456

Sub UBoundLBound ()Dim strNames (1 -től 4 -ig) karakterlánckéntMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub

A kettő kivonása (és az 1 hozzáadása) megadja a hosszúságot:

1	GetArrLength = UBound (strNames) - LBound (strNames) + 1

Tömbhossz funkció

Itt van egy függvény az egydimenziós tömb hosszának meghatározásához:

1234567

Nyilvános funkció GetArrLength (változatként), ameddigHa IsEmpty (a) AkkorGetArrLength = 0MásGetArrLength = UBound (a) - LBound (a) + 1Vége HaVége funkció

Kell kiszámítani a 2D tömb méretét? Nézze meg bemutatónkat: A tömb méretének kiszámítása.

Loop Through Array

A tömbön keresztül kétféleképpen lehet ciklusba lépni. Az első ciklusok a tömb számpozícióinak megfelelő egész számokon keresztül haladnak. Ha ismeri a tömb méretét, akkor közvetlenül megadhatja:

12345678910111213

RésztömbExample_Loop1 ()Dim strNames (1 -től 4 -ig) karakterlánckéntDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"I = 1 -től 4 -igMsgBox strNames (i)Következő iEnd Sub

Ha azonban nem ismeri a tömb méretét (ha a tömb dinamikus), használhatja az előző szakasz LBound és UBound függvényeit:

12345678910111213

AltömbExample_Loop2 ()Dim strNames (1 -től 4 -ig) karakterlánckéntDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"I = LBound (strNames) - UBound (strNames) eseténMsgBox strNames (i)Következő iEnd Sub

Minden tömbhurokhoz

A második módszer a For Every Loop. Ez végigmegy a tömb minden elemén:

12345678910111213

RésztömbExample_Loop3 ()Dim strNames (1 -től 4 -ig) karakterlánckéntHalvány elemstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Az strNames minden eleméhezMsgBox elemKövetkező tételEnd Sub

A For Every Array Loop többdimenziós tömbökkel fog működni az egydimenziós tömbök mellett.

VBA programozás | A kódgenerátor működik az Ön számára!

Loop 2D tömbön keresztül

Az UBound és az LBound függvényeket is használhatja a többdimenziós tömbök közötti ciklushoz. Ebben a példában egy 2D tömbön fogunk végigmenni. Vegye figyelembe, hogy az UBound és az LBound függvények lehetővé teszik a tömb melyik dimenziójának megadását a felső és alsó határok megtalálásához (1 az első dimenzióhoz, 2 a második dimenzióhoz).

1234567891011121314151617181920

RésztömbExample_Loop4 ()Dim strNames (1 -től 4 -ig, 1 -től 2 -ig) karakterlánckéntDim i As Long, j A 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"For j = LBound (strNames, 2) to UBound (strNames, 2)For i = LBound (strNames, 1) to UBound (strNames, 1)MsgBox strNames (i, j)Következő iKövetkező jEnd Sub

Egyéb tömbfeladatok

Tiszta tömb

Egy teljes tömb törléséhez használja a Törlés utasítást:

1	Az strNames törlése

Használati példa:

12345678910

AltömbPélda ()Dim strNames (1-4) karakterlánckéntstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Az strNames törléseEnd Sub

Alternatív megoldásként a tömb átméretezésével átméretezheti a tömb egy részét:

1	ReDim strNames (1-2)

Ez átméretezi a tömböt 2 -es méretre, törli a 3. és 4. pozíciót.

Array gróf

Az UBound és az LBound függvények használatával (a fentiekben) megszámolhatja a pozíciók számát egy tömb minden dimenziójában.

A beírt elemek (vagy bizonyos feltételeknek megfelelő tételek) számát úgy is megszámolhatja, hogy a tömbön keresztül hurkolja.

Ez a példa végigmegy az objektumok tömbjén, és számolja a tömbben található nem üres karakterláncok számát:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 -től 4 -ig) karakterlánckéntDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"I = LBound (strNames) - UBound (strNames) eseténHa strNames (i) "" Akkorn = n + 1Vége HaKövetkező iMsgBox n & "nem üres értékek találhatók."End Sub

VBA programozás | A kódgenerátor működik az Ön számára!

Ismétlődések eltávolítása

Egy bizonyos ponton érdemes eltávolítani a másolatokat egy tömbből. Sajnos a VBA nem rendelkezik beépített funkcióval. Azonban írtunk egy funkciót, amely eltávolítja az ismétlődéseket egy tömbből (túl hosszú, hogy belefoglaljuk ebbe az oktatóanyagba, de további információkért keresse fel a linket).

Szűrő

A VBA szűrő funkció lehetővé teszi tömb szűrését. Ezt úgy hozza létre, hogy létrehoz egy új tömböt, amely csak a szűrt értékeket tartalmazza. Az alábbiakban egy gyors példát mutatunk be, de mindenképpen olvassa el a cikket, ahol további példákat talál a különböző igényekre.

1234567891011121314

Sub Filter_Match ()'Definiálja a tömbötDim strNames VariantstrNames = Tömb ("Steve Smith", "Shannon Smith", "Ryan Johnson")'SzűrőtömbDim strSubNames VariantstrSubNames = Szűrő (strNames, "Smith")- Gróf szűrt tömbMsgBox "Found" & UBound (strSubNames) - LBound (strSubNames) + 1 & "names".End Sub

IsArray függvény

Az IsArray függvény segítségével tesztelheti, hogy egy változó tömb:

123456789101112

Sub IsArrayEx ()'1,2,3 -as pozíciójú tömböt hoz létreDim arrDemo1 (3) karakterláncként'Létrehozza a szokásos karakterlánc -változótDim str Mint karakterláncMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Csatlakozz az Array -hez

Gyorsan „csatlakozhat” egy egész tömbhöz a Csatlakozás funkcióval:

123456789101112

Sub Array_Join ()Dim strNames (1 -től 4 -ig) karakterlánckéntDim joinNames mint karakterláncstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Csatlakozás (strNames, ",")MsgBox joinNamesEnd Sub

VBA programozás | A kódgenerátor működik az Ön számára!

Karakterlánc felosztása tömbbe

A VBA felosztási függvény egy szöveges karakterláncot oszt fel egy tömbre, amely az eredeti karakterlánc értékeit tartalmazza. Nézzünk egy példát:

123456789

Résztömb_Split ()Dim nevek () karakterlánckéntDim csatlakozott a nevekhez karakterlánckéntjoinNames = "Shelly, Steve, Nema, Jose"Names = Split (joinNames, ",")MsgBox nevek (1)End Sub

Itt ezt a „Shelly, Steve, Nema, Jose” szövegsort tömbre (4 -es méret) osztjuk fel vesszővel (, ”).

Const Array

Egy tömb nem tud állandónak kell nyilvánítani a VBA -ban. Ezt azonban megkerülheti egy függvény létrehozásával, amelyet tömbként használhat:

123456789

'Definiálja a ConstantArray -tFunkció ConstantArray ()ConstantArray = Tömb (4, 12, 21, 100, 5)Vége funkció'Retrive ConstantArray ValueSub RetrieveValues ​​()MsgBox ConstantArray (3)End Sub

Tömb másolása

Nincs beépített módja annak, hogy tömböt másoljon a VBA segítségével. Ehelyett hurkot kell használnia az értékek egyik tömbből a másikhoz való hozzárendeléséhez.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 -től 100 -ig)Dim Arr2 (1 -től 100 -ig)Dim i As Long'Tömb létrehozása1I = 1 és 100 közöttArr1 (i) = iKövetkező i„CopyArray1 - Array2I = 1 és 100 közöttArr2 (i) = Arr1 (i)Következő iMsgBox Arr2 (74)End Sub

Átültetés

Nincs beépített VBA funkció, amely lehetővé teszi tömb transzponálását. Azonban írtunk egy funkciót a 2D tömb transzponálásához. További információért olvassa el a cikket.

VBA programozás | A kódgenerátor működik az Ön számára!

Funkció visszatérési tömb

A VBA -fejlesztők gyakori kérdése az, hogyan hozzanak létre egy tömböt visszaadó függvényt. Úgy gondolom, hogy a legtöbb nehézséget a Variant Arys segítségével oldják meg. Írtunk egy cikket a témában: VBA Function Return Array.

Tömbök használata az Access VBA -ban

A fenti tömbpéldák többsége pontosan ugyanúgy működik az Access VBA -ban, mint az Excel VBA -ban. Az egyik fő különbség az, hogy ha tömböt szeretne feltölteni Access adatok segítségével, akkor a RecordSet objektumon kell áthaladnia, nem pedig a Range objektumon.

1234567891011121314151617181920212223

Sub RangeToArrayAccess ()Hiba esetén Folytassa a következőtDim strNames () karakterlánckéntDim i As LongDim iCount AmígDim dbs mint adatbázisDim rst RecordsetkéntÁllítsa be a dbs = CurrentDb értéketÁllítsa be az rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Az elsővel.MoveLast.MoveFirstiCount = .RecordCountReDim strNames (1 az iCount -hoz)I = 1 Az iCount -hozstrNames (i) = rst.Fields ("Ügyfélnév").MoveNextKövetkező iVége ezzelelső. Zárja beSet rst = SemmiSet dbs = SemmiEnd Sub

Tömb oktatóanyagok
Array Mega-Guide	Igen
Töltse le a tömb méretét
Tiszta tömb
Szűrő tömb
Tömb átültetése
Funkció visszatérési tömb
Ismétlődések eltávolítása