Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

VBA-Begriff: Range-Objekt

Workbooks-Auflistung (Workbook-Objekt)
Worksheets-Auflistung (Worksheet-Objekt)
Range-Objekt
Mehrere Objekte

Stellt eine Zelle, eine Zeile, eine Spalte, eine Auswahl von Zellen aus einem oder mehreren zusammenhängenden Zellblöcken oder einen 3D-Bereich dar.

Verwenden des Range-Objekts

In diesem Abschnitt werden die folgenden Eigenschaften und Methoden beschrieben, um ein Range-Objekt zurückzugeben:

  • Range-Eigenschaft

  • Cells-Eigenschaft

  • Range und Cells

  • Offset-Eigenschaft

  • Union-Methode

Range-Eigenschaft

Verwenden Sie Range(Arg), wobei arg den Bereich benennt, um ein Range-Objekt zurückzugeben, das eine einzelne Zelle oder einen Zellbereich darstellt. Im folgenden Beispiel wird der Wert der Zelle A1 in Zelle A5 übertragen.

Worksheets("Sheet1").Range("A5").Value = _
    Worksheets("Sheet1").Range("A1").Value

Im folgenden Beispiel wird der Bereich A1:H8 mit Zufallszahlen aufgefüllt, indem für jede Zelle des Bereichs die Formel festgelegt wird. Bei Verwendung ohne Objektkennzeichner (das Objekt links vom Punkt) gibt die Range-Eigenschaft einen Bereich im aktiven Blatt zurück. Ist das aktive Blatt kein Tabellenblatt, schlägt die Methode fehl. Aktivieren Sie mit Hilfe der Activate-Methode ein Tabellenblatt, bevor Sie die Range-Eigenschaft ohne expliziten Objektkennzeichner einsetzen.

Worksheets("sheet1").Activate
Range("A1:H8").Formula = "=rand()"    ' Range is on the active sheet

Im folgenden Beispiel wird der Inhalt des Bereichs mit dem Namen "Kriterien" gelöscht.

Worksheets(1).Range("criteria").ClearContents

Wenn Sie als Bereichsadresse ein Textargument einsetzen, müssen Sie die Adresse im A1-Bezugssystem angeben (die Z1S1-Schreibweise kann nicht verwendet werden).

Cells-Eigenschaft

Verwenden Sie Cells(Reihe, Spalte), wobei Reihe der Zeilenindex und Spalte der Spaltenindex ist, um eine einzelne Zelle zurückzugeben. Im folgenden Beispiel wird der Wert von Zelle A1 auf 24 gesetzt.

Worksheets(1).Cells(1, 1).Value = 24

Im folgenden Beispiel wird die Formel von Zelle A2 festgelegt.

ActiveSheet.Cells(2, 1).Formula = "=sum(B1:B5)"

Sie können die Zelle A1 auch mit Range("A1") angeben - gelegentlich ist jedoch die Cells-Eigenschaft praktischer, da sie die Angabe der Zeile oder Spalte durch eine Variable ermöglicht. Im folgenden Beispiel werden Zeilen- und Spaltenbeschriftungen in dem Tabellenblatt mit dem Namen Sheet1 erstellt. Beachten Sie, dass nach der Aktivierung des Tabellenblatts die Cells-Eigenschaft ohne explizite Blattangabe verwendet werden kann (sie gibt eine Zelle im aktiven Blatt zurück).

Sub SetUpTable()
Worksheets("sheet1").Activate
For theYear = 1 To 5
    Cells(1, theYear + 1).Value = 1990 + theYear
Next theYear
For theQuarter = 1 To 4
    Cells(theQuarter + 1, 1).Value = "Q" & theQuarter
Next theQuarter
End Sub

Sie könnten zwar die Visual Basic-Zeichenfolgenfunktionen verwenden, um Bezüge in der A1-Schreibweise zu ändern, es ist jedoch erheblich einfacher (und wesentlich besserer Programmierstil), die Schreibweise Cells(1, 1) zu verwenden.

Verwenden Sie Ausdruck.Cells(Reihe, Spalte) , wobei Ausdruck für einen Ausdruck steht, der ein Range-Objekt zurückgibt, und Reihe und Spalte relativ zur linken oberen Ecke des Bereichs angegeben werden, um einen Teilbereich zurückzugeben. Im folgenden Beispiel wird die Formel von Zelle C5 festgelegt.

Worksheets(1).Range("C5:C10").Cells(1, 1).Formula = "=rand()"

Range und Cells

Verwenden Sie Range(Zelle1, Zelle2), wobei Zelle1 und Zelle2 Range-Objekte sind, die die Anfangs- und Endzellen festlegen, um ein Range-Objekt zurückzugeben. Im folgenden Beispiel wird die Linienart des Rahmens um die Zellen A1:J10 festgelegt.

With Worksheets(1)
    .Range(.Cells(1, 1), _
        .Cells(10, 10)).Borders.LineStyle = xlThick
End With

Beachten Sie den Punkt vor jedem Aufruf der Cells-Eigenschaft. Der Punkt bewirkt, dass das Ergebnis der vorhergehenden With-Anweisung auf die Cells-Eigenschaft angewendet wird - in diesem Fall wird dadurch ausgedrückt, dass sich die Zellen im ersten Tabellenblatt befinden (ohne den Punkt würde die Cells-Eigenschaft Zellen im aktiven Blatt zurückgeben).

Offset-Eigenschaft

Verwenden Sie Offset(Reihe, Spalte), wobei Reihe und Spalte für den Versatz von Zeilen und Spalten stehen, um einen Bereich mit einem bestimmten Versatz zu einem anderen Bereich zurückzugeben. Im folgenden Beispiel wird die Zelle markiert, die sich eine Spalte rechts und drei Zeilen unter der linken oberen Eckzelle der aktuellen Auswahl befindet. Es ist nicht möglich, eine Zelle zu markieren, die sich nicht im aktiven Blatt befindet. Daher müssen Sie zunächst das Tabellenblatt aktivieren.

Worksheets("sheet1").Activate
  ' can't select unless the sheet is active
Selection.Offset(3, 1).Range("A1").Select

Union-Methode

Verwenden Sie Union(Bereich1, Bereich2, ...), um Mehrflächen-Bereiche, d.h. Bereiche aus zwei oder mehr zusammenhängenden Zellblöcken, zurückzugeben. Im folgenden Beispiel wird ein Objekt erstellt und als Vereinigung der Bereiche A1:B2 und C3:D4 definiert und ausgewählt.

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select

Bei der Arbeit mit einer Auswahl, die mehrere Bereiche umfasst, erweist sich die Areas-Eigenschaft als nützlich. Sie teilt eine Mehrbereichsauswahl in einzelne Range-Objekte auf und gibt diese als Auflistung zurück. Mit Hilfe der Count-Eigenschaft können Sie die zurückgegebene Auflistung auf eine Auswahl überprüfen, die mehrere Bereiche umfasst, wie im folgenden Beispiel dargestellt.

Sub NoMultiAreaSelection()
    numberOfSelectedAreas = Selection.Areas.Count
    If numberOfSelectedAreas > 1 Then
        MsgBox "You cannot carry out this command " & _
            "on multi-area selections"
    End If
End Sub