excel-vba
Bereiche und Zellen

Syntax

• Set - Der Operator, mit dem ein Verweis auf ein Objekt festgelegt wird, beispielsweise ein Bereich
• For Each - Der Operator, mit dem alle Elemente in einer Sammlung durchlaufen wurden

Bemerkungen

Erstellen eines Bereichs

Ein Bereich kann nicht auf die gleiche Weise erstellt oder gefüllt werden, wie eine Zeichenfolge:

Sub RangeTest()
    Dim s As String
    Dim r As Range 'Specific Type of Object, with members like Address, WrapText, AutoFill, etc.
    
    ' This is how we fill a String:
    s = "Hello World!"

    ' But we cannot do this for a Range:
    r = Range("A1") '//Run. Err.:  91 Object variable or With block variable not set//

    ' We have to use the Object approach, using keyword Set:
    Set r = Range("A1")
End Sub

Es gilt als bewährte Methode, Ihre Referenzen zu qualifizieren. Daher verwenden wir ab jetzt denselben Ansatz.
Weitere Informationen zum Erstellen von Objektvariablen (z. B. Range) in MSDN . Weitere Informationen zum Set-Statement in MSDN .

Es gibt verschiedene Möglichkeiten, denselben Bereich zu erstellen:

Sub SetRangeVariable()
    Dim ws As Worksheet
    Dim r As Range

    Set ws = ThisWorkbook.Worksheets(1) ' The first Worksheet in Workbook with this code in it
    
    ' These are all equivalent:
    Set r = ws.Range("A2")
    Set r = ws.Range("A" & 2)
    Set r = ws.Cells(2, 1) ' The cell in row number 2, column number 1
    Set r = ws.[A2] 'Shorthand notation of Range.
    Set r = Range("NamedRangeInA2") 'If the cell A2 is named NamedRangeInA2. Note, that this is Sheet independent.
    Set r = ws.Range("A1").Offset(1, 0) ' The cell that is 1 row and 0 columns away from A1
    Set r = ws.Range("A1").Cells(2,1) ' Similar to Offset. You can "go outside" the original Range.

    Set r = ws.Range("A1:A5").Cells(2) 'Second cell in bigger Range.
    Set r = ws.Range("A1:A5").Item(2) 'Second cell in bigger Range.
    Set r = ws.Range("A1:A5")(2) 'Second cell in bigger Range.
End Sub

Beachten Sie im Beispiel, dass Zellen (2, 1) dem Bereich ("A2") entspricht. Dies liegt daran, dass Cells ein Range-Objekt zurückgibt.
Einige Quellen: Chip Pearson-Cells in Ranges ; MSDN-Bereichsobjekt ; John Walkenback-Verweise auf Bereiche in Ihrem VBA-Code .

Beachten Sie außerdem, dass Sie in allen Fällen, in denen eine Nummer in der Deklaration des Bereichs verwendet wird und die Nummer selbst außerhalb von Anführungszeichen steht (z. B. Range ("A" & 2)), diese Nummer gegen eine Variable austauschen können, die ein Ganzzahl / lang. Zum Beispiel:

Sub RangeIteration()
    Dim wb As Workbook, ws As Worksheet
    Dim r As Range

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)

    For i = 1 To 10
        Set r = ws.Range("A" & i)
        ' When i = 1, the result will be Range("A1")
        ' When i = 2, the result will be Range("A2")
        ' etc.
        ' Proof:
        Debug.Print r.Address
    Next i
End Sub

Wenn Sie Doppelschleifen verwenden, ist Cells besser:

Sub RangeIteration2()
    Dim wb As Workbook, ws As Worksheet
    Dim r As Range

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets(1)

    For i = 1 To 10
        For j = 1 To 10
            Set r = ws.Cells(i, j)
            ' When i = 1 and j = 1, the result will be Range("A1")
            ' When i = 2 and j = 1, the result will be Range("A2")
            ' When i = 1 and j = 2, the result will be Range("B1")
            ' etc.
            ' Proof:
            Debug.Print r.Address
        Next j
    Next i
End Sub

Möglichkeiten, sich auf eine einzelne Zelle zu beziehen

Der einfachste Weg, auf eine einzelne Zelle im aktuellen Excel-Arbeitsblatt zu verweisen, besteht einfach darin, die A1-Form ihrer Referenz in eckige Klammern zu setzen:

[a3] = "Hello!"

Beachten Sie, dass eckige Klammern nur zweckmäßiger syntaktischer Zucker für die Evaluate Methode des Application Objekts sind. Technisch ist dies mit dem folgenden Code identisch:

Application.Evaluate("a3") = "Hello!"

Sie können auch die Cells Methode aufrufen, die eine Zeile und eine Spalte übernimmt und eine Zellreferenz zurückgibt.

Cells(3, 1).Formula = "=A1+A2"

Denken Sie daran, dass immer, wenn Sie von VBA aus eine Zeile und eine Spalte an Excel übergeben, die Zeile immer zuerst steht, gefolgt von der Spalte. Dies ist verwirrend, da die Spalte zuerst in der üblichen A1 Notation steht.

In beiden Beispielen haben wir kein Arbeitsblatt angegeben, daher verwendet Excel das aktive Blatt (das Blatt, das sich in der Benutzeroberfläche befindet). Sie können das aktive Blatt explizit angeben:

ActiveSheet.Cells(3, 1).Formula = "=SUM(A1:A2)"

Oder Sie können den Namen eines bestimmten Blattes angeben:

Sheets("Sheet2").Cells(3, 1).Formula = "=SUM(A1:A2)"

Es gibt eine Vielzahl von Methoden, um von einem Bereich zum anderen zu gelangen. Beispielsweise kann die Rows Methode verwendet werden, um zu den einzelnen Zeilen eines beliebigen Bereichs zu gelangen, und die Cells Methode kann verwendet werden, um zu einzelnen Zellen einer Zeile oder Spalte zu gelangen. Daher bezieht sich der folgende Code auf Zelle C1:

ActiveSheet.Rows(1).Cells(3).Formula = "hi!"

Speichern eines Verweises auf eine Zelle in einer Variablen

Um einen Verweis auf eine Zelle in einer Variablen zu speichern, müssen Sie die Set Syntax verwenden. Beispiel:

Dim R as Range
Set R = ActiveSheet.Cells(3, 1)

später...

R.Font.Color = RGB(255, 0, 0)

Warum ist das Set Schlüsselwort erforderlich? Set teilt Visual Basic mit, dass der Wert auf der rechten Seite von = ein Objekt sein soll.

Offset-Eigenschaft

• Offset (Zeilen, Spalten) - Der Operator, mit dem ein anderer Punkt der aktuellen Zelle statisch referenziert wird. Wird häufig in Schleifen verwendet. Es versteht sich, dass sich positive Zahlen im Zeilenabschnitt nach rechts bewegen, während Negative sich nach links bewegen. Bei den Spalten bewegen sich die Positiven nach unten und die Negativen nach oben.

dh

Private Sub this()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Select
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(1, 1).Value = "New Value"
    ActiveCell.Offset(-1, -1).Value = ActiveCell.Value
    ActiveCell.Value = vbNullString
End Sub

Dieser Code wählt B2 aus, fügt dort eine neue Zeichenfolge ein und verschiebt die Zeichenfolge anschließend wieder nach A1 und löscht anschließend B2.

Transponieren von Bereichen (horizontal in vertikal und umgekehrt)

Sub TransposeRangeValues()
    Dim TmpArray() As Variant, FromRange as Range, ToRange as Range

    set FromRange = Sheets("Sheet1").Range("a1:a12")         'Worksheets(1).Range("a1:p1")
    set ToRange = ThisWorkbook.Sheets("Sheet1").Range("a1")  'ThisWorkbook.Sheets("Sheet1").Range("a1")

    TmpArray = Application.Transpose(FromRange.Value)
    FromRange.Clear
    ToRange.Resize(FromRange.Columns.Count,FromRange.Rows.Count).Value2 = TmpArray
End Sub

Hinweis: Copy / PasteSpecial enthält auch die Option Transpose einfügen, mit der auch die Formeln der transponierten Zellen aktualisiert werden.