Een ArrayList-object is vergelijkbaar met een Collection-object, maar het heeft veel meer methoden en eigenschappen, en daarom veel meer flexibiliteit vanuit het oogpunt van programmeren.
Een Collection-object heeft slechts twee methoden (Toevoegen, Verwijderen) en twee eigenschappen (Count, Item), terwijl een Array List er veel meer heeft. Het Collection-object is ook alleen-lezen. Nadat waarden zijn toegevoegd, kan de geïndexeerde waarde niet worden gewijzigd, terwijl op een arraylijst bewerking mogelijk is.
Veel van de Array List-methoden gebruiken parameters. In tegenstelling tot veel van de standaard VBA-methoden, is geen van deze parameters optioneel. Ook worden sommige methoden en eigenschappen niet altijd met een hoofdletter geschreven wanneer ze op dezelfde manier worden ingevoerd als in Excel VBA. Ze werken echter nog steeds.
Het ArrayList-object wordt groter en kleiner, afhankelijk van het aantal items dat het bevat. Het hoeft voor gebruik niet te worden gedimensioneerd zoals een array.
De arraylijst is eendimensionaal (hetzelfde als het verzamelingsobject) en het standaardgegevenstype is Variant, wat betekent dat het elk type gegevens accepteert, of het nu numeriek, tekst of datum is.
In veel opzichten lost de Array List een aantal tekortkomingen van het Collection-object op. Het is zeker veel flexibeler in wat het kan doen.
Het object Array List maakt geen deel uit van de standaard VBA-bibliotheek. U kunt het in uw Excel VBA-code gebruiken door late of vroege binding te gebruiken
1234 | Sub LateBindingExample()Mijn lijst dimmen als objectSet MyList = CreateObject("System.Collections.ArrayList")Einde sub |
123 | Sub EarlyBindingExample()Dim MyList als nieuwe ArrayListEinde sub |
Om het vroege bindingsvoorbeeld te gebruiken, moet u eerst een verwijzing in VBA invoeren naar het bestand 'mscorlib.tlb'
Dit doe je door op ‘Extra | Verwijzingen ' in het venster Visual Basic Editor (VBE). Er verschijnt een pop-upvenster met alle beschikbare referenties. Scroll naar beneden naar 'mscorlib.dll' en vink het vakje ernaast aan. Klik op OK en die bibliotheek maakt nu deel uit van uw project:
Een van de grote nadelen van een Array List-object is dat het geen ‘Intellisense’ heeft. Normaal gesproken, waar u een object in VBA gebruikt, zoals een bereik, ziet u een pop-uplijst met alle beschikbare eigenschappen en methoden. Je krijgt dit niet met een Array List-object, en het moet soms zorgvuldig worden gecontroleerd om er zeker van te zijn dat je de methode of eigenschap correct hebt gespeld.
Ook als u op F2 drukt in het VBE-venster en zoekt op 'arraylist', wordt er niets weergegeven, wat niet erg handig is voor een ontwikkelaar.
Je code zal aanzienlijk sneller lopen met vroege binding, omdat het allemaal van tevoren wordt gecompileerd. Bij late binding moet het object worden gecompileerd terwijl de code wordt uitgevoerd
Uw Excel-toepassing met een matrixlijst distribueren
Zoals reeds opgemerkt, maakt het ArrayList-object geen deel uit van Excel VBA. Dit betekent dat al uw collega's aan wie u de applicatie distribueert, toegang moeten hebben tot het bestand 'mscorlib.tlb'
Dit bestand bevindt zich normaal gesproken in:
C:\Windows\Microsoft.NET\Framework\v4.0.30319
Het kan de moeite waard zijn om wat code te schrijven (met behulp van de Dir-methode) om te controleren of dit bestand bestaat wanneer een gebruiker de applicatie laadt, zodat ze een 'zachte landing' ervaren als ze niet worden gevonden. Als het niet aanwezig is en de code wordt uitgevoerd, zullen er fouten optreden.
De gebruiker moet ook de juiste .Net Framework-versie hebben geïnstalleerd. Zelfs als de gebruiker een latere versie heeft, moet V3.5 geïnstalleerd zijn, anders werkt je applicatie niet
Bereik van een matrixlijstobject
Qua bereik is het object Array List alleen beschikbaar als de werkmap is geopend. Het wordt niet opgeslagen wanneer de werkmap wordt opgeslagen. Als de werkmap opnieuw wordt geopend, moet het Array List-object opnieuw worden gemaakt met VBA-code.
Als u wilt dat uw Array List beschikbaar is voor alle code in uw codemodule, dan moet u het Array List-object declareren in het gedeelte Declare helemaal bovenaan het modulevenster
Dit zorgt ervoor dat al uw code binnen die module toegang heeft tot de arraylijst. Als u wilt dat een module in uw werkmap toegang krijgt tot het Array List-object, definieert u deze als een globaal object
1 | Wereldwijde MyCollection als nieuwe arrayList |
Invullen en lezen van uw arraylijst
De meest elementaire actie die u wilt ondernemen, is een arraylijst maken, er wat gegevens in plaatsen en vervolgens bewijzen dat de gegevens kunnen worden gelezen. Alle codevoorbeelden in dit artikel gaan ervan uit dat u vroege binding gebruikt en 'mscorlib.tlb' hebt toegevoegd aan de VBA-referenties, zoals hierboven beschreven
123456789101112 | Sub ArrayListExample()'Maak een nieuw arraylijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan lijstMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Herhaal door arraylijst om waarden te bewijzen'Voor N = 0 Naar MyList.Count - 1MsgBox MijnLijst(N)Volgende NEinde sub |
In dit voorbeeld wordt een nieuw ArrayList-object gemaakt, wordt het gevuld met 3 items en wordt de lijst herhaald met elk item.
Merk op dat de ArrayList-index begint bij 0, niet bij 1, dus u moet 1 aftrekken van de waarde Count
U kunt ook een 'For… Each'-lus gebruiken om de waarden te lezen:
123456789101112 | Sub ArrayListExample()'Maak een nieuw arraylijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan lijstMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Herhaal door arraylijst om waarden te bewijzen'Voor elke ik in mijn lijstMsgBox Ivolgende ikEinde sub |
Items in een arraylijst bewerken en wijzigen
Een groot voordeel van een Array List ten opzichte van een Collection is dat de items in de lijst binnen uw code kunnen worden bewerkt en gewijzigd. Het Collection-object is alleen-lezen, terwijl het Array List-object lezen / schrijven is
123456789101112131415 | Sub ArrayListExample()'Maak een nieuw arraylijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan lijstMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Wijzig item 1 van 'Artikel 2' in 'Gewijzigd'MijnLijst(1) = "Gewijzigd"'Herhaal door de arraylijst om te bewijzen dat de verandering heeft gewerkt'Voor elke ik in mijn lijst'Naam item weergeven'MsgBox Ivolgende ikEinde sub |
In dit voorbeeld wordt het tweede item, 'Item2', gewijzigd in de waarde 'Gewijzigd' (onthoud dat de index begint bij 0). Wanneer de iteratie aan het einde van de code wordt uitgevoerd, wordt de nieuwe waarde weergegeven
Een array van waarden toevoegen aan een array-lijst
U kunt waarden in uw matrixlijst invoeren door een matrix te gebruiken die een lijst van deze waarden of verwijzingen naar celwaarden op een werkblad bevat
123456789101112131415161718 | Sub AddArrayExample()'Maak een array-lijstobject'Dim MyList als nieuwe ArrayList'doorloop arraywaarden en voeg ze toe aan de arraylijst'Voor elke v In Array ("A1", "A2", "A3")'Voeg elke matrixwaarde toe aan lijst'MijnLijst.Voeg toeVolgende'doorloop arraywaarden met werkbladverwijzingen en voeg ze toe aan de arraylijst'Voor elke v In Array (Bereik ("A5"). Waarde, Bereik ("A6"). Waarde)MijnLijst.Voeg toeVolgende'Herhaal door arraylijst om waarden te bewijzen'Voor N = 0 Naar MyList.Count - 1'Lijstitem weergeven'MsgBox MijnLijst.Item(N)Volgende NEinde sub |
Een reeks items uit een array-lijst lezen/ophalen
Door de GetRange-methode op een arraylijst te gebruiken, kunt u een reeks opeenvolgende items specificeren die moeten worden opgehaald. De twee benodigde parameters zijn de startindexpositie en het aantal op te halen items. De code vult een tweede Array List-object met de subset van items die vervolgens afzonderlijk kunnen worden gelezen.
123456789101112131415161718 | Sub ReadRangeExample()‘Definieer objectenDim MyList As New ArrayList, MyList1 As Object'Voeg items toe aan het object 'MijnLijst'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toeMijnLijst.Voeg "Artikel6" toeMijnLijst.Voeg "Artikel4" toeMijnLijst.Voeg "Artikel7" toe‘Vast 4 items in ‘MyList’ vanaf indexpositie 2Stel MijnLijst1 in = MijnLijst.GetRange(2, 4)'Doorloop het object 'MyList1' om de subset van items weer te gevenVoor elke I in MijnLijst1'Naam item weergeven'MsgBox Ivolgende ikEinde sub |
Zoeken naar items in een arraylijst
U kunt testen of een genoemd item in uw lijst staat door de methode 'Bevat' te gebruiken. Dit geeft True of False terug
1 | MsgBox MijnLijst.Bevat("Item2") |
U kunt de werkelijke indexpositie ook vinden met behulp van de 'IndexOf'-methode. U moet de startindex voor de zoekopdracht opgeven (meestal 0). De retourwaarde is de index van het eerste exemplaar van het gevonden item. U kunt dan een lus gebruiken om het startpunt te wijzigen in de volgende indexwaarde om verdere instanties te vinden als er meerdere dubbele waarden zijn.
Als de waarde niet wordt gevonden, wordt een waarde van -1 geretourneerd
Dit voorbeeld demonstreert het gebruik van 'Bevat', item niet gevonden en het doorlopen van de arraylijst om de positie van alle dubbele items te vinden:
1234567891011121314151617181920212223242526 | Sub ZoekenLijstVoorbeeld()‘Definieer arraylijst en variabelenDim MyList As New ArrayList, Sp As Integer, Pos As Integer'Nieuwe items toevoegen inclusief een duplicaatMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toeMijnLijst.Voeg "Artikel1" toe'Test of "Item2" in de lijst staat - geeft True terugMsgBox MijnLijst.Bevat("Item2")'Index van niet-bestaande waarde ophalen - retourneert -1'MsgBox MyList.IndexOf("Item", 0)‘Zet de startpositie voor het zoeken op nulSp = 0'Herhaal door de lijst om alle posities van 'Item1' te krijgenDoen'Verkrijg de indexpositie van het volgende 'Item1' op basis van de positie in de variabele 'Sp'Pos = MijnLijst.IndexOf("Item1", Sp)'Als er geen verdere instanties van 'Item1' worden gevonden, verlaat dan de lusAls Pos = -1 Sluit dan Do . af'Toon de volgende gevonden instantie en de indexpositie'MsgBox MyList(Pos) & " bij index " & Pos'Voeg 1 toe aan de laatst gevonden indexwaarde - dit wordt nu de nieuwe startpositie voor de volgende zoekopdrachtSp = Pos + 1LusEinde sub |
Houd er rekening mee dat de gebruikte zoektekst hoofdlettergevoelig is en jokertekens niet worden geaccepteerd.
Items plaatsen en verwijderen
Als u uw items niet aan het einde van de lijst wilt toevoegen, kunt u ze op een bepaalde indexpositie invoegen, zodat het nieuwe item in het midden van de lijst staat. De indexnummers worden automatisch aangepast voor de volgende items.
123456789101112131415 | Sub InsertVoorbeeld()'Definieer array-lijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan arraylijst'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toeMijnLijst.Voeg "Artikel1" toe'Voeg 'Item6' in op indexpositie 2MijnLijst.Insert 2, "Artikel6"'Itereer door items in de arraylijst om de nieuwe volgorde en indexpositie weer te geven'Voor N = 0 Naar MyList.Count - 1MsgBox MijnLijst(N) & "Index" & NVolgende NEinde sub |
In dit voorbeeld wordt 'Item6' toegevoegd aan de lijst op indexpositie 2, dus het 'item3' dat op indexpositie 2 stond, gaat nu naar indexpositie 3
Een individueel item kan worden verwijderd met behulp van de methode ‘Verwijderen’.
1 | MijnLijst.Verwijder "Artikel" |
Merk op dat er geen fout wordt geproduceerd als de itemnaam niet wordt gevonden. Alle volgende indexnummers worden aangepast aan de verwijdering.
Als u de indexpositie van het item kent, kunt u de 'RemoveAt'-methode gebruiken, b.v.
1 | MijnLijst.VerwijderenBij 2 |
Merk op dat als de opgegeven indexpositie groter is dan het aantal items in de arraylijst, er een fout wordt geretourneerd.
U kunt een reeks waarden uit de lijst verwijderen met de methode ‘RemoveRange’. De parameters zijn de startindex en vervolgens het aantal items dat moet worden verwijderd, b.v.
1 | MijnLijst.VerwijderenBereik 3, 2 |
Houd er rekening mee dat u een fout in uw code krijgt als het aantal items dat is verschoven vanaf de startwaarde groter is dan het aantal items in de arraylijst.
In zowel de 'RemoveAt'- als 'RemoveRange'-methoden, is het raadzaam om met code te controleren of de opgegeven indexnummers groter zijn dan het totale aantal items in de arraylijst om mogelijke fouten op te vangen. De eigenschap 'Count' geeft het totale aantal items in de arraylijst.
12345678910111213141516171819202122232425 | Sub RemoveExample()'Definieer array-lijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan arraylijst'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toeMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel4" toeMijnLijst.Voeg "Artikel5" toe'Voeg 'Item6' in op indexpositie 2MijnLijst.Insert 2, "Artikel6"'Verwijder 'Artikel 2'MijnLijst.Verwijder "Artikel2"'Verwijder 'Item' - dit bestaat niet in de arraylijst maar geeft geen foutMijnLijst.Verwijder "Artikel"'Verwijder het item op indexpositie 2'MijnLijst.VerwijderenBij 2'Verwijder 2 opeenvolgende items vanaf indexpositie 2'MijnLijst.VerwijderenBereik 3, 2'Herhaal door de arraylijst om te laten zien wat er nog over is en op welke indexpositie het zich nu bevindt'Voor N = 0 Naar MyList.Count - 1MsgBox MijnLijst(N) & "Index" & NVolgende NEinde sub |
Merk op dat als u 'RemoveAt' gebruikt om een item op een specifieke positie te verwijderen, zodra dat item wordt verwijderd, alle daaropvolgende indexposities worden gewijzigd. Als u meerdere verhuizingen heeft waarbij de indexpositie wordt gebruikt, is het een goed idee om te beginnen met het hoogste indexnummer en terug te gaan naar positie nul, zodat u altijd het juiste item verwijdert. Op deze manier heb je het probleem niet
Een arraylijst sorteren
Een ander groot voordeel ten opzichte van een collectie is dat je de items in oplopende of aflopende volgorde kunt sorteren.
Het object Array List is het enige object in Excel VBA met een sorteermethode. De sorteermethode is erg snel en dit kan een belangrijke overweging zijn bij het gebruik van een arraylijst.
In het collectieobject was wat 'out of the box'-denken vereist om alle items te sorteren, maar met een arraylijst is het heel eenvoudig.
De 'Sorteren'-methode sorteert in oplopende volgorde en de 'Reverse'-methode sorteert in aflopende volgorde.
12345678910111213141516171819202122 | Sub ArrayListExample()'Maak een arraylijst-object'Dim MyList als nieuwe ArrayList'Voeg artikelen toe in een niet-gesorteerde volgorde'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel3" toeMijnLijst.Voeg "Artikel2" toe'Sorteer de items in oplopende volgorde'MijnLijst.Sorteren'Doorloop de items om oplopende volgorde weer te gevenVoor elke ik in mijn lijst'Naam item weergeven'MsgBox Ivolgende ik'Sorteer de items in aflopende volgorde'MijnLijst.Omgekeerd'Doorloop de items om de aflopende volgorde weer te gevenVoor elke ik in mijn lijst'Naam item weergeven'MsgBox Ivolgende ikEinde sub |
Een arraylijst klonen
Een arraylijst heeft de mogelijkheid om een kloon of kopie van zichzelf te maken. Dit is handig als een gebruiker wijzigingen aanbrengt in de items met behulp van een frontend en uw VBA-code, maar u een kopie van de items in hun oorspronkelijke staat moet bewaren als back-up.
Dit kan de gebruiker een functie 'Ongedaan maken' bieden. Mogelijk hebben ze de wijzigingen aangebracht en willen ze teruggaan naar de oorspronkelijke lijst.
123456789101112131415 | Subkloonvoorbeeld()'Definieer twee objecten - arraylijst en een object'Dim MyList As New ArrayList, MyList1 As Object'Eerste object vullen met items'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Kopieer mijn lijst naar mijn lijst1'Stel MijnLijst1 = MijnLijst.Clone in'Herhaal door MyList1 om klonen te bewijzen'Voor elke I in MijnLijst1'Naam item weergeven'MsgBox Ivolgende ikEinde sub |
'MyList1' bevat nu alle items uit 'MyList' in dezelfde volgorde
Een lijstarray kopiëren naar een conventioneel VBA-arrayobject
U kunt een eenvoudige methode gebruiken om de arraylijst naar een normale VBA-array te kopiëren:
123456789101112131415 | Submatrixvoorbeeld()'Maak een array-lijstobject en een standaard array-object'Dim MyList als nieuwe arrayList, NewArray als variant'Arraylijst vullen met items'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Kopieer de arraylijst naar de nieuwe array'NewArray = MijnLijst.ToArray'Herhaal door de nieuwe array - merk op dat het aantal arraylijsten de maximale index biedtVoor N = 0 Naar MyList.Count - 1'Naam item weergeven'MsgBox NewArray(N)Volgende NEinde sub |
Een lijstarray kopiëren naar een werkbladbereik
U kunt uw arraylijst naar een specifiek werkblad en celverwijzing kopiëren zonder dat u door de arraylijst hoeft te bladeren. U hoeft alleen de eerste celverwijzing op te geven
123456789101112131415 | Subbereikvoorbeeld()'Maak een nieuw arraylijstobject'Dim MyList als nieuwe ArrayList'Voeg items toe aan lijstMijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Wis het doelblad'Bladen ("Blad1").UsedRange.Clear'Kopieer items over een rij'Spreadsheets ("Blad1").Bereik ("A1").Resize(1, MijnLijst.Aantal).Waarde = MijnLijst.naarArray'Kopieer items in een kolom'Spreadsheets ("Blad1").Bereik ("A5").Resize(MijnLijst.Aantal, 1).Waarde = _WerkbladFunctie.Transponeren (MijnLijst.naarArray)Einde sub |
Alle items uit een arraylijst leegmaken
Er is een eenvoudige functie (Clear) om de arraylijst volledig te wissen
1234567891011121314 | Sub ClearListExample()'Maak array-lijstobject'Dim MyList als nieuwe ArrayList'Nieuwe items toevoegen'MijnLijst.Voeg "Artikel1" toeMijnLijst.Voeg "Artikel2" toeMijnLijst.Voeg "Artikel3" toe'Toon aantal items'MsgBox MyList.Count'Alle items wissen'MijnLijst.Clear'Toon aantal items om te bewijzen dat clear heeft gewerkt'MsgBox MyList.CountEinde sub |
In dit voorbeeld worden items in een arraylijst gemaakt en vervolgens wordt de arraylijst gewist. Berichtvakken bewijzen voor en na het aantal items in de arraylijst.
Samenvatting van matrixlijstmethoden voor Excel VBA
Taak | Parameters: | Voorbeelden |
Item toevoegen / bewerken | Waarde | MijnLijst.Voeg “Artikel1” toe |
MijnLijst(4)= “Artikel2” | ||
Een matrixlijst klonen | Geen | Mijn lijst dimmen als object |
Stel MijnLijst2 = MijnLijst.Clone in | ||
Kopiëren naar array | Geen | Dim MyArray als variant |
MijnArray = MijnLijst.ToArray | ||
Kopiëren naar een werkbladbereik (rij) | Geen | Bladen ("Blad1").Bereik ("A1").Resize(1, MijnLijst.Aantal).Waarde = MijnLijst.ToArray |
Kopiëren naar een werkbladbereik (kolom) | Geen | Spreadsheets ("Blad1").Bereik ("A3").Resize (MyList.Count, 1).Value = WorksheetFunction.Transpose (MyList.ToArray) |
Creëren | "System.Collections.ArrayList" | Mijn lijst dimmen als object |
Stel MyList = CreateObject ("System.Collections.ArrayList") in | ||
Verklaren | Nvt | Mijn lijst dimmen als object |
Zoek / controleer of item bestaat | Item te vinden | MijnLijst.Bevat(“Artikel2”) |
Vind de positie van een item in de ArrayList | 1. Item te vinden. | Dim IndexNee zo lang |
2. Positie om te beginnen met zoeken. | IndexNo = MijnLijst.IndexOf(“Item3”, 0) | |
IndexNo = MijnLijst.IndexOf(“Item5”, 3) | ||
Aantal items ophalen | Geen | MsgBox MyList.Count |
Artikel invoegen | 1. Index - positie om in te voegen. | MijnLijst.Insert 0, "Artikel5" |
2 Waarde - object of waarde om in te voegen. | MijnLijst.Insert 4, “Artikel7” | |
Artikel lezen | Index - lang geheel getal | MsgBox MijnLijst.Item(0) |
MsgBox MijnLijst.Item(4) | ||
Laatste toegevoegd item lezen | Index - lang geheel getal | MsgBox MijnLijst.Item(lijst.Aantal - 1) |
Eerst toegevoegd item lezen | Index - lang geheel getal | MsgBox MijnLijst.Item(0) |
Lees alle items (voor elk) | Nvt | Dimelement Als Variant |
Voor elk element in MijnLijst | ||
MsgBox-element | ||
Volgende element | ||
Lees alle artikelen(Voor) | Index - lang geheel getal | Dim ik zo lang |
Voor i = 0 Naar MyList.Count - 1 | ||
MsgBox i | ||
volgende i | ||
Alle items verwijderen | Geen | MijnLijst.Clear |
Item op positie verwijderen | Indexpositie waar het item zich bevindt | MijnLijst.VerwijderenBij 5 |
Item op naam verwijderen | Het item dat uit de ArrayList moet worden verwijderd | MijnLijst.Verwijder “Artikel3” |
Een reeks items verwijderen | 1. Index - startpositie. | MijnLijst.VerwijderenBereik 4,3 |
2. Tellen - het aantal items dat moet worden verwijderd. | ||
Sorteer in aflopende volgorde | Geen | MijnLijst.Omgekeerd |
Sorteer in oplopende volgorde | Niet | MijnLijst.Sorteren |