3. Über SET-Definition deklariertem Objektvariable : Sub Ansprache()
1.4 UMGANG MIT ZELLEN UND BEREICHEN „RANGE“ / „CELLS“
Eine Zelle und ein Zellenbereich in VBA-Programmierung erfasst am größten Teil der Programmierung gegenüber Arbeitsmappen „Workbooks“ oder Tabellenblätter „Worksheets“.
Denn, wenn eine Zelle oder ein Bereich markiert ist, gibt es viele Möglichkeit sie zu formatieren. Das Markieren ist ein Selektion beziehungsweise das Ansprechen einer Zelle oder eines Bereiches.
Der Zugriff auf Zellen oder Zellbereiche ist nicht trivial, weil VBA mit zahlreichen, inhaltlich ähnlichen Begriffen und Objekten arbeitet. Wie man so schön sagt: „Viele Wege führen nach Rom.“ Viele Beispiele, die wir in diesem Buch gesehen und sehen werden, sind auch unterschiedlichen Wegen realisierbar.
Eine Zelle / ein Bereich wird selektiert zum:
• Bearbeiten
• Kopieren
• Ausschneiden
• Einfügen
• Umbenennen
• Formatieren nach
○ Ausrichtung
○ Schriftgestaltung
○ Rahmengestaltung
○ Farbfüllung
○ Schutz
○ Ein- und Ausblenden der Spalten / Zeile
○ Automatische Spaltenbreite und Zeilenhöhe
• Sortieren
• Auf null setzen
• Formel einfügen
• Druckbereich setzen
Die Zellen werden mit folgenden Möglichkeiten angesprochen:
• mit dem Range-Objekt
• mit dem Cells-Objekt
• ein Mix aus dem Beiden.
Das Range-Objekt wird mit der üblichen Zellenbezeichnung und das Cells-Objekt mit der Adressierung der Zellen- und Spaltennummern, angesprochen.
Beim Range-Objekt wird eine Zelle innerhalb zwei Klammern und Hochkommas geschrieben.
In der Reihenfolge werden erst die Spalte dann die Zeile geschrieben.
Beispielsweise („A1“) => Die Buchstabe „A“ gibt die Spalte an. Der Ziffer „1“ gibt die Zeile an.
Beim Cells-Objekt wird eine Zelle innerhalb offenem und geschlossenem Klammern geschrieben und mit einem Komma getrennt. In der Reihenfolge werden erst die Zeile dann die Spalte geschrieben.
Beispielsweise (3,4) => Die erste Ziffer (3) gibt die Zeile und die zweite Ziffer (4) gibt die Spalte-D an. Diese Schreibweise eignet sich besonders mit den Variablen zu arbeiten.
Range Cells
(“A1”) (1,1)
(“D3”) (3,4)
(“Z13”) (13,26)
Tabelle 3: Range / Cells
Bevor wir mit den Zellen und Bereichen anfangen, schauen wir kurz wie der Cursor in die Zelle positionieren werden kann.
Die Positionierung wird entweder mit den Methoden „Select“ oder „Activate“ gemacht.
Select-Methode markiert eine Zelle oder einen Zellenbereich.
Activate-Methode aktiviert eine einzelne Zelle, die sich innerhalb der aktuellen Markierung befindet.
Im folgendem Beispiel in der Tabelle „VBA“ ist der Bereich mit Range(„A1:C3“) markiert und mit Cells(1,1) die Zelle „A1“ aktiviert. Also der Cursor befindet sich in der Zelle „A1“.
Sub BspActivate_Select() Worksheets(„VBA“).Activate Range(“A1:C3”).Select Cells(1,1).Activate End Sub
Die folgenden Beispiele stellt die Schriftart in der Zelle A1 der Tabelle „VBA“ die blaue Farbe ein.
Nun Beispiele mit und ohne Select:
Sub MitActivate_Select() Worksheets(„VBA“).Activate Range(“A1”).Select
Selection.Font.Color = vbBlue End Sub
Jetzt etwas schneller in der Ausführung und weniger Tipparbeit:
Sub OhneActivate_Select()
Worksheets(“VBA”).Range(“A1”).Font.Color = vbBlue End Sub
Wenn wir in die Zelle etwas eintragen möchten, dann können wir OHNE Select bzw.
Activate-Methode arbeiten. Dadurch wird unsere Prozedur um einiges kürzer und schneller.
Hierzu ein Beispiel:
Sub Eintrag() Text = „Harun“
‚
Range(„C1“).Activate Range(„C1“).Value = Text ActiveCell.Value = Text Cells(4, 3).Activate Cells(4, 3).Value = Text ActiveCell.Value = Text
‚
Range(„B1“).Select ActiveCell.Value = Text Cells(3, 3).Select ActiveCell.Value = Text
‚
Range(„A1“) = Text Cells(2, 3) = Text End Sub
1.4.1 KOMBINATIONEN VON CELLS UND RANGE
Es ist aber auch beide Schreibvarianten miteinander zu kombinieren.
Die Kombination mit den Cells und Range wird meistens in den Schleifen verwendet. Denn die Zellbereiche mittels berechneter Indizes besser adressierbar sind.
Das untere Beispiel füllt folgende Zellen mit errechneten Werten:
Anstelle jede Zeile zu erklären, bitte eintippen und mit „F8“ step-to-step laufenlassen. Sub Kombination_Cells_Range()
Dim i As Integer For i = 2 To 4
Worksheets(“Tabelle1”).Range(Cells(2, 2), Cells(i + 2, i)).Value = 20 * i Next
End Sub
Oder wir markieren einen bestimmten Bereich:
Sub Bereich_markieren()
Worksheets(“Tabelle1”).Range(Cells(1, 2), Cells(1, 4)).Select ‚markiert B1:D1 End Sub
Oder mit Variablen:
Sub Kombination_Range_Cells() Dim Zeile, Spalte As Integer
Worksheets(„Tabelle1“).Activate Zeile=InputBox(„Anzahl Zeilen“) Spalte=InputBox(„Anzahl Spalte“)
Range(Cells(3, 3), Cells(2+Zeilen, 2+Spalten)).Activate End Sub
1.4.2 LETZTE BESCHRIEBENE ZELLE ERMITTELN
Die letzte beschriebene Zelle in der Tabelle mit folgenden Eigenschaften ermittelt:
Rows.Count letzte beschriebene Zelle in der Spalte und Columns.Count letzte beschriebene Zelle in der Zeile.
Abbildung 9: Letzte Zeile
Sub Letzte_beschriebene_Zelle_Spalte() ‚von oben nach unten gezählt Dim strLetzteZeile As String
strLetzteZeile = Cells(Rows.Count, 1).End(xlUp).Row End Sub
ÖDas Ergebnis ist 4. “A4” ist zuletzt beschrieben!
Unser Beispiel dazu sieht wie folgt aus:
Sub Letzte_beschriebene_Zelle_Zeile() ‚ von links nach rechts gezählt Dim strLetzteSpalte As String
strLetzteSpalte = Cells(1, Columns.Count).End(xlToLeft).Column End Sub
ÖDas Ergebnis ist 2. “B1” ist zuletzt beschrieben! Das Ergebnis hierzu ist keine Buchstabe, sondern ein Zahl!
1.4.3 IN DIE LETZTE ZU BESCHREIBENDE ZELLE IN DER SPALTE EINTRAGEN Das kommt in der Praxis auch oft vor. Unterste beschreibbare Zelle wird wie folgt eingetragen:
Sub Letzte_zu_beschreibende_Zelle_befüllen_2() Dim strLetzteZeile As String
strLetzteZeile = Cells(Rows.Count, 3).End(xlUp).Row
Range(„C“ & strLetzteZeile).Offset(1, 0) = „Ich werde eingetragen“
End Sub
Abbildung 10: Letzte Spalte
1.4.4 AKTIVE ZEILE / SPALTE / ADRESSE ERMITTELN
Nun, ermitteln wir die aktive Zeile, aktive Spalte und die aktive Position des Cursors.
ActiveCell.Row gibt uns die Zeile als String an ActiveCell.Column gibt uns die Spalte als String an
ActiveCell.Address gibt uns die Adresse in Range-Format mit $-Zeichen davor.
Sub Aktive_Infos()
Zeile = ActiveCell.Row Spalte = ActiveCell.Column Adress = ActiveCell.Address
Adress_2 = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) MsgBox „Der Cursor befindet sich in „ & vbLf & _
„Zeile „ & Zeile & vbLf & _
„Spalte „ & Spalte & vbLf & _
„Adresse_1 „ & Adress & vbLf & _
„Adresse_2 „ & Adress_2 End Sub
Abbildung 11: Zelleninformationen
Um das Ergebnis ohne $-Zeichen zu bekommen, setzen wir die Attributen RowAbsolte:=False bzw.
ColumnAbsolute:=False ein.
In unserem Beispiel oben sah so aus:
ÖAdress_2 = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) Genug mit Zellen. Jetzt schauen wir die Tabellen an.
1.4.5 AKTIVE TABELLE ERMITTELN
Folgende Eigenschaften mit der Anweisung „.Name“ ermitteln die Namen des aktiven Tabellenblattes:
• ActiveSheet.Name
• ActiveCell.Parent.Name Sub Aktiven_Tabellenname_ermitteln()
Dim strTabName_1, strTabName_2 As String strTabName_1 = ActiveSheet.Name ‚ Variante 1 strTabName_2 = ActiveCell.Parent.Name ‚ Variante 2 MsgBox strTabName_1 & vbLf & strTabName_2 End Sub
1.4.6 AKTIVE DATEI ERMITTELN
Jetzt kommen wir zur Ermittlung der aktiven Datei. Um der Name der aktiven Datei zu ermitteln, verwenden wir folgende Eigenschaft mit der „.Name“-Anweisung:
• ActiveWorkbook.Name
• ActiveSheet.Parent.Name Sub Aktive_Dateiname_ermitteln()
Dim strDateiName_1, strDateiName_2 As String strDateiName_1 = ActiveWorkbook.Name ‚ Variante 1 strDateiName_2 = ActiveSheet.Parent.Name ‚ Variante 2 MsgBox strDateiName_1 & vbLf & strDateiName_2 End Sub
1.4.7 AKTIVES FENSTER ERMITTELN
Wenn wir mehrere Excel-Dateien gleichzeitig geöffnet haben, diese Dateien sind dann praktisch „digital“ gestapelt. Wir können mit der „ActiveWindow“-Eigenschaft ermitteln, welches Fenster beziehungsweise welche Excel-Datei ganz oben ist. Das erreichen wir mit der „ActiveWindow“-Eigenschaft angehängt die „.Caption“-Eigenschaft.
Sub Aktives_Fenster_ermitteln() Dim strFenster As String
strFenster = ActiveWindow.Caption MsgBox strFenster
End Sub
Hier haben wir einige Befehle auf einem Haufen verwendet. Diese haben Funktionen wie folgt:
Befehl Aktivität
ActiveCell.Address Ermittelt die Adresse der aktiven Zelle ActiveCell.Row Ermittelt den Zeilennummer der aktiven Zelle ActiveCell.Column Ermittelt den Spaltennummer der aktiven Zelle ActiveCell.Parent.Name Ermittelt den Namen der Tabelle mit der aktiven Zelle ActiveSheet.Parent.Name Ermittelt den Namen der Arbeitsmappe
ActiveSheet.Name Gibt die Name des aktiven Tabellenblattes
ActiveWindow Gibt ein Window-Objekt zurück, das für das aktive Fenster steht ActiveWorkbook.Name Gibt die Name der aktiven Arbeitsmappe
Tabelle 4: Wichtige Active-Eigenschaften
1.4.8 SUCHEN UND ERSETZEN DER ZELLENINHALTEN
Das Suchen und Ersetzen nach bestimmten Inhalten in der Tabelle werden mit den folgenden Methoden realisiert:
• .Find Duchsucht den Bereich nach bestimmten Begriff.
• .Replace Duchsucht den Bereich und ersetzt mit anderem Begriff.
Als erstes schauen wir nach dem „Range.Find“ „Suchen“!
Wir lassen in bestimmten Bereichen nach einem Wert, hier „Harun“ suchen und davon die Adresse ausgeben.
Abbildung 12: Unser Suchbegriff
Die Listing dazu:
Sub Suchen_Adresse_ausgeben() Dim strAdresse As String
strAdresse = Range(“A1:C5”).Find(“Harun”).Address MsgBox strAdresse
End Sub
Und das Ergebnis:
Abbildung 13: Suchergebnis
Jetzt lassen wir mehrere gleiche Begriffe suchen und die Adresse dazu ausgeben:
Abbildung 14: Unsere Suchbegriffe
Die Listing dazu:
Sub Suchen_Adresse_ausgeben_2() Dim rgBereich As Range
Dim strAdresse, strBereiche As String Set rgBereich = Cells.Find(“Auto”) If Not rgBereich Is Nothing Then strAdresse = rgBereich.Address Do
Set rgBereich = Cells.FindNext(rgBereich)
strBereiche = rgBereich.Address & vbLf & strBereiche Loop Until (rgBereich.Address = strAdresse)
MsgBox strBereiche End If
End Sub
Abbildung 15: Suchergebnis
Nun lassen wir nach den Werten suchen, die dann dessen Zellen mit blauer Farbe befüllen.
Unsere Tabelle dazu sieht wie folgt aus:
Abbildung 16: Werte Suchmaske
Gesucht werden alle Zellen im bestimmten Bereich mit For Each nach nummerischen Werten die größer 20 sind. Beim Finden wird die jeweilige Zelle mit blauer Farbe befüllt.
Das Suchen nach Zellinhalten, die in mehreren Zellen auftreten können, ist am einfachsten über For Each-Schleife am einfachsten. Dazu setzen wir die „>“ Größer- / „<“ Kleiner- oder
„=“ das Gleichheitszeichen-Operator ein.
Sub Suchen_mit_Farbe_befuellen() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If IsNumeric(rngZelle.Value) Then
If rngZelle.Value > 20 Then rngZelle.Interior.ColorIndex = 5 End If
End If Next End Sub
Abbildung 17: Farbbefüllte Zellen
1.4.9 ERSETZEN DER ZELLENINHALTEN
Aber jetzt sollten wir ein paar Beispiele mit dem Ersetzen machen. Das folgende Beispiel soll unsere Zellen, die „aut“ beinhalten mit dem „Mercedes Benz“ ersetzen. Auch hier setzen wir For Each-Schleife ein. Zusätzlich den „Like“-Operator ein, um Inhalte zu vergleichen.
Für die Suche nach Zelleninhalten benutzen wir die „Value“-Eigenschaft. Die Inhalte der Zellen können unterschiedlich sein. Also, sie können aus numerisch, alphanumerisch oder aus Formeln bestehen.
Sub Suchen_Ersetzen_1() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If LCase(rngZelle.Value) Like “aut*” Then
rngZelle.Value = “Mercedes Benz”
End If Next End Sub
Oder auch mit Range.Replace-Methode:
Sub Suchen_Ersetzen_1()
Worksheets(“Tabelle1”).Range(“A1:C10”).Replace _ What:=”*aut*”, Replacement:=”Mercedes Benz”, _ SearchOrder:=xlByColumns, MatchCase:=True End Sub
Abbildung 18: Ergebnis Suchen_Ersetzen_1
Nun, alle Zellen, die *ben* beinhalten, sollen mit dem Doppelrahmen, mit der Farbe „27“
befüllt werden. Auch hier setzen wir For Each-Schleife ein. Zusätzlich den „Like“-Operator ein, um Inhalte zu vergleichen.
Sub Suche_Ersetzen_2() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If LCase(rngZelle.Value) Like “*ben*” Then
rngZelle.Borders.LineStyle = xlDouble End If
Next End Sub
Abbildung 19: Ergebnis Suchen_Ersetzen_2
Alle Zellen, die mit blauer Farbe befüllt sind, werden mit punktiertem Rahmen, mit gelber Farbe und als Wert „Daimler“ ersetzt.
Sub Suche_Ersetzen_3() Dim rngZelle As Range
For Each rngZelle In Range(“A1:C10”) If Zelle.Interior.ColorIndex = 5 Then
With Zelle
.Borders.LineStyle = xlDot .Interior.ColorIndex = 27 .Value = “Daimler”
End With End If Next End Sub
Abbildung 20: Ergebnis Suchen_Ersetzen_3
1.4.10 LÖSCHEN VON ZELLINHALTEN UND ZEILEN
Zellen können Texte, Zahlen, Formeln oder auch Kommentare beinhalten. Wir können sie mit einer Rahmenart, mit bestimmter Füllfarbe oder auch mit einem Muster formatieren.
Alles, was zu einer Zelle eingegeben wurden, können einzeln oder auf einem Mal mit deren Formatierungen gelöscht werden.
Dazu sind folgende Methoden zuständig:
Löschfunktion Bedeutung
Clear Alle Einträge und Formatierungen eines Bereiches löschen ClearContents Nur Einträge eines Bereiches löschen
ClearComments Nur Kommentare eines Bereiches löschen ClearFormats Nur Format eines Bereiches löschen
Delete Zellen löschen
Tabelle 5: Löschmethoden
Sub Formatierung_löschen()
Worksheets(“Tabelle1”).Range(“A1”).Clear
Worksheets(“Tabelle1”).Range(“A2:C2”).ClearContents Worksheets(“Tabelle1”).Range(“A3”).ClearFormats
Worksheets(“Tabelle1”).Range(Cells(4, 1), Cells(4, 2)).ClearComments Worksheets(“Tabelle1”).Range(“A5”).Delete
ActiveCell.EntireRow.Clear ‘Spalte löschen
ActiveCell.EntireColumn.ClearContents ‘Zeile Löschen End Sub
Wenn wir gesamten Einträge der Tabelle löschen möchten, dann wie folgt:
Sub Zellinhalte_des_gesamten_Tabellenblattes_löschen()
Cells.ClearContents ‘ClearContents löscht die Inhalte und Formeln aus einem Bereich End Sub
Nun, Löschung mit allem, wie folgt:
Sub Gesamtes_Tabellenblatt_löschen() Cells.Delete
End Sub