Wat is een VBA-array in Excel en hoe programmeer je er een?

VBA maakt al vele jaren deel uit van de Microsoft Office-suite. Hoewel het niet de volledige functionaliteit en kracht van een volledige VB-toepassing heeft, biedt VBA Office-gebruikers de flexibiliteit om Office-producten te integreren en het werk dat u erin doet te automatiseren.

Een van de krachtigste tools die beschikbaar zijn in VBA, is de mogelijkheid om een ​​hele reeks gegevens in een enkele variabele te laden, een array genaamd. Door gegevens op deze manier te laden, kunt u dat gegevensbereik op verschillende manieren manipuleren of berekeningen uitvoeren.

Dus wat is een VBA-array? In dit artikel zullen we die vraag beantwoorden en je laten zien hoe je er een kunt gebruiken in je eigen VBA-script.

Wat is een VBA-array?

Het gebruik van een VBA-array in Excel is heel eenvoudig, maar het begrijpen van het concept van arrays kan een beetje ingewikkeld zijn als je er nog nooit een hebt gebruikt.

Denk aan een array als een doos met secties erin. Een eendimensionale array is een doos met één lijn secties. Een tweedimensionale array is een doos met twee secties.

U kunt gegevens in elke gewenste volgorde in elk gedeelte van dit "vak" plaatsen.

Aan het begin van uw VBA-script moet u deze "box" definiëren door uw VBA-array te definiëren. Dus om een ​​array te maken die één set gegevens kan bevatten (een eendimensionale array), zou je de volgende regel schrijven.

Dim arrMyArray (1 tot 6) als string

Later in uw programma kunt u gegevens in elke sectie van deze array plaatsen door te verwijzen naar de sectie tussen haakjes.

arrMyArray (1) = "Ryan Dube"

U kunt een tweedimensionale array maken met behulp van de volgende regel.

Dim arrMyArray (1 tot 6,1 tot 2) als geheel getal

Het eerste cijfer staat voor de rij en het tweede voor de kolom. Dus de bovenstaande array kan een bereik bevatten met 6 rijen en 2 kolommen.

U kunt als volgt elk element van deze array met gegevens laden.

arrMyArray (1,2) = 3

Hierdoor wordt een 3 in cel B1 geladen.

Een array kan elk type data als een reguliere variabele bevatten, zoals strings, boolean, integers, floats en meer.

Het getal tussen haakjes kan ook een variabele zijn. Programmeurs gebruiken gewoonlijk een For-lus om door alle secties van een array te tellen en gegevenscellen in de spreadsheet in de array te laden. Hoe u dit doet, leest u verderop in dit artikel.

Hoe een VBA-array in Excel te programmeren

Laten we eens kijken naar een eenvoudig programma waarin u informatie uit een spreadsheet in een multidimensionale array wilt laden.

Laten we als voorbeeld eens kijken naar een spreadsheet voor productverkoop, waar u de naam, het item en de totale verkoop van de verkoper uit de spreadsheet wilt halen.

Merk op dat wanneer u in VBA naar rijen of kolommen verwijst, u meetelt rijen en kolommen beginnend vanaf linksboven bij 1. Dus de rep-kolom is 3, de itemkolom is 4 en de totale kolom is 7.

Om deze drie kolommen over alle 11 rijen te laden, moet u het volgende script schrijven.

Dim arrMyArray (1 tot 11, 1 tot 3) As String
Dim i als geheel getal, j als geheel getal

Voor i = 2 tot 12
Voor j = 1 tot 3
arrMyArray (i-1, j) = Cellen (i, j). Waarde
volgende j
volgende i

U zult merken dat om de kopregel over te slaan, het rijnummer in de eerste For look moet beginnen bij 2 in plaats van 1. Dit betekent dat u 1 moet aftrekken voor de arrayrijwaarde wanneer u de celwaarde in de array laadt met behulp van Cellen (i, j). Waarde.

Waar u uw VBA-arrayscript kunt invoegen

Om een ​​programma een VBA-script in Excel te plaatsen, moet u de VBA-editor gebruiken. U kunt dit openen door de te selecteren Ontwikkelaar menu en selecteren Bekijk code in het gedeelte Besturingselementen van het lint.

Als u de ontwikkelaar niet in het menu ziet, moet u deze toevoegen. Selecteer hiervoor Bestand en Opties om het venster Excel-opties te openen.

Wijzig de kiescommando's van de vervolgkeuzelijst in Alle opdrachten. Selecteer Ontwikkelaar in het linkermenu en selecteer de Toevoegen om het naar het rechterdeelvenster te verplaatsen. Schakel het selectievakje in om het in te schakelen en selecteer OK af te maken.

Wanneer het venster Code-editor wordt geopend, moet u ervoor zorgen dat het blad waarin uw gegevens zich bevinden, is geselecteerd in het linkerdeelvenster. Selecteer werkblad in de linker vervolgkeuzelijst en Activeren rechts. Dit maakt een nieuwe subroutine aan met de naam Worksheet_Activate().

Deze functie wordt uitgevoerd wanneer het spreadsheetbestand wordt geopend. Plak de code in het scriptvenster in deze subroutine.

Dit script werkt door de 12 rijen en laadt de naam van de vertegenwoordiger uit kolom 3, het item uit kolom 4 en de totale verkoop uit kolom 7.

Zodra beide For-lussen zijn voltooid, bevat de tweedimensionale array arrMyArray alle gegevens die u van het oorspronkelijke blad hebt opgegeven.

Arrays manipuleren in Excel VBA

Stel dat u 5% omzetbelasting wilt toepassen op alle uiteindelijke verkoopprijzen en vervolgens alle gegevens op een nieuw blad wilt schrijven.

U kunt dit doen door na de eerste nog een For-lus toe te voegen, met een opdracht om de resultaten naar een nieuw blad te schrijven.

Voor k = 2 tot 12
Bladen ("Blad2"). Cellen (k, 1). Waarde = arrMyArray (k - 1, 1)
Bladen ("Blad2"). Cellen (k, 2). Waarde = arrMyArray (k - 1, 2)
Bladen ("Blad2"). Cellen (k, 3). Waarde = arrMyArray (k - 1, 3)
Bladen ("Blad2"). Cellen (k, 4). Waarde = arrMyArray (k - 1, 3) * 0,05
volgende k

Hiermee wordt de hele array "uitgeladen" in Sheet2, met een extra rij die het totaal bevat vermenigvuldigd met 5% voor het belastingbedrag.

Het resulterende blad ziet er als volgt uit.

Zoals u kunt zien, zijn VBA-arrays in Excel uiterst nuttig en zo veelzijdig als elke andere Excel-truc.

Het bovenstaande voorbeeld is een heel eenvoudig gebruik voor een array. U kunt veel grotere arrays maken en sorteer-, middelings- of vele andere functies uitvoeren op basis van de gegevens die u erin opslaat.

Als je echt creatief wilt worden, kun je zelfs twee arrays maken met een reeks cellen van twee verschillende bladen en voer berekeningen uit tussen elementen van elke array.

De toepassingen worden alleen beperkt door uw verbeeldingskracht.

Willen indruk maken je vrienden en familie met geweldig technische nerd?

Abonneer u op Help Desk Geek en ontvang dagelijks geweldige handleidingen, tips en trucs! We sturen alleen nuttige dingen op!