VBA-arraylijst

Inhoudsopgave

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
wave wave wave wave wave