Nizki VBA

V VBA je an Niz je ena sama spremenljivka, ki lahko vsebuje več vrednosti. Zamislite si niz kot obseg celic: vsaka celica lahko shrani vrednost. Nizovi so lahko enodimenzionalni (pomislite na en sam stolpec), dvodimenzionalni (pomislite na več vrstic in stolpcev) ali večdimenzionalni. Do vrednosti niza lahko dostopate po njihovem položaju (indeksni številki) v matriki.

Hitri list matrike VBA

Nizovi

OpisKoda VBAUstvariZatemni arr (1 do 3) kot varianto
arr (1) = "ena"
arr (2) = "dva"
arr (3) = "tri"Ustvari iz ExcelaZatemni arr (1 do 3) kot varianto
Zatemni celico kot obseg, i kot celo število
i = LBound (arr)
Za vsako celico v razponu ("A1: A3")
i = i + 1
arr (i) = celica.vrednost
Naslednja celicaPreberite vse postavkeDim i as Long
Za i = LBound (arr) Za UBound (arr)
MsgBox arr (i)
Naprej iIzbrišiIzbriši naslNiz v nizZatemni ime kot niz
sName = Pridruži se (arr, “:”)Povečajte velikostReDim Preserve arr (0 do 100)Nastavi vrednostarr (1) = 22

Hitri primeri niza VBA

Oglejmo si celoten primer, preden se poglobimo v podrobnosti:

12345678910

Primer podmočij ()Zatemni imena str (1 do 4) kot nizstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"msgbox strNames (3)End Sub

Tu smo ustvarili enodimenzionalno niz nizov: strNames s velikostjo štiri (lahko vsebujejo štiri vrednosti) in jim dodelili štiri vrednosti. Nazadnje v polju za sporočila prikažemo tretjo vrednost.

V tem primeru je korist uporabe polja nizka: namesto štirih je potrebna le ena deklaracija spremenljivke.

Poglejmo pa primer, ki bo pokazal resnično moč matrike:

12345678

Sub ArrayExample2 ()Zatemni imena imen (od 1 do 60000) kot nizDim i As LongZa i = 1 do 60000strNames (i) = Celice (i, 1) .VrednostNaprej iEnd Sub

Tu smo ustvarili matriko, ki lahko vsebuje 60.000 vrednosti, in hitro napolnili matriko iz stolpca A delovnega lista.

Prednosti matrike? - Hitrost!

Morda si omislite matrike, podobne Excelovim delovnim listom:

• Vsaka celica (ali element v matriki) lahko vsebuje svojo vrednost
• Do vsake celice (ali elementa v matriki) lahko dostopate s položajem vrstice in stolpca.
  • Delovni list Ex. celice (1,4) .value = “Vrstica 1, stolpec 4”
  • Niz Ex. arrVar (1,4) = “Vrstica 1, stolpec 4”

Zakaj bi se torej ukvarjali z Arrays? Zakaj preprosto ne berete in ne zapisujete vrednosti neposredno v celice v Excelu? Ena beseda: Hitrost!

Branje / pisanje v celice Excel je počasen proces. Delo z Arrays je veliko hitrejše!

Ustvari / razglasi niz (zatemni)

Opomba: Nizki imajo lahko več "dimenzij". Če želimo stvari poenostaviti, se bomo najprej lotili dela z enodimenzionalnimi matrikami. Kasneje v vadnici vam bomo predstavili matrike z več dimenzijami.

Statični niz

Statični nizi so matrike, ki ne morejo spremeniti velikosti. Nasprotno pa Dinamični nizi lahko spremeni velikost. Razglašeni so nekoliko drugače. Najprej si oglejmo statične matrike.

Opomba: Če se vaša matrika ne spremeni v velikosti, uporabite statično matriko.

Razglasitev spremenljivke statične matrike je zelo podobna razglasitvi običajne spremenljivke, le da morate določiti velikost matrike. Velikost polja lahko nastavite na več različnih načinov.

Začetni in končni položaj matrike lahko izrecno deklarirate:

123456789101112

Sub StaticArray1 ()'Ustvari matriko s položaji 1,2,3,4Zatemni arrDemo1 (1 do 4) kot niz'Ustvari matriko s položaji 4,5,6,7Zatemni arrDemo2 (4 do 7) tako dolgo'Ustvari matriko s položaji 0,1,2,3Zatemni arrDemo3 (0 do 3) tako dolgoEnd Sub

Lahko pa vnesete samo velikost matrike:

123456

Sub StaticArray2 ()'Ustvari matriko s položaji 0,1,2,3Zatemni arrDemo1 (3) Kot nizEnd Sub

Pomembno! Upoštevajte, da se matrike privzeto začnejo na položaju 0. Torej Dim arrDemo1 (3) ustvari matriko s položaji 0,1,2,3.

Lahko izjavite Osnovna možnost 1 na vrhu modula, tako da se matrika namesto tega začne na položaju 1:

12345678

Osnovna možnost 1Sub StaticArray3 ()'Ustvari matriko s položaji 1,2,3Zatemni arrDemo1 (3) Kot nizEnd Sub

Vendar se mi zdi, da je veliko lažje (in manj zmedeno) samo izrecno razglasiti začetni in končni položaj nizov.

Utrujen od iskanja primerov kode VBA? Preizkusite AutoMacro!

Dinamični niz

Dinamični nizi so matrike, katerih velikost je mogoče spremeniti (ali katerih velikosti ni treba definirati).

Dinamični niz lahko razglasite na dva načina.

Različni nizi

Prvi način razglasitve dinamičnega niza je nastavitev matrike na tipkanje Različica.

1	Zatemni arrVar () kot varianto

Z Različica Array, velikosti niza vam ni treba določiti. Velikost se samodejno prilagodi. Ne pozabite, da se niz začne s položajem 0 (razen če na vrh modula dodate Option Base 1)

12345678910111213

Sub VariantArray ()Zatemni arrVar () kot varianto'Določi vrednosti (velikost = 0,1,2,3)arrVar = Niz (1, 2, 3, 4)'Spremeni vrednosti (velikost = 0,1,2,3,4)arrVar = Niz ("1a", "2a", "3a", "4a", "5a")'Izhodni položaj 4 ("5a")MsgBox arrVar (4)End Sub

Nespremenljivi dinamični nizi

Pri variabilnih matrikah morate določiti velikost matrike, preden ji dodelite vrednosti. Vendar je postopek ustvarjanja matrike nekoliko drugačen:

1234567

Sub DynamicArray1 ()Zatemni arrDemo1 () kot niz'Spremeni velikost matrike s položaji 1,2,3,4ReDim arrDemo1 (1 do 4)End Sub

Najprej razglasite matriko, podobno statični matriki, razen če izpustite velikost matrike:

1	Zatemni arrDemo1 () kot niz

Zdaj, ko želite nastaviti velikost matrike, uporabite datoteko ReDim ukaz za velikost matrike:

12	'Spremeni velikost matrike s položaji 1,2,3,4ReDim arrDemo1 (1 do 4)

ReDim spremeni velikost matrike. Spodaj preberite razliko med ReDim in ReDim Preserve.

ReDim proti ReDim Preserve

Ko uporabljate ReDim ukaz počistite vse obstoječe vrednosti iz matrike. Namesto tega lahko uporabite ReDim Preserve ohraniti vrednosti matrike:

12	'Spreminjanje velikosti matrike s položaji 1,2,3,4 (ohranjanje obstoječih vrednosti)ReDim Preserve arrDemo1 (1 do 4)

Razglašanje poenostavljenih nizov

Morda se počutite preobremenjeni, ko preberete vse zgoraj. Da bi bilo preprosto, bomo do konca članka večinoma delali s statičnimi matrikami.

Nastavite vrednosti matrike

Nastavitev vrednosti matrike je zelo enostavna.

S statično matriko morate določiti vsak položaj matrike, enega za drugim:

12345678

Primer podmočij ()Zatemni imena str (1 do 4) kot nizstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"End Sub

Z nizom variant lahko celotno matriko definirate z eno vrstico (praktično le za majhne matrike):

123456

Sub ArrayExample_1Line ()Zatemni strNames () kot variantostrNames = Array ("Shelly", "Steve", "Neema", "Jose")End Sub

Če poskusite določiti vrednost za lokacijo niza, ki ne obstaja, boste prejeli napako Subscript Out of Range:

1	strNames (5) = "Shannon"

V razdelku »Dodelitev obsega matriki« Spodaj vam bomo pokazali, kako z zanko hitro dodeliti veliko število vrednosti nizom.

Pridobite vrednost matrike

Na enak način lahko pridobite vrednosti matrike. V spodnjem primeru bomo celice zapisali vrednosti matrike:

1234	Obseg ("A1"). Vrednost = imena str (1)Razpon ("A2"). Vrednost = imena str (2)Obseg ("A3"). Vrednost = imena str (3)Razpon ("A4"). Vrednost = imena str (4)

Programiranje VBA | Generator kod deluje za vas!

Dodelite obseg nizu

Če želite razponu dodeliti niz, lahko uporabite zanko:

12345678

PodrazponToArray ()Zatemni imena imen (od 1 do 60000) kot nizDim i As LongZa i = 1 do 60000strNames (i) = Celice (i, 1) .VrednostNaprej iEnd Sub

To bo krožilo skozi celice A1: A60000 in dodelilo vrednosti celic matriki.

Izhodni niz v obseg

Lahko pa uporabite zanko, če želite nizu dodeliti obseg:

123	Za i = 1 do 60000Celice (i, 1) .Vrednost = strNames (i)Naprej i

To bo naredilo obratno: celicam A1: A60000 dodelite vrednosti matrike

2D / večdimenzionalni nizi

Doslej smo delali izključno z enodimenzionalnimi (1D) matrikami. Polja pa imajo lahko do 32 dimenzij.

Pomislite na matriko 1D kot eno vrstico ali stolpec Excelovih celic, matriko 2D kot celoten Excelov delovni list z več vrsticami in stolpci, matrika 3D pa je kot celoten delovni zvezek, ki vsebuje več listov, od katerih vsak vsebuje več vrstic in stolpcev (vi lahko bi si zamislili tudi 3D matriko kot Rubikovo kocko).

Primeri večdimenzionalnih nizov

Zdaj pa pokažimo primere dela z matrikami različnih dimenzij.

Programiranje VBA | Generator kod deluje za vas!

Primer 1D matrike

Ta postopek združuje prejšnje primere nizov v en postopek, ki prikazuje, kako lahko matrike uporabljate v praksi.

1234567891011121314

Sub ArrayEx_1d ()Zatemni imena imen (od 1 do 60000) kot nizDim i As Long'Dodelite vrednosti matrikiZa i = 1 do 60000strNames (i) = Celice (i, 1) .VrednostNaprej i'Vrednosti izhodnega niza v razponuZa i = 1 do 60000Listi ("Output"). Celice (i, 1) .Vrednost = strNames (i)Naprej iEnd Sub

Primer 2D matrike

Ta postopek vsebuje primer 2D matrike:

123456789101112131415161718

Sub ArrayEx_2d ()Zatemni imena imen (od 1 do 60000, od 1 do 10) kot nizDim i As Long, j As Long'Dodelite vrednosti matrikiZa i = 1 do 60000Za j = 1 do 10strNames (i, j) = Celice (i, j) .VrednostNaprej jNaprej i'Vrednosti izhodnega niza v razponuZa i = 1 do 60000Za j = 1 do 10Listi ("Output"). Celice (i, j) .Vrednost = strNames (i, j)Naprej jNaprej iEnd Sub

Primer matrike 3D

Ta postopek vsebuje primer matrike 3D za delo z več listi:

12345678910111213141516171819202122

Sub ArrayEx_3d ()Zatemni imena imen (od 1 do 60000, od 1 do 10, od 1 do 3) kot nizDim i As Long, j As Long, k As Long'Dodelite vrednosti matrikiZa k = 1 do 3Za i = 1 do 60000Za j = 1 do 10strNames (i, j, k) = Sheets ("Sheet" & k). Celice (i, j) .VrednostNaprej jNaprej iNaprej k'Vrednosti izhodnega niza v razponuZa k = 1 do 3Za i = 1 do 60000Za j = 1 do 10Listi ("Output" & k). Celice (i, j) .Vrednost = strNames (i, j, k)Naprej jNaprej iNaprej kEnd Sub

Dolžina / velikost matrike

Doslej smo vas seznanili z različnimi vrstami nizov in vas naučili, kako razglasiti matrike in pridobiti/nastaviti vrednosti matrike. Nato se bomo osredotočili na druge potrebne teme za delo z matrikami.

Programiranje VBA | Generator kod deluje za vas!

UBound in LBound funkcije

Prvi korak k pridobivanju dolžine / velikosti matrike je uporaba funkcij UBound in LBound za pridobitev zgornje in spodnje meje matrike:

123456

Sub UBoundLBound ()Zatemni imena imen (1 do 4) kot nizMsgBox UBound (strNames)MsgBox LBound (strNames)End Sub

Če odštejete dva (in dodate 1), dobite dolžino:

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

Funkcija dolžine niza

Tukaj je funkcija za določitev dolžine enorazsežne matrike:

1234567

Javna funkcija GetArrLength (a As Variant) As LongČe je IsEmpty (a) PotemGetArrLength = 0Sicer paGetArrLength = UBound (a) - LBound (a) + 1Konec ČeKončana funkcija

Ali morate izračunati velikost 2D matrike? Oglejte si našo vadnico: Izračunajte velikost matrike.

Loop Through Array

Obstajata dva načina za kroženje po nizu. Prva se vrti skozi cela števila, ki ustrezajo številčnim položajem matrike. Če poznate velikost matrike, jo lahko določite neposredno:

12345678910111213

Sub ArrayExample_Loop1 ()Zatemni imena imen (1 do 4) kot nizDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za i = 1 do 4MsgBox strNames (i)Naprej iEnd Sub

Če pa ne poznate velikosti matrike (če je matrika dinamična), lahko uporabite funkcije LBound in UBound iz prejšnjega razdelka:

12345678910111213

Sub ArrayExample_Loop2 ()Zatemni imena imen (1 do 4) kot nizDim i As LongstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za i = LBound (strNames) Za UBound (strNames)MsgBox strNames (i)Naprej iEnd Sub

Za vsako zanko niza

Druga metoda je z zanko za vsako. To se vrti skozi vsak element v matriki:

12345678910111213

Sub ArrayExample_Loop3 ()Zatemni imena imen (1 do 4) kot nizZatemnjen elementstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Za vsak element v imenih strMsgBox elementNaslednji elementEnd Sub

Zanka For Every Array Loop bo poleg enodimenzionalnih nizov delovala tudi z večdimenzionalnimi matrikami.

Programiranje VBA | Generator kod deluje za vas!

Loop Through 2D Array

Funkcije UBound in LBound lahko uporabite tudi za prehod skozi večdimenzionalno matriko. V tem primeru bomo krožili skozi 2D matriko. Upoštevajte, da vam funkciji UBound in LBound omogočata, da določite, v kateri dimenziji matrike najdete zgornje in spodnje meje (1 za prvo dimenzijo, 2 za drugo dimenzijo).

1234567891011121314151617181920

Sub ArrayExample_Loop4 ()Zatemni imena imen (1 do 4, 1 do 2) kot nizDim 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)Naprej iNaprej jEnd Sub

Druge naloge matrike

Počisti niz

Če želite počistiti celotno matriko, uporabite izjavo o brisanju:

1	Izbrišite imena str

Primer uporabe:

12345678910

Primer podmočij ()Zatemni imena str (1 do 4) kot nizstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"Izbrišite imena strEnd Sub

Druga možnost je, da tudi spremenite velikost matrike, da ji spremenite velikost, in počistite del matrike:

1	ReDim strNames (1 do 2)

S tem spremenite velikost matrike na velikost 2 in izbrišete položaji 3 in 4.

Grof Array

Število položajev v vsaki dimenziji matrike lahko preštejete s funkcijami UBound in LBound (obravnavano zgoraj).

Število vnesenih elementov (ali elementov, ki izpolnjujejo določena merila) lahko preštejete tudi tako, da prelistate matriko.

Ta primer se bo vrtel skozi niz predmetov in prešteval število praznih nizov, ki jih najdemo v matriki:

123456789101112131415

Sub ArrayLoopandCount ()Zatemni imena imen (1 do 4) kot nizDim i As Long, n As LongstrNames (1) = "Shelly"strNames (2) = "Steve"Za i = LBound (strNames) Za UBound (strNames)Če strNames (i) "" Potemn = n + 1Konec ČeNaprej iMsgBox n & "najdene prazne vrednosti."End Sub

Programiranje VBA | Generator kod deluje za vas!

Odstranite podvojene datoteke

V nekem trenutku boste morda želeli odstraniti dvojnike iz niza. Žal VBA za to nima vgrajene funkcije. Vendar smo napisali funkcijo za odstranjevanje dvojnikov iz niza (predolga je za vključitev v to vadnico, vendar za več informacij obiščite povezavo).

Filter

Funkcija filtriranja VBA omogoča filtriranje matrike. To stori tako, da ustvari novo polje samo s filtriranimi vrednostmi. Spodaj je hiter primer, vendar preberite članek za več primerov za različne potrebe.

1234567891011121314

Sub Filter_Match ()'Določi nizZatemni imena str. Kot različicostrNames = Array ("Steve Smith", "Shannon Smith", "Ryan Johnson")'Niz filtrovZatemni strSubNames kot variantostrSubNames = Filter (strNames, "Smith")'Preštej filtriran nizMsgBox "Najdeno" & UBound (strSubNames) - LBound (strSubNames) + 1 & "imena".End Sub

Funkcija IsArray

Če je spremenljivka matrika, lahko preizkusite s funkcijo IsArray:

123456789101112

Sub IsArrayEx ()'Ustvari matriko s položaji 1,2,3Zatemni arrDemo1 (3) Kot niz'Ustvari običajno spremenljivko nizaDim str kot nizMsgBox IsArray (arrDemo1)MsgBox IsArray (str)End Sub

Pridružite se Array

S funkcijo Join se lahko hitro pridružite celotnemu nizu:

123456789101112

Sub Array_Join ()Zatemni imena imen (1 do 4) kot nizZatemni joinNames As StringstrNames (1) = "Shelly"strNames (2) = "Steve"strNames (3) = "Neema"strNames (4) = "Jose"joinNames = Pridruži se (strNames, ",")MsgBox joinNamesEnd Sub

Programiranje VBA | Generator kod deluje za vas!

Razdeli niz v niz

Funkcija VBA Split bo razdelila niz besedila v niz, ki vsebuje vrednosti iz prvotnega niza. Poglejmo primer:

123456789

Sub Array_Split ()Zatemni imena () kot nizDim joinedNames As StringjoinNames = "Shelly, Steve, Nema, Jose"Names = Split (joinedNames, ",")Imena sporočil (1)End Sub

Tu razdelimo ta niz besedila »Shelly, Steve, Nema, Jose« v matriko (velikost 4) z ločevalnikom vej (, «).

Const Array

Niz ne more razglasi za konstanto v VBA. Vendar pa se tega lahko izognete tako, da ustvarite funkcijo za uporabo kot matriko:

123456789

'Določite ConstantArrayFunkcija ConstantArray ()ConstantArray = Niz (4, 12, 21, 100, 5)Končana funkcija'Pridobite vrednost ConstantArraySub RetrieveValues ​​()MsgBox ConstantArray (3)End Sub

Kopiraj niz

Ni vgrajenega načina za kopiranje niza z uporabo VBA. Namesto tega boste morali z zanko dodeliti vrednosti iz enega niza drugemu.

12345678910111213141516171819

Sub CopyArray ()Dim Arr1 (1 do 100) tako dolgoDim Arr2 (1 do 100) tako dolgoDim i As Long'Ustvari niz1Za i = 1 do 100Arr1 (i) = iNaprej i'CopyArray1 v Array2Za i = 1 do 100Arr2 (i) = Arr1 (i)Naprej iMsgBox Arr2 (74)End Sub

Transponiraj

Ni vgrajene funkcije VBA, ki bi omogočala prenašanje matrike. Vendar smo napisali funkcijo za prenos 2D matrike. Preberite članek, če želite izvedeti več.

Programiranje VBA | Generator kod deluje za vas!

Vrnitev niza funkcij

Pogosto vprašanje razvijalcev VBA je, kako ustvariti funkcijo, ki vrne matriko. Mislim, da se večina težav odpravi z uporabo variacijskih nizov. Napisali smo članek na temo: Vrstni niz funkcij VBA.

Uporaba nizov v Access VBA

Večina zgornjih primerov Array deluje v programu Access VBA popolnoma enako kot v Excelu VBA. Glavna razlika je v tem, da bi morali, če želite matriko zapolniti z Accessovimi podatki, prečkati objekt RecordSet in ne objekt Range.

1234567891011121314151617181920212223

PodrazponToArrayAccess ()On Napaka Nadaljuj NaprejDim strNames () Kot nizDim i As LongZatemni iCount tako dolgoZatemni dbs kot bazo podatkovZatemni najprej kot zapis zapisaNastavite dbs = CurrentDbNastavite rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvim.Premaknite se nazadnje.Premakni se najprejiCount = .RecordCountReDim strNames (1 Za iCount)Za i = 1 Za iCountstrNames (i) = rst.Fields ("ClientName").Premakni se naprejNaprej iKončaj snajprej ZapriNastavi rst = NičNastavi dbs = ničEnd Sub

Vadnice o nizu
Mega-vodič po nizu	ja
Pridobite velikost polja
Počisti niz
Niz filtrov
Transponiraj niz
Vrnitev niza funkcij
Odstranite podvojene datoteke