Meny

Excel VBA Array og Array Methods With Eksempler

  • Hoved
  • Annen
  • Excel VBA Array og Array Methods With Eksempler

excel vba array array methods with examples

Prøv Instrumentet Vårt For Å Eliminere Problemer

Denne opplæringen vil forklare VBA Array, forskjellige array-typer, variant array og array-metoder ved hjelp av programmeringseksempler:

En vanlig VBA-variabel er en plassholder som lagrer verdien av en enkelt data. Den har et forhold mellom 1 og 1, dvs. 1 variabel for 1 verdi.

Tenk deg å lagre flere verdier som er av samme type. I stedet for å lage flere variabler, kan du bare opprette en variabel og lagre alle de samme verdiene. Denne variabelen kalles en ARRAY.

=> Besøk her for å se VBA Training Series for All

Arrays I VBA

gratis programvare for å fikse registerfeil

I denne opplæringen vil du bli kjent med hva som er et VBA-utvalg, endimensjonale og todimensjonale matriser sammen med de forskjellige typene matriser som Fixed og Dynamic. Vi vil også forstå forskjellige matrismetoder som brukes i VBA.

Hva du vil lære:

VBA Array

Arrays er en spesiell type variabel som kan lagre flere verdier av samme datatype.

For eksempel, Hvis du har navnene på 100 ansatte, kan du i stedet for å lage 100 variabler med datatypestrengen bare opprette en arrayvariabel av typestrengen og tildele 100 verdier til den samme arrayvariabelen.

One Dimensional Array

En matrise som har alle elementene i en enkelt rad eller i en enkelt kolonne kalles en endimensjonal matrise. Å føre opp navnene til alle elevene i klassen i en enkelt kolonne er et eksempel på en endimensjonal matrise. Det er erklært som vist nedenfor.

Demp matrisenavn (nedre til øvre bunn) som datatype

Det er flere måter å erklære en matrise på. Nedenfor er noen eksempler.

Eksempel:

# 1) Dim MyArrayExample (0 til 3) som heltall

Oppretter en matrise med plassering 0,1,2,3 som aksepterer heltallverdier.

# 2) Dim MyArray2 (3) Som streng

Standardinnstillingen er 0 til 3 og oppretter en matrise med plassering 0,1,2,3 som aksepterer strengverdier.

# 3) Dim MyArray2 (13 til 15) Som dobbelt

Oppretter en matrise som starter fra 13, dvs. 13, 14 og 15, og aksepterer doble verdier. Vi har nevnt nedre grense som 13, så matrisen vil begynne å tildele verdier fra sted 13 i stedet for 0.

La oss lage en enkel kode og forstå alle de tre måtene for matrixerklæring.

Merk: Å skrive VB-kode Åpne Microsoft Excel (støttede versjoner er Excel 2007, 2010, 2013, 2016, 2019). Navigere til Utviklerfane -> Visual Basic (Alternativt kan du bruke snarvei Alt + F11). Klikk på i VB-redigereren Sett inn -> Modul og lim inn koden nedenfor.

Vurder prosedyren nedenfor som viser de forskjellige typene erklæringer.

Private Sub arrayExample1() Dim firstQuarter(0 To 2) As String ‘creates array with index 0,1,2 firstQuarter(0) = 'Jan' firstQuarter(1) = 'Feb' firstQuarter(2) = 'Mar' MsgBox 'First Quarter in calendar ' & ' ' & firstQuarter(0) & ' ' & firstQuarter(1) & ' ' & firstQuarter(2) End Sub Private Sub arrayExample2() Dim secondQuarter(2) As String ‘creates array with index 0,1,2 secondQuarter(0) = 'April' secondQuarter(1) = 'May' secondQuarter(2) = 'June' MsgBox 'Second Quarter in calendar ' & ' ' & secondQuarter(0) & ' ' & secondQuarter(1) & ' ' & secondQuarter(2) End Sub Private Sub arrayExample3() Dim thirdQuarter(13 To 15) As String ‘creates array with index 13,14,15 thirdQuarter(13) = 'July' thirdQuarter(14) = 'Aug' thirdQuarter(15) = 'Sep' MsgBox 'Third Quarter in calendar ' & ' ' & thirdQuarter(13) & ' ' & thirdQuarter(14) & ' ' & thirdQuarter(15) End Sub

Trykk på F5 eller trykk på run-knappen på verktøylinjen for å utføre koden.

One Dimensional Array - utgang

Regular Variable Vs Array Variable

Vi vet nå hvordan en endimensjonal matrise fungerer. Så la oss ta et øyeblikk for å forstå hvorfor matriser er så avgjørende i programmeringsspråk.

Anta at du må oppgi lønnen til 5 ansatte. For å oppnå dette ved hjelp av en vanlig variabel, må du opprette 5 variabler.

Public Sub RegularVariable() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') ' Declare variable for each student Dim Emp1 As String Dim Emp2 As String Dim Emp3 As String Dim Emp4 As String Dim Emp5 As String ' Read student marks from cell Emp1 = shet.Range('A' & 2).Value Emp2 = shet.Range('A' & 3).Value Emp3 = shet.Range('A' & 4).Value Emp4 = shet.Range('A' & 5).Value Emp5 = shet.Range('A' & 6).Value ' Print student marks Debug.Print 'Emp Name' Debug.Print Emp1 Debug.Print Emp2 Debug.Print Emp3 Debug.Print Emp4 Debug.Print Emp5 End Sub

La oss nå bygge den samme koden ved hjelp av en Array-variabel.

Option Explicit Public Sub ArrayVarible() Dim shet As Worksheet Set shet = ThisWorkbook.Worksheets('Sheet1') Dim Employee(1 To 6) As String Dim i As Integer For i = 1 To 6 Employee(i) = shet.Range('A' & i).Value Debug.Print Employee(i) Next i End Sub

Her har vi nettopp brukt en arrayvariabel som vil lagre alle ansattens navn. Anta at du trenger å legge til 100 ansattnavn, så trenger du bare å endre størrelsen på matrisen og ikke trenger å opprette en ny variabel.

Dette vil redusere antall linjer i koden og dermed gjøre den lett forståelig og lesbar.

To-dimensjonal matrise

En todimensjonal matrise har to indekser - den første indeksen vil representere radene og den andre indeksen vil representere kolonnen. Den har flere rader og kolonner og er vanligvis representert i et tabellformat.

Erklæringen om et 2-dim array er som følger:

Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) Som datatype.

Tenk på et eksempel på lagring av karakterer for 2 studenter som er oppnådd i 3 fag. Så vi vil lage en todimensjonal matrise som tar 2 rader og 3 kolonner.

Vi starter matrisen fra rad 1 til rad 2 og kolonne 1 til kolonne 3.

Sub Twodim() Dim totalMarks(1 To 2, 1 To 3) As Integer totalMarks(1, 1) = 23 totalMarks(2, 1) = 34 totalMarks(1, 2) = 33 totalMarks(2, 2) = 55 totalMarks(1, 3) = 45 totalMarks(2, 3) = 44 Msgbox “Total Marks in Row 2 and column 2 is “ &totalMarks(2,2) Msgbox “Total Marks in Row 1 and column 3 is “ &totalMarks(1,3) End Sub

Trykk på F5 eller trykk på run-knappen på verktøylinjen for å utføre koden.

Rad 2 og kolonne 2

To dimensjonal matrise - Eksempel

Rad 1 og kolonne 3

To-dimensjonalt array - utgang

Faste arrays

Faste matriser også kalt statiske matriser har en fast nedre og øvre grense, og denne størrelsen kan ikke endres på kjøretid. Størrelsen på matrisen er spesifisert under erklæringen i parentes. Alle eksemplene ovenfor er faste matriser, ettersom vi har nevnt størrelsen på den under erklæringen.

Faste matriser brukes vanligvis når du er sikker på størrelsen på matrisen. For eksempel, antall dager i en uke, kan du opprette en matrise med nedre grense 0 og øvre grense 6 og være sikker på at du aldri vil endre størrelsen.

Dynamiske matriser

Dynamiske matriser lar oss endre størrelse på matrisen i løpet av kjøretiden. Disse er nyttige når du ikke er sikker på størrelsen på matrisen. Anta at du ved collegeopptak ikke er sikker på hvor mange studenter som faktisk får opptak, så du kan ikke bestemme størrelsen på design- eller erklæringstidspunktet.

Erklæring om en dynamisk matrise ligner på en statisk matrise med tomme parenteser.

Dunk ansatt () som streng

REDIM

Når vi vil endre størrelsen vi trenger å bruke REDIM , må vi merke oss at den nedre grensen ikke kan endres, vi kan bare endre den øvre grensen til matrisen.

Sub dynamicArray() Dim dynArray() As String Dim curdate As Date curdate = Now ReDim dynArray(2) ‘ Redim will help to change the array size during runtime dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled after ' & curdate & ' are “ & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) End Sub

REDIM

Nå vet vi at vi kan endre størrelsen på matrisen i løpet av kjøretiden, og derfor kan vi bruke ReDim-setningen når vi trenger å øke det tilbaketrukne til en matrise. La oss prøve å øke matrisestørrelsen en gang til og legge til et nytt studentnavn.

Sub RedimExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim dynArray(3) ‘ Redim will reinitialise the array and destroy the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub

REDim Eksempel

Du ville ha observert at resultatet ikke viste navnene på studentene som ble lagt til før, det gir en nullverdi. Det er fordi Redim-setningen vil skape en ny matrise med en ny størrelse og ødelegge de gamle verdiene.

ReDim Preserve

Representative statement hjelper oss med å overvinne begrensningen av ReDim ved å bevare de gamle verdiene og derved øke størrelsen på arrayet.

La oss omskrive koden ovenfor ved hjelp av ReDim Preserve.

Sub preserveExample() Dim dynArray() As String Dim curdate As Date curdate = Now Dim size As Integer ReDim dynArray(2) dynArray(0) = 'John' dynArray(1) = 'Tom' dynArray(2) = 'Tonny' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) ReDim preserve dynArray(3) ‘ Redim preserve will retain the old values dynArray(3) = 'John' MsgBox 'Students Enrolled untill ' & curdate & ' are ' & dynArray(0) & ', ' & dynArray(1) & ', ' & dynArray(2) & ' , ' & dynArray(3) End Sub

REDim Preserve

Ettersom vi har brukt bevare nøkkelordet, går ikke de tidligere angitte verdiene tapt, og den nye verdien blir lagt til.

Variant Array

Til nå har vi sett en matrise som aksepterer samme type verdier. La oss nå erklære matrisen som en variant og lagre de forskjellige typene data som String, Date, Long, Integer i en enkelt array.

Eksempel:

Sub arrayVariant() Dim arrayData(3) As Variant arrayData(0) = 'Vikram Vikrant' arrayData(1) = 411234567890# arrayData(2) = 38 arrayData(3) = '06-09-1972' MsgBox 'Details of person ' & arrayData(0) & ' is ' & ' Phone No ' & arrayData(1) & ' ,Id ' & arrayData(2) & ' ,DOB ' & arrayData(3) End Sub

Variant Array

VBA Array Methods

Det er flere metoder i VBA-arrays som vil hjelpe oss å utføre forskjellige funksjoner, som nevnt nedenfor.

Sl. NeiNavnSyntaksBeskrivelse
7 Bli med Bli med (sourcearray, (delimiter))Føyer sammen flere understreng i en matrise og returnerer en strengverdi.
1 Array Array (falske)Konverterer en vanlig variant
variabel til en matrise.
to Viske ut Slett matrisenavnBrukes til å gjeninnføre den faste størrelsesmatrisen
og frigjør minnet for Dynamic array.
3 IsArray IsArray (variablenavn)Bestemmer om en variabel er en matrise.
4 Lbound LBound (ArrayName, (Dimension))Returnerer det laveste abonnementet
av en matrise.
5 Ubundet UBound (ArrayName, (Dimension))Returnerer det høyeste abonnementet
av en matrise.
6 Dele Split (uttrykk, (skilletegn, (begrense, (sammenligne))))Den deler en streng i flere understreng og returnerer en nullbasert matrise.
8 Filter Filter (sourcearray, match, (include, (Compare)))Filter lar oss søke a
spesifisert samsvar fra en matrise.

La oss diskutere hver av dem i detalj med et eksempel.

# 1) Array

La oss erklære en vanlig variantvariabel og bruke den som en matrise. Når du vil endre en vanlig variantvariabel til en matrise, må vi bruke en ARRAY fungerer som vist i eksemplet nedenfor.

Array-funksjoner godtar et argument som inneholder kommaadskilte verdier. Disse verdiene er tildelt som et element i matrisen.

Sub variantArray() Dim varData As Variant varData = Array('Mon Bel', '+61 112334123', 567, '06-09-1972') MsgBox 'Details of person ' & varData(0) & ' is ' & ' Phone No ' & varData(1) & ' ,Id ' & varData(2) & ' ,DOB ' & varData(3) End Sub

Array

Du må identifisere en arrayvariabel ved hjelp av en indeks, og derfor blir verdiene hentet som varData (0) varData (2) varData (3) i ovenstående eksempel.

# 2) Slett

Denne funksjonen vil slette alle verdiene som er angitt for en matrise med fast størrelse og frigjøre minne for en dynamisk matrise.

Syntaks: Slett matrisenavn

Slett har ulik oppførsel for forskjellige datatyper som angitt nedenfor.

  • For et fast tall: Alle verdiene tilbakestilles til null.
  • For en datatype med fast streng: Alle verdiene tilbakestilles til null lengde.
  • For et dynamisk utvalg: Frigjør minnet som brukes av matrisen.

Eksempel:

Sub eraseExample() Dim NumArray(3) As Integer Dim decArray(2) As Double Dim strArray(2) As String NumArray(0) = 12345 decArray(1) = 34.5 strArray(1) = 'Erase Function' Dim DynaArray() ReDim DynaArray(3) MsgBox ' Values before Erase ' & (NumArray(0)) & ',' & (decArray(1)) & ' , ' & (strArray(1)) Erase NumArray Erase decArray Erase strArray Erase DynaArray ' Free the memory ' All values are erased. MsgBox ' Values after Erase ' & NumArray(0) & ',' & decArray(1) & ' , ' & strArray(1) End Sub

Resultat før du bruker Slett-funksjonen

Resultat før du bruker Slett-funksjonen

Resultat etter bruk Erase

Resultat etter bruk Erase

# 3) IsArray

Denne funksjonen brukes til å bestemme om den gitte inngangsvariabelen er en matrise eller ikke. Det returnerer sant hvis den angitte variabelen er sant, ellers returnerer den usann.

Syntaks: IsArray (variablename)

Eksempel:

Sub isArrayTest() Dim arr1, arr2 As Variant arr1 = Array('Jan', 'Feb', 'Mar') arr2 = '12345' MsgBox ('Is arr1 an Array : ' & IsArray(arr1)) MsgBox ('Is arr2 an Array : ' & IsArray(arr2)) End

Resultatet fra den første Msgbox

IsArray - resultat fra første Msgbox

Resultatet fra den andre msgbox

IsArray - resultat fra andre Msgbox

# 4) Lbound

Det returnerer det laveste tegnet av matrisen som er spesifisert som argumentet for funksjonen Lbound.

Syntaks: LBound (ArrayName, (Dimension))

ArrayName er navnet på matrisen.

Dimensjon er den valgfrie heltallverdien. Hvis matrisen har flere dimensjoner, kan du spesifisere hvilken dimensjon du vil bestemme Lbound.

Eksempel:

Sub lboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim Arraywithoutlbound(10) Result1 = LBound(ArrayValue, 1) ' Returns 1. Result2 = LBound(ArrayValue, 3) ' Returns 10. Result3 = LBound(Arraywithoutlbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub

bundet

# 5) Ubundet

Den returnerer det øvre abonnementet til matrisen som er angitt som et argument i funksjonen ubundet.

Syntaks: UBound (ArrayName, (Dimension))

ArrayName er navnet på matrisen.

Dimensjon er den valgfrie heltallverdien. Hvis matrisen har flere dimensjoner, kan du spesifisere hvilken dimensjon du vil bestemme ubundet.

Eksempel:

Sub UboundTest() Dim Result1, Result2, Result3 Dim ArrayValue(1 To 10, 5 To 15, 10 To 20) ' Declare array variables. Dim ArraywithoutUbound(10) Result1 = UBound(ArrayValue, 1) Result2 = UBound(ArrayValue, 3) Result3 = UBound(ArraywithoutUbound) MsgBox 'Lowest subscript in first array ' & Result1 & ' lowest subscript in 3rd array ' & Result2 & ' Lowest subscript in Arraywithoutlbound ' & Result3 End Sub

Ubundet

# 6) Del

Den returnerer en matrise med et antall understreng avledet fra den gitte hele strengen.

Syntaks: Split (uttrykk, (skilletegn, (begrense, (sammenligne))))

  • Uttrykk: Dette er hele strengen som skal brukes til å produsere underlag.
  • Avgrenser: Ved å bruke den spesifiserte skillelinjen vil understrenger bli generert. Hvis dette ikke er nevnt, blir plass betraktet som avgrenseren.
  • Grense: Antall underlag som skal returneres.
  • Sammenligne: Etter at underlaget er produsert, kan du bruke forskjellige sammenligningsalternativer for å teste resultatet.

Eksempel: I eksemplet nedenfor bruker vi skilletegn som - og begrenser som 3.

Derfor vil split-funksjonen skille hele strengen i substring basert på avgrenseren. Men vi har også nevnt grensen som 3 slik at understrenger ikke blir dannet etter grensen 3. Dermed vil den siste skilletegnet - hoppes over.

Sub splitExample() Dim MyString As String Dim Result() As String Dim DisplayText As String MyString = 'This is the example for-VBA-Split-Function' Result = Split(MyString, '-',3) MsgBox Result(0) & vbNewLine & Result(1) & vbNewLine & Result(2) & vbNewLine & Result(3) End Sub

Dele

# 7) Bli med

Dette er bare motsatt av splittelsen, Join vil opprette en streng ved å kombinere flere understreng.

Syntaks: Bli med (sourcearray, (delimiter))

Sourcearray: Endimensjonalt utvalg av strenger du vil knytte til en.

Avgrenser: Spesifisert skilletegn blir lagt til etter hver streng mens du blir med.

Eksempel:

Sub joinExample() Dim Result As String Dim dirarray(0 To 2) As String dirarray(0) = 'D:' dirarray(1) = 'SoftwareTestingHelp' dirarray(2) = 'Arrays' Result = Join(dirarray, '') MsgBox 'Date after joining ' & Result End Sub

Alle de tre verdiene er sammenføyd og er plassert mellom hvert ord, som vi har nevnt som avgrenseren.

Bli med

# 8) Filter

Filteret lar oss søke etter et spesifisert treff fra en matrise. Basert på filterkriteriene returneres delsettet til en strengmatrise.

Syntaks: Filter (sourcearray, match, (include, (Compare)))

Eksempel:

Sub filterExample() Dim Mystring As Variant Mystring = Array('Software Testing', 'Testing help', 'Software help') filterString = Filter(Mystring, 'help') MsgBox 'Found ' & UBound(Mystring) - LBound(Mystring) + 1 & ' words matching the criteria ' End Sub

Dette eksemplet vil søke etter ordet 'help' i hele arraystrengen ved hjelp av filterfunksjonen.

Filter

ofte stilte spørsmål

Q # 1) Hvordan få lengden på en matrise i VBA?

Svar: For å få lengden på en matrise bruker vi Ubound-funksjonen. Denne funksjonen vil gi oss et øvre underskrift av en spesifisert matrise.

Q # 2) Hvordan deklarere en matrise i VBA?

hvordan legge til arrays i java

Svar: Endimensjonalt array er erklært som vist nedenfor.

Demp matrisenavn (nedre til øvre bunn) som datatype

Eksempel: Dim Myarray (0 til 2) som heltal

En todimensjonal matrise er deklarert som vist nedenfor.

Dim ArrayName (FirstIndex To LastIndex, FirstIndex To LastIndex) Som datatype.

Eksempel: Dimmerker (1 til 3, 0 til 2) som heltal

Q # 3) Hvordan konvertere Range til Array?

Svar: Vi kan bruke transponeringsfunksjonen til å konvertere området til en matrise. Denne koden vil opprette Mys (10)

Sub Example() Dim Mys As Variant Mys = Application.Transpose(Range('A1:A10')) End Sub

konverter Range til array

Q # 4) Hva er en arrayvariant i VBA?

Svar: En variant matrise vil akseptere alle typer datatyper for indeksen, dvs. du kan lagre forskjellige typer verdier i en enkelt matrise.

Eksempel:

Dim arrayData (3) Som variant
arrayData (0) = 'Vikas Vipal'
arrayData (1) = 411234567890 #

Spørsmål nr. 5) Hva er en opsjonssett?

Svar: Dette brukes til å erklære det nedre abonnementet til en matrise og brukes ved starten av en modul. Som standard er det nedre abonnementet 0 hvis du erklærer Option Base 1 på modulnivå, og for alle arrays vil standard nedre abonnement være 1.

Konklusjon

I denne opplæringen har vi lært hvordan Arrays brukes i VBA, og vi så hvordan Arrays er forskjellige fra en vanlig variabel. Vi utforsket endimensjonale og todimensjonale matriser med eksempler. Vi diskuterte også faste og dynamiske matriser.

Måtene å endre størrelse på matrisen under kjøretid og også bevare verdiene ved hjelp av redim preserve ble diskutert med eksempler. Til slutt lærte vi Array-metoder som vil hjelpe oss med å utføre flere operasjoner.

=> Sjekk ALLE VBA-veiledninger her

Anbefalt lesing

^