VBA 2D Arrays in Excel — zweidimensionale Arrays, Gitter und Range.Value

Kurzfassung — Ein VBA-2D-Array ist ein Array mit zwei Indizes — ein Gitter aus Zeilen und Spalten, g(row, column) — kein Array aus Arrays. Dim g(1 To 3, 1 To 4) ist drei Zeilen mal vier Spalten. Die einzige nützlichste Tatsache für die Excel-Arbeit: Das Lesen von Range("A1:D3").Value reicht Ihnen in einem Schritt ein einsbasiertes zweidimensionales Variant-Array, und das Zurückschreiben eines Arrays mit Range(...).Value = arr schreibt es in einem Schritt — weshalb die Array-Verarbeitung dramatisch schneller ist als das Schleifen Zelle für Zelle.

Sub SheetToArrayAndBack()
    Dim data As Variant
    data = Range("A1:D100").Value          ' ein Zugriff aufs Blatt -> 1-basiertes 2D-Array

    Dim r As Long
    For r = 1 To UBound(data, 1)           ' Zeilen
        data(r, 4) = data(r, 2) * data(r, 3)   ' Spalte D = Spalte B * Spalte C, im Speicher
    Next r

    Range("A1:D100").Value = data          ' ein Zugriff zurueck -> sofort
End Sub

Ein 2D-Array ist der Ort, an dem Arrays und Arbeitsblätter aufeinandertreffen: Ein Blatt ist ein Gitter, und der schnellste Weg, mit einem Block von Zellen zu arbeiten, ist, ihn in ein 2D-Array zu ziehen, im Speicher zu rechnen und zurückzuschreiben. Es baut direkt auf dem Dimensionieren von Arrays mit ReDim und dem Lesen ihrer Form mit UBound auf — ein Gitter hat einfach eine Grenze pro Dimension. Das eine, worüber die Leute stolpern, ist, dass Range.Value-Arrays einsbasiert sind und dass ReDim Preserve keine Zeilen hinzufügen kann.

Was Sie lernen

Das Denkmodell — ein Gitter mit zwei Indizes, g(row, column), keine verschachtelten Arrays
Ein 2D-Array mit Dim g(1 To 3, 1 To 4) und g(r, c) deklarieren und ansprechen
Der große Punkt: Range.Value gibt Ihnen in einem einzigen Schritt ein einsbasiertes 2D-Variant-Array
Ein Array in einem Rutsch in einen Bereich zurückschreiben — und warum das so viel schneller ist
Zwei Randfälle: Eine einzelne Zelle ist kein Array, und eine einzelne Spalte ist (n, 1)
Zeilen hinzufügen: warum ReDim Preserve das nicht kann und was Sie stattdessen tun
Wie sich ein verzweigtes „Array aus Arrays" von einem echten 2D-Array unterscheidet

Das Denkmodell: ein Array, zwei Indizes

Ein eindimensionales Array ist eine nummerierte Reihe von Kästchen: a(i). Ein zweidimensionales Array ist ein Gitter — Sie sprechen jedes Kästchen mit zwei Zahlen an, einer Zeile und einer Spalte: g(r, c). Entscheidend ist, dass es ein einziges Array ist, das sich zufällig auf zwei Weisen ansprechen lässt, keine Liste, deren Elemente selbst Listen sind:

Dim g(1 To 3, 1 To 4) As Long   ' 3 Zeilen, 4 Spalten = 12 Elemente
g(1, 1) = 10                     ' Zeile 1, Spalte 1
g(3, 4) = 99                     ' Zeile 3, Spalte 4

Der erste Index ist üblicherweise die Zeile, der zweite die Spalte — dieselbe Reihenfolge, in der Sie einen Zellbezug lesen. Halten Sie diese Reihenfolge konsequent ein, und ein 2D-Array bildet sich sauber auf einen rechteckigen Block von Zellen ab.

Ein Gitter deklarieren und durchlaufen

Deklarieren Sie die zwei Grenzen und durchlaufen Sie dann mit einem verschachtelten For — außen über Zeilen, innen über Spalten — mit UBound und einer Dimensionsnummer:

Dim g(1 To 3, 1 To 4) As Long
Dim r As Long, c As Long
For r = LBound(g, 1) To UBound(g, 1)       ' Zeilen
    For c = LBound(g, 2) To UBound(g, 2)   ' Spalten
        g(r, c) = r * 10 + c
    Next c
Next r

UBound(g, 1) ist die Zeilenanzahl, UBound(g, 2) die Spaltenanzahl. Von LBound bis UBound auf jeder Dimension zu iterieren bedeutet, dass derselbe Code funktioniert, ob das Array 1 To n ist (wie Range.Value es Ihnen gibt) oder nullbasiert.

Der große Punkt: Range.Value ist ein einsbasiertes 2D-Array

Das ist die Tatsache, die 2D-Arrays lernenswert macht. Das Lesen des .Value eines mehrzelligen Bereichs gibt Ihnen in einem einzigen Schritt ein zweidimensionales Variant-Array, dessen Grenzen bei 1 beginnen — unabhängig davon, wo der Bereich auf dem Blatt liegt:

Dim data As Variant
data = Range("C5:E7").Value      ' ein Block aus 3 Zeilen, 3 Spalten irgendwo auf dem Blatt
' data ist jetzt data(1 To 3, 1 To 3) - IMMER 1-basiert, Zeile dann Spalte
Debug.Print data(1, 1)          ' der Wert von C5
Debug.Print data(3, 3)          ' der Wert von E7

Beachten Sie zweierlei. Erstens ist das Array einsbasiert, nicht nullbasiert — data(1, 1) ist also die Zelle oben links, und eine For i = 0-Schleife würde die erste Zeile verfehlen und Fehler 9 treffen. Zweitens sind die Indizes relativ zum Block, nicht zum Blatt: data(1, 1) ist die Zelle oben links des Bereichs, ob der Bereich nun A1 oder C5 ist. Deklarieren Sie die aufnehmende Variable als Variant (nicht As Long() usw.) — VBA füllt ein frisches 2D-Variant für Sie.

In einem Rutsch zurückschreiben

Derselbe Trick funktioniert umgekehrt. Weisen Sie ein korrekt geformtes 2D-Array dem .Value eines Bereichs zu, und Excel schreibt den ganzen Block auf einmal:

Range("C5:E7").Value = data     ' schreibt alle 9 Zellen in einem Vorgang

Deshalb ist der Array-Ansatz schnell. Zellen einzeln anzufassen überschreitet bei jedem Lesen und Schreiben die Grenze zwischen VBA und Excel — Tausende Grenzübertritte für einen großen Bereich. In ein Array einzulesen, im Speicher zu rechnen und zurückzuschreiben bedeutet insgesamt nur zwei Übertritte. Für alles jenseits weniger Hundert Zellen ist das der Unterschied zwischen einem Makro, das sich sofort anfühlt, und einem, dem Sie beim Ackern zusehen. Der geschriebene Bereich muss der Form des Arrays entsprechen (Zeilen × Spalten), dimensionieren Sie den Zielbereich also auf UBound(arr, 1) Zeilen mal UBound(arr, 2) Spalten.

Zwei Randfälle, die beißen

Eine einzelne Zelle ist kein Array. Range("A1").Value gibt einen schlichten Skalar zurück, kein 1 To 1, 1 To 1-Array — Code, der ein Array annimmt, scheitert also bei einem Ein-Zellen-Bereich. Könnte der Bereich eine einzelne Zelle sein, testen Sie mit IsArray(), bevor Sie ihn indizieren.

Eine einzelne Zeile oder Spalte ist trotzdem 2D. Range("A1:A10").Value ist data(1 To 10, 1 To 1) — zehn Zeilen, eine Spalte — Sie sprechen es also weiterhin als data(i, 1) an, nicht als data(i). Dasselbe gilt für eine einzelne Zeile: data(1, j). Es ist leicht, den zweiten Index bei einem „eindimensionalen" Ausschnitt eines Blatts zu vergessen.

Zeilen hinzufügen: ReDim Preserve kann es nicht

Weil ReDim Preserve nur die letzte Dimension ändert, können Sie die Zeilenanzahl (die erste Dimension) eines 2D-Arrays nicht vergrößern und dabei die Daten behalten. Drei praktische Optionen:

Einmal dimensionieren. Kennen Sie die Zeilenanzahl im Voraus (eine letzte Zeile, eine Datensatzanzahl), machen Sie ReDim auf das Gitter in seiner endgültigen Größe vor der Schleife und nie Preserve.
Stattdessen die Spalten vergrößern. Deklarieren Sie das Array als Spalten × Zeilen, sodass die Dimension, die Sie erweitern, die letzte ist, und transponieren Sie beim Hinausschreiben mit Application.Transpose.
Neu aufbauen. Für wirklich unbekanntes Wachstum sammeln Sie in eine Collection oder eine verzweigte Struktur und bauen das 2D-Array einmal am Ende.

Für die Blattarbeit trifft die erste Option fast immer zu, denn die Zeilenanzahl ist eine billige Sache, die man vor dem Start in Erfahrung bringt.

Verzweigte Arrays sind etwas anderes

Ein echtes 2D-Array ist rechteckig — jede Zeile hat dieselbe Anzahl Spalten. Ein verzweigtes Array ist ein eindimensionales Array, dessen Elemente selbst Arrays sind, sodass jede „Zeile" eine andere Länge haben kann. Sie sprechen es mit zwei Klammerpaaren an, nicht mit zwei Indizes:

Dim jagged(1 To 2) As Variant
jagged(1) = Array(1, 2, 3)      ' eine Zeile mit 3 Elementen
jagged(2) = Array(9, 8)         ' eine Zeile mit 2 Elementen
Debug.Print jagged(1)(3)        ' 3   <- zwei Klammerpaare, nicht (1, 3)

Verzweigte Arrays sind praktisch für unregelmäßige Daten, aber sie funktionieren nicht mit Range.Value zusammen, das immer rechteckig ist. Wenn die Daten ein Rechteck sind — und ein Blattblock ist das immer —, greifen Sie zu einem echten 2D-Array.

Wie ExcelMaster hilft

Effizient mit einem Block von Zellen zu arbeiten ist ein Muster — Range.Value in ein einsbasiertes 2D-Array lesen, im Speicher rechnen, in einem Rutsch zurückschreiben — und die Fallen stecken alle im Detail: der einsbasierte Index, der Ein-Zellen-Skalar, die Zeile, die Sie mit Preserve nicht vergrößern können.

ExcelMaster lässt Sie die Aufgabe beschreiben — „setz für jede Zeile im benutzten Bereich Menge mal Preis in Spalte E" — und liest den Block in ein 2D-Array, iteriert von 1 bis UBound(arr, 1), schreibt das Ergebnis in einer einzigen Zuweisung zurück und behandelt die Fälle der einzelnen Zelle und der einzelnen Spalte, sodass das Makro sowohl korrekt als auch schnell ist. Sie behalten die Arbeitsmappe und den Code.

Häufig gestellte Fragen

Wie deklariere ich ein 2D-Array in VBA?

Geben Sie zwei durch ein Komma getrennte Grenzen an: Dim g(1 To 3, 1 To 4) As Long erzeugt ein Gitter aus drei Zeilen und vier Spalten. Sprechen Sie ein Element mit zwei Indizes an, g(row, column), etwa g(2, 3). Für ein dynamisches 2D-Array deklarieren Sie es offen mit Dim g() und dimensionieren es später mit ReDim g(1 To 3, 1 To 4).

Wie lese ich einen Bereich in ein 2D-Array in VBA?

Weisen Sie den .Value des Bereichs einem Variant zu: Dim data As Variant: data = Range("A1:D100").Value. VBA füllt ein einsbasiertes zweidimensionales Array, data(1 To rows, 1 To columns), in einem einzigen Schritt. Sprechen Sie Zellen als data(row, column) an, wobei die Indizes relativ zur Ecke oben links des Bereichs sind. Das ist der schnellste Weg, viele Zellen zu verarbeiten.

Warum ist mein Range.Value-Array einsbasiert statt nullbasiert?

Weil Excel es so zurückgibt — ein Range.Value-Array beginnt immer bei Index 1 auf beiden Dimensionen, egal wo der Bereich auf dem Blatt liegt. Iterieren Sie von LBound(data, 1) bis UBound(data, 1) (oder einfach 1 To UBound), damit Sie nicht die erste Zeile überspringen oder mit einer nullbasierten Schleife über das Ende hinauslaufen.

Wie schreibe ich ein 2D-Array in einen Bereich zurück?

Weisen Sie es dem .Value des Bereichs zu: Range("A1:D100").Value = data. Der Bereich muss dieselbe Form wie das Array haben — dieselbe Anzahl Zeilen und Spalten —, dimensionieren Sie das Ziel also mit UBound(data, 1) Zeilen und UBound(data, 2) Spalten. Das schreibt den ganzen Block in einem Vorgang, weit schneller als Zelle für Zelle.

Kann ich einem 2D-Array in VBA eine Zeile hinzufügen?

Nicht mit ReDim Preserve — es kann nur die letzte (Spalten-)Dimension ändern, sodass das Vergrößern der Zeilenanzahl Fehler 9 auslöst. Dimensionieren Sie das Array entweder vor der Schleife auf seine endgültige Zeilenanzahl, deklarieren Sie es als Spalten × Zeilen und transponieren, oder bauen Sie es aus einer Collection und erzeugen das 2D-Array einmal am Ende.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 24.09.2026.

Verwandte Anleitungen: VBA ReDim · VBA UBound · VBA Array · VBA For Loop · VBA Range