Zugriff auf Daten über VBA und Bereichsnamen

Wenn für einen Zellbereich ein Bereichsname vergeben wurde, kann auf diesen über VBA zugegriffen, also gelesen und geschrieben werden.

In diesem Beispiel wurde ein Bereich mit einer Größe von 12 Zeilen und 2 Spalten definiert. Als Bereichsname wurde der Begriff dutzend vergeben, so dass der Zugriff auf die einzelnen Werte über diesen Namen erfolgt.

In der folgenden Abbildung sehen Sie den Aufbau des Datenbereichs mit dem Bereichsnamen dutzend:

Ein großer Vorteil bei dieser Vorgehensweise liegt darin, dass der definierte Bereich beliebig auf dem Tabellenblatt verschoben werden kann und der Zugriff über VBA trotzdem ohne Anpassung des Codes funktioniert, da der Code nicht auf Zellen sondern auf den vergebenen Namen referenziert.

Die folgenden Beispiele zeigen, wie der gesamte Bereich, einzelne Spalten des Bereichs oder nur einzelne Werte  ausgelesen und per VBA verändert werden können.

1. Alle Werte aus dem Bereich auslesen

Über eine For-Each-Next-Schleicht wird der gesamte Bereich von Zelle B7 bis C18 durchlaufen und die Werte werden über eine MessageBox ausgegeben.

Sub AlleWerteAusNamensbereich()
'** Alle Werte aus dem Bereichsnamen "dutzend" auslesen
'** Dimensionierung der Variablen
Dim rngZelle As Range

'** Durchlaufen aller Zellen im Bereich
For Each rngZelle In Range("dutzend")
  MsgBox rngZelle
Next rngZelle

End Sub

2. Nur 1. Spalte aus dem Bereich auslesen

Sub ZellbereichAuslesen_Spalte1()
'** Alle Werte aus dem Bereichsnamen "dutzend" auslesen

'** Dimensionierung der Variablen
Dim arBereich, lngS As Long, lngZ As Long

'** Bereich definieren
arBereich = [dutzend]

'** Spalte 1 durchlaufen
For lngZ = LBound(arBereich) To UBound(arBereich)
  MsgBox "Zeile : " & lngZ & ", Spalte : " & 1 & _
    ", Inhalt : " & arBereich(lngZ, 1)
Next

End Sub

3. Nur 2. Spalte aus dem Bereich auslesen

Der Code aus diesem Beispiel ist fast identisch mit dem Code aus Beispiel 2. Einziger Unterschied ist die Angabe der Spaltennummer innerhalb der MessageBox. Dort wird anstatt Spalte 1 die Spalte 2 ausgegeben.

Sub ZellbereichAuslesen_Spalte1()
'** Alle Werte aus dem Bereichsnamen "dutzend" auslesen

'** Dimensionierung der Variablen
Dim arBereich, lngS As Long, lngZ As Long

'** Bereich definieren
arBereich = [dutzend]

'** Spalte 1 durchlaufen
For lngZ = LBound(arBereich) To UBound(arBereich)
  MsgBox "Zeile : " & lngZ & ", Spalte : " & 2 & _
    ", Inhalt : " & arBereich(lngZ, 2)
Next

End Sub

4. Alle Daten aus dem Zellbereich auslesen (Version 2)

Diese Variante entspricht inhaltlich dem Beispiel 1. Im Beispiel 1 wird der komplette Inhalt des Datenbereichs über eine For-Each-Next-Schleife abgegriffen. In diesem Beispiel erfolgt das Auslesen des Datenbereichs über zwei verschachtelte For-Next-Schleifen. Diese Variante ist unter Umständen für die eine oder andere Anwendung etwas flexibler.

Sub ZellbereichAuslesen_AlleSpalten()
'** Alle Werte aus dem Bereichsnamen "dutzend" auslesen (Version 2)

'** Dimensionierung der Variablen
Dim arBereich, lngS As Long, lngZ As Long

'** Bereich definieren
arBereich = [dutzend]
       
'** alle Zeilen und alle Spalten durchlaufen
For lngZ = LBound(arBereich) To UBound(arBereich)
    For lngS = LBound(Application.Transpose(arBereich)) _
        To UBound(Application.Transpose(arBereich))
            MsgBox "Zeile : " & lngZ & ", Spalte : " & lngS & _
                ", Inhalt : " & arBereich(lngZ, lngS)
    Next lngS
Next lngZ
End Sub

5. Einzelne Werte auslesen

Über die Angabe der Koordinaten (Zeile, Spalte) kann auf einzelne Zellen innerhalb des Datenbereichs zugegrifen werden. So liest der Befehl wert = arBereich(11,2) den Wert aus Zeile 11 in Spalte 2 aus. Das Ergebnis lautet damit "Wert B11".

Sub einzelnetWert()
'** Einzelnen Wert auslesen

'** Dimensionierung der Variablen
Dim arBereich

'** Bereich definieren
arBereich = [dutzend] 'Liste1 ist der benannte Bereich !

'** Wert auslesen und ausgeben
wert = arBereich(11, 2)
MsgBox wert
End Sub

6. Ersten Wert aus dem Datenbereich auslesen

Über eine spezielle Funktion kann der erste Wert des Datenbereichs gelesen werden, im Beispiel also den Wert "Wert A1".

Sub ErsterWert()
'** Ersten Wert aus dem Bereich auslesen

'** Dimensionierung der Variablen
Dim bereich

'** Bereich definieren
bereich = Range("dutzend")

'** ersten Wert auslesen und ausgeben
MsgBox LBound(bereich)
MsgBox bereich(LBound(bereich), 1)

End Sub

7. Letzten Wert aus dem Datenbereich auslesen

Über eine spezielle Funktion kann der letzte Wert des Datenbereichs gelesen werden, im Beispiel also den Wert "Wert B12".

Sub LetzerWert()
'** Letzten Wert aus dem Bereich auslesen

'** Dimensionierung der Variablen
Dim bereich, lngS

'** Bereich definieren
bereich = Range("dutzend")

'** letzte Spalte ermitteln
lngS = UBound(Application.Transpose(bereich))

'** Letzten Wert ausgeben
MsgBox "Zeile: " & UBound(bereich) & " Spalte: " & lngS
MsgBox bereich(UBound(bereich), lngS)

End Sub

8. Wert gezielt ändern

Dieses Beispiel zeigt, wie ein Wert im Datenbereich über Angabe der Koordinaten für Zeile und Spalte gezielt geändert werden kann. Der Befehl bereich(6, 2) = "Wert XY" ändert den Wert in Zeile 6, Spalte 2 entsprechend ab.

Sub Wert_Aendern()
'** Wert im Zellbereich gezielt ändern

'** Dimensionierung der Variablen
Dim bereich As Range

'** Bereich definieren
Set bereich = Range("dutzend")

'** Zeile 6, Spalte 2 ändern
bereich(6, 2) = "Wert XY"
End Sub