VBA har været en del af Microsoft Office -pakken i mange år. Selvom det ikke har den fulde funktionalitet og kraft som en fuld VB-applikation, giver VBA Office - brugere fleksibiliteten til at integrere Office - produkter og automatisere det arbejde, du udfører i dem.

Et af de mest kraftfulde værktøjer, der er tilgængelige i VBA , er evnen til at indlæse en hel række af data i en enkelt variabel kaldet et array. Ved at indlæse data på denne måde kan du manipulere eller udføre beregninger på dette dataområde på en række forskellige måder.

Så hvad er et VBA -array? I denne artikel vil vi besvare det spørgsmål og vise dig, hvordan du bruger en i dit eget VBA-script^{(your own VBA script)} .

Hvad er et VBA-array?

Det er meget simpelt at bruge et VBA -array i Excel , men at forstå konceptet med arrays kan være lidt komplekst, hvis du aldrig har brugt et.

Tænk^(Think) på et array som en kasse med sektioner indeni. Et endimensionelt array er en kasse med en linje af sektioner. Et todimensionelt array er en kasse med to linjer af sektioner.

Du kan lægge data ind i hver sektion af denne "boks" i den rækkefølge, du ønsker.

I begyndelsen af ​​dit VBA -script skal du definere denne "boks" ved at definere dit VBA -array. Så for at skabe et array, der kan indeholde et sæt data (en en-dimensionel array), ville du skrive følgende linje.

Dim arrMyArray(1 To 6) As String

Senere i dit program kan du placere data i en hvilken som helst sektion af dette array ved at henvise til sektionen inden for parentes.

arrMyArray(1) = "Ryan Dube"

Du kan oprette et todimensionalt array ved hjælp af følgende linje.

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

Det første tal repræsenterer rækken, og det andet er kolonnen. Så ovenstående array kunne indeholde et interval med 6 rækker og 2 kolonner.

Du kan indlæse ethvert element i dette array med data som følger.

arrMyArray(1,2) = 3

Dette vil indlæse en 3'er i cellen B1.

Et array kan indeholde enhver type data som en regulær variabel, såsom strenge, boolean, heltal, flydende og mere.

Tallet inde i parentesen kan også være en variabel. Programmerere bruger almindeligvis en For Loop til at tælle gennem alle sektioner af et array og indlæse dataceller i regnearket i arrayet. Du vil se, hvordan du gør dette senere i denne artikel.

Sådan programmeres et VBA-array i Excel

Lad os tage et kig på et simpelt program, hvor du måske ønsker at indlæse information fra et regneark til et multidimensionelt array.

Lad os som et eksempel se på et produktsalgsregneark, hvor du vil trække sælgerens navn, vare og samlede salg ud af regnearket.

Bemærk, at i VBA , når du refererer til rækker eller kolonner, tæller du rækker og kolonner^{(rows and columns)} startende fra øverst til venstre ved 1. Så rep-kolonnen er 3, varekolonnen er 4, og den samlede kolonne er 7.

For at indlæse disse tre kolonner over alle 11 rækker, skal du skrive følgende 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

Du vil bemærke, at for at springe overskriftsrækken over, skal rækkenummeret i den første For look starte på 2 i stedet for 1. Det betyder, at du skal trække 1 fra for matrixrækkeværdien, når du indlæser celleværdien ind i arrayet ved hjælp af celler^(Cells) (i, j). Værdi.

Hvor skal du indsætte dit VBA Array Script^{(Your VBA Array Script)}

For at placere et VBA -script i Excel , skal du bruge VBA - editoren. Du kan få adgang til dette ved at vælge Udviklermenuen^(Developer) og vælge Vis kode^{(View Code)} i sektionen Kontrolelementer^(Controls) på båndet.

Hvis du ikke kan se udvikleren^(Developer) i menuen, skal du tilføje den. For at gøre dette skal du vælge Filer^(File) og indstillinger^(Options) for at åbne vinduet Excel-indstillinger.

Skift vælg kommandoer fra rullemenuen til Alle kommandoer^{(All Commands)} . Vælg Udvikler^(Developer) i menuen til venstre, og vælg knappen Tilføj^(Add) for at flytte den til ruden til højre. Marker afkrydsningsfeltet for at aktivere det, og vælg OK for at afslutte.

Når vinduet Code Editor åbner, skal du sørge for, at arket dine data er valgt i venstre rude. Vælg Arbejdsark^(Worksheet) i rullemenuen til venstre og Aktiver^(Activate) til højre. Dette vil oprette en ny underrutine kaldet Worksheet_Activate ().

Denne funktion vil køre, når regnearksfilen åbnes. Indsæt koden i scriptruden inde i denne underrutine.

Dette script vil arbejde gennem de 12 rækker og vil indlæse rep-navnet fra kolonne 3, varen fra kolonne 4 og det samlede salg fra kolonne 7.

Når begge For-løkker er færdige, indeholder det todimensionelle array arrMyArray alle de data, du har angivet fra det originale ark.

Manipulering af arrays i Excel VBA

Lad os sige, at du vil anvende en moms på 5 % på alle de endelige salgspriser og derefter skrive alle data til et nyt ark.

Du kan gøre dette ved at tilføje en anden For-løkke efter den første, med en kommando til at skrive resultaterne til et nyt ark.

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

Dette vil "aflæse" hele arrayet i Sheet2 , med en ekstra række, der indeholder totalen ganget med 5% for afgiftsbeløbet.

Det resulterende ark vil se sådan ud.

Som du kan se, er VBA -arrays i Excel ekstremt nyttige og lige så alsidige som ethvert andet Excel-trick^{(any other Excel trick)} . 

Eksemplet ovenfor er en meget enkel anvendelse af et array. Du kan oprette meget større arrays og udføre sortering, gennemsnitsberegning eller mange andre funktioner i forhold til de data, du gemmer i dem.

Hvis du vil være rigtig kreativ, kan du endda oprette to arrays , der indeholder en række celler^{(containing a range of cells)} fra to forskellige ark, og udføre beregninger mellem elementer i hvert array.

Ansøgningerne er kun begrænset af din fantasi.

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

VBA has been part of the Microsoft Office suite for many уears. While it doesn’t have the full fυnctionality and power of a full VB application, VBA provideѕ Office users with the flexibility to integrate Office products 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

• Sådan slettes tomme linjer i Excel

• Sådan bruger du Excels Tal Cells-funktion

• Sådan indsætter du et Excel-regneark i et Word-dokument

• Sådan bruges Excels What-If-analyse

• Sådan rettes en række i Excel

• Skriv hurtigt tal i Word og Excel

• En avanceret VBA-vejledning til MS Excel

• Gruppér rækker og kolonner i et Excel-regneark

• Sådan flyttes kolonner i Excel

• Tilslutning af Excel til MySQL

• Brug af Excels What-If Analysis Goal Seek Tool

• Sådan indsætter du CSV eller TSV i et Excel-regneark

• Sådan opretter du flere linkede rullelister i Excel

• Google Sheets vs Microsoft Excel – Hvad er forskellene?

• 3 måder at opdele en celle i Excel

• Sådan bruges PMT-funktionen i Excel

• Sådan opretter du en VBA-makro eller et script i Excel

• 2 måder at bruge Excels transponeringsfunktion

• Brug dynamiske områdenavne i Excel til fleksible dropdowns

• Centrer dine regnearksdata i Excel til udskrivning