excel-vba
Zakresy i komórki

Składnia

• Zestaw - operator używany do ustawiania odwołania do obiektu, takiego jak Zakres
• For Each - Operator przeskakiwał przez każdy element w kolekcji

Uwagi

Tworzenie zakresu

Zakres nie może zostać utworzony ani wypełniony w taki sam sposób, jak ciąg:

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

Za najlepszą praktykę uznaje się kwalifikowanie referencji , dlatego odtąd będziemy stosować to samo podejście.
Więcej informacji o tworzeniu zmiennych obiektowych (np. Zasięg) w MSDN . Więcej informacji o instrukcji Set w MSDN .

Istnieją różne sposoby utworzenia tego samego zakresu:

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

Należy zauważyć w przykładzie, że komórki (2, 1) są równoważne zakresowi („A2”). Wynika to z tego, że Cells zwraca obiekt Range.
Niektóre źródła: Chip Pearson-Cells Within Ranges ; Obiekt MSDN-Range ; John Walkenback - odnoszący się do zakresów w kodzie VBA .

Należy również pamiętać, że w każdym przypadku, gdy liczba jest używana w deklaracji zakresu, a sama liczba jest poza znakami cudzysłowu, takich jak Zakres („A” i 2), można zamienić tę liczbę na zmienną zawierającą liczba całkowita / długa. Na przykład:

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

Jeśli używasz podwójnych pętli, Komórki są lepsze:

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

Sposoby odwoływania się do pojedynczej komórki

Najprostszym sposobem odwołania się do pojedynczej komórki w bieżącym arkuszu programu Excel jest po prostu umieszczenie formy A1 jej odwołania w nawiasach kwadratowych:

[a3] = "Hello!"

Zauważ, że nawiasy kwadratowe są po prostu wygodnym cukrem syntaktycznym dla metody Evaluate obiektu Application , więc technicznie jest to identyczne z następującym kodem:

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

Możesz także wywołać metodę Cells , która pobiera wiersz i kolumnę i zwraca odwołanie do komórki.

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

Pamiętaj, że za każdym razem, gdy przekazujesz wiersz i kolumnę do Excela z VBA, wiersz jest zawsze pierwszy, a następnie kolumna, co jest mylące, ponieważ jest przeciwieństwem zwykłej notacji A1 której kolumna pojawia się pierwsza.

W obu tych przykładach nie określono arkusza roboczego, więc program Excel użyje aktywnego arkusza (arkusza znajdującego się z przodu w interfejsie użytkownika). Możesz jawnie określić aktywny arkusz:

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

Lub możesz podać nazwę konkretnego arkusza:

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

Istnieje wiele różnych metod, które można wykorzystać do przejścia z jednego zakresu do drugiego. Na przykład można zastosować metodę Rows aby dostać się do poszczególnych wierszy dowolnego zakresu, a metodę Cells można użyć, aby dostać się do poszczególnych komórek wiersza lub kolumny, więc poniższy kod odnosi się do komórki C1:

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

Zapisanie odwołania do komórki w zmiennej

Aby zapisać odwołanie do komórki w zmiennej, musisz użyć składni Set , na przykład:

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

później...

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

Dlaczego wymagane jest słowo kluczowe Set ? Set mówi Visual Basicowi, że wartość po prawej stronie = ma być obiektem.

Właściwość przesunięcia

• Przesunięcie (wiersze, kolumny) - operator służy do statycznego odniesienia do innego punktu z bieżącej komórki. Często używany w pętlach. Należy rozumieć, że liczby dodatnie w sekcji wierszy przesuwają się w prawo, a gdzie ujemne - w lewo. W sekcji kolumn dodatnie przesuwają się w dół, a negatywne w górę.

to znaczy

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

Ten kod wybiera B2, umieszcza tam nowy ciąg, a następnie przenosi ten ciąg z powrotem do A1, a następnie usuwa B2.

Jak transponować zakresy (od poziomego do pionowego i odwrotnie)

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

Uwaga: Copy / PasteSpecial ma również opcję Wklej transponowanie, która aktualizuje również formuły transponowanych komórek.