VBA nizovi

U VBA -i, an Niz je jedna varijabla koja može sadržavati više vrijednosti. Zamislite niz kao raspon ćelija: svaka ćelija može pohraniti vrijednost. Nizovi mogu biti jednodimenzionalni (zamislite jedan stupac), dvodimenzionalni (pomislite na više redaka i stupaca) ili višedimenzionalni. Vrijednostima niza može se pristupiti njihovim položajem (indeksnim brojem) unutar niza.

Brzi list VBA polja

Nizovi

OpisVBA kodStvoritiPriguši arr (1 do 3) kao varijantu
arr (1) = "jedan"
arr (2) = "dva"
arr (3) = "tri"Stvorite iz programa ExcelPriguši arr (1 do 3) kao varijantu
Dim cell As Range, i As Integer
i = LBound (arr)
Za svaku ćeliju u rasponu ("A1: A3")
i = i + 1
arr (i) = ćelija.vrijednost
Sljedeća ćelijaPročitajte sve stavkeDim i kao Long
Za i = L vezan (arr) za UBound (arr)
MsgBox arr (i)
Sljedeći iIzbrišiIzbriši arrNiz za nizDim sName As String
sName = Pridruži se (arr, “:”)Povećajte veličinuReDim Preserv arr (0 do 100)Postavljena vrijednostarr (1) = 22

Brzi primjeri niza VBA

Pogledajmo cijeli primjer prije nego što uđemo u pojedinosti:

12345678910

Primjer podmare ()Dim strNames (1 do 4) kao StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)Kraj podm

Ovdje smo stvorili jednodimenzionalni niz nizova: strNames s veličinom četiri (mogu sadržavati četiri vrijednosti) i dodijelili četiri vrijednosti. Zadnji put prikazujemo treću vrijednost u okviru za poruku.

U ovom slučaju, korist od polja je mala: potrebna je samo jedna deklaracija varijable umjesto četiri.

No, pogledajmo primjer koji će pokazati pravu moć niza:

12345678

Primjer podmare2 ()Dim strNames (1 To 60000) As StringDim i As LongZa i = 1 do 60000strNames (i) = Cells (i, 1) .VrijednostSljedeći iKraj podm

Ovdje smo stvorili niz koji može sadržavati 60.000 vrijednosti i brzo smo popunili niz iz stupca A radnog lista.

Prednosti niza? - Brzina!

Možda ćete pomisliti na nizove slične radnim listovima programa Excel:

• Svaka ćelija (ili stavka u nizu) može sadržavati vlastitu vrijednost
• Svakoj ćeliji (ili stavci u nizu) može se pristupiti po položaju retka i stupca.
  • Radni list Ex. ćelije (1,4) .value = "Red 1, stupac 4"
  • Niz Ex. arrVar (1,4) = "Red 1, stupac 4"

Zašto se onda mučiti s nizovima? Zašto jednostavno ne čitate i ne zapisujete vrijednosti izravno u ćelije u Excelu? Jedna riječ: Ubrzati!

Čitanje / pisanje u Excel ćelije je spor proces. Rad s nizovima mnogo je brži!

Izradi / proglasi niz (prigušeno)

Napomena: Nizovi mogu imati više "dimenzija". Da pojednostavimo stvari, počet ćemo samo s radom s jednodimenzionalnim nizovima. Kasnije u vodiču upoznat ćemo vas s nizovima s više dimenzija.

Statički niz

Statički nizovi su nizovi koji ne mogu promijeniti veličinu. Obrnuto, Dinamički nizovi može promijeniti veličinu. Deklarirani su nešto drugačije. Prvo, pogledajmo statičke nizove.

Napomena: Ako se vaš niz neće promijeniti u veličini, upotrijebite statički niz.

Deklariranje varijable statičkog niza vrlo je slično deklaraciji regularne varijable, osim što morate definirati veličinu niza. Postoji nekoliko različitih načina za postavljanje veličine niza.

Možete izričito deklarirati početnu i završnu poziciju niza:

123456789101112

Podstatički niz1 ()'Stvara niz s pozicijama 1,2,3,4Priguši arrDemo1 (1 do 4) kao niz'Stvara niz s pozicijama 4,5,6,7Dim arrDemo2 (4 do 7) Dugo'Stvara niz s pozicijama 0,1,2,3Dim arrDemo3 (0 do 3) DugoKraj podm

Ili možete unijeti samo veličinu polja:

123456

Podstatički niz2 ()'Stvara niz s pozicijama 0,1,2,3Dim arrDemo1 (3) Kao nizKraj podm

Važno! Primijetite da prema zadanim postavkama nizovi počinju na poziciji 0. Dakle Dim arrDemo1 (3) stvara niz s pozicijama 0,1,2,3.

Možete se prijaviti Baza mogućnosti 1 na vrhu vašeg modula, tako da niz umjesto toga počinje na poziciji 1:

12345678

Baza mogućnosti 1Sub StaticArray3 ()'Stvara niz s pozicijama 1,2,3Dim arrDemo1 (3) Kao nizKraj podm

Međutim, smatram da je mnogo lakše (i manje zbunjujuće) samo eksplicitno deklarirati početnu i završnu poziciju polja.

Umorni ste od traženja primjera VBA koda? Isprobajte AutoMacro!

Dinamički niz

Dinamički nizovi su nizovi čija se veličina može promijeniti (ili čiju veličinu nije potrebno definirati).

Postoje dva načina za deklariranje dinamičkog niza.

Nizovi varijanti

Prvi način deklariranja dinamičkog niza je postavljanje niza na tip Varijanta.

1	Dim arrVar () Kao varijanta

S Varijanta Array, ne morate definirati veličinu niza. Veličina će se automatski prilagoditi. Samo zapamtite da niz počinje s položajem 0 (osim ako ne dodate Option Base 1 na vrh vašeg modula)

12345678910111213

Sub VariantArray ()Dim arrVar () Kao varijanta'Definirajte vrijednosti (veličina = 0,1,2,3)arrVar = Polje (1, 2, 3, 4)'Promijeni vrijednosti (veličina = 0,1,2,3,4)arrVar = Polje ("1a", "2a", "3a", "4a", "5a")'Izlazni položaj 4 ("5a")MsgBox arrVar (4)Kraj podm

Nevarijantni dinamički nizovi

Kod varijantnih nizova morate definirati veličinu polja prije dodjeljivanja vrijednosti nizu. Međutim, postupak stvaranja niza malo je drugačiji:

1234567

Sub DynamicArray1 ()Dim arrDemo1 () As String'Mijenja veličinu polja s pozicijama 1,2,3,4ReDim arrDemo1 (1 do 4)Kraj podm

Prvo deklarirate niz, sličan statičkom nizu, osim što izostavljate veličinu niza:

1	Dim arrDemo1 () As String

Sada kada želite postaviti veličinu polja koristite ReDim naredba za veličinu polja:

12	'Mijenja veličinu polja s pozicijama 1,2,3,4ReDim arrDemo1 (1 do 4)

ReDim mijenja veličinu polja. U nastavku pročitajte razliku između ReDim i ReDim Preserve.

ReDim vs. ReDim Preserve

Kada koristite ReDim naredba za brisanje svih postojećih vrijednosti iz niza. Umjesto toga možete koristiti Rezervat ReDim za očuvanje vrijednosti niza:

12	'Promjenjuje veličinu polja s pozicijama 1,2,3,4 (očuvanje postojećih vrijednosti)Očuvanje ReDim arrDemo1 (1 do 4)

Pojednostavljivanje deklariranja nizova

Možda ćete se osjećati preopterećeno nakon što pročitate sve gore navedeno. Da pojednostavimo stvari, za ostatak članka uglavnom ćemo raditi sa statičkim nizovima.

Postavite vrijednosti niza

Postavljanje vrijednosti niza vrlo je jednostavno.

Sa statičkim nizom morate definirati svaku poziciju niza, jednu po jednu:

12345678

Primjer podmare ()Dim strNames (1 do 4) kao StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Kraj podm

Pomoću varijantnog niza možete definirati cijeli niz jednim retkom (praktično samo za male nizove):

123456

Pod matrica Primjer_1Line ()Dim strNames () Kao varijantastrNames = Array ("Shelly", "Steve", "Neema", "Jose")Kraj podm

Ako pokušate definirati vrijednost za mjesto niza koje ne postoji, dobit ćete pogrešku Subscript Out of Range:

1	strNames (5) = "Shannon"

U odjeljku "Dodijeli raspon nizu" U nastavku ćemo vam pokazati kako koristiti petlju za brzo dodjeljivanje velikog broja vrijednosti nizovima.

Dobijte vrijednost niza

Na isti način možete dohvatiti vrijednosti niza. U donjem primjeru zapisat ćemo vrijednosti polja u ćelije:

1234	Raspon ("A1"). Vrijednost = nazivi str (1)Raspon ("A2"). Vrijednost = nazivi str (2)Raspon ("A3"). Vrijednost = nazivi str (3)Raspon ("A4"). Vrijednost = nazivi str (4)

VBA programiranje | Generator koda radi za vas!

Dodijeli raspon nizu

Za dodjeljivanje raspona nizu možete koristiti petlju:

12345678

Pod rasponToArray ()Dim strNames (1 To 60000) As StringDim i As LongZa i = 1 do 60000strNames (i) = Cells (i, 1) .VrijednostSljedeći iKraj podm

Ovo će proći kroz ćelije A1: A60000, dodjeljujući vrijednosti ćelije nizu.

Izlazni niz u raspon

Ili možete koristiti petlju za dodjeljivanje niza rasponu:

123	Za i = 1 do 60000Ćelije (i, 1). Vrijednost = strNames (i)Sljedeći i

To će učiniti obrnuto: dodijeliti vrijednosti niza ćelijama A1: A60000

2D / višedimenzionalni nizovi

Do sada smo radili isključivo s jednodimenzionalnim (1D) nizovima. No, nizovi mogu imati do 32 dimenzije.

Zamislite 1D niz kao jedan redak ili stupac Excel ćelija, 2D niz kao cijeli Excel radni list s više redaka i stupaca, a 3D niz je poput cijele radne knjige koja sadrži više listova od kojih svaki sadrži više redaka i stupaca (vi također mogao zamisliti 3D niz kao Rubikovu kocku).

Primjeri višedimenzionalnih nizova

Pokažimo sada primjere rada s nizovima različitih dimenzija.

VBA programiranje | Generator koda radi za vas!

Primjer 1D niza

Ovaj postupak kombinira prethodne primjere niza u jedan postupak, pokazujući kako možete koristiti nizove u praksi.

1234567891011121314

Sub ArrayEx_1d ()Dim strNames (1 To 60000) As StringDim i As Long'Dodijelite vrijednosti nizuZa i = 1 do 60000strNames (i) = Cells (i, 1) .VrijednostSljedeći i'Vrijednosti izlaznog niza u rasponZa i = 1 do 60000Listovi ("Izlaz"). Ćelije (i, 1). Vrijednost = imena imena (i)Sljedeći iKraj podm

Primjer 2D niza

Ovaj postupak sadrži primjer 2D niza:

123456789101112131415161718

Sub ArrayEx_2d ()Dim strNames (1 To 60000, 1 To 10) As StringDim i As Long, j As Long'Dodijelite vrijednosti nizuZa i = 1 do 60000Za j = 1 do 10strNames (i, j) = Cells (i, j) .VrijednostSljedeće jSljedeći i'Vrijednosti izlaznog niza u rasponZa i = 1 do 60000Za j = 1 do 10Listovi ("Izlaz"). Ćelije (i, j). Vrijednost = imena imena (i, j)Sljedeće jSljedeći iKraj podm

Primjer 3D niza

Ovaj postupak sadrži primjer 3D niza za rad s više listova:

12345678910111213141516171819202122

PodpoljEx_3d ()Zatamni nazive str (1 do 60000, 1 do 10, 1 do 3) kao nizDim i As Long, j As Long, k As Long'Dodijelite vrijednosti nizuZa k = 1 do 3Za i = 1 do 60000Za j = 1 do 10strNames (i, j, k) = Sheets ("Sheet" & k). Cells (i, j) .VrijednostSljedeće jSljedeći iSljedeće k'Vrijednosti izlaznog niza u rasponZa k = 1 do 3Za i = 1 do 60000Za j = 1 do 10Listovi ("Izlaz" & k). Ćelije (i, j). Vrijednost = imena str (i, j, k)Sljedeće jSljedeći iSljedeće kKraj podm

Dužina / veličina polja

Do sada smo vas upoznali s različitim vrstama polja i naučili vas kako deklarirati nizove i dobiti/postaviti vrijednosti niza. Zatim ćemo se usredotočiti na druge potrebne teme za rad s nizovima.

VBA programiranje | Generator koda radi za vas!

UBound i LBound funkcije

Prvi korak do dobivanja duljine / veličine niza je pomoću funkcija UBound i LBound za dobivanje gornje i donje granice niza:

123456

Sub UBoundLBound ()Dim strNames (1 To 4) As StringMsgBox UBound (strNames)MsgBox LBound (strNames)Kraj podm

Oduzimanjem dva (i zbrajanjem 1) dobit ćete duljinu:

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

Funkcija duljine niza

Evo funkcije za dobivanje duljine niza s jednom dimenzijom:

1234567

Javna funkcija GetArrLength (a As Variant) As LongAko je IsEmpty (a) TadaGetArrLength = 0DrugoGetArrLength = UBound (a) - LBound (a) + 1Završi akoZavršna funkcija

Trebate izračunati veličinu 2D niza? Pogledajte naš vodič: Izračunajte veličinu niza.

Loop Through Array

Postoje dva načina prolaska kroz niz. Prvi se petlja kroz cijele brojeve koji odgovaraju brojnim pozicijama niza. Ako znate veličinu polja, možete je izravno navesti:

12345678910111213

Primjer podpolja_Pretva1 ()Dim strNames (1 To 4) As StringDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za i = 1 do 4MsgBox strNames (i)Sljedeći iKraj podm

Međutim, ako ne znate veličinu polja (ako je niz dinamičan), možete koristiti funkcije LBound i UBound iz prethodnog odjeljka:

12345678910111213

Primjer podmare_ petlja2 ()Dim strNames (1 To 4) As StringDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za i = LBound (strNames) Za UBound (strNames)MsgBox strNames (i)Sljedeći iKraj podm

Za svaku petlju niza

Druga metoda je s petljom za svaku. Ovo se ponavlja kroz svaku stavku u nizu:

12345678910111213

Primjer podmare_ petlja3 ()Dim strNames (1 To 4) As StringZatamnjena stavkastrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za svaku stavku u strNamesStavka msgBoxSljedeća stavkaKraj podm

Petlja za svaki niz radiće s višedimenzionalnim nizovima osim s jednodimenzionalnim nizovima.

VBA programiranje | Generator koda radi za vas!

Petlja kroz 2D niz

Također možete koristiti funkcije UBound i LBound za petlju kroz višedimenzionalni niz. U ovom primjeru ćemo proći kroz 2D niz. Uočite da vam funkcije UBound i LBound omogućuju da odredite koju dimenziju niza želite pronaći gornju i donju granicu (1 za prvu dimenziju, 2 za drugu dimenziju).

1234567891011121314151617181920

Primjer podmare_Loop4 ()Dim strNames (1 To 4, 1 To 2) As StringDim 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"Za j = LBound (strNames, 2) Za UBound (strNames, 2)Za i = LBound (strNames, 1) Za UBound (strNames, 1)MsgBox strNames (i, j)Sljedeći iSljedeće jKraj podm

Ostali zadaci niza

Čisti niz

Za brisanje cijelog niza upotrijebite izjavu o brisanju:

1	Izbrišite imena str

Primjer upotrebe:

12345678910

Primjer podmare ()Dim strNames (1 do 4) kao StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Izbrišite imena strKraj podm

Alternativno, možete i ponovno prilagoditi niz kako biste mu promijenili veličinu, brišući dio niza:

1	ReDim strNames (1 do 2)

Time se polje mijenja u veličinu 2, brišući pozicije 3 i 4.

Grof Array

Možete brojati broj pozicija u svakoj dimenziji niza pomoću funkcija UBound i LBound (o kojima je gore bilo riječi).

Također možete izbrojati broj unetih stavki (ili stavki koje zadovoljavaju određene kriterije) petljanjem kroz niz.

Ovaj primjer će se kretati kroz niz objekata i brojati broj nizova koji nisu prazni pronađeni u nizu:

123456789101112131415

Sub ArrayLoopandCount ()Dim strNames (1 To 4) As StringDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Za i = LBound (strNames) Za UBound (strNames)Ako strNames (i) "" Tadan = n + 1Završi akoSljedeći iMsgBox n & "pronađene su vrijednosti koje nisu prazne."Kraj podm

VBA programiranje | Generator koda radi za vas!

Uklonite duplikate

U jednom ćete trenutku možda htjeti ukloniti duplikate iz niza. Nažalost, VBA nema ugrađenu značajku za to. Međutim, napisali smo funkciju za uklanjanje duplikata iz niza (predugo je za uključivanje u ovaj vodič, ali posjetite vezu da biste saznali više).

filtar

VBA funkcija filtriranja omogućuje filtriranje niza. To čini stvaranjem novog niza samo s filtriranim vrijednostima. Dolje je kratak primjer, ali svakako pročitajte članak za više primjera za različite potrebe.

1234567891011121314

Podfilter_podudaranja ()'Definiraj nizDim strNames As VariantstrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Niz filtaraDim strSubNames kao varijantastrSubNames = Filter (strNames, "Smith")'Broji filtrirani nizMsgBox "Pronađeno" & UBound (strSubNames) - LBound (strSubNames) + 1 & "imena".Kraj podm

IsArray funkcija

Možete provjeriti je li varijabla niz pomoću funkcije IsArray:

123456789101112

Sub IsArrayEx ()'Stvara niz s pozicijama 1,2,3Dim arrDemo1 (3) Kao niz'Stvara regularnu varijablu nizaDim str Kao nizMsgBox IsArray (arrDemo1)MsgBox IsArray (str)Kraj podm

Pridružite se Arrayu

Možete brzo "pridružiti" cijeli niz zajedno s funkcijom pridruživanja:

123456789101112

Sub Array_Join ()Dim strNames (1 To 4) As StringZatamnite imena pridruživanja kao nizstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Pridruži se (strNames, ",")MsgBox pridružena imenaKraj podm

VBA programiranje | Generator koda radi za vas!

Podijeli niz u niz

VBA Split funkcija će podijeliti niz teksta u niz koji sadrži vrijednosti iz izvornog niza. Pogledajmo primjer:

123456789

Sub Array_Split ()Zatamni imena () Kao nizDim joinedNames As StringjoinNames = "Shelly, Steve, Nema, Jose"Names = Split (joinedNames, ",")MsgBox imena (1)Kraj podm

Ovdje smo ovaj niz teksta "Shelly, Steve, Nema, Jose" podijelili u niz (veličina 4) pomoću graničnika zareza (, ").

Const Array

Niz ne mogu biti deklariran kao konstanta u VBA. Međutim, možete to zaobići stvaranjem funkcije koja će se koristiti kao niz:

123456789

'Definirajte ConstantArrayFunkcija ConstantArray ()ConstantArray = Polje (4, 12, 21, 100, 5)Završna funkcija'Dohvati vrijednost ConstantArrayPodredne vrijednosti dohvaćanja ()MsgBox ConstantArray (3)Kraj podm

Kopiraj niz

Ne postoji ugrađen način kopiranja niza pomoću VBA. Umjesto toga morat ćete koristiti petlju za dodjeljivanje vrijednosti iz jednog niza u drugi.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 do 100) koliko je dugoDim Arr2 (1 do 100) koliko je dugoDim i As Long'Izradi niz1Za i = 1 do 100Arr1 (i) = iSljedeći i'CopyArray1 u Array2Za i = 1 do 100Arr2 (i) = Arr1 (i)Sljedeći iMsgBox Arr2 (74)Kraj podm

Transponiraj

Ne postoji ugrađena VBA funkcija koja vam omogućuje transponiranje niza. Međutim, napisali smo funkciju za transponiranje 2D niza. Pročitajte članak kako biste saznali više.

VBA programiranje | Generator koda radi za vas!

Niz povratnih funkcija

Uobičajeno pitanje koje VBA programeri imaju je kako stvoriti funkciju koja vraća niz. Mislim da se većina poteškoća rješava korištenjem Variant Arrays. Napisali smo članak na temu: Niz povratnih funkcija VBA.

Korištenje niza u Access VBA

Većina gornjih primjera niza radi potpuno isto u Access VBA kao i u Excel VBA. Jedna je velika razlika u tome što kada želite popuniti niz pomoću Accessovih podataka, trebali biste proći kroz objekt RecordSet, a ne objekt Range.

1234567891011121314151617181920212223

Pod RangeToArrayAccess ()Uključeno Slijedi nastavak greškeDim strNames () Kao nizDim i As LongZatamni iCount koliko god dugoDim dbs kao baza podatakaDim rst As RecordsetPostavite dbs = CurrentDbPostavi rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvim.Pomičite se posljednje.Pomiči se prvoiCount = .RecordCountReDim strNames (1 Za iCount)Za i = 1 Za iCountstrNames (i) = rst.Fields ("ClientName").Pomiči seSljedećiSljedeći iZavrši sprvo.ZatvoriPostavite rst = NištaPostavite dbs = NištaKraj podm

Vodiči o nizu
Mega-vodič kroz niz	Da
Dobijte veličinu niza
Čisti niz
Niz filtera
Transponiraj niz
Niz povratnih funkcija
Uklonite duplikate