Bereiche und Zellen in VBA
Excel-Tabellen speichern Daten in Zellen. Zellen sind in Zeilen und Spalten angeordnet. Jede Zelle kann durch den Schnittpunkt ihrer Zeile und Spalte identifiziert werden (Bsp. B3 oder R3C2).
Ein Excel-Bereich bezieht sich auf eine oder mehrere Zellen (z. B. A3:B4)
Zellenadresse
A1-Notation
In der A1-Notation wird auf eine Zelle durch ihren Spaltenbuchstaben (von A bis XFD) gefolgt von ihrer Zeilennummer (von 1 bis 1.048.576) verwiesen.
In VBA können Sie mit dem auf jede Zelle verweisen Bereichsobjekt.
| 123456789 | ' Siehe Zelle B4 auf dem derzeit aktiven BlattMsgBox-Bereich("B4")' Siehe Zelle B4 auf dem Blatt mit dem Namen 'Daten'MsgBox Worksheets("Daten").Range("B4")' Siehe Zelle B4 auf dem Blatt mit dem Namen 'Daten' in einer anderen OPEN-Arbeitsmappe“ mit dem Namen „Meine Daten“MsgBox Workbooks("Meine Daten").Worksheets("Daten").Range("B4") |
R1C1-Notation
In der R1C1-Notation wird eine Zelle mit R gefolgt von der Zeilennummer, dann dem Buchstaben "C" gefolgt von der Spaltennummer bezeichnet. zB B4 in R1C1-Notation wird von R4C2 referenziert. In VBA verwenden Sie die Zellen-Objekt um die R1C1-Notation zu verwenden:
| 12 | ' Siehe Zelle R[6]C[4] d.h. D6Zellen(6, 4) = "D6" |
Zellbereich
A1-Notation
Um auf mehr als eine Zelle zu verweisen, verwenden Sie ein „:“ zwischen der Startzellenadresse und der letzten Zelladresse. Das Folgende bezieht sich auf alle Zellen von A1 bis D10:
| 1 | Bereich("A1:D10") |
R1C1-Notation
Um auf mehr als eine Zelle zu verweisen, verwenden Sie ein „“, zwischen der Startzellenadresse und der letzten Zelladresse. Das Folgende bezieht sich auf alle Zellen von A1 bis D10:
| 1 | Bereich (Zellen(1, 1), Zellen(10, 4)) |
In Zellen schreiben
Um Werte in eine Zelle oder eine zusammenhängende Gruppe von Zellen zu schreiben, beziehen Sie sich einfach auf den Bereich, setzen Sie ein =-Zeichen und schreiben Sie dann den zu speichernden Wert:
| 12345678910 | ' F5 in Zelle mit Adresse F6 speichernBereich("F6") = "F6"' E6 in Zelle mit Adresse R[6]C[5] speichern, d.h. E6Zellen(6, 5) = "E6"' Speichere A1:D10 im Bereich A1:D10Bereich("A1:D10") = "A1:D10"' oderRange(Cells(1, 1), Cells(10, 4)) = "A1:D10" |
Aus Zellen lesen
Um Werte aus Zellen zu lesen, verweisen Sie einfach auf die Variable, um die Werte zu speichern, setzen Sie ein =-Zeichen und beziehen Sie sich dann auf den zu lesenden Bereich:
| 1234567891011 | Dimmwert1Dimmwert2' Aus Zelle F6 lesenval1 = Bereich("F6")' Aus Zelle E6 lesenval2 = Zellen(6, 5)MsgBox val1Msgbox val2 |
Hinweis: Um Werte aus einem Zellbereich zu speichern, müssen Sie anstelle einer einfachen Variablen ein Array verwenden.
Nicht zusammenhängende Zellen
Um auf nicht zusammenhängende Zellen zu verweisen, verwenden Sie ein Komma zwischen den Zellenadressen:
| 123456 | ' 10 in den Zellen A1, A3 und A5 speichernBereich("A1,A3,A5") = 10' 10 in den Zellen A1:A3 und D1:D3) speichernBereich("A1:A3, D1:D3") = 10 |
Kreuzung von Zellen
Um auf nicht zusammenhängende Zellen zu verweisen, verwenden Sie ein Leerzeichen zwischen den Zellenadressen:
| 123 | ' 'Col D' in D1:D10 speichern', was zwischen A1:D10 und D1:F10 gemeinsam istBereich("A1:D10 D1:G10") = "Spalte D" |
Versatz von einer Zelle oder einem Bereich
Mit der Offset-Funktion können Sie die Referenz aus einem bestimmten Bereich (Zelle oder Zellengruppe) um die angegebene Anzahl_Zeilen und Anzahl_Spalten verschieben.
Offset-Syntax
Range.Offset(number_of_rows, number_of_columns)
Versatz von einer Zelle
| 12345678910111213141516 | ' OFFSET von einer Zelle A1'Beziehen Sie sich auf die Zelle selbst' 0 Zeilen und 0 Spalten verschiebenBereich("A1").Offset(0, 0) = "A1"' 1 Zeilen und 0 Spalten verschiebenBereich("A1").Offset(1, 0) = "A2"' 0 Zeilen und 1 Spalten verschiebenBereich("A1").Offset(0, 1) = "B1"' 1 Zeile und 1 Spalte verschiebenBereich("A1").Offset(1, 1) = "B2"' Verschiebe 10 Zeilen und 5 SpaltenBereich("A1").Offset(10, 5) = "F11" |
Versatz von einem Bereich
| 123 | ' Referenz auf Bereich A1:D4 um 4 Zeilen und 4 Spalten verschieben'Neue Referenz ist E5:H8Bereich("A1:D4").Offset(4,4) = "E5:H8" |
Referenz auf einen Bereich setzen
So weisen Sie einer Bereichsvariablen einen Bereich zu: Deklarieren Sie eine Variable vom Typ Bereich und verwenden Sie dann den Befehl Set, um sie auf einen Bereich zu setzen. Bitte beachten Sie, dass Sie den SET-Befehl verwenden müssen, da RANGE ein Objekt ist:
| 12345678 | ' Eine Range-Variable deklarierenDim myRange als Range' Setzen Sie die Variable auf den Bereich A1:D4Set myRange = Range("A1:D4")' druckt $A$1:$D$4MsgBox myRange.Address |
Größe eines Bereichs ändern
Die Resize-Methode des Range-Objekts ändert die Dimension des Referenzbereichs:
| 1234567 | Dim myRange als Range'GrößenänderungsbereichSet myRange = Range("A1:F4")' druckt $A$1:$E$10Debug.Print myRange.Resize(10, 5).Adresse |
Die obere linke Zelle des Größenänderungsbereichs entspricht der oberen linken Zelle des ursprünglichen Bereichs
Größenänderungssyntax
Range.Resize(number_of_rows, number_of_columns)
OFFSET vs. Größenänderung
Offset ändert die Abmessungen des Bereichs nicht, sondern verschiebt ihn um die angegebene Anzahl von Zeilen und Spalten. Die Größenänderung ändert nicht die Position des ursprünglichen Bereichs, sondern ändert die Abmessungen auf die angegebene Anzahl von Zeilen und Spalten.
Alle Zellen im Blatt
Das Cells-Objekt verweist auf alle Zellen im Arbeitsblatt (1048576 Zeilen und 16384 Spalten).
| 12 | 'Alle Zellen in Arbeitsblättern löschenZellen.Löschen |
GebrauchtSortiment
Die UsedRange-Eigenschaft gibt Ihnen den rechteckigen Bereich von der oberen linken verwendeten Zelle bis zur rechten unteren verwendeten Zelle des aktiven Arbeitsblatts.
| 1234567 | Dim ws als ArbeitsblattSet ws = ActiveSheet' $B$2:$L$14 wenn L2 die erste Zelle mit einem beliebigen Wert ist' und L14 ist die letzte Zelle mit einem beliebigen Wert auf dem'aktives BlattDebug.Print ws.UsedRange.Address |
AktuelleRegion
Die CurrentRegion-Eigenschaft gibt Ihnen den zusammenhängenden rechteckigen Bereich von der oberen linken Zelle bis zur rechten unteren verwendeten Zelle, die die referenzierte Zelle/den referenzierten Bereich enthält.
| 1234567891011 | Dim myRange als RangeSet myRange = Range("D4:F6")' druckt $B$2:$L$14' Wenn es einen gefüllten Pfad von D4:F16 nach B2 UND L14 gibtDebug.Print myRange.CurrentRegion.Address' Sie können sich auch auf eine einzelne Startzelle beziehenSet myRange = Range("D4") ' Druckt $B$2:$L$14 |
Bereichseigenschaften
Sie können Adresse, Zeilen-/Spaltennummer einer Zelle und Anzahl der Zeilen/Spalten in einem Bereich wie unten angegeben abrufen:
| 123456789101112131415161718192021 | Dim myRange als RangeSet myRange = Range("A1:F10")' druckt $A$1:$F$10Debug.Print myRange.AddressSet myRange = Range("F10")' Druckt 10 für Zeile 10Debug.Print myRange.Row' Druckt 6 für Spalte FDebug.Print myRange.ColumnSet myRange = Range("E1:F5")' Gibt 5 für die Anzahl der Zeilen im Bereich ausDebug.Print myRange.Rows.Count' Gibt 2 für die Anzahl der Spalten im Bereich ausDebug.Print myRange.Columns.Count |
Letzte Zelle im Tabellenblatt
Sie können verwenden Zeilen.Anzahl und Spalten.Anzahl Eigenschaften mit Zellen Objekt, um die letzte Zelle auf dem Blatt zu erhalten:
| 1234567891011 | 'Drucken Sie die letzte Zeilennummer'Drucke 1048576Debug.Print "Zeilen im Blatt: " & Rows.Count' Drucken Sie die letzte Spaltennummer'Drucke 16384Debug.Print "Spalten im Blatt: " & Columns.Count' Adresse der letzten Zelle ausgeben' druckt $XFD$1048576Debug.Print "Adresse der letzten Zelle im Tabellenblatt: " & Cells(Rows.Count, Columns.Count) |
Zuletzt verwendete Zeilennummer in einer Spalte
Die END-Eigenschaft führt Sie zur letzten Zelle im Bereich und End(xlUp) führt Sie zur ersten verwendeten Zelle dieser Zelle.
| 123 | Dim lastRow As LonglastRow = Cells(Rows.Count, "A").End(xlUp).Row |
Zuletzt verwendete Spaltennummer in einer Zeile
| 123 | Dim lastCol As LonglastCol = Cells(1, Columns.Count).End(xlToLeft).Column |
Die END-Eigenschaft führt Sie zur letzten Zelle im Bereich, und End(xlToLeft) führt Sie nach links zur ersten verwendeten Zelle dieser Zelle.
Sie können auch die Eigenschaften xlDown und xlToRight verwenden, um zur ersten unteren oder rechten verwendeten Zelle der aktuellen Zelle zu navigieren.
Zelleneigenschaften
Allgemeine Eigenschaften
Hier ist Code zum Anzeigen häufig verwendeter Zelleigenschaften
| 12345678910111213141516171819202122 | Zelle als Bereich dimmenZelle setzen = Range("A1")Zelle.AktivierenDebug.Print cell.Address'$$1 ausdruckenDebug.Print cell.Value'Druckt 456' Die AnschriftDebug.Print cell.Formula' Druckt =SUMME(C2:C3)' KommentarDebug.Print cell.Comment.Text' StilDebug.Print cell.Style'ZellenformatDebug.Print cell.DisplayFormat.NumberFormat |
Zellenschriftart
Das Cell.Font-Objekt enthält Eigenschaften des Cell Font:
| 1234567891011121314151617181920 | Zelle als Bereich dimmenZelle setzen = Range("A1")' Normal, Kursiv, Fett und Fett Kursivcell.Font.FontStyle = "Fett kursiv"' Gleich wiecell.Font.Bold = Truecell.Font.Italic = True' Schriftart auf Courier setzencell.Font.FontStyle = "Kurier"'Schriftfarbe einstellencell.Font.Color = vbBlue' odercell.Font.Color = RGB(255, 0, 0)'Schriftgröße einstellencell.Font.Size = 20 |
Kopieren und Einfügen
Alles einfügen
Bereiche/Zellen können von einem Ort zum anderen kopiert und eingefügt werden. Der folgende Code kopiert alle Eigenschaften des Quellbereichs in den Zielbereich (entspricht STRG-C und STRG-V)
| 1234567 | 'Einfache KopieBereich("A1:D20").KopierenWorksheets("Sheet2").Range("B10").Paste'oder' Vom aktuellen Blatt in das Blatt mit dem Namen 'Sheet2' kopierenRange("A1:D20").Kopierziel:=Worksheets("Sheet2").Range("B10") |
Spezial einfügen
Ausgewählte Eigenschaften des Quellbereichs können mit der Option PASTESPECIAL zum Ziel kopiert werden:
| 123 | ' Füge den Bereich nur als Werte einBereich("A1:D20").KopierenWorksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues |
Hier sind die möglichen Optionen für die Option Einfügen:
| 12345678910111213 | 'Sondertypen einfügenxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlEinfügenKommentarexlPasteFormatsxlPasteFormelnxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
AutoFit-Inhalte
Die Größe der Zeilen und Spalten kann mit dem folgenden Code an den Inhalt angepasst werden:
| 12345 | ' Ändern Sie die Größe der Zeilen 1 bis 5, um sie an den Inhalt anzupassenZeilen("1:5").AutoFit'Größe der Spalten A bis B an den Inhalt anpassenSpalten("A:B").AutoFit |
Weitere Beispiele für Reichweiten
Es wird empfohlen, Macro Recorder zu verwenden, während Sie die erforderliche Aktion über die GUI ausführen. Es wird Ihnen helfen, die verschiedenen verfügbaren Optionen zu verstehen und zu verwenden.
Für jeden
Es ist einfacher, einen Bereich mit zu durchlaufen Für jeden konstruieren wie unten gezeigt:
| 123 | Für jede Zelle im Bereich ("A1: B100")'Mach etwas mit der ZelleNächste Zelle |
Bei jeder Iteration der Schleife wird eine Zelle des Bereichs der Variablen c zugewiesen und Anweisungen in der For-Schleife werden für diese Zelle ausgeführt. Die Schleife wird beendet, wenn alle Zellen verarbeitet wurden.
Sortieren
Sortieren ist eine Methode des Range-Objekts. Sie können einen Bereich sortieren, indem Sie Optionen für die Sortierung in Range.Sort angeben. Der folgende Code sortiert die Spalten A:C basierend auf dem Schlüssel in Zelle C2. Die Sortierreihenfolge kann xlAscending oder xlDescending sein. Header:= xlYes sollte verwendet werden, wenn die erste Zeile die Kopfzeile ist.
| 12 | Columns("A:C").Sort key1:=Range("C2"), _order1:=xlAufsteigend, Header:=xlYes |
Finden
Find ist auch eine Methode von Range Object. Es findet die erste Zelle, deren Inhalt den Suchkriterien entspricht, und gibt die Zelle als Range-Objekt zurück. Es kehrt zurück Nichts wenn es keine Übereinstimmung gibt.
Verwenden Nächstes finden -Methode (oder FindPrevious), um das nächste (vorherige) Vorkommen zu finden.
Der folgende Code ändert die Schriftart für alle Zellen im Bereich, die mit „John“ beginnen, in „Arial Black“:
| 12345 | Für jedes c im Bereich("A1:A100")Wenn c wie "John*" Dannc.Font.Name = "Arial Black"Ende WennNächstes c |
Der folgende Code ersetzt alle Vorkommen von „To Test“ bis „Passed“ im angegebenen Bereich:
| 12345678910 | Mit Reichweite("a1:a500")Set c = .Find("Zu Testen", LookIn:=xlValues)Wenn nicht c nichts ist, dannerste Adresse = c.AdresseTunc.Wert = "Bestanden"Setze c = .FindNext(c)Schleife, während nicht c nichts ist und c.Adresse firstaddressEnde WennEnde mit |
Beachten Sie, dass Sie einen Bereich angeben müssen, um FindNext zu verwenden. Außerdem müssen Sie eine Stoppbedingung angeben, sonst wird die Schleife für immer ausgeführt. Normalerweise wird die Adresse der ersten gefundenen Zelle in einer Variablen gespeichert und die Schleife wird gestoppt, wenn Sie diese Zelle wieder erreichen. Sie müssen auch prüfen, ob nichts gefunden wird, um die Schleife zu stoppen.
Bereichsadresse
Verwenden Sie Range.Address, um die Adresse im A1-Stil zu erhalten
| 123 | MsgBox Range("A1:D10").Adresse' oderDebug.Print Range("A1:D10").Adresse |
Verwenden Sie xlReferenceStyle (Standard ist xlA1), um die Adresse im R1C1-Stil zu erhalten
| 123 | MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)' oderDebug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) |
Dies ist nützlich, wenn Sie in Variablen gespeicherte Bereiche bearbeiten und nur für bestimmte Adressen verarbeiten möchten.
Bereich zu Array
Es ist schneller und einfacher, einen Bereich in ein Array zu übertragen und die Werte dann zu verarbeiten. Sie sollten das Array als Variant deklarieren, um zu vermeiden, dass die Größe berechnet wird, die zum Auffüllen des Bereichs im Array erforderlich ist. Die Dimensionen des Arrays sind so eingestellt, dass sie der Anzahl der Werte im Bereich entsprechen.
| 123456789 | Dim DirArray als Variante' Werte im Bereich im Array speichernDirArray = Range("a1:a5").Value' Schleife um die Werte zu verarbeitenFür jedes c in DirArrayDebug.Print cNächste |
Array zu Bereich
Nach der Verarbeitung können Sie das Array in einen Bereich zurückschreiben. Um das Array im obigen Beispiel in einen Bereich zu schreiben, müssen Sie einen Bereich angeben, dessen Größe der Anzahl der Elemente im Array entspricht.
Verwenden Sie den folgenden Code, um das Array in den Bereich D1:D5 zu schreiben:
| 123 | Range("D1:D5").Value = DirArrayRange("D1:H1").Value = Application.Transpose(DirArray) |
Bitte beachten Sie, dass Sie das Array transponieren müssen, wenn Sie es in eine Zeile schreiben.
Summenbereich
| 12 | SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))Debug.Print SumOfRange |
Sie können viele in Excel verfügbare Funktionen in Ihrem VBA-Code verwenden, indem Sie Application.WorkSheetFunction angeben. vor dem Funktionsnamen wie im obigen Beispiel.
Zählbereich
| 1234567 | ' Anzahl der Zellen mit Zahlen im Bereich zählenCountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))Debug.Print CountOfCells' Anzahl der nicht leeren Zellen im Bereich zählenCountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))Debug.Print CountOfNonBlankCells |
Geschrieben von: Vinamra Chandra
