VBA maakt^(VBA) 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^{(your own 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 u er nog nooit een hebt gebruikt.

Zie^(Think) 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 lijnen met secties.

U kunt gegevens in elke sectie van deze "box" in elke gewenste volgorde 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 To 6) As 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 To 6,1 to 2) As Integer

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 Loop 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 rijen en kolommen^{(rows and columns)} telt vanaf de linkerbovenhoek 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 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

U zult merken dat om de koprij 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 laadt in de array met behulp van Cellen^(Cells) (i, j). Waarde.

Waar u uw VBA-arrayscript kunt invoegen^{(Your VBA Array Script)}

Om een ​​programma een VBA - script in Excel te plaatsen , moet u de VBA- editor gebruiken. U kunt dit openen door het menu Ontwikkelaars^(Developer) te selecteren en Code weergeven^{(View Code)} te selecteren in het gedeelte Besturingselementen^(Controls) van het lint.

Als u de ontwikkelaar^(Developer) niet in het menu ziet, moet u deze toevoegen. Om dit te doen, selecteert u Bestand^(File) en opties^(Options) om het venster Excel-opties te openen.

Wijzig de keuzeopdrachten van de vervolgkeuzelijst in Alle opdrachten^{(All Commands)} . Selecteer Ontwikkelaar^(Developer) in het linkermenu en selecteer de knop Toevoegen^(Add) om deze naar het rechterdeelvenster te verplaatsen. Schakel het selectievakje in om het in te schakelen en selecteer OK om te voltooien.

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^(Worksheet) in de linker vervolgkeuzelijst en Activeer^(Activate) 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 originele 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.

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next 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 buitengewoon nuttig en net zo veelzijdig als elke andere Excel-truc^{(any other Excel trick)} . 

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 u echt creatief wilt worden, kunt u zelfs twee arrays maken met een reeks cellen^{(containing a range of cells)} van twee verschillende bladen en berekeningen uitvoeren tussen elementen van elke array.

De toepassingen worden alleen beperkt door uw verbeeldingskracht.

What Is a VBA Array in Excel and How to Program One

VBA has bеen part of the Microsoft Office suite for many years. Whilе it doesn’t have the full functionality and power of a full VB application, VBA provides Office users with the flexibility to integrate Office prodυcts and automate the work you do in them.

One of the most powerful tools available in VBA is the ability to load an entire range of data into a single variable called an array. By loading data in this way, you can manipulate or perform calculations on that range of data in a variety of ways.

So what is a VBA array? In this article we’ll answer that question, and show you how to use one in your own VBA script.

What Is a VBA Array?

Using a VBA array in Excel is very simple, but understanding the concept of arrays can be a little bit complex if you’ve never used one.

Think of an array like a box with sections inside it. A one-dimensional array is a box with one line of sections. A two-dimensional array is a box with two lines of sections.

You can put data into each section of this “box” in any order you like.

At the beginning of your VBA script, you need to define this “box” by defining your VBA array. So to create an array that can hold one set of data (a one-dimension array), you would write the following line.

Dim arrMyArray(1 To 6) As String

Later in your program, you can place data into any section of this array by referencing the section inside of parentheses.

arrMyArray(1) = "Ryan Dube"

You can create a two-dimensional array using the following line.

Dim arrMyArray(1 To 6,1 to 2) As Integer

The first number represents the row, and the second is the column. So the above array could hold a range with 6 rows and 2 columns.

You can load any element of this array with data as follows.

arrMyArray(1,2) = 3

This will load a 3 into the cell B1.

An array can hold any type of data as a regular variable, such as strings, boolean, integers, floats and more.

The number inside the parenthesis can also be a variable. Programmers commonly use a For Loop to count through all sections of an array and load data cells in the spreadsheet into the array. You’ll see how to do this later in this article.

How to Program a VBA Array in Excel

Let’s take a look at a simple program where you may want to load information from a spreadsheet into a multidimensional array.

As an example, let’s look at a product sales spreadsheet, where you want to pull the sales rep’s name, item, and total sale out of the spreadsheet.

Note that in VBA when you reference rows or columns, you count rows and columns starting from the top left at 1. So the rep column is 3, the item column is 4, and the total column is 7.

To load these three columns over all 11 rows, you’ll need to write the following script.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

You’ll notice that in order to skip the header row, the row number in the first For look needs to start at 2 rather than 1. This means you need to subtract 1 for the array row value when you’re loading the cell value into the array using Cells(i, j).Value.

Where to Insert Your VBA Array Script

To place program a VBA script in Excel, you need to use the VBA editor. You can access this by selecting the Developer menu and selecting View Code in the Controls section of the ribbon.

If you don’t see the Developer in the menu, you’ll need to add it. To do this, select File and Options to open the Excel Options window.

Change the choose commands from dropdown to All Commands. Select Developer from the left menu and select the Add button to move it to the pane on the right. Select the checkbox to enable it and select OK to finish.

When the Code Editor window opens, make sure the sheet your data is in is selected in the left pane. Select Worksheet in the left dropdown and Activate in the right. This will create a new subroutine called Worksheet_Activate().

This function will run whenever the spreadsheet file is opened. Paste the code into the script pane inside this subroutine.

This script will work through the 12 rows and will load the rep name from column 3, the item from column 4, and the total sale from column 7.

Once both For loops are finished, the two dimensional array arrMyArray contains all of the data you specified from the original sheet.

Manipulating Arrays in Excel VBA

Let’s say you want to apply a 5% sales tax to all of the final sale prices, and then write all of the data to a new sheet.

You can do this by adding another For loop after the first, with a command to write the results to a new sheet.

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

This will “unload” the entire array into Sheet2, with an additional row containing the total multiplied by 5% for the tax amount.

The resulting sheet will look like this.

As you can see, VBA arrays in Excel are extremely useful and as versatile as any other Excel trick. 

The example above is a very simple use for an array. You can create much larger arrays and perform sorting, averaging, or many other functions against the data you store in them.

If you want to get real creative, you could even create two arrays containing a range of cells from two different sheets, and perform calculations between elements of each array.

The applications are limited only by your imagination.

Related posts

• Hoe lege regels in Excel te verwijderen

• De functie voor het uitspreken van cellen in Excel gebruiken

• Een Excel-werkblad invoegen in een Word-document

• De What-If-analyse van Excel gebruiken

• Hoe een rij in Excel te repareren

• Hoe rasterlijnen in Excel te verwijderen

• Hoe een IF-formule / verklaring in Excel te schrijven?

• Wanneer Index-Match gebruiken in plaats van VERT.ZOEKEN in Excel?

• Hoe If en Geneste If-statements in Excel te gebruiken?

• Waarom u benoemde bereiken in Excel zou moeten gebruiken?

• Een VBA-macro of -script maken in Excel

• 5 manieren om tekst naar getallen in Excel te converteren

• Hoe te alfabetiseren in Excel

• Snel meerdere rijen invoegen in Excel

• 2 manieren om de transponeerfunctie van Excel te gebruiken

• 4 manieren om Excel naar Google Spreadsheets te converteren

• Snel getallen spellen in Word en Excel

• Hoe de Z-score in Excel te berekenen

• Basis gegevenssortering in één kolom en meerdere kolommen in Excel-spreadsheets

• Gegevens in meerdere Excel-bestanden samenvoegen