VBA: Bereiche mit Range und Cells ansprechen

Wer sich schon einmal in der VBA-Programmierung versucht hat, weiß, dass zur Ansprache eines Bereiches die Anweisungen RANGE oder CELLS verwendet werden können. Beide Anweisungen liefern letztlich das gleiche Ergebnis.

Dennoch gibt es Unterschiede, mit denen sich der Beitrag beschäftigt.

1. Einordnung von Range und Cells

VBA ist eine objekt- und ereignisorientierte Programmierung. Aufgrund der vorhandenen Objekthierarchie ist es leicht möglich, Elemente, die mit dem Code manipuliert werden sollen, anzusprechen und zu identifizieren.

Wichtig für das Verständnis von Objekten ist, dass Objekte ihren Zustand über ihre komplette Lebensdauer behalten.

Range ist ein Objekt und so in der Objekthierarchie eingeordnet:

Bereiche1

Ein Range-Objekt kann eine einzelne Zelle oder einen Zellbereich umfassen.

Mit der Range-Eigenschaft kann eine einzelne Zelle oder ein Zellbereich zurück gegeben werden.

Dagegen kann die Cells-Eigenschaft immer nur eine einzelne Zelle zurück geben.

Die nachfolgenden Beispiele sollen die Handhabung beider Eigenschaften verdeutlichen.

2. Zellen und Bereich selektieren und aktivieren

Die Range-Eigenschaft verwendet die A1-Schreibweise, also die Zelladresse.

Auswahl einer einzelnen Zelle

Sub Bereich1()
Range(„A2“).Select
End Sub

Auswahl eines verbundenen Zellbereiches

Sub Bereich2()
Range(„A2:B10“).Select
End Sub

Auswahl eines getrennten Zellbereiches

Sub Bereich3()
Range(„A2:A10,C2:C10“).Select
End Sub

Auswahl eines namentlich benannten Bereiches (Benenne z.B. einen Bereich B2:C9 mit „Beispielbereich“)

Sub Bereich4()
Range(„Beispielbereich“).Select
End Sub

Die Cells-Eigenschaft verwendet die Z1S1-Schreibweise, also erst die Zeilen- dann die Spaltenangabe.

Auswahl einer einzelnen Zelle

Sub Bereich5()
Cells(2,1).Select
End Sub

Auswahl eines verbundenen Zellbereiches

Sub Bereich6()
Cells(Cells(2,1),Cells(10,2)).Select
End Sub

Schreibst Du den Code so, erhältst Du eine Fehlermeldung. Verwendest Du wiederum Range am Anfang und bezeichnest Anfang und Ende des Bereiches miz Cells, ist der Code erfolgreich.

Sub Bereich7()
Range(Cells(2,1),Cells(10,2)).Select
End Sub

Die Auswahl eines getrennten Bereiches ist mit Cells nicht möglich.

Das verdeutlicht, dass mit Range auch Zellbereiche, mit Cells aber nur Zellen bestimmt werden können.

Willst Du Zellen oder Zellbereiche aktivieren statt zu selektieren, setzt Du statt der Anweisung „Select“ die Anweisung „Activate“ ein.

Zur Erinnerung: Geht es um eine einzelne Zelle, geben Select und Activate das gleiche Objekt zurück. Geht es um eine Zellbereich, wird er insgesamt selektiert, aber nur die erste Zelle aktiviert.

3. Arbeitsblattfunktionen

Hierzu gibt es im Blog bereits einen speziellen Beitrag.

Dennoch sollen hier die Unterschiede zwischen der Verwendung von Range oder Cells gezeigt werden.

Beispielhaft hast Du einen kleinen Wertebereich, über den Du mit einem Makro die Summe bilden willst.

Bereiche2

Bei Verwendung der Range-Eigenschaft musst Du zunächst die Zelle, die das Ergebnis der Berechnung aufnehmen soll, selektieren oder aktivieren.

Sub Bereich8()
Range(„D2“).Select
Selection.Formula=“=SUM(B2:B7)“
End Sub

oder

Sub Bereich9()
Range(„D2“).Activate
ActiveCell.Formula=“=SUM(B2:B7)“
End Sub

In beiden Fällen erhältst Du in D2 das Ergebnis 210.

Bereiche7

Verwendest Du die Cells-Eigenschaft, sparst Du Dir Selektieren oder Aktivieren. Mit Cells hast Du die Ergebniszelle bereits selektiert.

Sub Bereich10()
Cells(2,4).Formula=“=SUM(B2:B7)“
End Sub

Das Ergebnis ist wieder 210.

4. Bereiche unter Verwendung von Variablen ansprechen

Programmierst Du eine Schleife mit VBA, um Anweisungen solange zu wiederholen, bis ein bestimmtes Kriterium erfüllt ist, wirst Du in der Regel auch solange Zählvariablen verwenden.

Nimm den oben abgebildeten Wertebereich B2:B7. Du willst mittels einer For-Next-Schleife den Bereich durchlaufen, die Werte mit der Zeilen-Nr. multiplizieren und die Ergebnisse im Bereich C2:C7 ablegen.

Sub Bereich11()
Dim i As Integer
For i=2 To 7
Range(„C“&i)=Range(„B“&i)*i
Next i
End Sub

Das kleine Makro hat die Aufgabenstellung erfüllt.

Bereiche8

Statt mit Range soll jetzt noch mit Cells gearbeitet werden.

Sub Bereich12()
Dim i As Integer
For i=2 To 7
Cells(i,3)=Cells(i,2)*i
Next i
End Sub

Damit erhältst Du die gleichen Ergebnisse.

Ein Vorteil der Cells-Eigenschaft ist, so wird vielfach erwähnt, ist die einfachere Schreibweise innerhalb der Klammer.

5. Zellen innerhalb eines definierten Bereiches ansprechen

Angenommen, Du hast einen Bereich mit fünf Zeilen und drei Spalten.

Bereiche5

Aus diesem Bereich willst Du den Wert der 2. Zeile / 2. Spalte abfragen. Dazu könntest Du die INDEX-Funktion nutzen.

Es genügt aber auch, mit Range und Cells zu arbeiten, das geht so:
Der Wert soll in B9 abgelegt werden.

Sub Bereich13()
Range(„B9“)=Range(„B3:D7“).Cells(2,2)
End Sub

Mit einem zweiten Makro willst Du den Wert der 3. Zelle des Bereiches abfragen:

Sub Bereich14()
Range(„B11“)=Range(B3:D7“).Cells(3)
End Sub

Du siehst, um zur 3. Zelle des Bereiches zu gelangen, gehst Du zeilenweise von links nach rechts vor.

Hier nun die Ergebnisse:

Bereiche6

4 Kommentare zu „VBA: Bereiche mit Range und Cells ansprechen“

  1. Ich komme damit irgendwie nicht klar…
    Ich will einen Teil aus einem benannten Bereich in einer inaktiven Datei in einem inaktiven Arbeitsblatt einer Variable zuweisen.
    Wenn ich das Blatt geöffnet habe, dann funktioniert:

    With Workbooks(ABook).Names(StatZeile).RefersToRange
    cRng = .Range(Cells(1, 1), Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    End With

    Sobald ich aber woanders bin, dann wählt der mit:

    With Workbooks(ABook).Names(StatZeile).RefersToRange
    cRng = .Range(Cells(1, 1), Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    End With
    irgendwas aus, aber nicht den Bereich, den ich haben will…

    Was mache ich falsch?

  2. Hallo Ben,

    Deine Code Zeilen sind irgendwie aus dem Zusammenhang gerissen, ich weiß nicht so recht, was Du willst.
    Mein Blogbeitrag hat sich auch nicht so speziell mit dieser Thematik beschäftigt. So ohne weiteres kann ich Dir da nicht helfen.
    Support habe ich nicht geplant. Vielleicht solltest Du bei einem der vielen Foren anfragen.

  3. Ich habe es zwischenzeitlich selbst herausgefunden – der Punkt vor dem ersten Range war falsch. Ich weiß zwar nicht so richtig warum, aber so ist es richtig:

    Dim cRng

    With Workbooks(„RangeWorkbook“).Names(„Bereichname“).RefersToRange
    cRng = Range(.Cells(1, 1), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
    End With

    Ziel ist es, von irgendwo (anderes Tabellenblatt, anderes Workbook) her auf einen Teilbereich (hier auf den gefüllten Teil der ersten Zeile) eines benannten Bereichs zuzugreifen. Könnte vielleicht als Ergänzung mit rein – ich bin mit solchen Fragen irgendwie immer auf Deiner Seite gelandet, und sonst war dazu nichts zu finden… Und ich habe es jetzt zwar gelöffelt, aber ich vermute nicht, daß ich der einzige bin, der darüber gestolpert ist 😉

    Allgemein:
    #AZeile – Nummer der Anfangszeile, #EZeile – ~ Endzeile
    #ASpalte/#ESpalte analog
    cRng steht für current Range – allgemeine Übergabevariable für Bereichs-Daten

    Dim cRng

    With Workbooks(„RangeWorkbook“).Names(„Bereichname“).RefersToRange
    cRng = Range(.Cells(#AZeile, #ASpalte), .Cells(#Zeile, #ESpalte))
    End With

    Oder ausgeschrieben:

    Dim cRng

    cRng = Range( _
    Workbooks(„RangeWorkbook“).Names(„Bereichname“).RefersToRange.Cells(#AZeile, #ASpalte), _
    Workbooks(„RangeWorkbook“).Names(„Bereichname“).RefersToRange.Cells(#EZeile, #ESpalte))

    Danke trotzdem!
    Schön, daß es solche Seiten, wie Deine gibt…

    Gruß
    Ben

  4. Hallo Ben, es freut mich, dass Du das Problem alleine lösen konntest.
    In meinem Beitrag ging es eigentlich nur um den Unterschied von Range und Cells. Ihn um das Problem zu ergänzen, will ich nicht. Es gäbe da vieles mehr, was ich noch einbauen könnte.
    Vielleicht schreibe ich einen neuen Beitrag, u. a. Hierzu.
    Grüße
    Gerhard

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s