Wie verwende ich bereich und zellen in vba?

Einführung in Bereich und Zellen in VBA

Wenn Sie sich in einer Excel-Arbeitsmappe umsehen, werden Sie feststellen, dass rund um die Zellen alles funktioniert. In einer Zelle und einem Zellbereich speichern Sie Ihre Daten und dann beginnt alles.

Um VBA optimal nutzen zu können, müssen Sie lernen, wie Sie Zellen und Bereiche in Ihren Codes verwenden. Dazu benötigen Sie ein solides Verständnis von Range-Objekten. Damit können Sie auf folgende Weise auf Zellen in Ihren Codes verweisen:

• Eine einzelne Zelle.
• Eine Reihe von Zellen
• Eine Zeile oder Spalte
• Ein dreidimensionales Sortiment

Das RANGE-OBJEKT ist Teil der Objekthierarchie von Excel: Anwendung ➜ Arbeitsmappen ➜ Arbeitsblätter ➜ Bereich und an anderer Stelle im Arbeitsblatt. Wenn Sie also Code schreiben, der auf das RANGE-Objekt verweist, sieht das so aus:

 Application.Workbook(“Workbook-Name”).Worksheets(“Sheet-Name”).Range

Indem Sie auf eine Zelle oder einen Zellbereich verweisen, können Sie Folgendes tun:

• Sie können den Wert ablesen.
• Dort können Sie einen Wert eingeben.
• Und Sie können Änderungen am Format vornehmen.

Um all diese Dinge tun zu können, müssen Sie lernen, wie Sie auf eine Zelle oder einen Zellbereich verweisen. Im nächsten Abschnitt dieses Tutorials erfahren Sie, wie Sie auf unterschiedliche Weise auf eine Zelle verweisen. Um auf eine Zelle oder einen Zellbereich zu verweisen, können Sie drei verschiedene Methoden verwenden.

• Strandgrundstück
• Zelleneigenschaft
• Offset-Eigenschaft

Nun, welche davon die beste ist, hängt von Ihren Bedürfnissen ab, aber es lohnt sich, alle drei zu lernen, damit Sie diejenige auswählen können, die am besten zu Ihnen passt.

Also lasst uns anfangen.

Strandgrundstück

Die Range-Eigenschaft ist die gebräuchlichste und beliebteste Möglichkeit, in Ihren VBA-Codes auf einen Bereich zu verweisen. Mit der Range-Eigenschaft verweisen Sie einfach auf die Zelladresse. Lassen Sie mich Ihnen die Syntax erklären.

 expression.range(address)

Hier ist der Ausdruck eine Variable, die ein VBA-Objekt darstellt. Wenn Sie sich also auf Zelle A1 beziehen müssen, müssen Sie folgende Codezeile schreiben:

 Application.Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Der obige Code teilt VBA mit, dass Sie sich auf Zelle A1 beziehen, die sich im Arbeitsblatt „Sheet1“ und in der Arbeitsmappe „Book1“ befindet.

Hinweis: Wenn Sie eine Zellenadresse in das Bereichsobjekt eingeben, achten Sie darauf, diese in doppelte Anführungszeichen zu setzen. Aber hier ist etwas zu verstehen. Da Sie VBA in Excel verwenden, ist es nicht nötig, das Wort „Anwendung“ zu verwenden. Der Code wäre also:

 Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Und wenn Sie sich in Book1 befinden, können Sie Ihren Code weiter reduzieren:

 Worksheets(“Sheet1”).Range(“A1”)

Wenn Sie sich jedoch bereits im Arbeitsblatt „Sheet1“ befinden, können Sie Ihren Code weiter reduzieren und nur Folgendes verwenden:

 Range(“A1”)

Angenommen, Sie möchten auf einen gesamten Zellbereich (also mehrere Zellen) verweisen, müssen Sie den Code wie folgt schreiben:

 Range("A1:A5")

Im obigen Code haben Sie sich auf den Bereich A1 bis A5 bezogen, der aus fünf Zellen besteht. Sie können auch mit dem Range-Objekt auf einen benannten Bereich verweisen. Angenommen, Sie haben einen benannten Bereich mit dem Namen „Sales Discount“, um darauf zu verweisen. Sie können Code wie diesen schreiben:

 Range("Sales Discount")

Wenn Sie auf einen nicht kontinuierlichen Bereich verweisen möchten, müssen Sie etwa Folgendes tun:

 Range("A1:B5,D5:G10")

Und wenn Sie sich auf eine ganze Zeile oder Spalte beziehen möchten, müssen Sie einen Code wie den folgenden eingeben:

 Range("1:1") Range("A:A")

An diesem Punkt wissen Sie genau, wie man sich auf eine Zelle und den Zellbereich bezieht. Aber um das Beste daraus zu machen, müssen Sie lernen, es auch für andere Zwecke zu nutzen.

1. Wählen Sie eine Zelle aus und aktivieren Sie sie

Wenn Sie eine Zelle auswählen möchten, können Sie den Bereich verwenden. Wählen Sie die Methode aus. Wenn Sie beispielsweise Zelle A5 auswählen möchten, müssen Sie lediglich den Bereich angeben und anschließend „.Select“ hinzufügen.

 Range(“A1”).Select

Dieser Code weist VBA an, Zelle A5 auszuwählen. Wenn Sie einen Zellbereich auswählen möchten, beziehen Sie sich einfach auf diesen Bereich und fügen Sie danach einfach „.Select“ hinzu.

 Range(“A1:A5”).Select

Es gibt auch eine andere Methode, mit der Sie eine Zelle aktivieren können.

 Range(“A1”).Activate

Dabei ist zu beachten, dass jeweils nur eine Zelle aktiviert werden kann. Selbst wenn Sie einen Bereich mit der Methode „.Activate “ angeben, wird dieser Bereich ausgewählt, aber die aktive Zelle ist die erste Zelle im Bereich.

2. Geben Sie einen Wert in eine Zelle ein

Mit der Range-Eigenschaft können Sie einen Wert in eine Zelle oder einen Zellbereich eingeben. Lassen Sie uns anhand eines einfachen Beispiels verstehen, wie es funktioniert:

 Range("A1").Value = "Exceladvisor"

Im obigen Beispiel haben Sie A1 als Bereich angegeben und anschließend „ .Value “ hinzugefügt, was VBA anweist, auf die Value-Eigenschaft der Zelle zuzugreifen.

Als nächstes müssen Sie das Gleichheitszeichen und dann den Wert eingeben, den Sie eingeben möchten (Sie müssen doppelte Anführungszeichen verwenden, wenn Sie einen Textwert eingeben). Für eine Zahl würde der Code so aussehen:

 Range("A1").Value = 9988

Und wenn Sie einen Wert in einen Bereich von Zellen eingeben möchten, also in mehrere Zellen, dann müssen Sie nur diesen Bereich angeben.

 Range("A1:A5").Value = "Exceladvisor"

Und hier ist der Code, wenn Sie sich auf den nicht kontinuierlichen Bereich beziehen.

 Range("A1:A5 , E2:E3").Value = "Exceladvisor"

3. Kopieren Sie eine Zelle/einen Bereich und fügen Sie sie ein

Mit der Range-Eigenschaft können Sie die Methode „.Copy“ verwenden, um eine Zelle zu kopieren und sie dann in eine Zielzelle einzufügen. Nehmen wir an, Sie müssen Zelle A5 kopieren. Der entsprechende Code wäre:

 Range("A5").Copy

Wenn Sie diesen Code ausführen, kopiert er nur Zelle A5, aber der nächste Schritt besteht darin, diese kopierte Zelle in eine Zielzelle einzufügen. Dazu müssen Sie das Schlüsselwort „destination“ dahinter und anschließend die Zelle hinzufügen, in die Sie es einfügen möchten. Wenn Sie also Zelle A1 kopieren und dann in Zelle E5 einfügen möchten, wäre der Code:

 Range("A1").Copy Destination:=Range("E5")

Wenn Sie es mit einem Bereich mit mehreren Zellen zu tun haben, würde der Code ähnlich wie folgt aussehen:

 Range("A1:A5").Copy Destination:=Range("E5:E9")

Wenn Sie einen Zellbereich kopiert und dann eine Zelle als Zielbereich angegeben haben, kopiert VBA den gesamten kopierten Bereich aus der Zelle, die Sie als Ziel angegeben haben.

 Range("A1:A5").Copy Destination:=Range("B1")

Wenn Sie den obigen Code ausführen, kopiert VBA den Bereich A1:A5 und fügt ihn in B1:B5 ein, obwohl Sie nur B1 als Zielbereich angegeben haben.

Tipp: Genau wie die Methode „.Copy“ können Sie die Methode „.Cut“ verwenden, um eine Zelle auszuschneiden und sie dann einfach an einem Zielort einzufügen.

4. Verwenden Sie die Font-Eigenschaft mit der Range-Eigenschaft

Mit der Range-Eigenschaft können Sie auf die Schriftarteigenschaft einer Zelle zugreifen und so alle Schriftarteinstellungen ändern. Es gibt insgesamt 18 verschiedene Eigenschaften für die Schriftart, auf die Sie zugreifen können. Nehmen wir an, Sie möchten den Text in Zelle A1 fett formatieren. Der Code wäre:

 Range("A1").Font.Bold = True

Dieser Code weist VBA an, auf die BOLD-Eigenschaft der Schriftart im A1-Bereich zuzugreifen, und Sie haben diese Eigenschaft auf TRUE gesetzt. Nehmen wir nun an, Sie möchten Zelle A1 durchstreichen . Dieser Timecode wäre:

Wie gesagt, es gibt insgesamt 18 verschiedene Eigenschaften, die Sie nutzen können. Schauen Sie sich also unbedingt alle an, um herauszufinden, welche für Sie nützlich ist.

5. Löschen Sie die Formatierung einer Zelle

Mit der Methode „.ClearFormats“ können Sie nur das Format einer Zelle oder eines Zellbereichs löschen. Alles, was Sie tun müssen, ist „.ClearFormat“ hinzuzufügen, nachdem Sie den Bereich angegeben haben, wie unten:

 Range("A1").ClearFormats

Wenn Sie den obigen Code ausführen, werden alle Formatierungen aus Zelle A1 gelöscht. Wenn Sie dies für einen gesamten Bereich tun möchten, wissen Sie, was zu tun ist, oder?

 Range("A1:A5").ClearFormats

Jetzt entfernt der obige Code einfach die Formatierung aus dem Bereich A1 bis A5.

Zelleneigenschaft

Zusätzlich zur RANGE-Eigenschaft können Sie die Eigenschaft „Cells“ verwenden, um auf eine Zelle oder einen Zellbereich in Ihrem Arbeitsblatt zu verweisen. In der Zelleneigenschaft müssen Sie statt der Zellreferenz die Spaltennummer und Zeilennummer der Zelle eingeben.

 expression.Cells(Row_Number, Column_Number)

Hier ist der Ausdruck ein VBA-Objekt und Row_Number ist die Zeilennummer der Zelle und Column_Number ist die Spalte der Zelle. Wenn Sie also auf Zelle A5 verweisen möchten, können Sie den folgenden Code verwenden:

 Cells(5,1)

Dieser Code weist VBA nun an, auf die Zelle zu verweisen, die sich in Zeile Nummer fünf und Spalte Nummer eins befindet. Wie aus der Syntax hervorgeht, müssen Sie als Adresse die Spaltennummer eingeben. In Wirklichkeit können Sie jedoch auch das Spaltenalphabet verwenden, indem Sie es einfach in doppelte Anführungszeichen setzen.

Der folgende Code bezieht sich auch auf Zelle A5:

 Cells(5,"A")

Und um es in VBA auszuwählen, fügen Sie am Ende einfach „.Select“ hinzu.

 Cells(5,1).Select

Der obige Code wählt Zelle A5 aus, die sich in der 5. Zeile und ersten Spalte des Arbeitsblatts befindet.

OFFSET-Eigenschaft

Wenn Sie in VBA gut mit Bereichen arbeiten möchten, müssen Sie wissen, wie Sie die OFFSET-Eigenschaft verwenden. Es ist nützlich, sich auf eine Zelle zu beziehen, die eine bestimmte Anzahl Zeilen und Spalten von einer anderen Zelle entfernt ist.

Angenommen, Ihre aktive Zelle ist gerade B5 und Sie möchten zu der Zelle navigieren, die 3 Spalten rechts und 1 Zeile tiefer von B5 liegt. Sie können diese UMSCHALTTASTE ausführen. Nachfolgend finden Sie die Syntax, die Sie für OFFSET verwenden sollten:

 expression.Offset (RowOffset, ColumnOffset)

• RowOffset: In diesem Argument müssen Sie eine Zahl angeben, die VBA angibt, wie viele Zeilen Sie durchlaufen möchten. Eine positive Zahl definiert eine Linie nach unten und eine negative Zahl definiert eine Linie nach oben.
• ColumnOffset : In diesem Argument müssen Sie eine Zahl angeben, die VBA angibt, durch wie viele Spalten Sie navigieren möchten. Eine positive Zahl definiert eine rechte Spalte und eine negative Zahl definiert eine linke.

Schreiben wir zum Beispiel einen einfachen Code, den wir oben besprochen haben.

1. Zunächst müssen Sie den Bereich definieren, aus dem Sie navigieren möchten, und dazu den folgenden Code eingeben:
   
2. Geben Sie anschließend „.Offset“ ein und geben Sie öffnende Klammern ein, wie unten:
   
3. Als nächstes müssen Sie die Zeilennummer und dann die Spaltennummer eingeben, zu der Sie navigieren möchten.
   
4. Am Ende müssen Sie „.Select“ hinzufügen, um VBA anzuweisen, die Zelle auszuwählen, zu der Sie navigieren möchten.
   

Wenn Sie diesen Code ausführen, wird die Zelle ausgewählt, die eine Zeile tiefer und drei Spalten rechts von Zelle B5 liegt.

Ändern Sie die Größe eines Bereichs mit OFFSET

Mit OFFSET können Sie nicht nur zu einer Zelle navigieren, sondern auch die Größe des Bereichs weiter ändern. Fahren wir mit dem obigen Beispiel fort.

 Range("B5").Offset(1, 3).Select

Der obige Code führt Sie zu Zelle E6. Nehmen wir nun an, Sie müssen den Zellbereich auswählen, der aus den fünf Spalten und drei Zeilen von E6 besteht. Was Sie also tun müssen, ist, nach der Verwendung von OFFSET die Größenänderungseigenschaft zu verwenden, indem Sie „.Resize“ hinzufügen.

 Range("B5").Offset(1, 3).Resize

Jetzt müssen Sie die Zeilengröße und Spaltengröße eingeben. Geben Sie eine führende Klammer ein und geben Sie die Zahl ein, um die Zeilengröße festzulegen, und dann eine Zahl, um die Spaltengröße festzulegen.

 Range("B5").Offset(1, 3).Resize(3,5)

Fügen Sie am Ende „.Select“ hinzu, um VBA anzuweisen, den Bereich auszuwählen. Wenn Sie diesen Code ausführen, wird der Bereich ausgewählt.

 Range("B5").Offset(1, 3).Resize(3, 5).Select

Wenn Sie diesen Code ausführen, wird der Bereich E6 bis I8 ausgewählt.

 Range("A1").Font.Strikethrough = True