VBA ist seit vielen Jahren Teil der Microsoft Office -Suite. Obwohl es nicht die volle Funktionalität und Leistung einer vollständigen VB-Anwendung bietet, bietet VBA Office - Benutzern die Flexibilität, Office - Produkte zu integrieren und Ihre Arbeit darin zu automatisieren.

Eines der leistungsstärksten Werkzeuge, die in VBA verfügbar sind, ist die Fähigkeit, eine ganze Reihe von Daten in eine einzelne Variable namens Array zu laden. Durch das Laden von Daten auf diese Weise können Sie diesen Datenbereich auf vielfältige Weise manipulieren oder Berechnungen durchführen.

Was ist also ein VBA -Array? In diesem Artikel beantworten wir diese Frage und zeigen Ihnen, wie Sie eines in Ihrem eigenen VBA-Skript^{(your own VBA script)} verwenden .

Was ist ein VBA-Array?

Die Verwendung eines VBA -Arrays in Excel ist sehr einfach, aber das Verständnis des Konzepts von Arrays kann etwas komplex sein, wenn Sie noch nie eines verwendet haben.

Stellen Sie^(Think) sich ein Array wie eine Kiste mit Abschnitten darin vor. Ein eindimensionales Array ist eine Box mit einer Reihe von Abschnitten. Ein zweidimensionales Array ist eine Box mit zwei Abschnittszeilen.

Sie können in jedem Abschnitt dieser „Box“ Daten in beliebiger Reihenfolge eingeben.

Am Anfang Ihres VBA -Skripts müssen Sie diese „Box“ definieren, indem Sie Ihr VBA -Array definieren. Um also ein Array zu erstellen, das einen Datensatz enthalten kann (ein eindimensionales Array), würden Sie die folgende Zeile schreiben.

Dim arrMyArray(1 To 6) As String

Später in Ihrem Programm können Sie Daten in jeden Abschnitt dieses Arrays einfügen, indem Sie auf den Abschnitt in Klammern verweisen.

arrMyArray(1) = "Ryan Dube"

Mit der folgenden Zeile können Sie ein zweidimensionales Array erstellen.

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

Die erste Zahl steht für die Zeile, die zweite für die Spalte. Das obige Array könnte also einen Bereich mit 6 Zeilen und 2 Spalten enthalten.

Sie können jedes Element dieses Arrays wie folgt mit Daten laden.

arrMyArray(1,2) = 3

Dadurch wird eine 3 in die Zelle B1 geladen.

Ein Array kann jede Art von Daten als reguläre Variable enthalten, z. B. Zeichenfolgen, boolesche Werte, Ganzzahlen, Gleitkommazahlen und mehr.

Die Zahl in der Klammer kann auch eine Variable sein. Programmierer verwenden üblicherweise eine For - Schleife^(Loop) , um durch alle Abschnitte eines Arrays zu zählen und Datenzellen in der Tabelle in das Array zu laden. Wie das geht, erfahren Sie später in diesem Artikel.

So programmieren Sie ein VBA-Array in Excel

Werfen wir einen Blick auf ein einfaches Programm, in dem Sie Informationen aus einer Tabellenkalkulation in ein mehrdimensionales Array laden möchten.

Sehen wir uns als Beispiel eine Produktverkaufstabelle an, in der Sie den Namen, den Artikel und den Gesamtverkauf des Vertriebsmitarbeiters aus der Tabelle ziehen möchten.

Beachten Sie, dass Sie in VBA , wenn Sie auf Zeilen oder Spalten verweisen, Zeilen und Spalten^{(rows and columns)} beginnend bei 1 oben links zählen. Die Spalte „rep“ ist also 3, die Spalte „item“ ist 4 und die Spalte „total“ ist 7.

Um diese drei Spalten über alle 11 Zeilen zu laden, müssen Sie das folgende Skript schreiben.

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

Sie werden feststellen, dass zum Überspringen der Kopfzeile die Zeilennummer im ersten For-Look bei 2 und nicht bei 1 beginnen muss. Das bedeutet, dass Sie 1 für den Array-Zeilenwert subtrahieren müssen, wenn Sie den Zellenwert laden in das Array mit Cells (i, j).Value.

Wo Sie Ihr VBA-Array-Skript einfügen^{(Your VBA Array Script)}

Um ein VBA- Skript in Excel zu programmieren , müssen Sie den VBA- Editor verwenden. Sie können darauf zugreifen, indem Sie das Menü „ Entwickler^(Developer) “ auswählen und im Abschnitt „ Steuerelemente^(Controls) “ des Menübands „ Code anzeigen“ auswählen.^{(View Code)}

Wenn Sie den Entwickler^(Developer) nicht im Menü sehen, müssen Sie ihn hinzufügen. Wählen Sie dazu Datei^(File) und Optionen^(Options) , um das Fenster Excel-Optionen zu öffnen.

Ändern Sie die Auswahlbefehle aus der Dropdown-Liste in Alle Befehle^{(All Commands)} . Wählen Sie Entwickler^(Developer) aus dem linken Menü und wählen Sie die Schaltfläche Hinzufügen^(Add) , um sie in den Bereich auf der rechten Seite zu verschieben. Aktivieren Sie das Kontrollkästchen, um es zu aktivieren, und wählen Sie OK , um den Vorgang abzuschließen.

Wenn das Code-Editor- Fenster geöffnet wird, vergewissern Sie sich, dass das Blatt, in dem sich Ihre Daten befinden, im linken Bereich ausgewählt ist. Wählen Sie im linken Dropdown-Menü Arbeitsblatt und rechts ^(Worksheet)Aktivieren^(Activate) aus. Dadurch wird eine neue Subroutine namens Worksheet_Activate () erstellt.

Diese Funktion wird immer dann ausgeführt, wenn die Tabellenkalkulationsdatei geöffnet wird. Fügen Sie den Code in das Skriptfenster innerhalb dieser Subroutine ein.

Dieses Skript durchläuft die 12 Zeilen und lädt den Namen des Vertriebsmitarbeiters aus Spalte 3, den Artikel aus Spalte 4 und den Gesamtverkauf aus Spalte 7.

Sobald beide For-Schleifen abgeschlossen sind, enthält das zweidimensionale Array arrMyArray alle Daten, die Sie aus dem ursprünglichen Blatt angegeben haben.

Bearbeiten von Arrays in Excel VBA

Angenommen, Sie möchten eine Umsatzsteuer von 5 % auf alle endgültigen Verkaufspreise anwenden und dann alle Daten in ein neues Blatt schreiben.

Sie können dies tun, indem Sie nach der ersten eine weitere For-Schleife mit einem Befehl hinzufügen, um die Ergebnisse in ein neues Blatt zu schreiben.

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

Dadurch wird das gesamte Array in Sheet2^(Sheet2) „entladen“ , mit einer zusätzlichen Zeile, die die Summe multipliziert mit 5 % für den Steuerbetrag enthält.

Das resultierende Blatt sieht so aus.

Wie Sie sehen können, sind VBA- Arrays in Excel äußerst nützlich und so vielseitig wie jeder andere Excel-Trick^{(any other Excel trick)} . 

Das obige Beispiel ist eine sehr einfache Verwendung für ein Array. Sie können viel größere Arrays erstellen und die darin gespeicherten Daten sortieren, mitteln oder viele andere Funktionen ausführen.

Wenn Sie wirklich kreativ werden möchten, können Sie sogar zwei Arrays erstellen , die eine Reihe von Zellen^{(containing a range of cells)} aus zwei verschiedenen Blättern enthalten, und Berechnungen zwischen den Elementen jedes Arrays durchführen.

Die Anwendungen sind nur durch Ihre Vorstellungskraft begrenzt.

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

VBA has been part of the Microѕoft Office suite for many years. While it doesn’t have the full functionality and power of a full VB apрlication, VBA provides Office users with the flexibility to integrate Office products аnd 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

• So löschen Sie leere Zeilen in Excel

• So verwenden Sie die Speak Cells-Funktion von Excel

• So fügen Sie ein Excel-Arbeitsblatt in ein Word-Dokument ein

• So verwenden Sie die Was-wäre-wenn-Analyse von Excel

• So reparieren Sie eine Zeile in Excel

• So alphabetisieren Sie in Excel

• Wann sollte Index-Match anstelle von SVERWEIS in Excel verwendet werden?

• So erstellen Sie mehrere verknüpfte Dropdown-Listen in Excel

• 10 Excel-Tipps und Tricks für 2019

• So verwenden Sie die PMT-Funktion in Excel

• So schreiben Sie eine IF-Formel/Anweisung in Excel

• 3 Möglichkeiten, eine Zelle in Excel zu teilen

• So entfernen Sie doppelte Zeilen in Excel

• So verwenden Sie absolute Bezüge in Excel

• Grundlegende einspaltige und mehrspaltige Datensortierung in Excel-Tabellen

• So verfolgen Sie Abhängigkeiten in Excel

• 4 Möglichkeiten, ein Häkchen in Excel zu verwenden

• So verwenden Sie If- und verschachtelte If-Anweisungen in Excel

• So berechnen Sie den Z-Score in Excel

• Zahlen schnell in Word und Excel buchstabieren